Subqueries

SUBQUERIES:

A sub query in the where clause of a select statement is called as Nested sub query.

A sub query in the FROM clause of a SELECT statement is called INLINE VIEW.

Syntax: SELECCT selectlist FROM Tablename

                        WHERE Columnname operator (

                                                                                                SELECT Selectlist

                                                                                                FROM Tablename);

SYNTAX:

SELECT  selectlist

    FROM  (SELECT selectlist FROM Tablename)

Single row sub queries: These queries returns only one row, by taking this value outer query returns more.

Q) Get the details of employees who are equally designated with Smith

Ans: SQL> SELECT Ename, Sal, Job

FROM Emp

WHERE Job = (SELECT Job

FROM Emp

WHERE Ename = UPPER('smith'))

ORDER BY Sal;

Q) Get the details of employees who are juniors of Mr. Turner.

Ans: SQL> select ename, hiredate, deptno

from emp

where hiredate >   (select hiredate

from emp where ename='TURNER');

Q) Get the details of employees who are seniors of Mr. Turner.

Ans:   select ename, hiredate, deptno

from emp

where hiredate  < (select hiredate f

rom emp where ename='TURNER');

Q) Get the details of employees who joined on the same day as turner?

select ename, hiredate, deptno

from emp

where hiredate =(select hiredate

from emp where ename='TURNER');

Q) Get the details of all the employees from sales department.

SQL> SELECT Empno, Ename, Sal, Job

    FROM Emp

    WHERE Deptno = (SELECT Deptno

                                                FROM Dept

                                                WHERE Dname = 'SALES');

Q) Get the details of all the employees who are equally designated with Smith (Excluding Smith).

SQL> SELECT Ename, Sal, Job

            FROM Emp

             WHERE Job = (SELECT Job

            FROM Emp

            WHERE Ename = UPPER('smith')

            ) AND

    Ename <> 'SMITH'

   ORDER BY Sal

Q) Get the details of all the employees who are working in 'DALLAS'.

SELECT Empno, Ename, Sal, Comm, Sal + NVL( Comm, 0 )

  FROM Emp

    WHERE Deptno = (SELECT Deptno

                                                FROM Dept

                                                WHERE Loc = 'DALLAS');

Q) Get the details of all the employees who are earning more than the avg investment of 30^th department?

SQL> select ename, sal, deptno

from emp

   where sal > (select avg(sal)

from emp where deptno=30);

Q) Get the details of all the employees whose salary is greater than the empno 7566

SQL> SELECT Ename, Sal, Job

    FROM Emp

    WHERE Sal > (SELECT Sal

                                    FROM Emp

                                    WHERE Empno = 7566);

Q) Get the details of highest paid employee in the organization?

SQL> SELECT Ename, Job , Sal

    FROM Emp

    WHERE Sal = (SELECT MAX(Sal)

             FROM Emp);

Q) Get the details of employees whose salary is greater than the avg salary

SQL> SELECT Ename, Job, Sal

    FROM Emp

    WHERE Sal > (SELECT AVG(Sal)

             FROM Emp);

Q) Display dept wise least investments only which cross dept 20^th lease investment.

SQL> select deptno, min(sal)

    from emp

    group by deptno

    having min(sal) > (select min(sal)

    from emp

    where deptno=20);

Multiple row sub Queries:

Q) Get the details of all the employees who are equal earners to highest investment of dept?

Select ename, sal, deptno

From emp

Where sal in (select max(sal)

From emp group by deptno);

Q) Get the details of all the employees who are equal earners to highest investment of job?

Select ename, sal, deptno, job

From emp

Where sal in (select max(sal) from emp group by job );

Q) Who are earning more than their managers?

sel empno, ename, sal, e.deptno from emp_t e where e.sal >

(sel m.sal from emp_t m

where e.mgr=m.empno).

Q) get the details of emp who are having managers?

sel e.ename from emp_t e where exists (sel * from emp_t m where e.mgr=m.empno).

Q) get the details of emp who are not having managers?

sel e.ename from emp_t e where not exists (sel * from emp_t m where e.mgr=m.empno)