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);