דוגמאות קוד - תתי שאילתות SQL

תת שאילתות SQL – דוגמאות קוד. ניתן להעזר בדוגמאות הבאות כדי לתרגל את תתי השאילתות בנוסף הייתי ממליץ להעזר בפוסט הבא
-------------------------------
-- Sub Queries
-------------------------------

SELECT productID , productName, unitPrice
FROM products 

SELECT unitPrice
FROM products
WHERE productID = 5 

SELECT productID , productName, unitPrice
FROM products
WHERE unitPrice &amp;amp;amp;gt; 51.35

SELECT productID , productName, unitPrice
FROM products
WHERE unitPrice &amp;amp;amp;gt; (	SELECT unitPrice
					FROM products
					WHERE productID = 5 )

-- Single Row SQ 

SELECT productID , productName, unitPrice
FROM products
WHERE unitPrice &amp;amp;amp;gt; (	SELECT unitPrice
					FROM products
					WHERE productID = 5 )

SELECT productID , productName, unitPrice
FROM products
WHERE unitPrice &amp;amp;amp;gt; (	SELECT unitPrice
					FROM products
					 )

SELECT productID , productName, unitPrice
FROM products
WHERE unitPrice &amp;amp;amp;gt; (	SELECT unitPrice , productName
					FROM products
					WHERE productID = 5 )

SELECT * FROM products 

SELECT productName , unitPrice , categoryID
FROM products
WHERE unitPrice &amp;amp;amp;gt;
					(SELECT AVG(unitPrice)
					 FROM products )
SELECT *
FROM customers
WHERE city =
					(SELECT city
					 FROM customers
					 WHERE customerID = 'ANATR')
AND customerID &amp;amp;amp;lt;&amp;amp;amp;gt; '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 &amp;amp;amp;gt; (	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 (&amp;amp;amp;gt; ALL , &amp;amp;amp;lt;ALL , =ALL)
SELECT productID , productName, unitPrice , categoryID
FROM products
WHERE unitPrice &amp;amp;amp;gt;ALL (SELECT unitPrice
					FROM products
					WHERE categoryID = 5 )

SELECT productID , productName, unitPrice , categoryID
FROM products
WHERE unitPrice &amp;amp;amp;lt;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 (&amp;amp;amp;gt; ANY , &amp;amp;amp;lt;ANY , =ANY) 

SELECT productID , productName, unitPrice , categoryID
FROM products
WHERE unitPrice &amp;amp;amp;gt; ANY (SELECT unitPrice
					FROM products
					WHERE categoryID = 5 )

SELECT productID , productName, unitPrice , categoryID
FROM products
WHERE unitPrice &amp;amp;amp;lt; 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 &amp;amp;amp;gt; (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)
דוגמאות קוד – תתי שאילתות SQL
