11. Working with Subqueries

-- Oefening 1

SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';


-- Oefening 2

SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);


-- Oefening 3

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
					FROM OrderItems
					WHERE prod_id = 'RGAN01');
					

-- Opdracht 1

/* Toon de cust_name van degene die het product BR02 hebben besteld. */

-- cust_name
-- Fun4All

SELECT 								
											
-- Opdracht 2

/* Toon de cust_name van de klanten die niet in januari hebben besteld. */

--	cust_name
--	Village Toys                                
--	The Toy Store                                    

SELECT 

-- Opdracht 3

/* Toon de cust_name, cust_state met het aantal bestellingen. */

-- cust_name		cust_state	orders
-- Fun4All			IN   		1
-- Fun4All			AZ   		1
-- Kids Place		OH   		0
-- The Toy Store	IL   		1
-- Village Toys		MI   		2

SELECT	

-- Opdracht 4

/* Toon de prod_name van het duurste product. */

-- prod_name
-- 18 inch teddy bear

SELECT 

-- Opdracht 1

/* Toon de cust_name van degene die het product BR02 hebben besteld. */

-- cust_name
-- Fun4All

SELECT cust_name
FROM Customers
WHERE cust_id IN (	SELECT cust_id
					FROM Orders
					WHERE order_num IN (	SELECT order_num
											FROM OrderItems
											WHERE prod_id = 'BR02'));										
											
-- Opdracht 2

/* Toon de cust_name van de klanten die niet in januari hebben besteld. */

--	cust_name
--	Village Toys                                
--	The Toy Store                                     

SELECT cust_name
FROM Customers
WHERE cust_id IN (	SELECT cust_id
					FROM Orders
					WHERE NOT DATEPART (MM,order_date) = 1);

-- MySQL
SELECT cust_name
FROM Customers
WHERE cust_id IN (	SELECT cust_id
					FROM Orders
					WHERE NOT MONTH(order_date) = 1)

-- Opdracht 3

/* Toon de cust_name, cust_state met het aantal bestellingen. */

-- cust_name		cust_state	orders
-- Fun4All			IN   		1
-- Fun4All			AZ   		1
-- Kids Place		OH   		0
-- The Toy Store	IL   		1
-- Village Toys		MI   		2

SELECT	cust_name, 
		cust_state,
		(SELECT COUNT(*)
		FROM Orders
		WHERE Orders.cust_id = Customers.cust_id) as orders 
FROM Customers
ORDER BY cust_name;

-- Opdracht 4

/* Toon de prod_name van het duurste product. */

-- prod_name
-- 18 inch teddy bear

SELECT prod_name 
FROM Products
WHERE prod_price IN (	SELECT MAX(prod_price) 			
						FROM Products);