Creating a view SQL

Question

These are the tables I have to work with.

customer (cust_id, name, city, postcode)

order (order_id, cust_id, date)

orderline (order_id, video_id, quantity)

video (video_id, description, price, category_id)

category (category_id, description)

I need to:

Create a view named CustomerSummary which shows the customer name along with the total number of videos ordered and total order value for each customer.

order is a reserved word. Don't use it as an identifier, even if it appears to work. — wildplasser
– wildplasser, Commented May 12, 2013 at 10:06

pratik garg · Accepted Answer · 2013-05-12 10:32:33Z

1

you have to join three tables for required output as below -

    SELECT c.Cust_Id,
         c.NAME,
         COUNT(DISTINCT o.Order_Id) Num_Of_Order,
         COUNT(*) Num_Of_Video
    FROM Customer c, Order_Table o, Orderline Ol
   WHERE c.Cust_Id = o.Cust_Id
     AND o.Order_Id = Ol.Order_Id
   GROUP BY c.Cust_Id, c.NAME

now for this query you need to create view - Just add Create or Replace View CustomerSummary as before this query to create view with name CustomerSummary

answered May 12, 2013 at 10:32

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a view SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related