sqlh13.3

-- Opdracht 1

/* Toon het order_num met alle prod_name */

--	order_num	prod_name
--	20007		Fish bean bag toy
--	20008		Fish bean bag toy
--	20009		Fish bean bag toy
--	20007		Bird bean bag toy
--	20008		Bird bean bag toy
--	20009		Bird bean bag toy
--	20007		Rabbit bean bag toy
--	20008		Rabbit bean bag toy
--	20009		Rabbit bean bag toy
--	20005		8 inch teddy bear
--	20006		8 inch teddy bear
--	20006		12 inch teddy bear 
--	20005		18 inch teddy bear 
--	20006		18 inch teddy bear 
--	20007		18 inch teddy bear 
--	20008		18 inch teddy bear 
--	20007		Raggedy Ann
--	20008		Raggedy Ann
--	NULL		King doll
--	NULL		Queen doll 

SELECT O.order_num, P.prod_name
FROM OrderItems AS O
RIGHT OUTER JOIN Products AS P ON O.prod_id = P.prod_id;

-- Opdracht 2

/* Toon de prod_name van de producten die niet besteld zijn. */

-- prod_name
-- King doll
-- Queen doll

SELECT P.prod_name
FROM OrderItems AS O
RIGHT OUTER JOIN Products AS P ON O.prod_id = P.prod_id
WHERE O.order_num IS NULL;

-- Opdracht 3

/* Toon de aantallen vendors die geen producten hebben geleverd. */

-- (No column name)
-- 3

SELECT COUNT(*)
FROM Products AS P 
RIGHT OUTER JOIN Vendors AS V ON P.vend_id = V.vend_id
WHERE P.prod_id IS NULL;

-- Opdracht 4

/* Maak een SELF JOIN die de cust_id, cust_name en cust_contact toont met dezelfde cust_name als die van Jim Jones. */

--	cust_id		cust_name	cust_contact
--	1000000003	Fun4All 	Jim Jones 
--	1000000004	Fun4All 	Denise L. Stephens

SELECT C1.cust_id, C1.cust_name, C1.cust_contact
FROM Customers AS C1 INNER JOIN Customers AS C2 ON C1.cust_name = C2.cust_name 
WHERE C2.cust_contact = 'Jim Jones';