דוגמאות קוד – תתי שאילתות 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)