sqlh12.3

-- Opdracht 1

/* Vertaal de onderstaande SQL instructie naar een SQL instructie zonder subquery. */

-- cust_name
-- Fun4All

SET STATISTICS TIME ON

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'));
											
SELECT C.cust_name
FROM Customers AS C
INNER JOIN Orders AS O ON C.cust_id = O.cust_id
INNER JOIN OrderItems AS I ON O.order_num = I.order_num 
WHERE prod_id='BR02';

SELECT C.cust_name
FROM Customers AS C, Orders AS O, OrderItems AS I
WHERE C.cust_id = O.cust_id AND O.order_num = I.order_num AND prod_id='BR02';

SET STATISTICS TIME OFF
											
-- Opdracht 2

/* Vertaal de onderstaande SQL instructie naar een SQL instructie zonder subquery. */

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

SELECT DISTINCT C.cust_name
FROM Customers AS C
INNER JOIN Orders AS O ON C.cust_id = O.cust_id 
WHERE NOT DATEPART (MM,order_date) = 1;

SELECT DISTINCT C.cust_name
FROM Customers AS C, Orders AS O
WHERE C.cust_id = O.cust_id AND NOT DATEPART (MM,order_date) = 1;

-- MySQL: MONTH (order_date) = 1
											
-- Opdracht 3

/* Vertaal de onderstaande SQL instructie naar een SQL instructie zonder subquery. */

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

SELECT C.cust_name, C.cust_state, Count(O.order_num) AS orders
FROM Customers AS C
INNER JOIN Orders AS O ON C.cust_id = O.cust_id
GROUP BY C.cust_name, C.cust_state
ORDER BY C.cust_name;

SELECT C.cust_name, C.cust_state, Count(O.order_num) AS orders
FROM Customers AS C, Orders AS O
WHERE C.cust_id = O.cust_id
GROUP BY C.cust_name, C.cust_state
ORDER BY C.cust_name;

-- Opdracht 4

/* Vertaal de onderstaande SQL instructie naar een SQL instructie zonder subquery. */

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

-- kan niet