SUB QUERY

               SUB QUERY

*The Query with in another is query is known as "SUB QUERY".
*In Sub query the inner part is execute first and the Outer query will execute second.
*Can not use the WHERE condition after the GROUP BY Clause.
*Sub query start with "(" and ends with ")" after the conditional operator.

Simple Example 1::


SELECT * FROM employees WHERE Salary>
(SELECT Salary FROM employees where First_Name='Neena');

In this query,The inner part executes first and get the "Salary" of employee "Neena" and then will returns the Output,Who
all are having more than(Greater than) "Neena's" salary.

Simple Example 2::


SELECT * FROM employees WHERE Salary>
(SELECT ROUND(AVG(Salary)) FROM employees);

In this query the same Inner part is executes first and then Get the "Average salary" of employees and then return the Output
Who all are having greater then "AVG" salary.

TYPES OF SUB QUERIES::


There are following two types of subsidiaries are there.

1.Single Row Sub Queries.
2.Multiple Row Sub Queries.

1.Single Row Sub Queries::

Query that return only one row from the inner SELECT statement is Known as "Single Row Sub Queries".

Here below i have mentioned some single-row comparison operators::
 -----------------------------------------
= - Equal To
< - Less than
<= - Less than or Equal to
> - Greater than
>= - Greater than or Equal to
<> - Not Equal to
------------------------------------------


Places Which we are Using SUB Queries::

1.USING Group Functions in Sub Query(MIN,MAX,AVG....)
2.After SELECT Key word
3.After HAVING clause

Examples and their explanations for Single Row Sub quries::


SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);

OUTPUT::
In this the Inner Query returns the Output is "ST_CLERK"

So it gives the Output like Below format::
----------------------------------
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
....Cont...
------------------------

SELECT last_name, job_id
FROM employees
WHERE salary>
(SELECT salary
FROM employees
WHERE employee_id = 143);

OUTPUT::
In this the Inner Query returns the Output is "2600" Then outer query execute then gives the Outputs.

So it gives the Output like Below format::
-------------------------------------
King AD_PRES 24000
Kochhar AD_VP 17000
De Haan AD_VP 17000
Hunold IT_PROG 9000
...Cont....


USING SUBQUERIES AFTER HAVING CALUES::


SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);

OUTPUT::
---------------------------
JOB_ID AVG(SALARY)
---------------------------
PU_CLERK 2780
---------------------------

2.Multiple Row Sub Queries::

*If Inner Query returns more than one row then it's known as "Multiple Row Sub queries".
*Using Following Operators after the Conditional Operators to Solve this Simple.

--------------------------------------------------------
IN - Equal to any member in the list

ANY - Compare value to each value returned by
the sub query

ALL - Compare value to every value returned
by the sub query
--------------------------------------------------------

USING IN::

While Using in Don't Specify the Conditional Operator With the Word "IN"

Example::

SELECT Employee_Id,First_Name,Salary
FROM employees
WHERE Salary IN
(SELECT Salary from employees where First_Name='Alexandar');

This returns the Out Put in Below Format because the First_Name='Alexandar' having 2 times and the Same Alexander Salary is
equal to Some employees in employee tables.


--------------------------------------
EMPLOYEE_ID FIRST_NAME SALARY
--------------------------------------
158 Allan 9000
152 Peter 9000
109 Daniel 9000
103 Alexander 9000
196 Alana 3100
181 Jean 3100
142 Curtis 3100
115 Alexander 3100
--------------------------------------

USING "ANY" OPERATOR::

Using ANY Operator Which Compares the Each sub query value with the Outer Query and give the OUTPUT based on below
CONDITION BASES.
=================================================================
< ANY --- Get the Max Value in the Sub Query and Give Output Lessthan than the MAX                                 Value.
------------------------------------------------------------------------------------------------
> ANY --- Get the MIN Value in the Sub Query and give Output Greterthan than the MIN                               Value
------------------------------------------------------------------------------------------------
= ANY --- Which Gives the result same as IN Condition
=================================================================

Example::
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(select Salary from employees where Job_Id='IT_PROG');

Here first the Sub query will excute and it check the MAX Salary of IT_PROG(MAX is 9000) and returns the output like Less than 9000
who all are get

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
132 Olson ST_CLERK 2100
128 Markle ST_CLERK 2200
136 Philtanker ST_CLERK 2200
127 Landry ST_CLERK 2400
135 Gee ST_CLERK 2400
119 Colmenares PU_CLERK 2500
131 Marlow ST_CLERK 2500
..Cont.........
------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ANY
(select Salary from employees where Job_Id='IT_PROG');
OUTPUT::

                ------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
201 Hartstein MK_MAN 13000
108 Greenberg FI_MGR 12000
-------------------------------------------------------

USING "ALL" OPERATOR::

Following are the conditions for using ALL Operator.

=================================================================
< ALL --- Get the MIN Value in the Sub Query and Give Output Less than than the MIN Value.
------------------------------------------------------------------------------------------------
> ALL --- Get the MAX Value in the Sub Query and give Output Grater than than the MAX Value
=================================================================

Examples::


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
115 Khoo PU_CLERK 3100
116 Baida PU_CLERK 2900
117 Tobias PU_CLERK 2800
118 Himuro PU_CLERK 2600
119 Colmenares PU_CLERK 2500
125 Nayer ST_CLERK 3200
126 Mikkilineni ST_CLERK 2700
127 Landry ST_CLERK 2400
128 Markle ST_CLERK 2200
-------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
108 Greenberg FI_MGR 12000
114 Raphaely PU_MAN 11000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
147 Errazuriz SA_MAN 12000
148 Cambrault SA_MAN 11000
------------------------------------------------------

 =====>SUB QUERY END<=====