--------------------------------------------------------------
--SUB QUERY:

SELECT OrderID, OrderDate,
      (SELECT MAX(UnitPrice)
       FROM  [Order Details] AS OrdDet 
       WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice 
FROM Orders AS Ord 



select *
from orders

select *
from [Order Details]





--Two types of Subqueries:
-- 1. Single row (returns only one row)
-- 2. Multiple Row (Returns more than one row)

-----------------------------------------------------------------------------
-- 1. Single row
-----------------------------------------------------------------------------
--show the employees names which where born after employee named 'KING'
-- 1. when was king born?
SELECT BirthDate
FROM dbo.Employees
WHERE LastName = 'KING'

--2. find all employees which their birthdate is after 1960-05-29
SELECT EmployeeID, LastName, BirthDate
FROM dbo.Employees
WHERE  BirthDate > '1960-05-29'

-- Subquery:
SELECT EmployeeID, LastName, BirthDate
FROM  Employees
WHERE  BirthDate > (SELECT BirthDate
					FROM Employees
					WHERE LastName = 'KING')

-- Use group function:
SELECT AVG(UnitPrice)
FROM dbo.Products

--find all the products that their unit price is more then the average unit price
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE  UnitPrice > (SELECT AVG(UnitPrice)
					FROM dbo.Products)

SELECT ProductName, UnitPrice
FROM Products
WHERE CategoryID = (SELECT CategoryID
                       FROM Products
                       WHERE ProductName = 'Chai')


SELECT CategoryID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG (UnitPrice)
                       FROM Products
                       WHERE CategoryID = 2)


SELECT CategoryID, AVG(UnitPrice)
FROM Products
GROUP BY CategoryID
HAVING AVG(UnitPrice)>
                      (SELECT AVG (UnitPrice)
                       FROM Products
                       WHERE CategoryID = 3)


SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice> (SELECT UnitPrice
                  FROM Products
                  WHERE ProductID = 5)
AND CategoryID = (SELECT CategoryID
  FROM Products
  WHERE ProductName = 'Chai')
AND ProductName <> 'Konbu'


-----------------------------------------------------------------------------
-- 2. Multiple row
-----------------------------------------------------------------------------

-- Multiple row subqueries need special operators:
-- =, >, < ... - will not work...
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE  UnitPrice > (SELECT UnitPrice
					FROM Products
					WHERE ProductID IN (1, 2, 8, 7))

SELECT UnitPrice
FROM Products
WHERE ProductID IN (1, 2, 8, 7)

-- 2. Multiple Row (Returns more than one row) - Need Special operators
-- IN:
select *
from Products


SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE  UnitPrice IN (SELECT UnitPrice
					FROM Products
					WHERE ProductID IN (1, 2, 8, 7))

--NOT IN:
--Emplopyees that aren't managers:
SELECT EmployeeID, LastName, LastName
FROM Employees
WHERE EmployeeID NOT IN (SELECT ReportsTo
                         FROM Employees)
--we cant see any results. in order to see resluts we need to avoid nulls in our resluts:

SELECT EmployeeID, LastName, LastName
FROM Employees
WHERE EmployeeID NOT IN (SELECT ReportsTo
                         FROM Employees
                         WHERE ReportsTo IS NOT NULL)


SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE  UnitPrice NOT IN (SELECT UnitPrice
					FROM Products
					WHERE ProductID IN (1, 2, 8, 7))



-- > ANY - greater than any of the values (greater then the smallest)
SELECT   ProductName, UnitsInStock  
FROM     Products     
WHERE    UnitsInStock  < ANY 
                      (SELECT  UnitsInStock
                       FROM    Products
             WHERE   CategoryID = 5)
ORDER BY 2 desc

SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE  UnitPrice > ANY (SELECT UnitPrice
					FROM Products
					WHERE ProductID IN (1, 2, 8, 7))

-- < ANY 
SELECT   ProductName, UnitsInStock  
FROM     Products     
WHERE    UnitsInStock  < ANY 
                      (SELECT  UnitsInStock
                       FROM    Products
             WHERE   CategoryID = 5)
ORDER BY 2 DESC

-- = ANY 
SELECT   ProductName, UnitsInStock  
FROM     Products     
WHERE    UnitsInStock = ANY      --Like writing IN statement 
                      (SELECT  UnitsInStock
                       FROM    Products
             WHERE   CategoryID = 5)
ORDER BY 2


-- >ALL

SELECT   ProductName, UnitsInStock  
FROM     Products     
WHERE    UnitsInStock  > ALL
                      (SELECT  UnitsInStock
                       FROM    Products
             WHERE   CategoryID = 5)
ORDER BY 2

SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
WHERE  UnitPrice > ALL (SELECT UnitPrice
					FROM Products
					WHERE ProductID IN (1, 2, 8, 7))
order by UnitPrice desc

-- <ALL

SELECT   ProductName, UnitsInStock  
FROM     Products     
WHERE    UnitsInStock  < ALL
                      (SELECT  UnitsInStock
                       FROM    Products
             WHERE   CategoryID = 5)
ORDER BY 2 DESC


-- =ALL --Not a logical queary therefore the result will be 0
SELECT   ProductName, UnitsInStock  
FROM     Products     
WHERE    UnitsInStock  = ALL
                      (SELECT  UnitsInStock
                       FROM    Products
             WHERE   CategoryID = 5)
ORDER BY 2

--IF A SUB QUERY WILL NOT RETURN A VALUE, THE OUTER QUERY WILL NOT RETURN A VALUE AS WELL
SELECT EmployeeID, LastName, BirthDate
FROM dbo.Employees
WHERE  BirthDate > (SELECT BirthDate
		    FROM dbo.Employees
		    WHERE LastName = 'MIZI')


--Derived Table - SUBQUERY CAN BE USED IN THE FROM INSTEAD OF TABLE NAME 

SELECT CategoryID, AVG(UnitPrice) AVG_PRICE
FROM dbo.Products
GROUP BY CategoryID

SELECT ProductID, ProductName, UnitPrice, CA.CategoryID, AVG_PRICE
FROM Products P INNER JOIN (SELECT CategoryID, AVG(UnitPrice) AVG_PRICE
							FROM dbo.Products
							GROUP BY CategoryID) as CA
ON P.CategoryID = CA.CategoryID
AND P.UnitPrice > CA.AVG_PRICE


SELECT Ord .OrderID, Ord .OrderDate, OD.MaxUnitPrice
FROM  (SELECT OrderID ,
       MAX(UnitPrice) AS MaxUnitPrice
       FROM  [Order Details]
       GROUP BY OrderID) AS OD
INNER JOIN Orders AS Ord
ON Ord.OrderID = OD. OrderID

 ---------------------------------------------------
 -- EXISTS
 ---------------------------------------------------

--EXISTS
--find all the customers who ordered a product 
--(that have an orderId in the orders table)

-- All the customers with orders
SELECT CustomerID, CompanyName
FROM Customers C
WHERE EXISTS (SELECT 1 --> only used to check if value/s exists
			  FROM Orders O
	          WHERE O.CustomerID = C.CustomerID)

-- All the customers with no orders
SELECT CustomerID, CompanyName
FROM Customers C
WHERE NOT EXISTS (SELECT 1
				  FROM Orders O
	              WHERE O.CustomerID = C.CustomerID)

-- Returns only employees that are exists in both queries.
SELECT lastname, o.employeeID--, i.reportsTO
FROM employees o
WHERE EXISTS (SELECT i.EmployeeID
              FROM employees i
              WHERE o.employeeID=i.reportsTO)
          
select employeeID, reportsTO from Employees

-- All NON-managers:
SELECT lastname
FROM employees o
WHERE NOT EXISTS (SELECT o.EmployeeID
              FROM employees i
              WHERE o.employeeID=i.reportsTO)