-- 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
Download hier het bestand.