my-guitar-shop-database-exersices/How-to-code-summary-queries.sql

-- 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 email_address, SUM(item_price) * quantity AS item_price_total ,
       SUM(discount_amount) * quantity AS discount_amount_total
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 SUM(item_price) * quantity DESC;

-- Exercise 4
SELECT c.email_address,	COUNT(oi.order_id) AS 'order_count',
	   SUM(item_price - discount_amount) * quantity AS 'order_total'
FROM orders ord JOIN order_items oi ON o.order_id = oi.order_id
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY email_address
HAVING COUNT(oi.order_id) > 1
ORDER BY SUM(item_price - discount_amount) * COUNT(i.order_id) DESC;

-- Exercise 5
SELECT email_address, COUNT(o.order_id) AS 'order_count',
       SUM(item_price - discount_amount) * quantity AS 'order_total'
FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
              JOIN customers c ON o.customer_id = c.customer_id
WHERE oi.item_price > 400
GROUP BY email_address
HAVING COUNT(oi.order_id) > 1
ORDER BY SUM(item_price - discount_amount) * COUNT(oi.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;