SUB-QUERY Using emp and dept table process

/* Formatted on 10/19/2014 10:20:33 AM (QP5 v5.115.810.9015) */
SELECT   * FROM emp

select * from dept

   Same Table:-
====================
/* Formatted on 10/19/2014 10:20:29 AM (QP5 v5.115.810.9015) */
  SELECT   MAX (SAL), ENAME
    FROM   emp
GROUP BY   ENAME

/* Formatted on 10/20/2014 8:16:18 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   sal = (SELECT   MAX (SAL) FROM emp)

/* Formatted on 10/20/2014 8:16:13 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   sal = (SELECT   MAX (SAL)
                  FROM   emp
                 WHERE   DEPTNO = 10)

    Differnt Table:-
=========================

/* Formatted on 10/19/2014 10:31:13 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno IN (SELECT deptno FROM dept)

 IN
=====
/* Formatted on 10/19/2014 10:31:49 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno IN (SELECT   deptno
                      FROM   dept
                     WHERE   deptno = 10)



   NOT IN
==============
/* Formatted on 10/19/2014 10:52:26 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno NOT IN (SELECT   deptno
                          FROM   dept
                         WHERE   deptno = 10)
                        
                        
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE   deptno in (SELECT   deptno
                      FROM   dept
                     WHERE   deptno > 10)

   Operater Value:-
=======================
 ANY
======                       
/* Formatted on 10/19/2014 11:37:50 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   sal > ANY (SELECT   sal
                      FROM   emp
                     WHERE   sal > 13000)

/* Formatted on 10/19/2014 11:37:57 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   sal > ANY (SELECT   sal
                      FROM   emp
                     WHERE   sal >= 13000)

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   sal >= ANY (SELECT   sal
                       FROM   emp
                      WHERE   sal >= 13000)

  ALL
=======

/* Formatted on 10/19/2014 11:51:19 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB,
         deptno
  FROM   emp
 WHERE   sal > ALL (SELECT   sal
                      FROM   emp
                     WHERE   deptno = 10)




    IN and ANY Process:-
==============================

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME in (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')

  IF USING ANY AND ALL MUST ADD THE OPERATORS =,<,>,<>
===========================================================

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME = any (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')


   NOT IN and ANY process:-
===============================

/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME not in (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')
                     
/* Formatted on 10/19/2014 11:38:03 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         SAL,
         JOB
  FROM   emp
 WHERE   ENAME !=ANY (SELECT   ENAME
                       FROM   emp
                      WHERE   ENAME like 's%')
                     
  CORRELATED SUBQUERIES:-
============================

A correlated subquery is one where the inner query depends on values provided by the outer query.

/* Formatted on 10/20/2014 8:14:39 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp E1
 WHERE   sal = (SELECT   MAX (SAL)
                  FROM   emp
                 WHERE   EMPNO = E1.EMPNO AND DEPTNO = 10)
                
/* Formatted on 10/20/2014 8:14:39 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp E1
 WHERE   sal = (SELECT   SAL
                  FROM   emp
                 WHERE   EMPNO = E1.EMPNO AND DEPTNO = 10)

/* Formatted on 10/20/2014 8:14:39 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp E1 /*WHERE   sal = (SELECT   SAL
                  FROM   emp
                 WHERE   EMPNO = E1.EMPNO */where DEPTNO = 10
                
    Subqueries and the EXISTS operator:-
=============================================

/* Formatted on 10/20/2014 8:24:30 AM (QP5 v5.115.810.9015) */
SELECT   emp_last_name "Last Name", emp_first_name "First Name"
  FROM   employee
 WHERE   EXISTS (SELECT   *
                   FROM   dependent
                  WHERE   emp_ssn = dep_emp_ssn);
                 
/* Formatted on 10/19/2014 10:31:49 AM (QP5 v5.115.810.9015) */
SELECT   EMPNO,
         ENAME,
         JOB,
         DEPTNO,
         SAL
  FROM   emp
 WHERE  EXISTS (SELECT   *
                      FROM   dept E1
                     WHERE   deptno = E1.deptno)