SQL project 1

1. Shopping cart content for the customer with word David in their full name, sorted with the wish list at the end CUSTOMER PRODUCT NAME QUANTITY UNIT PRICE IN WISH LIST -------------------- -------------------- ---------------------- ---------------------- ------------ Bill G. Davidson Broadband Router 3 75 Bill G. Davidson Harry Potter 1 50 Bill G. Davidson Men's Jacket 1 99 Bill G. Davidson Lone Wolf 2 17 N Bill G. Davidson Laser Printer 2 250 N David S. Harrington Speaker System 1 65 David S. Harrington Student Backpack 4 35 David S. Harrington Binocular 2 60 David S. Harrington A Raisin in the Sun 1 11 Y 9 rows selected 2. List of ALL customers and the total price of their shopping carts, excluding the wish list CUSTOMER CART VALUE [$] -------------------- ---------------------- John M. Newton 130 Bob R. Hume 243 Jeff A. Newman Bill G. Davidson 908 David S. Harrington 325 3. List ALL shopping carts in descending order of the number of items, excluding the wish list CUSTOMER NUMBER OF ITEMS -------------------- ---------------------- Bill G. Davidson 5 Bob R. Hume 3 David S. Harrington 3 John M. Newton 2 Jeff A. Newman 0 4. List ALL products and the number of shopping carts they are in (if any) PRODUCT NAME SHOPPING CART OCCURENCES -------------------- ------------------------ USB Flash Drive 1 Harry Potter 1 Laser Printer 1 Laptop Mouse 1 iPhone Development 1 Broadband Router 1 A Raisin in the Sun 2 Swiss Army Knife 0 Student Backpack 1 Binocular 1 Men's Jacket 1 The Lost Years 1 Speaker System 1 Lone Wolf 1 Flash Light 2 15 rows selected 5. All products with a 'Brand' feature in Outdoors and Electronics categories PRODUCT BRAND CATEGORY -------------------- -------------------------------------------------- -------------------- Broadband Router Linksys Electronics Speaker System Logitech Electronics Laser Printer HP Electronics Laptop Mouse Logitech Electronics USB Flash Drive Kingston Electronics Binocular Celestron Outdoors Men's Jacket Columbia Outdoors Student Backpack Jan Sport Outdoors 8 rows selected 6. Create a view of unshipped goods, with individual quantities and prices, for each customer, and select all the rows from it. CREATE VIEW succeeded. CUSTID PRICE QUANTITY ---------------------- ---------------------- ---------------------- 3 35 2 5 25 3 5 65 2 7. Using the view created before, display all customers who have unshipped orders, with the total value of the unshipped orders per customer CUSTOMER VALUE OF UNSHIPPED GOODS [$] -------------------- ---------------------------- Bob R. Hume 70 Bill G. Davidson 205 8. The first three most sold products of category 'Books' (don't count unshipped orders) PRODUCT NUMBER OF TIMES SOLD -------------------- ---------------------- Harry Potter 2 A Raisin in the Sun 1 Lone Wolf 1 9. Number of HP products sold during the last month (from current date) HP PRODUCTS SOLD LAST MONTH --------------------------- 1 10. Use a correlated query to find the names of the customers who have more than 2 copies of the same item in their shopping cart CUSTOMERS WITH MORE COPIES -------------------------- Bill G. Davidson David S. Harrington 11. List of shopping carts with the cheapeast and the most expensive product in each CUSTOMER CHEAPEST PRODUCT PRICE MOST EXPENSIVE PRODUCT PRICE -------------------- -------------------- ---------------------- ---------------------- ---------------------- Bill G. Davidson Lone Wolf 17 Laser Printer 250 David S. Harrington A Raisin in the Sun 11 Speaker System 65 Bob R. Hume A Raisin in the Sun 11 Flash Light 80 John M. Newton The Lost Years 15 Flash Light 80