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

-- Exercise 1

SELECT category_name
FROM categories
WHERE category_id IN (SELECT DISTINCT category_id
						FROM products)
ORDER BY category_name;

-- Exercise 2

SELECT product_name,
	list_price
FROM products
WHERE list_price > (SELECT AVG(list_price)
					FROM products)
ORDER BY list_price DESC;

-- Exercise 3

SELECT category_name
FROM categories
WHERE NOT EXISTS (SELECT *
					FROM products
                    WHERE category_id = categories.category_id);

-- Exercise 4

SELECT email_address,
	i.order_id,
	SUM((i.item_price - i.discount_amount) * quantity) AS 'order_total'
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, order_id;

-- Exercise 4 Part 2

SELECT email_address,
	MAX(order_total)
FROM (SELECT email_address,
	i.order_id,
	SUM((i.item_price - i.discount_amount) * quantity) AS 'order_total'
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, order_id) f
GROUP BY email_address;

-- Exercise 5

SELECT product_name,
	discount_percent
FROM products
WHERE discount_percent NOT IN (SELECT
						discount_percent
					FROM products
                    GROUP BY discount_percent
                    HAVING COUNT(discount_percent) > 1)
ORDER BY product_name;

-- Exercise 6

SELECT email_address,
	order_id,
	order_date
FROM customers c JOIN orders o
	ON c.customer_id = o.customer_id
WHERE order_date = (SELECT MIN(order_date)
					FROM orders
					WHERE customer_id = o.customer_id);