SQL homework 3

1. Shopping cart content for the customer with word David in their full name, sorted with the wish list at the end SELECT CUSTOMERS.Name AS CUSTOMER, PRODUCTS.Name AS PRODUCT NAME, SHOPPING_CART_ITEMS.Quantity AS QUANTITY, PRODUCTS.Price AS UNIT PRICE, SHOPPING_CART_ITEMS.WishList AS IN WISH LIST FROM SHOPPING_CART_ITEMS JOIN CUSTOMERS, PRODUCTS JOIN SHOPPING_CART_ITEMS ON (PRODUCTS.ID=SHOPPING_CART_ITEMS.ProdID), (SHOPPING_CART_ITEMS.CustID=CUSTOMERS.ID) WHERE CUSTOMERS.Name LIKE'%David%' ORDER BY SHOPPING_CART_ITEMS.WishList ; 2.List of ALL customers and the total price of their shopping carts, excluding the wish list SELECT CUSTOMERS.Name AS CUSTOMER, (SHOPPING_CART_ITEMS.Quantity)*(PRODUCTS.Price) AS CART VALUE [$] FROM SHOPPING_CART_ITEMS JOIN CUSTOMERS, PRODUCTS JOIN SHOPPING_CART_ITEMS ON (PRODUCTS.ID=SHOPPING_CART_ITEMS.ProdID), (SHOPPING_CART_ITEMS.CustID=CUSTOMERS.ID); 3.List ALL shopping carts in descending order of the number of items, excluding the wish list SELECT CUSTOMERS.Name AS CUSTOMER, COUNT(SHOPPING_CART_ITEMS.ProdID) AS NUMBER OF ITEMS FROM CUSTOMERS JOIN SHOPPING_CART_ITEMS, ON (CUSTOMERS.ID = SHOPPING_CART_ITEMS.CustID) GROUP BY CustID ORDER BY COUNT(SHOPPING_CART_ITEMS.ProdID) DESC; 4. List ALL products and the number of shopping carts they are in (if any) SELECT PRODUCTS.Name AS PRODUCT NAME, COUNT(SHOPPING_CART_ITEMS.ProdID) AS SHOPPING CART OCCURENCES FROM PRODUCTS JOIN SHOPPING_CART_ITEMS ON (PRODUCTS.ID=SHOPPING_CART_ITEMS.ProdID); 5. All products with a 'Brand' feature in Outdoors and Electronics categories SELECT PRODUCTS.Name AS PRODUCT, PRODUCT_FEATURES.Value AS BRAND, CATEGORIES.Name AS CATEGORY FROM PRODUCTS JOIN PRODUCT_FEATURES, PRODUCTS JOIN CATEGORIES ON (PRODUCTS.ID=PRODUCT_FEATURES.ProdID), (PRODUCT.CatID=CATEGORIES.ID) WHERE PRODUCT_FEATURES.Name ='Brand' AND CATEGORIES.Name= 'Electronics' OR 'Outdoors'; 6. CREATE VIEW UnshippedGoods AS SELECT ORDERS.CustID, ORDER_ITEMS.Quantity, PRODUCTS.Price AS PRICE FROM ORDERS JOIN ORDER_ITEMS, ORDERS JOINS CUSTOMERS ON ( ORDERS.CustID= CUSTOMERS.ID), (ORDER_ITEMS.OrderID= ORDERS.ID) WHERE ORDER_ITEMS. DateShipped = 'NULL'; 7. Using the view created before, display all customers who have unshipped orders, with the total value of the unshipped orders per customer SELECT CustID AS CUSTOMER, (Quantity)*(PRIZE) AS VALUE OF UNSHIPPED GOODS [$] FROM UnshippedGoods; 8. The first three most sold products of category 'Books' (don't count unshipped orders) SELECT PRODUCTS.Name AS PRODUCT, COUNT(ORDER_ITEMS.OrderID) AS NUMBER OF TIMES SOLD FROM PRODUCTS JOIN ORDER_ITEMS ON (PRODUCTS.ID=ORDER_ITEMS.ProdID), (PRODUCTS.CatID= CATEGORIES.ID) ORDER BY NUMBER OF TIMES SOLD DESC WHERE ROWNUM<=3 AND CATEGORIES.Name = 'Books'; 9. Number of HP products sold during the last month (from current date) SELECT COUNT( ORDER_ITEMS.ProdID) AS HP PRODUCTS SOLD LAST MONTH FROM ORDER_ITEMS JOIN PRODUCT_FEATURES WHERE PRODUCT_FEATURES.Value= 'HP'; 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 SELECT CUSTOMERS.Name FROM CUSTOMERS JOIN SHOPPING_CART_ITEMS ON (CUSTOMERS.ID=SHOPPING_CART_ITEMS.CustID) WHERE ProdID = (SELECT COUNT(SHOPPING_CART_ITEMS.ProdID) FROM SHOPPING_CART_ITEMS WHERE COUNT(SHOPPING_CART_ITEMS.ProdID)>2 GROUP BY CustID); 11. List of shopping carts with the cheapeast and the most expensive product in each SELECT CUSTOMERS.Name, PRODUCS.Name as CHEAPEST PRODUCT , MIN(PRODUCTS.Price) AS PRIZE, PRODUCTS.Name AS MOST EXPENSIVE PRODUCT, MAX(PRODUCTS.Prize) AS PRIZE FROM CUSTOMERS JOIN SHOPPING_CART_ITEMS, SHOPPING_CART_ITEMS JOIN PRODUCTS ON (CUSTOMERS.ID=SHOPPING_CART_ITEMS.CustID), (PRODUCTS.ID=SHOPPING_CART_ITEMS.ProdID) GROUP BY CUSTOMERS.CustID;