--SUBQUERIES AND NESTED SUBQUERIES AND CORRELATED SUBQUERY,EXISTS , NOT EXISTS ,-------- DERIVED TABLES

==================================================================
SUBQUERIES OR NESTED QUERIES
=> A QUERY WITH IN ANOTHER QUERY IS KNOWN AS SUBQUERY OR NESTED QUERY
=> ONE QUERY IS KNOWN AS INNER QUERY/ CHILD/SUBQUERY
=> OTHER QUERY IS KNOWN AS OUTER QUERY/ PARENT QUERY

=> OUTERMOST QUERY WHOSE RESULT SET IS RETURNED TO THE CALLER IS KNOWN AS OUTER QUERY
=> THE INNER QUERY WHOSE RESULT SET IS USED BY OUTER QUERY IS KNOWN AS SUBQUERY

SELECT * FROM TABLENAME WHERE COLUMNAME = (SELECT COLMNAME FROM TABLENAME)


==========================================================================================================

=> SUBQUERY IS A SELECT STATEMENT  INSIDE ANOTHER SELECT , INSERT , UPDATE OR DELETE 
    STATEMENT.
=> OUTER QUERY CAN BE SELECT, INSERT, UPDATE , DELETE BUT INNER QUERY MUST ALWAYS BE 
   SELECT STATEMENT

KIND OF SUBQUERIES:

--1. SELF CONTAINED SUBQUERIES----------------

--THE SUBQUERY WHICH IS INDEPENDANT OF OUTER QUERY IS KNOWN AS SELF CONTAINED SUBQUERY 
--IN SUCH QUERY FIRST INNER QUERY IS EXECUTED AND ITS RESULT IS USED BY OUTER QUERY


7654

SELECT * FROM EMP WHERE EMPNO = (SELECT EMPNO FROM EMP WHERE ENAME = 'martin');

SELECT * FROM EMP WHERE EMPNO = 7654------------THIS IS THE RESULT 

--EASY TO DEBUG

I WANT ALL THE DATA FROM THE EMP TABLE WHOSE DEPARTMENT NUMBER IS SAME AS THE DEPARTMENT OF BLAKE

STEP-1 WE NEED TO RECTIFY THE DEPARTMENT OF BLAKE

SELECT DEPTNO FROM EMP WHERE ENAME = 'BLAKE'
--30

STPE-2 TAKE THAT DEPRATMENT IN THE WHERE CLAUSE AND RETRIEVE ALL THE EMP DETAILS WHO ARE WORKING ON THAT PARTICULAR DEPARTMENT

SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'BLAKE')

SELECT * FROM EMP WHERE DEPTNO = 30



--2. CORRELATED SUBQUERY 

--- IN THIS CASE INNER QUERY IS DEPENDANT OF OUTER QUERY 
--- IT IS DIFFICULT TO DEBUG

--SUBQUERIES 
--1. SINGLE VALUED -----------------WHEN AN INNER QUERY RETRIEVES A SINGLE VALUE THAN IT IS CALLED AS....
--2. MULTI VALUED 
--3. TABLE VALUED

SUBQUERIES ARE USED IN:  WHERE, IN, FROM , SELECT

=> DISPLAY THE DETAILS OF EMPLOYEES EARNING MORE THEN AVERAGE SALARY OF ORGANIZATION

1. AVERAGE SALARY OF ORGANIZATION --SELECT AVG(SAL) FROM EMP;

SELECT AVG(SAL) FROM EMP
------2073.214285

2. FILTER MY RESULT ON BASIS OF ABOVE RESULT 

SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);

SELECT * FROM EMP WHERE SAL > 2073.214285;


SELECT * FROM EMP WHERE SAL > AVG(SAL); ---ERROR -- AGGREGATION NOT ALLOWED IN WHERE

==>--DISPLAY THE EMPLOYEE DETAILS EARNING MORE THEN BLAKE

STEP -1  GET THE SALARY OF BLAKE

SELECT SAL FROM EMP WHERE ENAME ='BLAKE'

-- FILTER THE EMP TABLE AS PER ABOVE RESULT

SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME ='BLAKE');

======================
-- DISPLAY THE EMPLOYEE DETAILS WHO ARE SENIOR TO KING

STEP - 1 GET THE HIREDATE OF KING

SELECT HIREDATE FROM EMP WHERE ENAME = 'KING'

-- FILTER EMP TABLE AS PER ABOVE RESULT

SELECT * FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'KING');----SINGLE VALUED SELF CONTAINED SUBQUERY

==============================
-- DISPLAY EMPLOYEE NAME AND SALARY EARNING MAXIMUM SALARY
-- OBTAIN MAXIMUM SALARY

SELECT MAX(SAL) FROM EMP

--FILTER EMP TABLE AS PER ABOVE RESULT

SELECT ENAME,SAL FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);

==================================
- DISPLAY EMPLOYEE NAME AND HIREDATE HAVING MAXIMUM EXPERIENCE
-- OBTAIN MINIUM HIREDATE

SELECT MIN(HIREDATE) FROM EMP

--FILTER EMP TABLE AS PER ABOVE RESULT


SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP);


===================================================================================================================
MULTIVALUED SUBQUERIES

IF INNER QUERY RETURNS MULTIVALUES OR A LIST OF VALUES OR A COLUMN OF VALUES
IT IS KNOWN AS MULTIVALUED SUBQUERY

-- DISPLAY THE EMPLOYEE DETAILS WHOSE JOB IS SAME AS JOB OF BLAKE,ADAMS

STEP-1 I NEED TO IDENTIFY THE JOB OF BLANK AND ADAMS
STPE-2 I NEED TO FILTER ABOVE RESULT SET INTO MY OUTER QUERY

SELECT * FROM EMP WHERE JOB IN (SELECT JOB FROM EMP WHERE ENAME IN ('BLAKE','ADAMS'));

--Msg 512, Level 16, State 1, Line 136
--Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


OPERATORS WITH MULTIVALUED SUBQUERIES -- IN , NOT IN , SOME|ANY, ALL

ANY OPERATOR  (COMPARISION WITH MULTIPLE VALUES)
============
FOR COMPARISION WE CAN USE =, >, >=, <, <=
WHERE X > ANY(SUBQUERY)
WHERE X > ANY(1000,2000, 3000)=--------------------ANY = OR AND ALL = AND

X = 800  --X > ANY(1000,2000, 3000) -----FALSE
X = 1500 --X > ANY(1000,2000, 3000) -----TRUE 
X = 4500 --X > ANY(1000,2000, 3000) -----TRUE

WHERE X < ANY(SUBQUERY)
WHERE X < ANY(1000,2000, 3000)

X = 800  --X < ANY(1000,2000, 3000) -- TRUE
X = 1500 --X < ANY(1000,2000, 3000) --- TRUE
X = 4500 --X < ANY(1000,2000, 3000)----- FALSE


ALL OPERATOR  (COMPARISION WITH MULTIPLE VALUES)
============
WHERE X > ALL(SUBQUERY)
WHERE X > ALL(1000,2000, 3000)

X = 800  --X > ALL(1000,2000, 3000) FALSE  X>1000 AND X>2000 AND X>3000
X = 1500 --X > ALL(1000,2000, 3000) FALSE 
X = 4500 --X > ALL(1000,2000, 3000)  TRUE

=> DISPLAY EMPLOYEE DETAILS WHO ARE EARNING MORE THAN ALL THE MANAGERS

STEP-1 GET THE SALARY OF ALL THE MANAGERS-------------------INNER QUERY---------------MULTIVALUED SUBQUERY AND SELF CONTAINED SUBQUERY
2975.00
2850.00
2450.00

STEP-2 FILTER THE EMP TABLE AS PER ABOVE RESULT-------------OUTER QUERY


2975.00
2850.00
2450.00-------------

SELECT * FROM EMP

SELECT ENAME,SAL FROM EMP WHERE SAL >= SOME(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

-- DISPLAY EMPLOYEE DETAILS EARNING MORE THAN ATLEAST ONE MANAGER

SELECT ENAME,SAL FROM EMP WHERE SAL > ANY(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

==========================================================================
CO-RELATED SUBQUERY:
====================
=> INNER QUERY WILL REFERENCE THE OUTER QUERY
=> INNER QUERY IS NOT INDEPENDANT OF OUTER QUERY--------------------------INNER QUERY IS INDEPENDENT IN SELF CONTAINED
=> INNER QUERY IS DEPENDANT ON OUTER QUERY
=> EXECUTION WILL START FROM OUTER QUERY AND INNER QUERY WILL EXECUTED NUMBER OF TIMES
DEPENDANT ON NUMBER OF ROWS RETURNED BY OUTER QUERY
=> SUBQUERY GETS EXECUTED FOR EACH ROW RETURN BY OUTER QUERY

DIFFULCT TO DEBUG , 
YOU CANNOT EXECUTE THE INNER QUERY INDEPENDENTLY
SLOW IN PERFORMANCE , WHY BECAUSE IT IS EXECUTE FOR EACH ROW OF THE OUTERQUERY TO COMPARE WITH THE INNER QUERY

=> DISPLAY EMPLOYEES EARNING MORE THAN THE AVEGERAGE SALARY OF THEIR DEPARTMENT

1. DEPARTMENT WISE AVG SALARY FOR EACH OF THE EMPLOYEE

SELECT DEPTNO,AVG(SAL) FROM EMP P 
GROUP BY DEPTNO

SELECT * FROM EMP AS E WHERE SAL > (SELECT AVG(SAL) FROM EMP P WHERE P.DEPTNO = E.DEPTNO);

SELECT * FROM EMP
ORDER BY DEPTNO


=> DISPLAY EMPLOYEES EARNING LESS THAN THE AVEGERAGE SALARY OF THEIR DEPARTMENT

SELECT * FROM EMP AS E WHERE SAL < (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO);

=>DISPLAY EMPLOYEE DETAILS EARNING MAX SALARY IN THEIR DEPARTMENTS

SELECT DEPTNO,MAX(SAL) FROM EMP P 
GROUP BY DEPTNO

SELECT * FROM EMP AS E WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE  DEPTNO = E.DEPTNO);


SELECT * FROM EMP
ORDER BY DEPTNO