SQL

1) Write a query that returns list of items each customer has purchased. Return CustID, OrderID, Quantity, Title, and UnitPrice. Order the list by custID and then OrderID

First 5 Rows: ----------------------------------------------------------------- 1 19 1 On The Road With Burt Ruggles 17.50 1 479 1 More Songs About Structures and Comestibles 17.95 1 479 2 Umami In Concert 17.95 1 824 2 Rude Noises 13.00 1 824 1 No Rest For The Weary 16.95

*/

2) Write a query that returns list of customers and how much they have spent in orders and how many orders they have. Return the CustID, a field called TotalAmountPaid, and a field called TotalOrders. Order the list by the CustID.

First 5 Rows: ------------- 1 114.30 3 2 76.90 4 3 34.90 2 4 13.00 1 5 17.95 1

3) Write a query that returns a list of Customers that have not ordered

=========================================================================================

ProductOrders.sql

Customer table

Errors: ErrorID, Message, Number, Line, Procedure, Severity, State

OrderDetails:

Items:

Orders:

CustID Cust State Cust Zip OH 43221 Cust TotalOrders 0 1 1 Cust Last Name Blanca Randall Millerton 2 Cust First Name Korah Yash Johnathon Mikayla 2 CustAddress 1555 W Lane Ave 11 E Rancho Madera Rd 60 Madison Ave 2021 K Street Nw CustCity Columbus Madison New York Washington WI CustPhone 6145554435 2095551205 2125554800 2025555561 CustFax 6145553928 2095552262 NULL NULL 0 3 53707 10010 20006 3 NY 0 4 4 Damion DC 0 Quantity 1 1 OrderID 381 601 442 1 ItemID 1 9 1 1 9 2 3 4 5 1 1 1 1 523 630 5 1 778 1 1 1 1 1 7 8 693 118 264 10 1 1 1 9 7 1 Title ItemID 1 1 2 3 4 NONO Artist Umami The Ubemerds No Rest For The Weary No Rest For The Weary Burt Ruggles Sewed the Vest Pocket Jess & Odie Burt Ruggles Umami Unit Price 17.95 13.00 16.95 17.95 17.50 16.95 13.00 Umami In Concert Race Car Sounds No Rest For The Weary More Songs About Structures and Comestibles On The Road With Burt Ruggles No Fixed Address Rude Noises Burt Ruggles: An Intimate Portrait Zone Out With Umami 5 6 7 17.95 8 9 9 16.95 OrderID 19 1 2 29 32 45 3 4 5 6 CustID Order Date Shipped Date 1 2014-06-23 00:00:00.000 : 2014-06-28 00:00:00.000 : 8 2014-07-05 00:00:00.000 2014-07-11 00:00:00.000 11 2014-07-10 00:00:00.000 2014-07-13 00:00:00.000 2 2014-07-25 00:00:00.000 2014-07-30 00:00:00.000 10 2014-08-28 00:00:00.000 2014-09-07 00:00:00.000 22 2014-09-20 00:00:00.000 2014-09-22 00:00:00.000 20 2014-09-29 00:00:00.000 2014-10-02 00:00:00.000 3 2014-10-24 00:00:00.000 2014-10-28 00:00:00.000 17 2014-11-21 00:00:00.000 2014-11-29 00:00:00.000 5 70 89 7 97 8 118 9 144 CustID Cust State Cust Zip OH 43221 Cust TotalOrders 0 1 1 Cust Last Name Blanca Randall Millerton 2 Cust First Name Korah Yash Johnathon Mikayla 2 CustAddress 1555 W Lane Ave 11 E Rancho Madera Rd 60 Madison Ave 2021 K Street Nw CustCity Columbus Madison New York Washington WI CustPhone 6145554435 2095551205 2125554800 2025555561 CustFax 6145553928 2095552262 NULL NULL 0 3 53707 10010 20006 3 NY 0 4 4 Damion DC 0 Quantity 1 1 OrderID 381 601 442 1 ItemID 1 9 1 1 9 2 3 4 5 1 1 1 1 523 630 5 1 778 1 1 1 1 1 7 8 693 118 264 10 1 1 1 9 7 1 Title ItemID 1 1 2 3 4 NONO Artist Umami The Ubemerds No Rest For The Weary No Rest For The Weary Burt Ruggles Sewed the Vest Pocket Jess & Odie Burt Ruggles Umami Unit Price 17.95 13.00 16.95 17.95 17.50 16.95 13.00 Umami In Concert Race Car Sounds No Rest For The Weary More Songs About Structures and Comestibles On The Road With Burt Ruggles No Fixed Address Rude Noises Burt Ruggles: An Intimate Portrait Zone Out With Umami 5 6 7 17.95 8 9 9 16.95 OrderID 19 1 2 29 32 45 3 4 5 6 CustID Order Date Shipped Date 1 2014-06-23 00:00:00.000 : 2014-06-28 00:00:00.000 : 8 2014-07-05 00:00:00.000 2014-07-11 00:00:00.000 11 2014-07-10 00:00:00.000 2014-07-13 00:00:00.000 2 2014-07-25 00:00:00.000 2014-07-30 00:00:00.000 10 2014-08-28 00:00:00.000 2014-09-07 00:00:00.000 22 2014-09-20 00:00:00.000 2014-09-22 00:00:00.000 20 2014-09-29 00:00:00.000 2014-10-02 00:00:00.000 3 2014-10-24 00:00:00.000 2014-10-28 00:00:00.000 17 2014-11-21 00:00:00.000 2014-11-29 00:00:00.000 5 70 89 7 97 8 118 9 144