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

-- Exercise 1
SELECT COUNT(*) AS orders, SUM(tax_amount) AS tax
FROM orders;

-- Exercise 2
SELECT cat.category_name, list_price, COUNT(*) AS product_count
FROM categories cat
JOIN products pro ON cat.category_id = pro.category_id
GROUP BY category_name
ORDER BY product_count DESC
LIMIT 1;

-- Exercise 3
SELECT email_address, SUM(item_price) * quantity AS sum_price, SUM(discount_amount) * quantity AS sum_discount
FROM customers cus
JOIN orders ord ON ord.customer_id = cus.customer_id
JOIN order_items oi ON oi.order_id = ord.order_id
GROUP BY email_address
ORDER BY sum_price DESC;

-- Exercise 4
SELECT email_address, COUNT(*) AS order_count, (item_price - discount_amount) * quantity AS total_amount
FROM customers cus
JOIN orders ord ON cus.customer_id = ord.customer_id
JOIN order_items oid ON ord.customer_id = oid.order_id 
GROUP BY email_address HAVING order_count > 1
ORDER BY total_amount DESC;

-- Exercise 5
???

-- Exercise 6
???

-- Exercise 7
???