12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273 |
- -- Exercise 1
-
- SELECT COUNT(order_id),
- SUM(tax_amount)
- FROM orders;
-
- -- Exercise 2
-
- SELECT category_name,
- COUNT(product_id) AS count,
- MAX(list_price) AS max_price
- FROM categories c JOIN products p
- ON c.category_id = p.category_id
- GROUP BY category_name
- ORDER BY category_name DESC;
-
- -- Exercise 3
-
- SELECT c.email_address,
- SUM(item_price) * quantity,
- SUM(discount_amount) * quantity
- FROM orders o JOIN order_items i
- ON o.order_id = i.order_id
- JOIN customers c
- ON o.customer_id = c.customer_id
- GROUP BY c.customer_id
- ORDER BY item_price DESC;
-
- -- Exercise 4
-
- SELECT c.email_address,
- COUNT(i.order_id),
- SUM(item_price - discount_amount) * quantity AS 'total_amount'
- FROM orders o JOIN order_items i
- ON i.order_id = o.order_id
- JOIN customers c
- ON o.customer_id = c.customer_id
- GROUP BY email_address
- HAVING COUNT(i.order_id) > 1
- ORDER BY SUM(item_price - discount_amount) * COUNT(i.order_id) DESC;
-
- -- Exercise 5
-
- SELECT c.email_address,
- COUNT(i.order_id),
- SUM(item_price - discount_amount) * quantity AS 'total_amount'
- FROM orders o JOIN order_items i
- ON i.order_id = o.order_id
- JOIN customers c
- ON o.customer_id = c.customer_id
- WHERE i.item_price > 400
- GROUP BY email_address
- HAVING COUNT(i.order_id) > 1
- ORDER BY SUM(item_price - discount_amount) * COUNT(i.order_id) DESC;
-
- -- Exercise 6
-
- SELECT product_name,
- SUM(item_price - discount_amount) * quantity AS 'total_amount'
- FROM products p JOIN order_items i
- ON p.product_id = i.product_id
- GROUP BY product_name WITH ROLLUP;
-
- -- Exercise 7
-
- SELECT email_address,
- COUNT(product_id) AS 'distinct_products'
- FROM customers c JOIN orders o
- ON c.customer_id = o.customer_id
- JOIN order_items i
- ON o.order_id = i.order_id
- GROUP BY email_address
- HAVING COUNT(o.order_id) > 1;
|