תת שאילתות SQL – דוגמאות קוד. ניתן להעזר בדוגמאות הבאות כדי לתרגל את תתי השאילתות בנוסף הייתי ממליץ להעזר בפוסט הבא
------------------------------- -- Sub Queries ------------------------------- SELECT productID , productName, unitPrice FROM products SELECT unitPrice FROM products WHERE productID = 5 SELECT productID , productName, unitPrice FROM products WHERE unitPrice > 51.35 SELECT productID , productName, unitPrice FROM products WHERE unitPrice > ( SELECT unitPrice FROM products WHERE productID = 5 ) -- Single Row SQ SELECT productID , productName, unitPrice FROM products WHERE unitPrice > ( SELECT unitPrice FROM products WHERE productID = 5 ) SELECT productID , productName, unitPrice FROM products WHERE unitPrice > ( SELECT unitPrice FROM products ) SELECT productID , productName, unitPrice FROM products WHERE unitPrice > ( SELECT unitPrice , productName FROM products WHERE productID = 5 ) SELECT * FROM products SELECT productName , unitPrice , categoryID FROM products WHERE unitPrice > (SELECT AVG(unitPrice) FROM products ) SELECT * FROM customers WHERE city = (SELECT city FROM customers WHERE customerID = 'ANATR') AND customerID <> 'ANATR' SELECT * FROM products SELECT * FROM categories SELECT productID , productName , unitPrice , categoryID FROM products WHERE categoryID = (SELECT categoryID FROM categories WHERE categoryName = 'SeaFood') SELECT p.productID , p.productName , p.unitPrice , p.categoryID FROM products p JOIN categories c ON p.categoryID = c.CategoryID WHERE c.CategoryName = 'SeaFood' -- Multiple Row Sub Query -- error SELECT productID , productName, unitPrice FROM products WHERE unitPrice > ( SELECT unitPrice FROM products WHERE categoryID = 5 ) -- IN SELECT productID , productName, unitPrice , categoryID FROM products WHERE unitPrice IN (SELECT unitPrice FROM products WHERE categoryID = 5 ) -- ALL (> ALL , <ALL , =ALL) SELECT productID , productName, unitPrice , categoryID FROM products WHERE unitPrice >ALL (SELECT unitPrice FROM products WHERE categoryID = 5 ) SELECT productID , productName, unitPrice , categoryID FROM products WHERE unitPrice <ALL (SELECT unitPrice FROM products WHERE categoryID = 5 ) SELECT productID , productName, unitPrice , categoryID FROM products WHERE unitPrice =ALL (SELECT unitPrice FROM products WHERE categoryID = 5 ) -- ANY (> ANY , <ANY , =ANY) SELECT productID , productName, unitPrice , categoryID FROM products WHERE unitPrice > ANY (SELECT unitPrice FROM products WHERE categoryID = 5 ) SELECT productID , productName, unitPrice , categoryID FROM products WHERE unitPrice < ANY (SELECT unitPrice FROM products WHERE categoryID = 5 ) SELECT productID , productName, unitPrice , categoryID FROM products WHERE unitPrice = ANY (SELECT unitPrice FROM products WHERE categoryID = 5 ) -- Derived Table SELECT * FROM products SELECT p.productID , p.productName FROM (SELECT productID , productName , unitPrice FROM products) AS p -- Correlated SQ SELECT productID , productName , unitPrice , categoryID FROM products pro WHERE unitPrice > (SELECT AVG(unitPrice) FROM products WHERE categoryID = pro.categoryID) -- EXISTS SELECT lastname FROM employees o WHERE EXISTS (SELECT 'kookoo' FROM employees i WHERE o.employeeID=i.reportsTO) SELECT lastname FROM employees o WHERE NOT EXISTS (SELECT i.EmployeeID FROM employees i WHERE o.employeeID=i.reportsTO)