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

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

-- Exercise 2
SELECT
	category_name,
    COUNT(product_id) AS product_count,
    MAX(list_price) AS max_price
FROM
	categories c JOIN products p
		ON c.category_id = p.category_id
GROUP BY
	c.category_id
ORDER BY
	product_count DESC;

-- Exercise 3
SELECT
	email_address,
    SUM(item_price * quantity) AS 'total',
    SUM(discount_amount * quantity) AS 'total discount'
FROM
	customers c
	JOIN orders o
		ON c.customer_id = o.customer_id
	JOIN order_items oi
		ON o.order_id = oi.order_id
GROUP BY
	email_address
ORDER BY
	total DESC;

-- Exercise 4
SELECT
	email_address,
    COUNT(o.order_id) AS number_orders,
    (item_price - discount_amount) * quantity AS total_amount
FROM
	customers c JOIN orders o
		ON c.customer_id = o.customer_id
				JOIN order_items oi
		ON o.order_id = oi.order_id
GROUP BY
	c.customer_id
HAVING
	number_orders > 1
ORDER BY
	total_amount DESC;
    
-- Exercise 5
SELECT
	email_address,
    COUNT(o.order_id) AS number_orders,
    (item_price - discount_amount) * quantity AS total_amount
FROM
	customers c JOIN orders o
		ON c.customer_id = o.customer_id
				JOIN order_items oi
		ON o.order_id = oi.order_id
WHERE
	item_price > 400
GROUP BY
	email_address
HAVING
	COUNT(o.order_id) > 1
ORDER BY
	total_amount DESC;

-- Exercise 6
SELECT
	product_name,
    SUM((item_price - discount_amount) * quantity) AS 'product total'
FROM
	products p JOIN order_items oi
		ON p.product_id = oi.product_id
GROUP BY
	product_name WITH ROLLUP;

-- Exercise 7
SELECT
	email_address,
    COUNT(DISTINCT oi.product_id) AS 'number of products'
FROM
	customers c
		JOIN orders o
			ON c.customer_id = o.customer_id
		JOIN order_items oi
			ON o.order_id = oi.order_id
GROUP BY
	email_address
HAVING
	COUNT(DISTINCT oi.product_id) > 1
ORDER BY
	email_address;