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

-- Exercise 1
SELECT COUNT(order_id) AS order_count, SUM(tax_amount) AS tax_total
FROM orders

-- Exercise 2
SELECT category_name, COUNT(product_id) AS product_count, MAX(list_price) AS most_expensive_product
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 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 item_price_total DESC

-- Exercise 4
SELECT c.email_address, COUNT(i.order_id) AS order_count, SUM(item_price - discount_amount) * quantity AS order_total
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


-- Exercise 6


-- Exercise 7