subqueries ass.txt

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE MGR IS NOT IN(SELECT EMPNO
  4                      FROM EMP
  5                      WHERE ENAME='BLAKE') AND COMM IS NOT NULL;
WHERE MGR IS NOT IN(SELECT EMPNO
                 *
ERROR at line 3:
ORA-00908: missing NULL keyword 


SQL> SELECT *
  2  FROM EMP
  3  WHERE MGR IN(SELECT EMPNO
  4               FROM EMP
  5               WHERE ENAME='BLAKE' AND COMM IS NOT NULL);

no rows selected

SQL> SELECT *
  2  FROM EMP
  3  WHERE MGR IN(SELECT EMPNO
  4               FROM EMP
  5               WHERE ENAME='BLAKE' AND COMM IS NULL);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT *
  2  FROM EMP
  3  WHERE MGR IN(SELECT EMPNO
  4               FROM EMP
  5               WHERE ENAME='BLAKE' AND COMM>0);

no rows selected

SQL> ED
Wrote file afiedt.buf

  1  SELECT *
  2  FROM EMP
  3  WHERE MGR IN(SELECT EMPNO
  4               FROM EMP
  5*              WHERE ENAME='BLAKE' AND COMM>=0)
  6  /

no rows selected

SQL> ED
Wrote file afiedt.buf

  1  SELECT *
  2  FROM EMP
  3  WHERE MGR IN(SELECT EMPNO
  4               FROM EMP
  5*              WHERE ENAME='BLAKE' AND COMM<=0)
SQL> /

no rows selected

SQL> SELECT * FROM EMP
  2  ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT *
  2  FROM EMP
  3  WHERE MGR IN(SELECT EMPNO
  4               FROM EMP
  5               WHERE ENAME='BLAKE') AND COMM>=0;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT DNAME,LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE EMPNO IN(SELECT MGR
  6                                 FROM EMP
  7                                 WHERE EMPNO IN(SELECT MGR
  8                                                FROM EMP
  9                                                WHERE JOB='SALESMAN')));

DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT DNAME,LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='CLERK' AND MGR IN(SELECT EMPNO
  6                                               FROM EMP
  7                                               WHERE ENAME='BLAKE') AND SAL<(SELECT SAL
  8                                                                             FROM EMP
  9                                                                             WHERE ENAME='MARTIN'));

DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE MGR!=(SELECT EMPNO
  4              FROM EMP
  5              WHERE JOB='PRESIDENT')AND COMM>=0 AND SAL>ALL(SELECT MAX(SAL)
  6                                                             FROM EMP
  7                                                            WHERE JOB='CLERK');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE MGR!=(SELECT EMPNO
  4              FROM EMP
  5              WHERE JOB='PRESIDENT')AND COMM<=0 AND SAL>ALL(SELECT MAX(SAL)
  6                                                             FROM EMP
  7*                                                           WHERE JOB='CLERK')
SQL> /

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE MGR!=(SELECT EMPNO
  4              FROM EMP
  5              WHERE JOB='PRESIDENT')AND COMM IS NOT NULL AND SAL>ALL(SELECT MAX(SAL)
  6                                                             FROM EMP
  7*                                                           WHERE JOB='CLERK')
SQL> /

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE HIREDATE>ALL(SELECT MIN(HIREDATE)+(365*2)
  4                     FROM EMP) AND SAL>(SELECT SAL
  5                                        FROM EMP
  6                                        WHERE ENAME='BLAKE');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE MGR IN(SELECT EMPNO
  6                               FROM EMP
  7                               WHERE ENAME='BLAKE'));

LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE JOB IN(SELECT JOB
  4               FROM EMP
  5               WHERE ENAME='JONES') AND SAL<(SELECT SAL
  6                                             FROM EMP
  7                                             WHERE ENAME='SCOTT');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CLARK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT SAL*12 AS 1ST_YEAR_SAL,DNAME,LOC
  2  FROM EMP, DEPT
  3  WHERE EMP.DEPTNO = DEPT.DEPTNO AND ENAME='KING';
SELECT SAL*12 AS 1ST_YEAR_SAL,DNAME,LOC
                 *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected 


SQL> SELECT SAL*12,ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN(20,30) AND DEPTNO IN(SELECT DEPTNO
  4                                       FROM EMP
  5                                       GROUP BY DEPTNO
  6                                       HAVING COUNT(*)>=3);

    SAL*12 ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
     11400 JAMES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
     18000 TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
     34200 BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
     15000 MARTIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
     15000 WARD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
     19200 ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
     36000 FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
     13200 ADAMS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
     36000 SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
     35700 JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
      9600 SMITH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

11 rows selected.

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL < ANY(SELECT SAL
  4                  FROM EMP
  5                  WHERE JOB='SALESMAN');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
SMITH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
JAMES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
ADAMS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
WARD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
MARTIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MILLER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

7 rows selected.

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE HIREDATE < (SELECT MAX(HIREDATE)
  4                    FROM EMP);

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
SMITH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
WARD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
MARTIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
CLARK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
JAMES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
MILLER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

13 rows selected.

SQL> SELECT MIN(SAL)
  2  FROM EMP
  3  WHERE SAL > (SELECT MIN(SAL)
  4               FROM EMP
  5               WHERE SAL> (SELECT MIN(SAL)
  6                           FROM EMP));

  MIN(SAL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      1100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL > ANY (SELECT SAL
  4                   FROM EMP
  5                   WHERE JOB='MANAGER');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE HIREDATE > (SELECT MIN(HIREDATE)+(365*4)
  4                    FROM EMP) AND SAL<(SELECT SAL
  5                                       FROM EMP
  6                                       WHERE ENAME='BLAKE');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ADAMS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT DNAME,DEPTNO
  2  FROM DEPT
  3  WHERE LOC='NEW YORK';

DNAME              DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-------------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
ACCOUNTING             10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE ENAME LIKE 'A%' AND SAL BETWEEN 1000 AND 3000);

LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE MGR IN(SELECT EMPNO
  6                               FROM EMP
  7                               WHERE ENAME='BLAKE'));

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT DNAME,LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE EMPNO IN(SELECT MGR
  6                                 FROM EMP
  7                                 WHERE EMPNO IN(SELECT MGR
  8                                                FROM EMP
  9                                                WHERE ENAME='MARTIN')));

DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT ENAME,JOB,DNAME,DEPTNO
  2  FROM EMP, DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.COMM IS NULL;
SELECT ENAME,JOB,DNAME,DEPTNO
                       *
ERROR at line 1:
ORA-00918: column ambiguously defined 


SQL> SELECT ENAME,JOB,DNAME
  2  FROM EMP,DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.COMM IS NULL;

ENAME      JOB       DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
---------- --------- --------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
SMITH      CLERK     RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
JONES      MANAGER   RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
BLAKE      MANAGER   SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CLARK      MANAGER   ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
SCOTT      ANALYST   RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
KING       PRESIDENT ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
ADAMS      CLERK     RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
JAMES      CLERK     SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
FORD       ANALYST   RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
MILLER     CLERK     ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

10 rows selected.

SQL> SELECT ENAME,JOB,DNAME
  2  FROM EMP,DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE LOC IN('CHICAGO','DALLAS');
                WHERE LOC IN('CHICAGO','DALLAS')
                                               *
ERROR at line 5:
ORA-00907: missing right parenthesis 


SQL> 
SQL> SELECT MGR,JOB,DEPTNO
  2  FROM EMP
  3  WHERE COMM IS NOT NULL AND DEPTNO IN(SELECT DEPTNO
  4                                        FROM DEPT
  5                                        WHERE LOC IN('CHICAGO','DALLAS');
                                      WHERE LOC IN('CHICAGO','DALLAS')
                                                                     *
ERROR at line 5:
ORA-00907: missing right parenthesis 


SQL> ED
Wrote file afiedt.buf

  1  SELECT MGR,JOB,DEPTNO
  2  FROM EMP
  3  WHERE COMM IS NOT NULL AND DEPTNO IN(SELECT DEPTNO
  4                                        FROM DEPT
  5*                                       WHERE LOC='CHICAGO' OR LOC='DALLAS');
SQL> /
                                      WHERE LOC='CHICAGO' OR LOC='DALLAS');
                                                                          *
ERROR at line 5:
ORA-00911: invalid character 


SQL> SELECT MGR,JOB,DEPTNO
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE LOC IN('CHICAGO','DALLAS') AND DEPTNO IN(SELECT DEPTNO
  6                                                                 FROM EMP
  7                                                                 WHERE COMM IS NOT NULL));

       MGR JOB           DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 CLERK             30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7839 MANAGER           30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

6 rows selected.

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
---------- -------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
        10 ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
        20 RESEARCH       DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        30 SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        40 OPERATIONS     BOSTON                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT MGR,JOB,DEPTNO
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE LOC='CHICAGO' OR LOC='DALLAS') AND COMM IS NOT NULL;

       MGR JOB           DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7698 SALESMAN          30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

SQL> SELECT *
  2  FROM EMP
  3  WHERE ;
WHERE 
     *
ERROR at line 3:
ORA-00936: missing expression 


SQL> SELECT EMP.*,SAL*12
  2  FROM EMP
  3  WHERE SAL IN(SELECT MAX(COMM)
  4               FROM EMP);

no rows selected

SQL> SELECT EMP.*,SAL*12
  2  FROM EMP
  3  WHERE COMM IN(SELECT MAX(COMM)
  4                FROM EMP);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     SAL*12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      15000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE COMM IS NOT NULL AND HIREDATE <(SELECT MAX(HIREDATE)
  4                                        FROM EMP) AND DEPTNO IN(SELECT DEPTNO
  5                                                                 FROM DEPT
  6                                                                 WHERE DNAME='SALES');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
WARD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
MARTIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT MGR
  4                  FROM EMP
  5                  WHERE EMPNO IN(SELECT MGR
  6                                 FROM EMP
  7                                 WHERE ENAME='WARD'));

no rows selected

SQL> SELECT DNAME,MAX(COMM)
  2  FROM EMP,DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.SAL > ALL(SELECT AVG(SAL)
  4                                                 FROM EMP
  5                                                 WHERE JOB='CLERK'
  6                                                 GROUP BY JOB);
SELECT DNAME,MAX(COMM)
       *
ERROR at line 1:
ORA-00937: not a single-group group function 


SQL> ED
Wrote file afiedt.buf

  1  SELECT DNAME,MAX(EMP.COMM)
  2  FROM EMP,DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.SAL > ALL(SELECT AVG(SAL)
  4                                                 FROM EMP
  5                                                 WHERE JOB='CLERK'
  6*                                                GROUP BY JOB)
SQL> /
SELECT DNAME,MAX(EMP.COMM)
       *
ERROR at line 1:
ORA-00937: not a single-group group function 


SQL> ED
Wrote file afiedt.buf

  1  SELECT DEPT.DNAME,MAX(EMP.COMM)
  2  FROM EMP,DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.SAL > ALL(SELECT AVG(SAL)
  4                                                 FROM EMP
  5                                                 WHERE JOB='CLERK'
  6*                                                GROUP BY JOB)
SQL> /
SELECT DEPT.DNAME,MAX(EMP.COMM)
       *
ERROR at line 1:
ORA-00937: not a single-group group function 


SQL> SELECT DNAME
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE SAL>ALL(SELECT AVG(SAL)
  6                                FROM EMP
  7                                WHERE JOB='CLERK'));
SELECT DNAME
       *
ERROR at line 1:
ORA-00904: "DNAME": invalid identifier 


SQL> ED
Wrote file afiedt.buf

  1  SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE SAL>ALL(SELECT AVG(SAL)
  6                                FROM EMP
  7*                               WHERE JOB='CLERK'))
SQL> /

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT SAL+SAL*(25/100)
  2  FROM EMP
  3  WHERE HIREDATE IN(SELECT MAX(HIREDATE)
  4                    FROM EMP);

SAL+SAL*(25/100)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
----------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            1375                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> ED
Wrote file afiedt.buf

  1  SELECT EMP.*,SAL+SAL*(25/100)
  2  FROM EMP
  3  WHERE HIREDATE IN(SELECT MAX(HIREDATE)
  4*                   FROM EMP)
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO SAL+SAL*(25/100)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20             1375                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> SELECT DEPTNO
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME='SALES') AND DEPTNO IN(SELECT DEPTNO
  6                                                     FROM EMP
  7                                                     WHERE JOB='MANAGER');

    DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
        30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE SAL=(SELECT MIN(SAL)
  6                             FROM EMP
  7                             WHERE MGR IS NOT NULL));

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

SQL> SELECT HIRADATE,JOB
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME='SALES');
SELECT HIRADATE,JOB
       *
ERROR at line 1:
ORA-00904: "HIRADATE": invalid identifier 


SQL> ED
Wrote file afiedt.buf

  1  SELECT HIREDATE,JOB
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5*                 WHERE DNAME='SALES')
SQL> /

HIREDATE  JOB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
--------- ---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
20-FEB-81 SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
22-FEB-81 SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
28-SEP-81 SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
01-MAY-81 MANAGER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
08-SEP-81 SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
03-DEC-81 CLERK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

6 rows selected.

SQL> SELECT DNAME,LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='PRESIDENT');

DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT DENAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE SAL IN(SELECT MAX(SAL)
  6                               FROM EMP
  7                               WHERE SAL IN(SELECT SAL
  8                                            FROM EMP
  9                                            WHERE SAL<3000)));
SELECT DENAME
       *
ERROR at line 1:
ORA-00904: "DENAME": invalid identifier 


SQL> ED
Wrote file afiedt.buf

  1  SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE SAL IN(SELECT MAX(SAL)
  6                               FROM EMP
  7                               WHERE SAL IN(SELECT SAL
  8                                            FROM EMP
  9*                                           WHERE SAL<3000)))
SQL> /

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE MGR IN(SELECT EMPNO
  6                               FROM EMP WHERE ENAME='ADAMS'));

no rows selected

SQL> SELECT *
  2  FROM EMP
  3  WHERE HIREDATE IN(SELECT MAX(HIREDATE)
  4                    FROM EMP);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL >(SELECT AVG(SAL)
  4              FROM EMP
  5              WHERE DEPTNO=30);

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
CLARK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

7 rows selected.

SQL> SELECT COUNT(*)
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME='RESEARCH') AND SAL < ANY(SELECT SAL
  6                                                        FROM EMP
  7                                                        WHERE DEPTNO=10);

  COUNT(*)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='CLERK')'
  6  ;
ERROR:
ORA-01756: quoted string not properly terminated 


SQL> ED
Wrote file afiedt.buf

  1  SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5*                 WHERE JOB='CLERK')
  6  /

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DNAME LIKE '%L%';

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE HIREDATE >(SELECT HIREDATE
  4                   FROM EMP
  5                   WHERE ENAME='BLAKE');;
                 WHERE ENAME='BLAKE');
                                     *
ERROR at line 5:
ORA-00911: invalid character 


SQL> L
  1  SELECT ENAME
  2  FROM EMP
  3  WHERE HIREDATE >(SELECT HIREDATE
  4                   FROM EMP
  5*                  WHERE ENAME='BLAKE');
SQL> /
                 WHERE ENAME='BLAKE');
                                     *
ERROR at line 5:
ORA-00911: invalid character 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE HIREDATE >(SELECT HIREDATE
  4                   FROM EMP
  5*                  WHERE ENAME='BLAKE')
SQL> /

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
MARTIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
CLARK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
ADAMS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
JAMES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
MILLER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

9 rows selected.

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  GROUP BY DEPTNO
  6                  HAVING COUNT(*)
  7                  BETWEEN 3 AND 5);

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE MGR IN(SELECT EMPNO
  6                                FROM EMP
  7                                WHERE SAL>2000));

LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

SQL> SELECT ENAME,DNAME
  2  FROM EMP,DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME LIKE '%E%E%';

ENAME      DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------- --------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
SMITH      RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
JONES      RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
SCOTT      RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
ADAMS      RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
FORD       RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE LOC='CHICAGO');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
WARD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
MARTIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
JAMES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

6 rows selected.

SQL> SELECT ENAME,SAL
  2  FROM EMP
  3  WHERE SAL>ANY(SELECT SAL
  4                FROM EMP
  5                WHERE JOB='ANALYST');

ENAME             SAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING             5000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL = (SELECT MIN(SAL)
  4               FROM EMP
  5               WHERE DEPTNO IN(SELECT DEPTNO
  6                               FROM DEPT
  7                               WHERE DNAME='RESEARCH'));

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
SMITH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='SALESMAN');

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  GROUP BY DEPTNO
  6                  HAVING COUNT(*)>=3);

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME IN('ACCOUNTING','RESEARCH')) AND  EMPNO IN(SELECT MGR
  6                                                                         FROM EMP
  7                                                                         GROUP BY MGR
  8                                                                         HAVING COUNT(*)>=2);

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT ENAME,JOB,LOC
  2  FROM EMP
  3  ,DEPT
  4  WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB='MANAGER' AND LOC='CHICAGO';

ENAME      JOB       LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
---------- --------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
BLAKE      MANAGER   CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

SQL> SELECT ENAME,DNAME
  2  FROM EMP,DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.SAL IN(SELECT MAX(SAL)
  4                                              FROM EMP
  5                                              WHERE SAL<(SELECT MAX(SAL)
  6                                                          FROM EMP) AND LOC='DALLAS';
                                                        FROM EMP) AND LOC='DALLAS'
                                                                                 *
ERROR at line 6:
ORA-00907: missing right parenthesis 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME,DNAME
  2  FROM EMP,DEPT
  3  WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.SAL IN(SELECT MAX(SAL)
  4                                              FROM EMP
  5                                              WHERE SAL<(SELECT MAX(SAL)
  6*                                                         FROM EMP) AND LOC='DALLAS')
SQL> /

ENAME      DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------- --------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
SCOTT      RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
FORD       RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

SQL> SELECT *
  2  FROM EMP
  3  WHERE COMM IS NULL AND HIREDATE > '31-JULY-83';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME IN('SALES','RESEARCH')) AND EMPNO IN(SELECT MGR
  6                                                                   FROM EMP
  7                                                                   GROUP BY MGR
  8                                                                   HAVING COUNT(*)>=2);

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE MGR != (SELECT EMPNO
  4                FROM EMP
  5                WHERE ENAME='KING') AND COMM IS NOT NULL AND SAL > ALL(SELECT MAX(SAL)
  6                                                                       FROM EMP
  7                                                                       WHERE JOB='SALESMAN');

no rows selected

SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-81');

LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT MIN(SAL)
  2  FROM EMP
  3  WHERE SAL < (SELECT AVG(SAL)
  4               FROM EMP
  5              )
  6  GROUP BY DEPTNO;

  MIN(SAL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
       950                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
       800                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      1300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> ED
Wrote file afiedt.buf

  1  SELECT MIN(SAL)
  2  FROM EMP
  3  WHERE SAL < (SELECT AVG(SAL)
  4               FROM EMP)
  5* GROUP BY DEPTNO
SQL> /

  MIN(SAL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
       950                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
       800                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      1300                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE EMPNO IN(SELECT MGR
  4                 FROM EMP
  5                 WHERE ENAME='JONES');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE MGR IN(SELECT EMPNO
  4                 FROM EMP
  5*                WHERE ENAME='JONES')
SQL> /

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT *
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE LOC LIKE '%O%O%');
                WHERE LOC LIKE '%O%O%')
                      *
ERROR at line 5:
ORA-00904: "LOC": invalid identifier 


SQL> SELECT *
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE LOC LIKE '%O%O%');

no rows selected

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
---------- -------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
        10 ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
        20 RESEARCH       DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        30 SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        40 OPERATIONS     BOSTON                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN 10 AND SAL > (SELECT SAL
  4                                FROM EMP);
WHERE DEPTNO IN 10 AND SAL > (SELECT SAL
                              *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN 10 AND SAL > (SELECT SAL
  4                                FROM EMP
  5*                               WHERE DEPTNO IN(20,30,40))
SQL> /
WHERE DEPTNO IN 10 AND SAL > (SELECT SAL
                              *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row 


SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN 10 AND E1.SAL >(SELECT E1.SAL
  4                                  FROM EMP E1
  5                                  WHERE EMP.DEPTNO != E1.DEPTNO );
WHERE DEPTNO IN 10 AND E1.SAL >(SELECT E1.SAL
                       *
ERROR at line 3:
ORA-00904: "E1"."SAL": invalid identifier 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN 10 AND EMP.SAL >(SELECT E1.SAL
  4                                  FROM EMP E1
  5*                                 WHERE EMP.DEPTNO != E1.DEPTNO )
SQL> /
WHERE DEPTNO IN 10 AND EMP.SAL >(SELECT E1.SAL
                                 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row 


SQL> SELECT E.ENAME
  2  FROM EMP E
  3  WHERE E.DEPTNO IN 10 AND E.SAL> (SELECT E1.SAL
  4                                   FROM EMP E1
  5                                   WHERE E.DEPTNO != E1.DEPTNO);
WHERE E.DEPTNO IN 10 AND E.SAL> (SELECT E1.SAL
                                 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row 


SQL> ED
Wrote file afiedt.buf

  1  SELECT E.ENAME
  2  FROM EMP E
  3  WHERE E.DEPTNO IN 10 AND E.SAL>ALL (SELECT E1.SAL
  4                                   FROM EMP E1
  5*                                  WHERE E.DEPTNO != E1.DEPTNO)
SQL> /

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL IN(SELECT MAX(SAL)
  4               FROM EMP
  5               )
  6  GROUP BY JOB;
SELECT ENAME
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE SAL IN(SELECT MAX(SAL)
  4               FROM EMP
  5*              GROUP BY JOB)
SQL> /

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
MILLER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

6 rows selected.

SQL> SELECT EMPNO,ENAME
  2  FROM EMP
  3  WHERE JOB IN 'CLERK' AND SAL IN(SELECT MAX(SAL)
  4                                  FROM EMP
  5                                  WHERE JOB='CLERK');

     EMPNO ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT HIREDATE
  2  FROM EMP
  3  WHERE EMPNO IN(SELECT MGR
  4                 FROM EMP
  5                 WHERE EMPNO IN(SELECT MGR
  6                                FROM EMP
  7                                WHERE ENAME='SMITH'));

HIREDATE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
02-APR-81                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

SQL> SELECT COUNT(*)
  2  FROM EMP
  3  WHERE JOB='SALESMAN' AND DEPTNO IN(SELECT DEPTNO
  4                                     FROM DEPT
  5                                     WHERE LOC IN('NEWYORK','CHICAGO');
                                   WHERE LOC IN('NEWYORK','CHICAGO')
                                                                   *
ERROR at line 5:
ORA-00907: missing right parenthesis 


SQL> ED
Wrote file afiedt.buf

  1  SELECT COUNT(*)
  2  FROM EMP
  3  WHERE JOB='SALESMAN' AND DEPTNO IN(SELECT LOC
  4                                     FROM DEPT
  5*                                    WHERE LOC IN('NEWYORK','CHICAGO')
SQL> /
                                   WHERE LOC IN('NEWYORK','CHICAGO')
                                                                   *
ERROR at line 5:
ORA-00907: missing right parenthesis 


SQL> SELECT COUNT(*)
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE LOC IN('NEW YORK','CHICAGO') AND JOB='SALESMAN';
                WHERE LOC IN('NEW YORK','CHICAGO') AND JOB='SALESMAN'
                                                                    *
ERROR at line 5:
ORA-00907: missing right parenthesis 


SQL> SELECT COUNT(*)
  2  FROM EMP
  3  WHERE JOB='SALESMAN' AND DEPTNO IN(SELECT DEPTNO
  4                                     FROM DEPT
  5                                     WHERE LOC IN('NEW YORK','CHICAGO');
                                   WHERE LOC IN('NEW YORK','CHICAGO')
                                                                    *
ERROR at line 5:
ORA-00907: missing right parenthesis 


SQL> SELECT COUNT(*)
  2  FROM EMP
  3  WHERE JOB='SALESMAN' AND DEPTNO IN(SELECT DEPTNO
  4                                     FROM DEPT
  5                                     WHERE LOC='NEW YORK' AND LOC='CHICAGO');

  COUNT(*)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
---------- -------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
        10 ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
        20 RESEARCH       DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        30 SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        40 OPERATIONS     BOSTON                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-82' AND SAL < 1800);

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> ED
Wrote file afiedt.buf

  1  SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5*                 WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-82' AND SAL > 1800)
SQL> /

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE COMM IS NULL AND SAL IN(SELECT MAX(SAL)
  6                                                FROM EMP);
                                              FROM EMP)
                                                      *
ERROR at line 6:
ORA-00907: missing right parenthesis 


SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE SAL IN(SELECT MAX(SAL)
  6                               FROM EMP
  7                               WHERE COMM IS NULL));

LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
---------- -------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
        10 ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
        20 RESEARCH       DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        30 SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        40 OPERATIONS     BOSTON                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME='ACCOUNTING') AND SAL > (SELECT AVG(SAL)
  6                                                       FROM EMP);

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CLARK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL > (SELECT AVG(SAL)
  4               FROM EMP
  5               WHERE DEPTNO IN(SELECT DEPTNO
  6                               FROM DEPT
  7                               WHERE DNAME IN='ACCOUNTING'));
                             WHERE DNAME IN='ACCOUNTING'))
                                           *
ERROR at line 7:
ORA-00936: missing expression 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE SAL > (SELECT AVG(SAL)
  4               FROM EMP
  5               WHERE DEPTNO IN(SELECT DEPTNO
  6                               FROM DEPT
  7*                              WHERE DNAME='ACCOUNTING'))
SQL> /

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

SQL> SELECT*FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
---------- -------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
        10 ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
        20 RESEARCH       DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        30 SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        40 OPERATIONS     BOSTON                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> SELECT AVG(SAL)
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME='ACCOUNTING');

  AVG(SAL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
2916.66667                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE COMM IS NOT NULL);

LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE MGR != (SELECT EMPNO
  4                FROM EMP
  5                WHERE JOB='PRESIDENT') AND COMM IS NOT NULL AND SAL > ALL(SELECT MAX(SAL)
  6                                                                          FROM EMP
  7                                                                          WHERE JOB='CLERK');

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TURNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL > (SELECT AVG(SAL)
  4               FROM EMP
  5               WHERE DEPTNO=30);

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
ALLEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
CLARK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

7 rows selected.

SQL> SELECT DNAME,LOC
  2  FROM DEPT
  3  WHERE JOB='CLERK' AND MGR IN(SELECT EMPNO
  4                               FROM EMP
  5                               WHERE ENAME='BLAKE' AND SAL <(SELECT SAL
  6                                                             FROM EMP
  7                                                             WHERE ENAME='MARTIN'));
WHERE JOB='CLERK' AND MGR IN(SELECT EMPNO
                      *
ERROR at line 3:
ORA-00904: "MGR": invalid identifier 


SQL> SELECT DNAME,LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='CLERK' AND MGR IN(SELECT EMPNO
  6                                               FROM EMP
  7                                               WHERE ENAME='BLAKE' AND SAL<(SELECT SAL
  8                                                                            FROM EMP
  9                                                                            WHERE ENAME='MARTIN')));

no rows selected

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL>(SELECT AVG(SAL)
  4             FROM EMP
  5             WHERE DEPTNO=20);

ENAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
JONES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
BLAKE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
CLARK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
SCOTT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
KING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
FORD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

6 rows selected.

SQL> SELECT LOC,DNAME
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='CLERK' AND SAL<(SELECT SAL
  6                                             FROM EMP
  7                                             WHERE JOB='CLERK'));
SELECT LOC,DNAME
           *
ERROR at line 1:
ORA-00904: "DNAME": invalid identifier 


SQL> SELECT LOC,DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='MANAGER' AND SAL<(SELECT SAL
  6                                               FROM EMP
  7                                               WHERE JOB='CLERK'));
                WHERE JOB='MANAGER' AND SAL<(SELECT SAL
                                             *
ERROR at line 5:
ORA-01427: single-row subquery returns more than one row 


SQL> ED
Wrote file afiedt.buf

  1  SELECT LOC,DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='MANAGER' AND SAL<ALL(SELECT SAL
  6                                               FROM EMP
  7*                                              WHERE JOB='CLERK'))
SQL> /

no rows selected

SQL> SELECT LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE COMM IS NOT NULL);

LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT DNAME,LOC
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='CLERK' AND  MGR IN(SELECT EMPNO
  6                                                FROM EMP
  7                                                WHERE ENAME='BLAKE') AND SAL<(SELECT SAL
  8                                                                              FROM EMP
  9                                                                              WHERE ENAME='MARTIN'));

DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> SELECT EMPNO,ENAME,JOB
  2  FROM EMP
  3  WHERE JOB LIKE '%E%'
  4  ORDER BY EMPNO DESC;

     EMPNO ENAME      JOB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
---------- ---------- ---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7934 MILLER     CLERK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
      7900 JAMES      CLERK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
      7876 ADAMS      CLERK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
      7844 TURNER     SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
      7839 KING       PRESIDENT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      7782 CLARK      MANAGER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      7698 BLAKE      MANAGER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      7654 MARTIN     SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
      7566 JONES      MANAGER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      7521 WARD       SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
      7499 ALLEN      SALESMAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
      7369 SMITH      CLERK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

12 rows selected.

SQL> SELECT DNAME,LOC,DEPTNO
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE MGR IN(SELECT MGR
  6                               FROM EMP
  7                               WHERE MGR IS NOT NULL
  8                               GROUP BY MGR
  9                               HAVING COUNT(MGR)>1));

DNAME          LOC               DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
-------------- ------------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
SALES          CHICAGO               30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
RESEARCH       DALLAS                20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
ACCOUNTING     NEW YORK              10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT AVG(SAL)
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME='ACCOUNTING');

  AVG(SAL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
2916.66667                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT *
  2  FROM EMP
  3  WHERE HIREDATE BETWEEN '01-JAN-81' AND '31-DEC-81';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

10 rows selected.

SQL> SELECT EMP.*,SAL+SAL*35/100
  2  FROM EMP
  3  WHERE ENAME='SMITH' OR JOB='PRESIDENT';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO SAL+SAL*35/100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20           1080                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      7839 KING       PRESIDENT            17-NOV-81       5000                    10           6750                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

SQL> SELECT COUNT(*)
  2  FROM EMP
  3  WHERE COMM > (SELECT SAL
  4                FROM EMP);
WHERE COMM > (SELECT SAL
              *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row 


SQL> ED
Wrote file afiedt.buf

  1  SELECT COUNT(*)
  2  FROM EMP
  3  WHERE COMM > ALL (SELECT SAL
  4*               FROM EMP)
SQL> /

  COUNT(*)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT COUNT(*)
  2  FROM EMP
  3  WHERE COMM>ALL(SELECT SAL
  4                 FROM EMP);

  COUNT(*)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> ED
Wrote file afiedt.buf

  1  SELECT COUNT(*)
  2  FROM EMP
  3  WHERE COMM>ANY(SELECT SAL
  4*                FROM EMP)
SQL> /

  COUNT(*)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
         1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL/30 > 1500 AND HIREDATE(SELECT SAL
  4                                   FROM EMP);
WHERE SAL/30 > 1500 AND HIREDATE(SELECT SAL
                                 *
ERROR at line 3:
ORA-00936: missing expression 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE SAL/30 > 1500 AND HIREDATE IN(SELECT SAL
  4*                                     FROM EMP)
SQL> /
WHERE SAL/30 > 1500 AND HIREDATE IN(SELECT SAL
                                 *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 


SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE SAL/30 >1500 AND HIREDATE < 82;
WHERE SAL/30 >1500 AND HIREDATE < 82
                                *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3* WHERE SAL/30 >1500 AND HIREDATE < '31-DEC-82'
SQL> /

no rows selected

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE JOB IN(SELECT JOB
  4               FROM EMP
  5               WHERE ENAME='SMITH') AND DEPTNO IN(SELECT DEPTNO
  6                                                  FROM EMP
  7                                                  WHERE ENAME='JONES') AND SAL>(SELECT SAL
  8                                                                                FROM EMP
  9                                                                                WHERE ENAME='TURNER');

no rows selected

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

14 rows selected.

SQL> SELECT ENAME
  2  FROM EMP
  3  WHERE ENAME LIKE 'S%' AND SAL >(SELECT SAL
  4                                  FROM EMP
  5                                  WHERE ENAME='ALLEN') SAL<(SELECT SAL
  6                                                            FROM EMP
  7                                                            WHERE ENAME='FORD');
                                WHERE ENAME='ALLEN') SAL<(SELECT SAL
                                                     *
ERROR at line 5:
ORA-00933: SQL command not properly ended 


SQL> ED
Wrote file afiedt.buf

  1  SELECT ENAME
  2  FROM EMP
  3  WHERE ENAME LIKE 'S%' AND SAL >(SELECT SAL
  4                                  FROM EMP
  5                                  WHERE ENAME='ALLEN') AND SAL<(SELECT SAL
  6                                                            FROM EMP
  7*                                                           WHERE ENAME='FORD')
SQL> /

no rows selected

SQL> SELECT JOB
  2  FROM EMP
  3  WHERE JOB IN('CLERK','ANALYST') AND DEPTNO IN(SELECT DEPTNO
  4                                                FROM DEPT
  5                                                WHERE LOC != 'DALLAS');

JOB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
CLERK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
CLERK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
---------- -------------- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
        10 ACCOUNTING     NEW YORK                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
        20 RESEARCH       DALLAS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        30 SALES          CHICAGO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        40 OPERATIONS     BOSTON                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE EMPNO IN(SELECT MGR
  6                                 FROM EMP
  7                                 GROUP BY MGR
  8                                 HAVING COUNT(MGR)>=1));

DNAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
--------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
SALES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
RESEARCH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
ACCOUNTING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> ED
Wrote file afiedt.buf

  1  SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='MANAGER' 
  6                  GROUP BY JOB
  7*                 HAVING COUNT(*)>=1)
SQL> /
WHERE DEPTNO IN(SELECT DEPTNO
                       *
ERROR at line 3:
ORA-00979: not a GROUP BY expression 


SQL> ED
Wrote file afiedt.buf

  1  SELECT DNAME
  2  FROM DEPT
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM EMP
  5                  WHERE JOB='MANAGER' 
  6                  GROUP BY JOB
  7*                 HAVING COUNT(*)>=1)
SQL> /
WHERE DEPTNO IN(SELECT DEPTNO
                       *
ERROR at line 3:
ORA-00979: not a GROUP BY expression 


SQL> SELECT MAX(SAL)
  2  FROM EMP
  3  WHERE SAL ANY(SELECT SAL
  4                FROM EMP
  5                WHERE JOB='MANAGER');
WHERE SAL ANY(SELECT SAL
          *
ERROR at line 3:
ORA-00920: invalid relational operator 


SQL> SELECT MAX(SAL)
  2  FROM EMP
  3  WHERE DEPTNO IN(SELECT DEPTNO
  4                  FROM DEPT
  5                  WHERE DNAME='SALES');

  MAX(SAL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      2850                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> SELECT MAX(SAL)
  2  FROM EMP
  3  WHERE SAL<(SELECT MAX(SAL)
  4             FROM EMP);

  MAX(SAL)