Re: SQL question

Something like:

create view CustomerSummary as select
  customer.name, count(video.video_id), sum(orderline.quantity*video.price)   from customer, order , orderline, video   where customer.cust_id = order.cust_id (all the orders for a customer)

   and order.order_id = orderline.order_id (all orderlines for an order)

    and orderline.video_id = video.video_id (all video's for an orderline)

strider wrote:

>Hi, not sure if this is the right place to post a question on SQL.
>Apologies if it isnt. I have an upcoming exam on databases and Ive
>been looking at some past papers
>One of the questions that Im having trouble with is:
>
>You have these tables:
>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)
>
>Create a view called CustomerSummary which shows the customer name
>along with the total number of videos ordered and total order value
>for each customer
>
>I know I need joins and a SUM and a COUNT somewhere but im getting
>confused with it all, so I would be very grateful if someone could
>give the SQL statement and an explanation of how it works
>
>Thanks v. much
>
>

-- 
Anton Versteeg
IBM Netherlands