guitar-shop-database/How_to_code_subqueries.sql

/*1*/
SELECT 
	DISTINCT category_name
FROM 
	categories c 
WHERE 
	category_id 
	IN (select product_id FROM products)
ORDER BY 
	category_name
LIMIT 3;

/*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;

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

/*4 part 1*/ /*kan het dat in bedrag anders is*/
SELECT
	email_address,
    o.order_id,
    SUM(item_price - discount_amount) * quantity AS 'order_total'
FROM 
    customers c
JOIN 
	orders o
USING 
	(customer_id)
JOIN
	order_items oi
USING 
	(order_id)
GROUP BY
	email_address,
    o.order_id;
    
/*4 part 2*/ /*fout*/
SELECT
	email_address,
    MAX(item_price)
FROM
	(SELECT
	email_address,
    o.order_id,
    SUM(item_price - discount_amount) * quantity AS 'order_total'
FROM 
    customers c
JOIN 
	orders o
USING 
	(customer_id)
JOIN
	order_items oi
USING 
	(order_id)
GROUP BY
	email_address,
    o.order_id) v
    JOIN order_items
    using (order_id);
    
/*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;

/*6*/ /*fout*/
SELECT
	email_address,
    order_id,
    order_date
FROM 
	customers c
JOIN 
	orders o
ON 
	c.customer_id = o.customer_id
WHERE 
	(SELECT MIN(ord.order_date) FROM orders ord
    JOIN customers c1 ON  ord.customer_id = c1.customer_id
    GROUP BY c1.customer_id);