Retrieving Data Using the SQL SELECT Statement

Scalar Queries

=>    Sub Queries follows select clause are called Sub Queries.
=>    A scalar query 1 value per row.

    Syntax:
    =>    Select (subQuery1), (subQuery2), .... From tableName;

=>    Sub Query can be used in
    a)    Where
    b)    From
    c)    Select etc..

    Examples:
    1. Display the no of records in Emp and Dept ?
    =>    Select ( select count(*) from emp ) as emp,
            ( select count(*) from dept ) as dept
        from dual;
   
    2. Display the following report ?
        ename    sal    deptno    dept_sumsal
   
    =>    Select ename, sal, deptno, (select sum(sal) from emp e where e.deptno = deptno) as dept_sumsal from emp;

Inline Views

=> Sub Query following From clause is called "Inline View".
=> The result of the Sub Query acts as a table for Outer Query.

=> Inline views are used in the following scenarios:
    (i)    To use column alias in where clause.
    (ii)    To use Window functions.
    (iii)    To use result of one process in another process.

    Syntax:
    =>    Select * from <SubQuery> [ where condition ];

    Examples:
    =>    Select * from ( Select ename, sal from emp e );

    1. Display 5th record from emp ?
    =>    Select * from ( select rownum, empno, ename, sal from emp) where rownum = 5;

    2. Get the top 3 max salaries from emp ?
    =>    Select * from ( select rownum, empno, ename, sal from emp order by sal desc) where rownum <4;

Co-related Sub Query

=>    If Sub Query references values of Outer Query then it is called as "Co-related Sub Query".
=>    In the co-related Sub QUery, execution starts from Outer Query and Inner Query will be executed as many times as no of records exists in the table.

    Examples:
    1. Select the TOP 3 Maximum salaries ?
    =>    Select distinct sal from emp e1
            where 3 > ( Select count( distinct sal ) from emp e2 where e1.sal < e2.sal ) Order By sal Desc;
   
    2. Select the TOP 3 Minimum salaries ?
    =>    Select distinct sal from emp e1
            where 3 > ( Select count( distinct sal ) from emp e2 where e1.sal > e2.sal );
   

Multi Row Sub Query

=>    If Inner query returns more than one column then Sub Query is called "Multi Row Sub Query".
=>    No of Columns returned by Inner Query = No of columns used in where clause of Outer Query.
=>    Corresponding columns datatype must be same.

    Syntax:
    =>    Where (col1, col2) OP ( Select column1, column2 from tableName );

=>    OP can be { =, IN, NOT IN }

    Examples:
    1. Display emp records earning max sal in their dept ?
    =>    Select * from emp where (deptno, sal) in ( select deptno, MAX(sal) from emp GROUP BY deptno);

Nested Sub Query

=>    A query embedded in another Query or Sub Query.
=>    Queries can be nested upto 255 level.

    Syntax:
    =>    Select ....
                 ( select ....
                     ( select ....


    Examples:
    1. Select 2nd max salary ?
    =>    Select MAX( sal ) from emp where sal < ( Select MAX( sal ) from emp );

    2. Find the Location of the employee who is earning 2nd max sal ?
    =>    Select loc from dept where deptno in ( select deptno from emp where sal = (
        Select MAX( sal ) from emp where sal < ( Select MAX( sal ) from emp ) ) );

Multi Row Sub Query

=>    If sub query returns more than one value then the sub query is called 'Multi Row Sub Query'.

    Syntax:
    =>    Select <collist> from <tableName> where colname OP ( Select statement);

=> OP can be { IN, NOT IN, ALL, EXISTS, ANY }

    Examples:

    1. Get the 5th record from emp ?
    =>    Select * from emp where empno in ( select DECODE( rownum, 5, empno) from emp );
       
    2. Display Alternate records from emp ?
    =>    Select * from emp where empno in ( select DECODE( MOD(rownum,2) , 1, empno ) from emp );

    3. Display names of employee's who are earning MIN and MAX salary ?
    =>    Select * from emp where sal IN ( select MAX(sal) from emp UNION select MIN(sal) from emp);