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

-- Exercise 1
SELECT *  FROM products
WHERE list_price > (SELECT AVG(list_price) FROM products);

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

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

-- Exercise 4
SELECT c.email_address,
       Max(order_cost) AS LargestOrder
FROM   customers c
       INNER JOIN orders o
               ON c.customer_id = o.customer_id
       JOIN (SELECT orders.order_id,
                    item_total * quantity AS OrderCost
             FROM   orders,
                    order_items
             WHERE  order_items.order_id = orders.order_id)largest
         ON largest.order_id = o.order_id
GROUP  BY c.email_address; 

-- Exercise 4 Part 2


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