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),
    MAX(list_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,
    SUM(discount_amount) * quantity
FROM orders ord  JOIN order_items o
	ON o.order_id = ord.order_id
    JOIN customers c
    ON ord.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY item_price DESC;

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



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

-- oef niet juist, 1 bedrag klopt niet. Ik vind de fout niet --
-- Exercise 6 --
SELECT
	product_name,
    SUM(item_price - discount_amount) * quantity AS 'Total Amount'
FROM products p JOIN order_items ord 
	ON p.product_id = ord.product_id
GROUP BY product_name WITH ROLLUP;

-- Exercise 7 --
SELECT 
	email_address,
    COUNT(product_id) AS number_of_products
FROM customers c JOIN orders o
	ON c.customer_id = o.customer_id
JOIN order_items ord 
	ON o.order_id = ord.order_id
GROUP BY email_address
HAVING COUNT(o.order_id) > 1;