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

-- Exercise 1
SELECT DISTINCT category_name
FROM categories 
WHERE category_id 
IN (select product_id FROM products)
ORDER BY category_name
LIMIT 3;

-- Exercise 2
SELECT product_name, list_price
FROM products
GROUP BY product_name 
HAVING AVG(list_price) > (SELECT AVG(list_price) FROM products)
ORDER BY list_price DESC;

-- Exercise 3
SELECT category_name
FROM categories c
WHERE NOT exists
(SELECT category_name, category_id 
FROM products p
WHERE c.category_id= p.category_id);


-- Exercise 4
SELECT email_address, os.order_id, SUM(item_price - discount_amount) * quantity AS 'order_total'
FROM customers c
JOIN orders os
USING (customer_id)
JOIN order_items oi
USING (order_id)
GROUP BY email_address, os.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 p1.product_name, p1.discount_percent
FROM products p1
WHERE p1.discount_percent NOT IN
    (SELECT p2.discount_Percent
     FROM Products p2
     WHERE p1.product_name <> p2.product_name)
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);