------------------------------------------------
-- SubQuery in the SELECT clause 
-----------------------------------------------

-- Get Employee Age
select lastname, DATEDIFF(yy, birthdate, getdate()) from Employees

-- Present AVG Age - Will It Run?
select lastname, DATEDIFF(yy, birthdate, getdate()), AVG(DATEDIFF(yy, birthdate, getdate()))  As AVG_AGE
from Employees

-- Use Nested SubQuery
SELECT lastname, DATEDIFF(yy, birthdate, getdate()),
	   (SELECT AVG(DATEDIFF(yy, birthdate, getdate())) FROM employees) As AVG_AGE
FROM employees emps 

-- Nested SubQuery With Column Calculation
SELECT lastname, DATEDIFF(yy, birthdate, getdate()),
	   (SELECT AVG(DATEDIFF(yy, birthdate, getdate())) FROM employees) As AVG_AGE,
	   DATEDIFF(yy, birthdate, getdate()) - 
	   (SELECT AVG(DATEDIFF(yy, birthdate, getdate())) FROM employees) As AVG_AGE
FROM employees emps 

-- Derived SubQuery
SELECT lastname, AGE, AVG_AGE, (AGE - AVG_AGE) As DELTA
FROM
(
SELECT lastname, DATEDIFF(yy, birthdate, getdate()) As AGE,
	   (SELECT AVG(DATEDIFF(yy, birthdate, getdate())) FROM employees) As AVG_AGE
FROM employees emps
) EMP 


-- Correlated SubQuery
select orderid, 
	   CustomerID,
	  (select avg(ot.UnitPrice) from [Order Details] ot where o.OrderID = ot.OrderID) As AVG_PRICE
from Orders o

--Check
select avg(UnitPrice) from [Order Details] where OrderID = 10248

----------------------------------------------
-- Subquery in the WHERE clause
---------------------------------------------
 
SELECT  last_name , 
				salary
FROM employees OUT 
WHERE salary > (SELECT avg(salary) FROM employees WHERE OUT.department_id = department_id)

------------------------------------------------
-- Subquery in the HAVING CLAUSE
------------------------------------------------

SELECT department_id , avg(salary)
FROM employees
GROUP BY department_id 
HAVING avg(salary) > (SELECT avg(salary) FROM employees WHERE department_id = 110) ; 

--------------------------------------------------
-- Subquery in the ORDER BY clause 
-------------------------------------------------

SELECT last_name , salary , department_id 
FROM employees OUT
ORDER BY (SELECT department_name FROM departments WHERE department_id = OUT.department_id) ;

-----------------------------------
-- EXISTS / NOT EXISTS
----------------------------------

SELECT employee_id , last_name , salary 
FROM employees OUT 
WHERE EXISTS (SELECT * FROM employees WHERE manager_id = OUT.employee_id );

SELECT employee_id , last_name , salary 
FROM employees OUT 
WHERE NOT EXISTS (SELECT * FROM employees WHERE manager_id = OUT.employee_id );