PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

SQL's using SQL Hierarchy Queries

Sub Queries


SQL> cl scr 
 
SQL> SELECT ColumnName 
  2  FROM TableName 
  3  WHERE ColumnName = ( 
  4      SELECT ColumnName 
  5      FROM TableName 
  6      WHERE ColumnName = Value 
  7     ) 
  8   
SQL> SELECT ColumnName 
  2  FROM TableName, ( 
  3    SELECT ColumName 
  4    FROM TableName 
  5    WHERE ColumnName = Value 
  6    ) 
  7* WHERE ColumnName = Value 
  8   
SQL> cl scr 
 
SQL> SELECT ColumnName, ( 
  2      SELECT ColumnName 
  3      FROM TableName 
  4      WHERE ColumnName = Value 
  5     ) 
  6  FROM TableName 
  7   
SQL> SELECT ColumnName, ( 
  2      SELECT ColumnName 
  3      FROM TableName 
  4      WHERE ColumnName = Value 
  5     ) 
  6  FROM TableName, ( 
  7     SELECT ColumnName 
  8     FROM TableName 
  9     WHERE ColumnName = Value 
 10     ) 
 11  WHERE ColumnName = ( 
 12      SELECT ColumnName 
 13      FROM TableName 
 14      WHERE ColumnName = ( 
 15        SELECT ColumnName 
 16        FROM TableName 
 17        WHERE ColumnName = Value 
 18        ) 
 19     ) 
 20   
SQL> cl scr 
 
SQL> COLUMN Empno FORMAT 9999 
SQL> COLUMN MGR FORMAT 9999 
SQL> COLUMN Deptno FORMAT 99 
SQL> COLUMN Sal FORMAT 9999 
SQL> COLUMN Comm FORMAT 9999 
SQL> SELECT * FROM Emp; 
 
 

EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
SQL> ( 
  2   SELECT * 
  3   FROM Emp 
  4  ); 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  ( 
  2   SELECT * 
  3   FROM Emp 
  4   ORDER BY Sal DESC 
  5* ) 
SQL> / 
 ORDER BY Sal DESC 
 * 
ERROR at line 4: 
ORA-00907: missing right parenthesis  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  ( 
  2   SELECT * 
  3   FROM Emp 
  4  ) 
  5* ORDER BY Sal DESC 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7839 KING       PRESIDENT       17-NOV-81  5000           10                    
 7902 FORD       ANALYST    7566 03-DEC-81  3000           20                    
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000           20                    
 7566 JONES      MANAGER    7839 02-APR-81  2975           20                    
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850           30                    
 7782 CLARK      MANAGER    7839 09-JUN-81  2450           10                    
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300     30                    
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0     30                    
 7934 MILLER     CLERK      7782 23-JAN-82  1300           10                    
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30                    
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30                    
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO                    
----- ---------- --------- ----- --------- ----- ----- ------                    
 7876 ADAMS      CLERK      7788 12-JAN-83  1100           20                    
 7900 JAMES      CLERK      7698 03-DEC-81   950           30                    
 7369 SMITH      CLERK      7902 17-DEC-80   800           20                    
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Sal 
  2     FROM Emp 
  3     WHERE Empno = 7566; 
 
  SAL                                                                            
-----                                                                            
 2975                                                                            
 
SQL> SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Sal > 2975; 
 
ENAME        SAL JOB                                                             
---------- ----- ---------                                                       
 

KING        5000 PRESIDENT                                                       
FORD        3000 ANALYST                                                         
SCOTT       3000 ANALYST                                                         
 
SQL> SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Sal > (SELECT Sal 
  4     FROM Emp 
  5     WHERE Empno = 7566); 
 
ENAME        SAL JOB                                                             
---------- ----- ---------                                                       
KING        5000 PRESIDENT                                                       
FORD        3000 ANALYST                                                         
SCOTT       3000 ANALYST                                                         
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT Sal 
  2                     FROM Emp 
  3                     WHERE Empno = 7566; 
 
  SAL                                                                            
-----                                                                            
 2975                                                                            
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=8)            
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car           
          d=1 Bytes=8)                                                           
                                                                                 
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)           
          ) (Cost=0 Card=1)                                                      
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Sal > 2975; 
 
ENAME        SAL JOB                                                             
---------- ----- ---------                                                       
KING        5000 PRESIDENT                                                       
FORD        3000 ANALYST                                                         
SCOTT       3000 ANALYST                                                         
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=7 Bytes=126           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=7 Bytes=           
          126)                                                                   
 

SQL> SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Sal > (SELECT Sal 
  4                     FROM Emp 
  5                     WHERE Empno = 7566); 
 
ENAME        SAL JOB                                                             
---------- ----- ---------                                                       
KING        5000 PRESIDENT                                                       
FORD        3000 ANALYST                                                         
SCOTT       3000 ANALYST                                                         
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=18)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          18)                                                                    
                                                                                 
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 C           
          ard=1 Bytes=8)                                                         
                                                                                 
   3    2       INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQU           
          E)) (Cost=0 Card=1)                                                    
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT Job 
  2      FROM Emp 
  3      WHERE Ename = 'SMITH'; 
 
JOB                                                                              
---------                                                                        
CLERK                                                                            
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=14)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          14)                                                                    
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Job = 'CLERK'; 
 
 

ENAME        SAL JOB                                                             
---------- ----- ---------                                                       
JAMES        950 CLERK                                                           
SMITH        800 CLERK                                                           
ADAMS       1100 CLERK                                                           
MILLER      1300 CLERK                                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=54)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Bytes=           
          54)                                                                    
                                                                                 
 
 
 
SQL> SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Job = (SELECT Job 
  4      FROM Emp 
  5      WHERE Ename = 'SMITH') 
  6  ORDER BY Sal; 
 
ENAME        SAL JOB                                                             
---------- ----- ---------                                                       
SMITH        800 CLERK                                                           
JAMES        950 CLERK                                                           
ADAMS       1100 CLERK                                                           
MILLER      1300 CLERK                                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=54)           
   1    0   SORT (ORDER BY) (Cost=7 Card=3 Bytes=54)                             
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Byte           
          s=54)                                                                  
                                                                                 
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 By           
          tes=14)                                                                
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Job = (SELECT Job 
  4    FROM Emp 
  5    WHERE Ename = 'SMITH' AND 
  6     Ename <> 'SMITH' 
  7    ) 
  8 ORDER BY Sal 
 

SQL> / 
 
no rows selected 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=54)           
   1    0   SORT (ORDER BY) (Cost=7 Card=3 Bytes=54)                             
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Byte           
          s=54)                                                                  
                                                                                 
   3    2       FILTER                                                           
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1            
          Bytes=14)                                                              
                                                                                 

SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Job = (SELECT Job 
  4    FROM Emp 
  5    WHERE Ename = 'SMITH'  
  6    ) AND 
  7    Ename <> 'SMITH' 
  8 ORDER BY Sal 
SQL> / 
 
ENAME        SAL JOB                                                             
---------- ----- ---------                                                       
JAMES        950 CLERK                                                           
ADAMS       1100 CLERK                                                           
MILLER      1300 CLERK                                                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=54)           
   1    0   SORT (ORDER BY) (Cost=7 Card=3 Bytes=54)                             
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Byte           
          s=54)                                                                  
                                                                                 
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 By           
          tes=14)                                                                
                                                                                 
 
 
 

SQL> cl scr 
 
SQL> SELECT Hiredate 
 

  2        FROM Emp 
  3        WHERE Ename = 'TURNER'; 
 
HIREDATE                                                                         
---------                                                                        
08-SEP-81                                                                        
 
SQL> SELECT Empno, Ename, Hiredate, Sal 
  2  FROM Emp 
  3  WHERE Hiredate > (SELECT Hiredate 
  4        FROM Emp 
  5        WHERE Ename = 'TURNER') 
  6  ORDER BY Sal; 
 
     EMPNO ENAME      HIREDATE         SAL                                       
---------- ---------- --------- ----------                                       
      7900 JAMES      03-DEC-81        950                                       
      7876 ADAMS      12-JAN-83       1100                                       
      7654 MARTIN     28-SEP-81       1250                                       
      7934 MILLER     23-JAN-82       1300                                       
      7902 FORD       03-DEC-81       3000                                       
      7788 SCOTT      09-DEC-82       3000                                       
      7839 KING       17-NOV-81       5000                                       
 
7 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Hiredate, Sal 
  2  FROM Emp 
  3  WHERE Hiredate < (SELECT Hiredate 
  4    FROM Emp 
  5    WHERE Ename = 'TURNER') 
  6 ORDER BY Sal 
SQL> / 
 
     EMPNO ENAME      HIREDATE         SAL                                       
---------- ---------- --------- ----------                                       
      7369 SMITH      17-DEC-80        800                                       
      7521 WARD       22-FEB-81       1250                                       
      7499 ALLEN      20-FEB-81       1600                                       
      7782 CLARK      09-JUN-81       2450                                       
      7698 BLAKE      01-MAY-81       2850                                       
      7566 JONES      02-APR-81       2975                                       
 
6 rows selected. 
 
SQL> cl scr 
 
SQL> 
  1  SELECT Empno, Ename, Hiredate, Sal 
  2  FROM Emp 
  3  WHERE Hiredate < (SELECT Hiredate 
  4    FROM Emp 
  5    WHERE Ename = 'TURNER') 
  6 ORDER BY Sal 
 
     EMPNO ENAME      HIREDATE         SAL                                       
---------- ---------- --------- ----------                                       
      7369 SMITH      17-DEC-80        800                                       
      7521 WARD       22-FEB-81       1250                                       
      7499 ALLEN      20-FEB-81       1600                                       
      7782 CLARK      09-JUN-81       2450                                       
      7698 BLAKE      01-MAY-81       2850                                       
      7566 JONES      02-APR-81       2975                                       
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Hiredate, Sal 
  2  FROM Emp 
  3  WHERE Hiredate = (SELECT Hiredate 
  4    FROM Emp 
  5    WHERE Ename = 'FORD') 
  6 ORDER BY Sal 
SQL> / 
 
     EMPNO ENAME      HIREDATE         SAL                                       
---------- ---------- --------- ----------                                       
      7900 JAMES      03-DEC-81        950                                       
      7902 FORD       03-DEC-81       3000                                       
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Hiredate, Sal 
  2  FROM Emp 
  3  WHERE Hiredate = (SELECT Hiredate 
  4    FROM Emp 
  5    WHERE Ename = 'FORD') AND 
  6    Ename <> 'FORD' 
  7 ORDER BY Sal 
SQL> / 
 
     EMPNO ENAME      HIREDATE         SAL                                       
---------- ---------- --------- ----------                                       
      7900 JAMES      03-DEC-81        950                                       
 
SQL> cl scr 
 
SQL> SELECT Empno, Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Deptno = (SELECT Deptno 
  4      FROM Dept 
  5      WHERE Dname = 'SALES'); 
 
     EMPNO ENAME             SAL JOB                                             
---------- ---------- ---------- ---------                                       
      7698 BLAKE            2850 MANAGER                                         
 

      7654 MARTIN           1250 SALESMAN                                        
      7499 ALLEN            1600 SALESMAN                                        
      7844 TURNER           1500 SALESMAN                                        
      7900 JAMES             950 CLERK                                           
      7521 WARD             1250 SALESMAN                                        
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Sal, Job 
  2  FROM Emp, Dept 
  3  WHERE Emp.Deptno = Dept.Deptno AND 
  4  Dept.Dname = 'SALES' 
SQL> / 
 
     EMPNO ENAME             SAL JOB                                             
---------- ---------- ---------- ---------                                       
      7698 BLAKE            2850 MANAGER                                         
      7654 MARTIN           1250 SALESMAN                                        
      7499 ALLEN            1600 SALESMAN                                        
      7844 TURNER           1500 SALESMAN                                        
      7900 JAMES             950 CLERK                                           
      7521 WARD             1250 SALESMAN                                        
 
6 rows selected. 
 
SQL> SET AUTOTRACE ON EXPLAIN 
SQL> SELECT Empno, Ename, Sal, Job 
  2  FROM Emp, Dept 
  3  WHERE Emp.Deptno = Dept.Deptno AND 
  4     Dept.Dname = 'SALES'; 
 
     EMPNO ENAME             SAL JOB                                             
---------- ---------- ---------- ---------                                       
      7698 BLAKE            2850 MANAGER                                         
      7654 MARTIN           1250 SALESMAN                                        
      7499 ALLEN            1600 SALESMAN                                        
      7844 TURNER           1500 SALESMAN                                        
      7900 JAMES             950 CLERK                                           
      7521 WARD             1250 SALESMAN                                        
 
6 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=5 Bytes=370           
          )                                                                      
                                                                                 
   1    0   HASH JOIN (Cost=7 Card=5 Bytes=370)                                  
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt           
          es=22)                                                                 
                                                                                 
   3    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
 

          es=728)                                                                
                                                                                 
 
 
 
SQL> SELECT Empno, Ename, Sal, Job 
  2  FROM Emp 
  3  WHERE Deptno = (SELECT Deptno 
  4                             FROM Dept 
  5                             WHERE Dname = 'SALES'); 
 
     EMPNO ENAME             SAL JOB                                             
---------- ---------- ---------- ---------                                       
      7698 BLAKE            2850 MANAGER                                         
      7654 MARTIN           1250 SALESMAN                                        
      7499 ALLEN            1600 SALESMAN                                        
      7844 TURNER           1500 SALESMAN                                        
      7900 JAMES             950 CLERK                                           
      7521 WARD             1250 SALESMAN                                        
 
6 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=52)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          52)                                                                    
                                                                                 
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt           
          es=22)                                                                 
                                                                                 
 
 
 
SQL> SELECT Empno, Ename, Sal, Comm, Sal + NVL( Comm, 0 ) TotSal 
  2  FROM Emp 
  3  WHERE Deptno = (SELECT Deptno 
  4      FROM Dept 
  5      WHERE Loc = 'DALLAS'); 
 
     EMPNO ENAME             SAL       COMM     TOTSAL                           
---------- ---------- ---------- ---------- ----------                           
      7566 JONES            2975                  2975                           
      7902 FORD             3000                  3000                           
      7369 SMITH             800                   800                           
      7788 SCOTT            3000                  3000                           
      7876 ADAMS            1100                  1100                           
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=59)           
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=           
          59)                                                                    
                                                                                 
 

   2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt           
          es=21)                                                                 
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SET AUTOTRACE OFF EXPLAIN 
SQL> cl scr 
 
SQL> SELECT MAX(Sal) FROM Emp; 
 
  MAX(SAL)                                                                       
----------                                                                       
      5000                                                                       
 
SQL> SELECT Ename, MAX(Sal) FROM Emp; 
SELECT Ename, MAX(Sal) FROM Emp 
       * 
ERROR at line 1: 
ORA-00937: not a single-group group function  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  
  2  Ename,  
  3  MAX(Sal)  
  4  FROM Emp 
  5 GROUP BY Ename 
SQL> / 
 
ENAME        MAX(SAL)                                                            
---------- ----------                                                            
ADAMS            1100                                                            
ALLEN            1600                                                            
BLAKE            2850                                                            
CLARK            2450                                                            
FORD             3000                                                            
JAMES             950                                                            
JONES            2975                                                            
KING             5000                                                            
MARTIN           1250                                                            
MILLER           1300                                                            
SCOTT            3000                                                            
 
ENAME        MAX(SAL)                                                            
---------- ----------                                                            
SMITH             800                                                            
TURNER           1500                                                            
WARD             1250                                                            
 
14 rows selected. 
 
 

SQL> SELECT MAX(Sal) FROM Emp; 
 
  MAX(SAL)                                                                       
----------                                                                       
      5000                                                                       
 
SQL> SELECT Ename, Sal, Deptno, Job 
  2  FROM Emp 
  3  WHERE Sal = 5000; 
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
KING             5000         10 PRESIDENT                                       
 
SQL> SELECT Ename, Job , Sal 
  2  FROM Emp 
  3  WHERE Sal = (SELECT MAX(Sal) 
  4     FROM Emp); 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
KING       PRESIDENT       5000                                                  
 
SQL> cl scr 
 
SQL> SELECT Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal = (SELECT MIN(Sal) 
  4     FROM Emp); 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
SMITH      CLERK            800                                                  
 
SQL> SELECT Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal > (SELECT AVG(Sal) 
  4     FROM Emp); 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
KING       PRESIDENT       5000                                                  
BLAKE      MANAGER         2850                                                  
CLARK      MANAGER         2450                                                  
JONES      MANAGER         2975                                                  
FORD       ANALYST         3000                                                  
SCOTT      ANALYST         3000                                                  
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal < (SELECT AVG(Sal) 
  4   FROM Emp) 
SQL> / 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
MARTIN     SALESMAN        1250                                                  
ALLEN      SALESMAN        1600                                                  
TURNER     SALESMAN        1500                                                  
JAMES      CLERK            950                                                  
WARD       SALESMAN        1250                                                  
SMITH      CLERK            800                                                  
ADAMS      CLERK           1100                                                  
MILLER     CLERK           1300                                                  
 
8 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal = (SELECT AVG(Sal) 
  4    FROM Emp) 
SQL> / 
 
no rows selected 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal <> (SELECT AVG(Sal) 
  4   FROM Emp) 
SQL> / 
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
KING       PRESIDENT       5000                                                  
BLAKE      MANAGER         2850                                                  
CLARK      MANAGER         2450                                                  
JONES      MANAGER         2975                                                  
MARTIN     SALESMAN        1250                                                  
ALLEN      SALESMAN        1600                                                  
TURNER     SALESMAN        1500                                                  
JAMES      CLERK            950                                                  
WARD       SALESMAN        1250                                                  
FORD       ANALYST         3000                                                  
SMITH      CLERK            800                                                  
 
ENAME      JOB              SAL                                                  
---------- --------- ----------                                                  
SCOTT      ANALYST         3000                                                  
ADAMS      CLERK           1100                                                  
MILLER     CLERK           1300                                                  
 
 

14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal, Job 
  2  FROM Emp 
  3  WHERE Sal = ( 
  4     SELECT MAX(Sal) 
  5     FROM Emp 
  6     WHERE Deptno = 10 
  7     ); 
 
ENAME          DEPTNO        SAL JOB                                             
---------- ---------- ---------- ---------                                       
KING               10       5000 PRESIDENT                                       
 
SQL> cl scr 
 
SQL> SELECT 
  2       MIN(Sal) 
  3       FROM Emp 
  4       WHERE  Deptno = 20; 
 
  MIN(SAL)                                                                       
----------                                                                       
       800                                                                       
 
SQL> SELECT Deptno, MIN(Sal) 
  2  FROM Emp 
  3  GROUP BY Deptno 
  4  HAVING  MIN(Sal) > (SELECT 
  5       MIN(Sal) 
  6       FROM Emp 
  7       WHERE  Deptno = 20); 
 
    DEPTNO   MIN(SAL)                                                            
---------- ----------                                                            
        10       1300                                                            
        30        950                                                            
 
SQL> cl scr 
 
SQL> SELECT AVG(Sal) 
  2  FROM Emp 
  3  GROUP BY Job; 
 
  AVG(SAL)                                                                       
----------                                                                       
      3000                                                                       
    1037.5                                                                       
2758.33333                                                                       
      5000                                                                       
      1400                                                                       
 
SQL> ED 
Wrote file afiedt.buf 
 

 
  1  SELECT MAX(AVG(Sal)) 
  2  FROM Emp 
  3 GROUP BY Job 
SQL> / 
 
MAX(AVG(SAL))                                                                    
-------------                                                                    
         5000                                                                    
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Job, MAX(AVG(Sal)) 
  2  FROM Emp 
  3 GROUP BY Job 
SQL> / 
SELECT Job, MAX(AVG(Sal)) 
       * 
ERROR at line 1: 
ORA-00937: not a single-group group function  
 
 
SQL> SELECT Job, AVG(Sal) 
  2  FROM Emp 
  3  GROUP BY Job 
  4  HAVING AVG(Sal) =  (SELECT 
  5       MIN(AVG(Sal)) 
  6       FROM Emp 
  7       GROUP BY Job); 
 
JOB         AVG(SAL)                                                             
--------- ----------                                                             
CLERK         1037.5                                                             
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Job, AVG(Sal) 
  2  FROM Emp 
  3  GROUP BY Job 
  4  HAVING AVG(Sal) =  (SELECT 
  5    MAX(AVG(Sal)) 
  6    FROM Emp 
  7  GROUP BY Job) 
SQL> / 
 
JOB         AVG(SAL)                                                             
--------- ----------                                                             
PRESIDENT       5000                                                             
 
SQL> cl scr 
 
SQL> COLUMN Empno FORMAT 9999 
SQL> COLUMN MGR FORMAT 9999 
SQL> COLUMN Sal FORMAT 9999 
 

SQL> COLUMN Comm FORMAT 9999 
SQL> cl scr 
 
SQL> SELECT * FROM Emp ORDER BY Sal DESC; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7839 KING       PRESIDENT       17-NOV-81  5000               10                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7876 ADAMS      CLERK      7788 12-JAN-83  1100               20                
 7900 JAMES      CLERK      7698 03-DEC-81   950               30                
 7369 SMITH      CLERK      7902 17-DEC-80   800               20                
 
14 rows selected. 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE 
  4  Sal BETWEEN 
  5    ( 
  6     SELECT Sal 
  7     FROM Emp 
  8     WHERE Ename = 'MILLER' 
  9    ) AND 
 10    ( 
 11     SELECT Sal 
 12     FROM Emp 
 13     WHERE Ename = 'JONES' 
 14    ); 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
 

  1  SELECT * 
  2  FROM Emp 
  3  WHERE 
  4  Sal BETWEEN 
  5    ( 
  6     SELECT Sal 
  7     FROM Emp 
  8     WHERE Ename = 'MILLER' 
  9    ) AND 
 10    ( 
 11     SELECT Sal 
 12     FROM Emp 
 13     WHERE Ename = 'JONES' 
 14    ) AND 
 15 Ename NOT IN('MILLER', 'JONES') 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3  WHERE 
  4  Sal >= ( 
  5   SELECT Sal 
  6   FROM Emp 
  7   WHERE Ename = 'MILLER' 
  8   ) AND 
  9  Sal <= ( 
 10    SELECT Sal 
 11    FROM Emp 
 12    WHERE Ename = 'JONES' 
 13   ) AND 
 14 Ename NOT IN('MILLER', 'JONES') 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3  WHERE 
  4  Sal >= ( 
  5   SELECT Sal 
  6   FROM Emp 
  7   WHERE Ename = 'MILLER' 
  8   ) AND 
  9  Sal <= ( 
 10    SELECT Sal 
 11    FROM Emp 
 12    WHERE Ename = 'JONES' 
 13   ) AND 
 14  Ename NOT IN('MILLER', 'JONES') AND 
 15  Deptno = ( 
 16    SELECT Deptno 
 17    FROM Dept 
 18    WHERE Dname = 'SALES' 
 19  ) 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3  WHERE 
  4  Sal >= ( 
  5   SELECT Sal 
  6   FROM Emp 
  7   WHERE Ename = 'MILLER' 
  8   ) AND 
  9  Sal <= ( 
 10    SELECT Sal 
 11    FROM Emp 
 12    WHERE Ename = 'JONES' 
 13   ) AND 
 14  Ename NOT IN('MILLER', 'JONES') AND 
 15  Deptno = ( 
 16    SELECT Deptno 
 17    FROM Dept 
 18    WHERE Dname = 'SALES' 
 19   ) AND 
 20  Job = ( 
 21   SELECT Job 
 22   FROM Emp 
 23   WHERE Ename = 'ALLEN' 
 24  ) 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 

 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3  WHERE 
  4  Sal >= ( 
  5   SELECT Sal 
  6   FROM Emp 
  7   WHERE Ename = 'MILLER' 
  8   ) AND 
  9  Sal <= ( 
 10    SELECT Sal 
 11    FROM Emp 
 12    WHERE Ename = 'JONES' 
 13   ) AND 
 14  Ename NOT IN('MILLER', 'JONES') AND 
 15  Deptno = ( 
 16    SELECT Deptno 
 17    FROM Dept 
 18    WHERE Dname = 'SALES' 
 19   ) AND 
 20  Job = ( 
 21   SELECT Job 
 22   FROM Emp 
 23   WHERE Ename = 'ALLEN' 
 24   ) AND 
 25 Ename <> 'ALLEN' 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 
SQL> cl scr 
 
SQL> SELECT * FROM Emp; 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7839 KING       PRESIDENT       17-NOV-81  5000               10                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7900 JAMES      CLERK      7698 03-DEC-81   950               30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7369 SMITH      CLERK      7902 17-DEC-80   800               20                
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 

 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 7876 ADAMS      CLERK      7788 12-JAN-83  1100               20                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 
14 rows selected. 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Sal IN 
  4    ( 
  5     SELECT Sal 
  6     FROM Emp 
  7     WHERE Ename = 'WARD' 
  8    , 
  9     SELECT Sal 
 10     FROM Emp 
 11     WHERE Ename = 'FORD' 
 12    ); 
   
 ERROR at line 8: 
ORA-00907: missing right parenthesis  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3  WHERE Sal IN 
  4    ( 
  5     ( 
  6     SELECT Sal 
  7     FROM Emp 
  8     WHERE Ename = 'WARD' 
  9    ), 
 10    ( 
 11     SELECT Sal 
 12     FROM Emp 
 13     WHERE Ename = 'FORD' 
 14    ) 
 15   ) 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 
SQL> cl scr 
 
SQL> Select MIN(Sal) 
  2      FROM Emp 
  3      GROUP BY Deptno; 
 

 
  MIN(SAL)                                                                       
----------                                                                       
      1300                                                                       
       800                                                                       
       950                                                                       
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal IN(Select MIN(Sal) 
  4      FROM Emp 
  5      GROUP BY Deptno); 
 
ENAME        SAL     DEPTNO                                                      
---------- ----- ----------                                                      
MILLER      1300         10                                                      
SMITH        800         20                                                      
JAMES        950         30                                                      
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal) 
  2  VALUES(1234, 'SAMPLE01', 20, 1300); 
 
1 row created. 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal) 
  2  VALUES(1235, 'SAMPLE02', 20, 950); 
 
1 row created. 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal) 
  2  VALUES(1236, 'SAMPLE03', 30, 1300); 
 
1 row created. 
 
SQL> SELECT Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal IN(Select MIN(Sal) 
  4                      FROM Emp 
  5                      GROUP BY Deptno); 
 
ENAME        SAL     DEPTNO                                                      
---------- ----- ----------                                                      
SAMPLE03    1300         30                                                      
SAMPLE01    1300         20                                                      
MILLER      1300         10                                                      
SMITH        800         20                                                      
SAMPLE02     950         20                                                      
JAMES        950         30                                                      
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
 

  1  SELECT Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal IN(Select MAX(Sal) 
  4                      FROM Emp 
  5                     GROUP BY Deptno) 
SQL> / 
 
ENAME        SAL     DEPTNO                                                      
---------- ----- ----------                                                      
KING        5000         10                                                      
SCOTT       3000         20                                                      
FORD        3000         20                                                      
BLAKE       2850         30                                                      
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal IN(Select MAX(Sal) 
  4                      FROM Emp 
  5                    GROUP BY Job) 
SQL> / 
 
ENAME        SAL     DEPTNO                                                      
---------- ----- ----------                                                      
SCOTT       3000         20                                                      
FORD        3000         20                                                      
SAMPLE03    1300         30                                                      
SAMPLE01    1300         20                                                      
MILLER      1300         10                                                      
JONES       2975         20                                                      
KING        5000         10                                                      
ALLEN       1600         30                                                      
 
8 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal IN(Select MIN(Sal) 
  4                      FROM Emp 
  5                     GROUP BY Job) 
SQL> / 
 
ENAME        SAL     DEPTNO                                                      
---------- ----- ----------                                                      
SCOTT       3000         20                                                      
FORD        3000         20                                                      
SMITH        800         20                                                      
CLARK       2450         10                                                      
KING        5000         10                                                      
WARD        1250         30                                                      
MARTIN      1250         30                                                      
 

SAMPLE02     950         20                                                      
JAMES        950         30                                                      
 
9 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal IN(Select AVG(Sal) 
  4                      FROM Emp 
  5                    GROUP BY Job) 
SQL> / 
 
ENAME        SAL     DEPTNO                                                      
---------- ----- ----------                                                      
SCOTT       3000         20                                                      
FORD        3000         20                                                      
KING        5000         10                                                      
 

SQL> cl scr 
 
SQL> SELECT HireDate 
  2           FROM Emp 
  3           WHERE Deptno = 20; 
 
HIREDATE                                                                         
---------                                                                        
02-APR-81                                                                        
03-DEC-81                                                                        
17-DEC-80                                                                        
09-DEC-82                                                                        
12-JAN-83                                                                        
 
SQL> SELECT Sal 
  2      FROM Emp 
  3      WHERE HireDate IN(SELECT HireDate 
  4           FROM Emp 
  5           WHERE Deptno = 20); 
 
       SAL                                                                       
----------                                                                       
      2975                                                                       
      3000                                                                       
       950                                                                       
       800                                                                       
      3000                                                                       
      1100                                                                       
 
6 rows selected. 
 
SQL> SELECT Empno, Ename, Job, Sal, Deptno, HireDate 
  2  FROM Emp 
  3  WHERE Sal IN(SELECT Sal 
  4      FROM Emp 
  5      WHERE HireDate IN(SELECT HireDate 
  6           FROM Emp 
  7           WHERE Deptno = 20) 
  8     ); 
 
     EMPNO ENAME      JOB              SAL     DEPTNO HIREDATE                   
---------- ---------- --------- ---------- ---------- ---------                  
      7566 JONES      MANAGER         2975         20 02-APR-81                  
      7900 JAMES      CLERK            950         30 03-DEC-81                  
      7902 FORD       ANALYST         3000         20 03-DEC-81                  
      7369 SMITH      CLERK            800         20 17-DEC-80                  
      7788 SCOTT      ANALYST         3000         20 09-DEC-82                  
      7876 ADAMS      CLERK           1100         20 12-JAN-83                  
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Job, Sal, Deptno, HireDate 
  2  FROM Emp 
  3  WHERE Sal IN( 
  4   SELECT Sal 
  5   FROM Emp 
  6   WHERE HireDate IN( 
  7     SELECT HireDate 
  8     FROM Emp 
  9     WHERE Deptno = ( 
 10       SELECT Deptno 
 11       FROM Dept 
 12       WHERE Dname = 'RESEARCH' 
 13       ) 
 14     ) 
 15  ) 
SQL> / 
 
     EMPNO ENAME      JOB              SAL     DEPTNO HIREDATE                   
---------- ---------- --------- ---------- ---------- ---------                  
      7566 JONES      MANAGER         2975         20 02-APR-81                  
      7900 JAMES      CLERK            950         30 03-DEC-81                  
      7902 FORD       ANALYST         3000         20 03-DEC-81                  
      7369 SMITH      CLERK            800         20 17-DEC-80                  
      7788 SCOTT      ANALYST         3000         20 09-DEC-82                  
      7876 ADAMS      CLERK           1100         20 12-JAN-83                  
 
6 rows selected. 
 
SQL> cl scr  
SQL> COLUMN Empno FORMAT 9999 
SQL> COLUMN Sal FORMAT 9999 
SQL> COLUMN Comm FORMAT 9999 
SQL> COLUMN MGR FORMAT 9999 
SQL> cl scr 
 

SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Sal >ANY(1100, 2750, 950); 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7839 KING       PRESIDENT       17-NOV-81  5000               10                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 7876 ADAMS      CLERK      7788 12-JAN-83  1100               20                
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 
12 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3 WHERE Sal > 1100 OR Sal > 2750 OR Sal > 950 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7839 KING       PRESIDENT       17-NOV-81  5000               10                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 7876 ADAMS      CLERK      7788 12-JAN-83  1100               20                
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 
12 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Sal >ANY( 
  4     SELECT Sal 
  5     FROM Emp 
  6     WHERE Deptno = 30 
  7     ) 
  8   
SQL> SELECT Sal 
  2  FROM Emp 
  3  WHERE Deptno = 30; 
 
  SAL                                                                            
-----                                                                            
 2850                                                                            
 1250                                                                            
 1600                                                                            
 1500                                                                            
  950                                                                            
 1250                                                                            
 
6 rows selected. 
 
SQL> SELECT * 
  2  FROM Emp 
  3  WHERE Sal >ANY( 
  4                     SELECT Sal 
  5                     FROM Emp 
  6                     WHERE Deptno = 30 
  7                     ); 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7839 KING       PRESIDENT       17-NOV-81  5000               10                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7876 ADAMS      CLERK      7788 12-JAN-83  1100               20                
 
12 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3  WHERE Sal >( 
  4                     SELECT MIN(Sal) 
  5                     FROM Emp 
  6                     WHERE Deptno = 30 
  7                   ) 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7839 KING       PRESIDENT       17-NOV-81  5000               10                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 7876 ADAMS      CLERK      7788 12-JAN-83  1100               20                
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 
12 rows selected. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM Emp 
  3  WHERE Sal >SOME( 
  4                     SELECT Sal 
  5                     FROM Emp 
  6                     WHERE Deptno = 30 
  7                   ) 
SQL> / 
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7839 KING       PRESIDENT       17-NOV-81  5000               10                
 7902 FORD       ANALYST    7566 03-DEC-81  3000               20                
 7788 SCOTT      ANALYST    7566 09-DEC-82  3000               20                
 7566 JONES      MANAGER    7839 02-APR-81  2975               20                
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850               30                
 7782 CLARK      MANAGER    7839 09-JUN-81  2450               10                
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600   300         30                
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500     0         30                
 7934 MILLER     CLERK      7782 23-JAN-82  1300               10                
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400         30                
 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500         30                
 
EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM     DEPTNO                
----- ---------- --------- ----- --------- ----- ----- ----------                
 7876 ADAMS      CLERK      7788 12-JAN-83  1100               20                
 

 
12 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Sal 
  2      FROM Emp 
  3      WHERE Job = 'CLERK'; 
 
  SAL                                                                            
-----                                                                            
  950                                                                            
  800                                                                            
 1100                                                                            
 1300                                                                            
 
SQL> SELECT Empno, Ename, Job 
  2  FROM Emp 
  3  WHERE Sal < ANY(SELECT Sal 
  4      FROM Emp 
  5      WHERE Job = 'CLERK'); 
 
EMPNO ENAME      JOB                                                             
----- ---------- ---------                                                       
 7369 SMITH      CLERK                                                           
 7900 JAMES      CLERK                                                           
 7876 ADAMS      CLERK                                                           
 7654 MARTIN     SALESMAN                                                        
 7521 WARD       SALESMAN                                                        
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal < ANY(SELECT Sal 
  4      FROM Emp 
  5     WHERE Job = 'CLERK') 
SQL> / 
 
EMPNO ENAME      JOB         SAL                                                 
----- ---------- --------- -----                                                 
 7369 SMITH      CLERK       800                                                 
 7900 JAMES      CLERK       950                                                 
 7876 ADAMS      CLERK      1100                                                 
 7654 MARTIN     SALESMAN   1250                                                 
 7521 WARD       SALESMAN   1250                                                 
 
SQL> SELECT Empno, Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal < ANY(Select Sal 
  4      FROM Emp 
  5      WHERE Deptno = 20) 
  6  AND Job <>  'CLERK'; 
 
EMPNO ENAME      JOB         SAL                                                 
 

----- ---------- --------- -----                                                 
 7654 MARTIN     SALESMAN   1250                                                 
 7521 WARD       SALESMAN   1250                                                 
 7844 TURNER     SALESMAN   1500                                                 
 7499 ALLEN      SALESMAN   1600                                                 
 7782 CLARK      MANAGER    2450                                                 
 7698 BLAKE      MANAGER    2850                                                 
 7566 JONES      MANAGER    2975                                                 
 
7 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal < ANY(SELECT Sal 
  4                             FROM Emp 
  5                             WHERE Job = 'CLERK') AND 
  6 Job <> 'CLERK' 
SQL> / 
 
EMPNO ENAME      JOB         SAL                                                 
----- ---------- --------- -----                                                 
 7654 MARTIN     SALESMAN   1250                                                 
 7521 WARD       SALESMAN   1250                                                 
 
SQL> cl scr 
 
SQL> SELECT DISTINCT MGR 
  2          FROM Emp; 
 
  MGR                                                                            
-----                                                                            
 7566                                                                            
 7698                                                                            
 7782                                                                            
 7788                                                                            
 7839                                                                            
 7902                                                                            
                                                                                 
 
7 rows selected. 
 
SQL> SELECT Sal 
  2      FROM Emp 
  3      WHERE MGR IN(SELECT DISTINCT MGR 
  4          FROM Emp); 
 
  SAL                                                                            
-----                                                                            
 2975                                                                            
 2450                                                                            
 2850                                                                            
 1250                                                                            
  950                                                                            
 

 1500                                                                            
 1600                                                                            
 1250                                                                            
 3000                                                                            
 3000                                                                            
  800                                                                            
 
  SAL                                                                            
-----                                                                            
 1100                                                                            
 1300                                                                            
 
13 rows selected. 
 
SQL> SELECT Empno, Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal < ANY(SELECT Sal 
  4      FROM Emp 
  5      WHERE MGR IN(SELECT DISTINCT MGR 
  6          FROM Emp) 
  7      ); 
 
EMPNO ENAME      JOB         SAL                                                 
----- ---------- --------- -----                                                 
 7698 BLAKE      MANAGER    2850                                                 
 7782 CLARK      MANAGER    2450                                                 
 7566 JONES      MANAGER    2975                                                 
 7654 MARTIN     SALESMAN   1250                                                 
 7499 ALLEN      SALESMAN   1600                                                 
 7844 TURNER     SALESMAN   1500                                                 
 7900 JAMES      CLERK       950                                                 
 7521 WARD       SALESMAN   1250                                                 
 7369 SMITH      CLERK       800                                                 
 7876 ADAMS      CLERK      1100                                                 
 7934 MILLER     CLERK      1300                                                 
 
11 rows selected. 
 
SQL> SELECT Sal 
  2      FROM Emp 
  3      WHERE Empno IN(SELECT DISTINCT MGR 
  4          FROM Emp); 
 
  SAL                                                                            
-----                                                                            
 2975                                                                            
 2850                                                                            
 2450                                                                            
 3000                                                                            
 5000                                                                            
 3000                                                                            
 
6 rows selected. 
 
SQL> SELECT Empno, Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal < ANY(SELECT Sal 
  4      FROM Emp 
  5      WHERE Empno IN(SELECT DISTINCT MGR 
  6          FROM Emp) 
  7      ); 
 
EMPNO ENAME      JOB         SAL                                                 
----- ---------- --------- -----                                                 
 7698 BLAKE      MANAGER    2850                                                 
 7782 CLARK      MANAGER    2450                                                 
 7566 JONES      MANAGER    2975                                                 
 7654 MARTIN     SALESMAN   1250                                                 
 7499 ALLEN      SALESMAN   1600                                                 
 7844 TURNER     SALESMAN   1500                                                 
 7900 JAMES      CLERK       950                                                 
 7521 WARD       SALESMAN   1250                                                 
 7902 FORD       ANALYST    3000                                                 
 7369 SMITH      CLERK       800                                                 
 7788 SCOTT      ANALYST    3000                                                 
 
EMPNO ENAME      JOB         SAL                                                 
----- ---------- --------- -----                                                 
 7876 ADAMS      CLERK      1100                                                 
 7934 MILLER     CLERK      1300                                                 
 
13 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Empno, Ename, Job, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal  ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Job, Sal, Deptno 
  2  FROM Emp 
  3  WHERE Sal  / 
 
EMPNO ENAME      JOB         SAL     DEPTNO                                      
 

----- ---------- --------- ----- ----------                                      
 7698 BLAKE      MANAGER    2850         30                                      
 7782 CLARK      MANAGER    2450         10                                      
 7566 JONES      MANAGER    2975         20                                      
 7654 MARTIN     SALESMAN   1250         30                                      
 7499 ALLEN      SALESMAN   1600         30                                      
 7844 TURNER     SALESMAN   1500         30                                      
 7900 JAMES      CLERK       950         30                                      
 7521 WARD       SALESMAN   1250         30                                      
 7369 SMITH      CLERK       800         20                                      
 7876 ADAMS      CLERK      1100         20                                      
 7934 MILLER     CLERK      1300         10                                      
 
11 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Empno, Ename, Job 
  2  FROM Emp 
  3  WHERE Sal >ANY(SELECT Sal 
  4      FROM Emp 
  5      WHERE Job = 'CLERK'); 
 
EMPNO ENAME      JOB                                                             
----- ---------- ---------                                                       
 7839 KING       PRESIDENT                                                       
 7902 FORD       ANALYST                                                         
 7788 SCOTT      ANALYST                                                         
 7566 JONES      MANAGER                                                         
 7698 BLAKE      MANAGER                                                         
 7782 CLARK      MANAGER                                                         
 7499 ALLEN      SALESMAN                                                        
 7844 TURNER     SALESMAN                                                        
 7934 MILLER     CLERK                                                           
 7654 MARTIN     SALESMAN                                                        
 7521 WARD       SALESMAN                                                        
 
EMPNO ENAME      JOB                                                             
----- ---------- ---------                                                       
 7876 ADAMS      CLERK                                                           
 7900 JAMES      CLERK                                                           
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Job 
  2  FROM Emp 
  3  WHERE Sal >(SELECT MIN(Sal) 
  4    FROM Emp 
  5   WHERE Job = 'CLERK') 
SQL> / 
 
EMPNO ENAME      JOB                                                             
----- ---------- ---------                                                       
 

 7839 KING       PRESIDENT                                                       
 7698 BLAKE      MANAGER                                                         
 7782 CLARK      MANAGER                                                         
 7566 JONES      MANAGER                                                         
 7654 MARTIN     SALESMAN                                                        
 7499 ALLEN      SALESMAN                                                        
 7844 TURNER     SALESMAN                                                        
 7900 JAMES      CLERK                                                           
 7521 WARD       SALESMAN                                                        
 7902 FORD       ANALYST                                                         
 7788 SCOTT      ANALYST                                                         
 
EMPNO ENAME      JOB                                                             
----- ---------- ---------                                                       
 7876 ADAMS      CLERK                                                           
 7934 MILLER     CLERK                                                           
 
13 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Empno, Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal >ALL(SELECT AVG(Sal) 
  4      FROM Emp 
  5      GROUP BY Deptno); 
 
EMPNO ENAME      JOB         SAL                                                 
----- ---------- --------- -----                                                 
 7839 KING       PRESIDENT  5000                                                 
 7566 JONES      MANAGER    2975                                                 
 7902 FORD       ANALYST    3000                                                 
 7788 SCOTT      ANALYST    3000                                                 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Job, Sal 
  2  FROM Emp 
  3  WHERE Sal >(SELECT MAX(AVG(Sal)) 
  4    FROM Emp 
  5   GROUP BY Deptno) 
SQL> / 
 
EMPNO ENAME      JOB         SAL                                                 
----- ---------- --------- -----                                                 
 7839 KING       PRESIDENT  5000                                                 
 7566 JONES      MANAGER    2975                                                 
 7902 FORD       ANALYST    3000                                                 
 7788 SCOTT      ANALYST    3000                                                 
 
SQL> SELECT Empno, Ename Job, Sal 
  2  FROM Emp 
  3  WHERE Sal  ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename Job, Sal 
  2  FROM Emp 
  3  WHERE Sal <(SELECT  MIN(AVG(Sal)) 
  4    FROM Emp 
  5   GROUP BY Deptno) 
SQL> / 
 
EMPNO JOB          SAL                                                           
----- ---------- -----                                                           
 7654 MARTIN      1250                                                           
 7844 TURNER      1500                                                           
 7900 JAMES        950                                                           
 7521 WARD        1250                                                           
 7369 SMITH        800                                                           
 7876 ADAMS       1100                                                           
 7934 MILLER      1300                                                           
 
7 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, Deptno, HireDate 
  2  FROM Emp 
  3  WHERE Sal > ( 
  4      SELECT MAX(Sal) 
  5      FROM Emp 
  6      WHERE Deptno = 30 
  7     ) AND 
  8      Deptno = ( 
  9     SELECT Deptno 
 10     FROM Dept 
 11     WHERE Dname = 'RESEARCH' 
 12     ); 
 
ENAME        SAL     DEPTNO HIREDATE                                             
---------- ----- ---------- ---------                                            
JONES       2975         20 02-APR-81                                            
FORD        3000         20 03-DEC-81                                            
SCOTT       3000         20 09-DEC-82                                            
 
 

SQL> WHERE (Sal, Deptno)SPOOL OFF 
SP2-0734: unknown command beginning "WHERE (Sal..." - rest of line ignored. 

SQL> cl scr 
 
SQL> ED 
SP2-0107: Nothing to save. 
SQL> cl scr 
 
SQL> SELECT ProdId, Qty 
  2        FROM Item 
  3        WHERE OrdID = 605; 
 
    PRODID        QTY                                                            
---------- ----------                                                            
    100861        100                                                            
    100870        500                                                            
    100890          5                                                            
    101860         50                                                            
    101863        100                                                            
    102130         10                                                            
 
6 rows selected. 
 
SQL> SELECT OrdID, ProdID, Qty 
  2  FROM Item; 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       610     100890          1                                                 
       611     100861          1                                                 
       612     100860        100                                                 
       601     200376          1                                                 
       602     100870         20                                                 
       604     100890          3                                                 
       604     100861          2                                                 
       604     100860         10                                                 
       603     100860          4                                                 
       610     100860          1                                                 
       610     100870          3                                                 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       613     200376        200                                                 
       614     100860        444                                                 
       614     100870       1000                                                 
       612     100861         20                                                 
       612     101863        150                                                 
       620     100860         10                                                 
       620     200376       1000                                                 
       620     102130        500                                                 
       613     100871        100                                                 
       613     101860        200                                                 
       613     200380        150                                                 
 
     ORDID     PRODID        QTY                                                 
 

---------- ---------- ----------                                                 
       619     102130        100                                                 
       617     100860         50                                                 
       617     100861        100                                                 
       614     100871       1000                                                 
       616     100861         10                                                 
       616     100870         50                                                 
       616     100890          2                                                 
       616     102130         10                                                 
       616     200376         10                                                 
       619     200380        100                                                 
       619     200376        100                                                 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       615     100861          4                                                 
       607     100871          1                                                 
       615     100870        100                                                 
       617     100870        500                                                 
       617     100871        500                                                 
       617     100890        500                                                 
       617     101860        100                                                 
       617     101863        200                                                 
       617     102130        100                                                 
       617     200376        200                                                 
       617     200380        300                                                 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       609     100870          5                                                 
       609     100890          1                                                 
       618     100860         23                                                 
       618     100861         50                                                 
       618     100870         10                                                 
       621     100861         10                                                 
       621     100870        100                                                 
       615     100871         50                                                 
       608     101860          1                                                 
       608     100871          2                                                 
       609     100861          1                                                 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       606     102130          1                                                 
       605     100861        100                                                 
       605     100870        500                                                 
       605     100890          5                                                 
       605     101860         50                                                 
       605     101863        100                                                 
       605     102130         10                                                 
       612     100871        100                                                 
       619     100871         50                                                 
 
64 rows selected. 
 
SQL> SELECT DISTINCT OrdID 
 

  2  FROM Item; 
 
     ORDID                                                                       
----------                                                                       
       601                                                                       
       602                                                                       
       603                                                                       
       604                                                                       
       605                                                                       
       606                                                                       
       607                                                                       
       608                                                                       
       609                                                                       
       610                                                                       
       611                                                                       
 
     ORDID                                                                       
----------                                                                       
       612                                                                       
       613                                                                       
       614                                                                       
       615                                                                       
       616                                                                       
       617                                                                       
       618                                                                       
       619                                                                       
       620                                                                       
       621                                                                       
 
21 rows selected. 
 
SQL> SELECT OrdID, COUNT(*) ItemCnt 
  2  FROM Item 
  3  GROUP BY OrdID; 
 
     ORDID    ITEMCNT                                                            
---------- ----------                                                            
       601          1                                                            
       602          1                                                            
       603          1                                                            
       604          3                                                            
       605          6                                                            
       606          1                                                            
       607          1                                                            
       608          2                                                            
       609          3                                                            
       610          3                                                            
       611          1                                                            
 
     ORDID    ITEMCNT                                                            
---------- ----------                                                            
       612          4                                                            
       613          4                                                            
       614          3                                                            
       615          3                                                            
       616          5                                                            
 

       617         10                                                            
       618          3                                                            
       619          4                                                            
       620          3                                                            
       621          2                                                            
 
21 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT ProdId, Qty 
  2        FROM Item 
  3        WHERE OrdID = 605; 
 
    PRODID        QTY                                                            
---------- ----------                                                            
    100861        100                                                            
    100870        500                                                            
    100890          5                                                            
    101860         50                                                            
    101863        100                                                            
    102130         10                                                            
 
6 rows selected. 
 
SQL> SELECT OrdID, ProdID, Qty 
  2  FROM Item 
  3  WHERE ProdID IN(100861, 100870, 100890, 101860, 101863, 102130) AND 
  4  OrdID <> 605; 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       610     100890          1                                                 
       611     100861          1                                                 
       602     100870         20                                                 
       604     100890          3                                                 
       604     100861          2                                                 
       610     100870          3                                                 
       614     100870       1000                                                 
       612     100861         20                                                 
       612     101863        150                                                 
       620     102130        500                                                 
       613     101860        200                                                 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       619     102130        100                                                 
       617     100861        100                                                 
       616     100861         10                                                 
       616     100870         50                                                 
       616     100890          2                                                 
       616     102130         10                                                 
       615     100861          4                                                 
       615     100870        100                                                 
       617     100870        500                                                 
       617     100890        500                                                 
 

       617     101860        100                                                 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       617     101863        200                                                 
       617     102130        100                                                 
       609     100870          5                                                 
       609     100890          1                                                 
       618     100861         50                                                 
       618     100870         10                                                 
       621     100861         10                                                 
       621     100870        100                                                 
       608     101860          1                                                 
       609     100861          1                                                 
       606     102130          1                                                 
 
33 rows selected. 
 
SQL> SELECT OrdID, ProdID, Qty 
  2  FROM Item 
  3  WHERE (ProdID, Qty) IN(SELECT ProdId, Qty 
  4        FROM Item 
  5        WHERE OrdID = 605) AND 
  6  OrdID <> 605; 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       617     100861        100                                                 
       617     100870        500                                                 
       616     102130         10                                                 
 
SQL> SELECT  OrdID, ProdID, Qty 
  2  FROM Item 
  3  WHERE ProdID IN(SELECT ProdID 
  4      FROM Item 
  5      WHERE OrdID = 605) 
  6  AND Qty IN (SELECT Qty 
  7     FROM Item 
  8     WHERE OrdID = 605) 
  9  AND OrdID <> 605 
 10  / 
 
     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       616     100861         10                                                 
       621     100861         10                                                 
       618     100861         50                                                 
       617     100861        100                                                 
       618     100870         10                                                 
       616     100870         50                                                 
       609     100870          5                                                 
       617     100870        500                                                 
       615     100870        100                                                 
       621     100870        100                                                 
       617     100890        500                                                 
 
 

     ORDID     PRODID        QTY                                                 
---------- ---------- ----------                                                 
       617     101860        100                                                 
       616     102130         10                                                 
       620     102130        500                                                 
       619     102130        100                                                 
       617     102130        100                                                 
 
16 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT OrdID, ProdID, Qty 
  2  FROM Item 
  3  WHERE (ProdID, Qty) IN(100861, 100); 
WHERE (ProdID, Qty) IN(100861, 100) 
                      * 
ERROR at line 3: 
ORA-00920: invalid relational operator  
 
 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal 
  2  FROM Emp 
  3  WHERE (Deptno, Sal) IN (SELECT Deptno, MAX(Sal) 
  4        FROM Emp 
  5        GROUP BY Deptno); 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
SCOTT              20       3000                                                 
FORD               20       3000                                                 
BLAKE              30       2850                                                 
 
SQL> SELECT Ename, Deptno, Sal 
  2  FROM Emp 
  3  WHERE Deptno IN (SELECT Deptno 
  4       FROM Emp 
  5       GROUP BY Deptno) AND 
  6    Sal IN (SELECT MAX(Sal) 
  7      FROM Emp 
  8      GROUP BY Deptno); 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
SCOTT              20       3000                                                 
FORD               20       3000                                                 
BLAKE              30       2850                                                 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal) 
  2  VALUES(1234, 'SAMPLE01', 10, 3000); 
 
1 row created. 
 

 
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal) 
  2  VALUES(1235, 'SAMPLE02', 10, 2850); 
 
1 row created. 
 
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal) 
  2  VALUES(1236, 'SAMPLE03', 20, 2850); 
 
1 row created. 
 
SQL> SELECT Ename, Deptno, Sal 
  2  FROM Emp 
  3  WHERE (Deptno, Sal) IN (SELECT Deptno, MAX(Sal) 
  4        FROM Emp 
  5        GROUP BY Deptno); 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
SCOTT              20       3000                                                 
FORD               20       3000                                                 
BLAKE              30       2850                                                 
 
SQL> SELECT Ename, Deptno, Sal 
  2  FROM Emp 
  3  WHERE Deptno IN (SELECT Deptno 
  4       FROM Emp 
  5       GROUP BY Deptno) AND 
  6    Sal IN (SELECT MAX(Sal) 
  7      FROM Emp 
  8      GROUP BY Deptno); 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
SCOTT              20       3000                                                 
FORD               20       3000                                                 
SAMPLE01           10       3000                                                 
BLAKE              30       2850                                                 
SAMPLE03           20       2850                                                 
SAMPLE02           10       2850                                                 
 
7 rows selected. 
 
SQL> ROLLBACK; 
 
Rollback complete. 
 
SQL> cl scr 
 
SQL> ROLLBACK; 
 
Rollback complete. 
 
SQL> cl scr 
 

 
SQL> SELECT Ename, Sal, Comm 
  2  FROM Emp 
  3  / 
 
ENAME             SAL       COMM                                                 
---------- ---------- ----------                                                 
KING             5000                                                            
BLAKE            2850                                                            
CLARK            2450                                                            
JONES            2975                                                            
MARTIN           1250       1400                                                 
ALLEN            1600        300                                                 
TURNER           1500          0                                                 
JAMES             950                                                            
WARD             1250        500                                                 
FORD             3000                                                            
SMITH             800                                                            
 
ENAME             SAL       COMM                                                 
---------- ---------- ----------                                                 
SCOTT            3000                                                            
ADAMS            1100                                                            
MILLER           1300                                                            
 
14 rows selected. 
 
SQL> SELECT Ename, Sal, Comm 
  2  FROM Emp 
  3  WHERE Comm IN(1400, 300, NULL); 
 
ENAME             SAL       COMM                                                 
---------- ---------- ----------                                                 
MARTIN           1250       1400                                                 
ALLEN            1600        300                                                 
 
SQL> SELECT Ename, Sal, Comm 
  2  FROM Emp 
  3  WHERE Comm NOT IN(1400, 300, NULL); 
 
no rows selected 
 
SQL> cl scr 
 
SQL> SELECT Ename, Empno, MGR 
  2  FROM Emp; 
 
ENAME           EMPNO        MGR                                                 
---------- ---------- ----------                                                 
KING             7839                                                            
BLAKE            7698       7839                                                 
CLARK            7782       7839                                                 
JONES            7566       7839                                                 
MARTIN           7654       7698                                                 
ALLEN            7499       7698                                                 
TURNER           7844       7698                                                 
 

JAMES            7900       7698                                                 
WARD             7521       7698                                                 
FORD             7902       7566                                                 
SMITH            7369       7902                                                 
 
ENAME           EMPNO        MGR                                                 
---------- ---------- ----------                                                 
SCOTT            7788       7566                                                 
ADAMS            7876       7788                                                 
MILLER           7934       7782                                                 
 
14 rows selected. 
 
SQL> SELECT E.Ename 
  2  FROM Emp E 
  3  WHERE E.Empno IN(SELECT  M.Mgr 
  4       FROM Emp M); 
 
ENAME                                                                            
----------                                                                       
JONES                                                                            
BLAKE                                                                            
CLARK                                                                            
SCOTT                                                                            
KING                                                                             
FORD                                                                             
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT E.Ename 
  2  FROM Emp E 
  3  WHERE E.Empno NOT IN(SELECT  M.Mgr 
  4    FROM Emp M) 
SQL> / 
 
no rows selected 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT E.Ename 
  2  FROM Emp E 
  3  WHERE E.Empno NOT IN(SELECT  NVL(M.Mgr, 0) 
  4    FROM Emp M) 
SQL> / 
 
ENAME                                                                            
----------                                                                       
SMITH                                                                            
ALLEN                                                                            
WARD                                                                             
MARTIN                                                                           
TURNER                                                                           
 

ADAMS                                                                            
JAMES                                                                            
MILLER                                                                           
 
8 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, Comm 
  2  FROM Emp 
  3  WHERE Comm IN(SELECT Comm 
  4       FROM Emp); 
 
ENAME             SAL       COMM                                                 
---------- ---------- ----------                                                 
MARTIN           1250       1400                                                 
ALLEN            1600        300                                                 
TURNER           1500          0                                                 
WARD             1250        500                                                 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Comm 
  2  FROM Emp 
  3  WHERE Comm NOT IN(SELECT Comm 
  4      FROM Emp) 
SQL> / 
 
no rows selected 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Comm 
  2  FROM Emp 
  3  WHERE Comm NOT IN(SELECT NVL(Comm, 0) 
  4     FROM Emp) 
SQL> / 
 
no rows selected 
 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal, HireDate 
  2  FROM Emp; 
 
ENAME          DEPTNO        SAL HIREDATE                                        
---------- ---------- ---------- ---------                                       
KING               10       5000 17-NOV-81                                       
BLAKE              30       2850 01-MAY-81                                       
CLARK              10       2450 09-JUN-81                                       
JONES              20       2975 02-APR-81                                       
MARTIN             30       1250 28-SEP-81                                       
ALLEN              30       1600 20-FEB-81                                       
TURNER             30       1500 08-SEP-81                                       
 

JAMES              30        950 03-DEC-81                                       
WARD               30       1250 22-FEB-81                                       
FORD               20       3000 03-DEC-81                                       
SMITH              20        800 17-DEC-80                                       
 
ENAME          DEPTNO        SAL HIREDATE                                        
---------- ---------- ---------- ---------                                       
SCOTT              20       3000 09-DEC-82                                       
ADAMS              20       1100 12-JAN-83                                       
MILLER             10       1300 23-JAN-82                                       
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM ( 
  3   SELECT Ename, Deptno, Sal, HireDate 
  4   FROM Emp 
  5  ) 
SQL> / 
 
ENAME          DEPTNO        SAL HIREDATE                                        
---------- ---------- ---------- ---------                                       
KING               10       5000 17-NOV-81                                       
BLAKE              30       2850 01-MAY-81                                       
CLARK              10       2450 09-JUN-81                                       
JONES              20       2975 02-APR-81                                       
MARTIN             30       1250 28-SEP-81                                       
ALLEN              30       1600 20-FEB-81                                       
TURNER             30       1500 08-SEP-81                                       
JAMES              30        950 03-DEC-81                                       
WARD               30       1250 22-FEB-81                                       
FORD               20       3000 03-DEC-81                                       
SMITH              20        800 17-DEC-80                                       
 
ENAME          DEPTNO        SAL HIREDATE                                        
---------- ---------- ---------- ---------                                       
SCOTT              20       3000 09-DEC-82                                       
ADAMS              20       1100 12-JAN-83                                       
MILLER             10       1300 23-JAN-82                                       
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Sal * 12 AnnSal, HireDate 
  2  FROM ( 
  3   SELECT Ename, Deptno, Sal, HireDate 
  4   FROM Emp 
  5  ) 
SQL> / 
 
ENAME             SAL     ANNSAL HIREDATE                                        
 

---------- ---------- ---------- ---------                                       
KING             5000      60000 17-NOV-81                                       
BLAKE            2850      34200 01-MAY-81                                       
CLARK            2450      29400 09-JUN-81                                       
JONES            2975      35700 02-APR-81                                       
MARTIN           1250      15000 28-SEP-81                                       
ALLEN            1600      19200 20-FEB-81                                       
TURNER           1500      18000 08-SEP-81                                       
JAMES             950      11400 03-DEC-81                                       
WARD             1250      15000 22-FEB-81                                       
FORD             3000      36000 03-DEC-81                                       
SMITH             800       9600 17-DEC-80                                       
 
ENAME             SAL     ANNSAL HIREDATE                                        
---------- ---------- ---------- ---------                                       
SCOTT            3000      36000 09-DEC-82                                       
ADAMS            1100      13200 12-JAN-83                                       
MILLER           1300      15600 23-JAN-82                                       
 
14 rows selected. 
 

SQL> cl scr 
 
SQL> SELECT Deptno, Dname, Loc 
  2  FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
 
SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal 
  2  FROM Emp; 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
BLAKE              30       2850                                                 
CLARK              10       2450                                                 
JONES              20       2975                                                 
MARTIN             30       1250                                                 
ALLEN              30       1600                                                 
TURNER             30       1500                                                 
JAMES              30        950                                                 
WARD               30       1250                                                 
FORD               20       3000                                                 
SMITH              20        800                                                 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
SCOTT              20       3000                                                 
 

ADAMS              20       1100                                                 
MILLER             10       1300                                                 
 
14 rows selected. 
 
SQL> SELECT Deptno, AVG(Sal) SalAvg 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
    DEPTNO     SALAVG                                                            
---------- ----------                                                            
        10 2916.66667                                                            
        20       2175                                                            
        30 1566.66667                                                            
 
SQL> SELECT Ename, E.Deptno, Sal, AvgSal 
  2  FROM Emp E, ( 
  3       SELECT Deptno, AVG(Sal) AvgSal 
  4       FROM Emp 
  5       GROUP BY Deptno 
  6     ) E1 
  7  WHERE E.Deptno = E1.Deptno; 
 
ENAME          DEPTNO        SAL     AVGSAL                                      
---------- ---------- ---------- ----------                                      
KING               10       5000 2916.66667                                      
BLAKE              30       2850 1566.66667                                      
CLARK              10       2450 2916.66667                                      
JONES              20       2975       2175                                      
MARTIN             30       1250 1566.66667                                      
ALLEN              30       1600 1566.66667                                      
TURNER             30       1500 1566.66667                                      
JAMES              30        950 1566.66667                                      
WARD               30       1250 1566.66667                                      
FORD               20       3000       2175                                      
SMITH              20        800       2175                                      
 
ENAME          DEPTNO        SAL     AVGSAL                                      
---------- ---------- ---------- ----------                                      
SCOTT              20       3000       2175                                      
ADAMS              20       1100       2175                                      
MILLER             10       1300 2916.66667                                      
 
14 rows selected. 
 
SQL> SELECT E.Ename, E.Sal , E.Deptno, E1.SalAvg 
  2  FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg 
  3     FROM Emp 
  4     GROUP BY Deptno) E1 
  5  WHERE E.Deptno = E1.Deptno AND 
  6  E.Sal > E1.SalAvg; 
 
ENAME             SAL     DEPTNO     SALAVG                                      
---------- ---------- ---------- ----------                                      
KING             5000         10 2916.66667                                      
BLAKE            2850         30 1566.66667                                      
 

JONES            2975         20       2175                                      
ALLEN            1600         30 1566.66667                                      
FORD             3000         20       2175                                      
SCOTT            3000         20       2175                                      
 
6 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT E.Ename, E.Sal, E.Deptno, 
  2  ROUND(E1.SalAvg, 2) DeptAvgSal, 
  3  ROUND(E.Sal - E1.SalAvg) DiffSalAvg 
  4  FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg 
  5     FROM Emp 
  6     GROUP BY Deptno) E1 
  7  WHERE E.Deptno = E1.Deptno 
  8  ORDER BY Deptno 
  9  / 
 
ENAME             SAL     DEPTNO DEPTAVGSAL DIFFSALAVG                           
---------- ---------- ---------- ---------- ----------                           
KING             5000         10    2916.67       2083                           
CLARK            2450         10    2916.67       -467                           
MILLER           1300         10    2916.67      -1617                           
JONES            2975         20       2175        800                           
FORD             3000         20       2175        825                           
SMITH             800         20       2175      -1375                           
SCOTT            3000         20       2175        825                           
ADAMS            1100         20       2175      -1075                           
BLAKE            2850         30    1566.67       1283                           
MARTIN           1250         30    1566.67       -317                           
ALLEN            1600         30    1566.67         33                           
 
ENAME             SAL     DEPTNO DEPTAVGSAL DIFFSALAVG                           
---------- ---------- ---------- ---------- ----------                           
TURNER           1500         30    1566.67        -67                           
JAMES             950         30    1566.67       -617                           
WARD             1250         30    1566.67       -317                           
 
14 rows selected. 
 
SQL> SELECT E.Ename, E.Sal, E.Deptno, E1.SalSum 
  2  FROM Emp E, (SELECT Deptno, SUM(Sal) SalSUM 
  3     FROM Emp 
  4     GROUP BY Deptno) E1 
  5  WHERE E.Deptno = E1.Deptno 
  6  ORDER BY Deptno; 
 
ENAME             SAL     DEPTNO     SALSUM                                      
---------- ---------- ---------- ----------                                      
KING             5000         10       8750                                      
CLARK            2450         10       8750                                      
MILLER           1300         10       8750                                      
JONES            2975         20      10875                                      
FORD             3000         20      10875                                      
SMITH             800         20      10875                                      
 

SCOTT            3000         20      10875                                      
ADAMS            1100         20      10875                                      
BLAKE            2850         30       9400                                      
MARTIN           1250         30       9400                                      
ALLEN            1600         30       9400                                      
 
ENAME             SAL     DEPTNO     SALSUM                                      
---------- ---------- ---------- ----------                                      
TURNER           1500         30       9400                                      
JAMES             950         30       9400                                      
WARD             1250         30       9400                                      
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT T1.Deptno, Dname, Staff 
  2  FROM Dept T1, 
  3  (SELECT Deptno, COUNT(*) AS Staff 
  4  FROM Emp 
  5  GROUP BY Deptno) T2 
  6  WHERE T1.Deptno = T2.Deptno 
  7  AND Staff >= 5; 
 
    DEPTNO DNAME               STAFF                                             
---------- -------------- ----------                                             
        20 RESEARCH                5                                             
        30 SALES                   6                                             
 
SQL> SELECT E.Deptno, Dname, COUNT(*) Staff 
  2  FROM Emp E, Dept D 
  3  WHERE E.Deptno = D.Deptno 
  4  GROUP BY E.Deptno, Dname 
  5  HAVING COUNT(*) >= 5; 
 
    DEPTNO DNAME               STAFF                                             
---------- -------------- ----------                                             
        20 RESEARCH                5                                             
        30 SALES                   6                                             
 
SQL> cl scr 
 
SQL> SELECT Deptno, SUM(Sal), 
  2  SUM(Sal)/Tot_Sal * 100 "Salary%" 
  3  FROM Emp, 
  4  (SELECT SUM(Sal) Tot_Sal 
  5   FROM Emp) 
  6  GROUP BY Deptno, Tot_Sal; 
 
    DEPTNO   SUM(SAL)    Salary%                                                 
---------- ---------- ----------                                                 
        10       8750 30.1464255                                                 
        20      10875 37.4677003                                                 
        30       9400 32.3858742                                                 
 
SQL> SELECT Job, SUM(Sal), 
 

  2  ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%" 
  3  FROM Emp, (SELECT SUM(Sal) Tot_Sal 
  4  FROM Emp) 
  5  GROUP BY Job, Tot_Sal; 
 
JOB         SUM(SAL)    Salary%                                                  
--------- ---------- ----------                                                  
CLERK           4150       14.3                                                  
ANALYST         6000      20.67                                                  
MANAGER         8275      28.51                                                  
SALESMAN        5600      19.29                                                  
PRESIDENT       5000      17.23                                                  
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal, SUM(Sal) 
  2  FROM Emp 
  3  GROUP BY Ename, Sal; 
 
ENAME             SAL   SUM(SAL)                                                 
---------- ---------- ----------                                                 
FORD             3000       3000                                                 
KING             5000       5000                                                 
WARD             1250       1250                                                 
ADAMS            1100       1100                                                 
ALLEN            1600       1600                                                 
BLAKE            2850       2850                                                 
CLARK            2450       2450                                                 
JAMES             950        950                                                 
JONES            2975       2975                                                 
SCOTT            3000       3000                                                 
SMITH             800        800                                                 
 
ENAME             SAL   SUM(SAL)                                                 
---------- ---------- ----------                                                 
MARTIN           1250       1250                                                 
MILLER           1300       1300                                                 
TURNER           1500       1500                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, (SUM(Sal) / OrgSal) * 100 
  2  FROM Emp, ( 
  3    SELECT SUM(Sal) OrgSal 
  4         FROM Emp 
  5    )  
  6 GROUP BY Ename, Sal, OrgSal 
SQL> / 
 
ENAME             SAL (SUM(SAL)/ORGSAL)*100                                      
---------- ---------- ---------------------                                      
FORD             3000            10.3359173                                      
KING             5000            17.2265289                                      
 

WARD             1250            4.30663221                                      
ADAMS            1100            3.78983635                                      
ALLEN            1600            5.51248923                                      
BLAKE            2850            9.81912145                                      
CLARK            2450            8.44099914                                      
JAMES             950            3.27304048                                      
JONES            2975            10.2497847                                      
SCOTT            3000            10.3359173                                      
SMITH             800            2.75624462                                      
 
ENAME             SAL (SUM(SAL)/ORGSAL)*100                                      
---------- ---------- ---------------------                                      
MARTIN           1250            4.30663221                                      
MILLER           1300             4.4788975                                      
TURNER           1500            5.16795866                                      
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal,  
  2  TRUNC((SUM(Sal) / OrgSal) * 100, 2)||' % Share' "%Share" 
  3  FROM Emp, ( 
  4    SELECT SUM(Sal) OrgSal 
  5         FROM Emp 
  6    )  
  7 GROUP BY Ename, Sal, OrgSal 
SQL> / 
 
ENAME             SAL %Share                                                     
---------- ---------- ------------------------------------------------           
FORD             3000 10.33 % Share                                              
KING             5000 17.22 % Share                                              
WARD             1250 4.3 % Share                                                
ADAMS            1100 3.78 % Share                                               
ALLEN            1600 5.51 % Share                                               
BLAKE            2850 9.81 % Share                                               
CLARK            2450 8.44 % Share                                               
JAMES             950 3.27 % Share                                               
JONES            2975 10.24 % Share                                              
SCOTT            3000 10.33 % Share                                              
SMITH             800 2.75 % Share                                               
 
ENAME             SAL %Share                                                     
---------- ---------- ------------------------------------------------           
MARTIN           1250 4.3 % Share                                                
MILLER           1300 4.47 % Share                                               
TURNER           1500 5.16 % Share                                               
 
14 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT TO_CHAR(HireDate, 'YYYY') Year, 
  2  SUM(Sal), 
  3  ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%" 
  4  FROM Emp, (SELECT SUM(Sal) Tot_Sal 
  5  FROM Emp) 
  6  GROUP BY TO_CHAR(HireDate, 'YYYY'), Tot_Sal; 
 
YEAR   SUM(SAL)    Salary%                                                       
---- ---------- ----------                                                       
1980        800       2.76                                                       
1981      22825      78.64                                                       
1982       4300      14.81                                                       
1983       1100       3.79                                                       
 
SQL> SELECT 
  2  TO_CHAR(HireDate, 'YYYY') Year, 
  3  TO_CHAR(HireDate, 'Month') "Month", 
  4  SUM(Sal), 
  5  ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%" 
  6  FROM Emp, (SELECT SUM(Sal) Tot_Sal 
  7  FROM Emp) 
  8  WHERE TO_CHAR(HireDate, 'YYYY') = &GiveYear 
  9  GROUP BY TO_CHAR(HireDate, 'YYYY'), TO_CHAR(HireDate, 'Month'), Tot_Sal; 
Enter value for giveyear: 1981 
old   8: WHERE TO_CHAR(HireDate, 'YYYY') = &GiveYear 
new   8: WHERE TO_CHAR(HireDate, 'YYYY') = 1981 
 
YEAR Month       SUM(SAL)    Salary%                                             
---- --------- ---------- ----------                                             
1981 April           2975      10.25                                             
1981 December        3950      13.61                                             
1981 February        2850       9.82                                             
1981 June            2450       8.44                                             
1981 May             2850       9.82                                             
1981 November        5000      17.23                                             
1981 September       2750       9.47                                             
 
7 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT E.EmpCount, D.DeptCount, S.GradeCnt 
  2  FROM 
  3  (SELECT COUNT(*) EmpCount 
  4   FROM Emp) E, 
  5  (SELECT COUNT(*) DeptCount 
  6   FROM Dept) D, 
  7  (SELECT COUNT(*) GradeCnt FROM 
  8   SalGrade) S; 
 
  EMPCOUNT  DEPTCOUNT   GRADECNT                                                 
---------- ---------- ----------                                                 
        14          4          5                                                 
 
SQL> SELECT 
  2  E.EmpCount, 
  3  D.DeptCount, 
  4  S.GradeCnt, 
 

  5  E.EmpCount + D.DeptCount + S.GradeCnt TotalRecCnt 
  6  FROM 
  7  (SELECT COUNT(*) EmpCount 
  8   FROM Emp) E, 
  9  (SELECT COUNT(*) DeptCount 
 10   FROM Dept) D, 
 11  (SELECT COUNT(*) GradeCnt 
 12   FROM SalGrade) S; 
 
  EMPCOUNT  DEPTCOUNT   GRADECNT TOTALRECCNT                                     
---------- ---------- ---------- -----------                                     
        14          4          5          23                                     
 
SQL> SELECT 
  2  E.EmpCount, 
  3  D.DeptCount, 
  4  S.GradeCnt, 
  5  E.EmpCount + D.DeptCount + S.GradeCnt TotalRecCnt, 
  6  GREATEST(E.EmpCount, D.DeptCount, S.GradeCnt) HighRecinTab, 
  7  LEAST(E.EmpCount, D.DeptCount, S.GradeCnt) LowRecinTab 
  8  FROM 
  9  (SELECT COUNT(*) EmpCount 
 10   FROM Emp) E, 
 11  (SELECT COUNT(*) DeptCount 
 12   FROM Dept) D, 
 13  (SELECT COUNT(*) GradeCnt 
 14   FROM SalGrade) S; 
 
  EMPCOUNT  DEPTCOUNT   GRADECNT TOTALRECCNT HIGHRECINTAB LOWRECINTAB            
---------- ---------- ---------- ----------- ------------ -----------            
        14          4          5          23           14           4            
 
SQL> cl scr 
 
SQL> SELECT A.Deptno "Department Number", 
  2  (A.NumEmp / B.TotalCount ) * 100  "%Employees", 
  3  (A.SalSum / B.TotalSal ) * 100 "%Salary" 
  4  FROM 
  5  (SELECT Deptno, COUNT(*) NumEmp, 
  6   SUM(Sal) SalSum 
  7   FROM Emp 
  8   GROUP BY Deptno) A, 
  9  (SELECT COUNT(*) TotalCount, 
 10   SUM(Sal) TotalSal 
 11   FROM Emp) B; 
 
Department Number %Employees    %Salary                                          
----------------- ---------- ----------                                          
               10 21.4285714 30.1464255                                          
               20 35.7142857 37.4677003                                          
               30 42.8571429 32.3858742                                          
 
SQL> cl scr 
 
SQL> SELECT Ename, Sal FROM Emp; 
 
 

ENAME             SAL                                                            
---------- ----------                                                            
KING             5000                                                            
BLAKE            2850                                                            
CLARK            2450                                                            
JONES            2975                                                            
MARTIN           1250                                                            
ALLEN            1600                                                            
TURNER           1500                                                            
JAMES             950                                                            
WARD             1250                                                            
FORD             3000                                                            
SMITH             800                                                            
 
ENAME             SAL                                                            
---------- ----------                                                            
SCOTT            3000                                                            
ADAMS            1100                                                            
MILLER           1300                                                            
 
14 rows selected. 
 
SQL> SELECT SUM(Sal) FROM Emp; 
 
  SUM(SAL)                                                                       
----------                                                                       
     29025                                                                       
 
SQL> SELECT Ename, Sal, (SELECT SUM(Sal) FROM Emp) OrgSal FROM Emp; 
 
ENAME             SAL     ORGSAL                                                 
---------- ---------- ----------                                                 
KING             5000      29025                                                 
BLAKE            2850      29025                                                 
CLARK            2450      29025                                                 
JONES            2975      29025                                                 
MARTIN           1250      29025                                                 
ALLEN            1600      29025                                                 
TURNER           1500      29025                                                 
JAMES             950      29025                                                 
WARD             1250      29025                                                 
FORD             3000      29025                                                 
SMITH             800      29025                                                 
 
ENAME             SAL     ORGSAL                                                 
---------- ---------- ----------                                                 
SCOTT            3000      29025                                                 
ADAMS            1100      29025                                                 
MILLER           1300      29025                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, 
  2  (SELECT SUM(Sal) FROM Emp) OrgSal, 
  3  (SELECT AVG(Sal) FROM Emp) OrgAvg 
  4 FROM Emp 
SQL> / 
 
ENAME             SAL     ORGSAL     ORGAVG                                      
---------- ---------- ---------- ----------                                      
KING             5000      29025 2073.21429                                      
BLAKE            2850      29025 2073.21429                                      
CLARK            2450      29025 2073.21429                                      
JONES            2975      29025 2073.21429                                      
MARTIN           1250      29025 2073.21429                                      
ALLEN            1600      29025 2073.21429                                      
TURNER           1500      29025 2073.21429                                      
JAMES             950      29025 2073.21429                                      
WARD             1250      29025 2073.21429                                      
FORD             3000      29025 2073.21429                                      
SMITH             800      29025 2073.21429                                      
 
ENAME             SAL     ORGSAL     ORGAVG                                      
---------- ---------- ---------- ----------                                      
SCOTT            3000      29025 2073.21429                                      
ADAMS            1100      29025 2073.21429                                      
MILLER           1300      29025 2073.21429                                      
 
14 rows selected. 
 

SQL> cl scr 
 
SQL> SELECT Ename, Deptno, Sal 
  2  FROM Emp 
  3  WHERE Sal > ( 
  4      SELECT AVG(Sal) 
  5      FROM Emp 
  6      WHERE Deptno = Deptno 
  7     ) 
  8   
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Sal 
  2  FROM Emp OE 
  3  WHERE OE.Sal > ( 
  4      SELECT AVG(Sal) 
  5      FROM Emp IE 
  6      WHERE IE.Deptno = OE.Deptno 
  7    ) 
SQL> / 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
BLAKE              30       2850                                                 
JONES              20       2975                                                 
ALLEN              30       1600                                                 
 

FORD               20       3000                                                 
SCOTT              20       3000                                                 
 
6 rows selected. 
 
SQL> SELECT Deptno, AVG(Sal) SalAvg 
  2  FROM Emp 
  3  GROUP BY Deptno; 
 
    DEPTNO     SALAVG                                                            
---------- ----------                                                            
        10 2916.66667                                                            
        20       2175                                                            
        30 1566.66667                                                            
 
SQL> SELECT Ename, OE.Deptno, Sal 
  2  FROM Emp OE, ( 
  3          SELECT Deptno, AVG(Sal) SalAvg 
  4          FROM Emp IE 
  5          GROUP BY Deptno 
  6         ) IE 
  7  WHERE OE.Deptno = IE.Deptno AND 
  8  OE.Sal > IE.SalAvg; 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
SCOTT              20       3000                                                 
FORD               20       3000                                                 
JONES              20       2975                                                 
ALLEN              30       1600                                                 
BLAKE              30       2850                                                 
 
6 rows selected. 
 
SQL> SELECT Ename, Deptno, Sal 
  2  FROM Emp OE 
  3  WHERE OE.Sal > ( 
  4               SELECT AVG(Sal) 
  5               FROM Emp IE 
  6               WHERE IE.Deptno = OE.Deptno 
  7              ); 
 
ENAME          DEPTNO        SAL                                                 
---------- ---------- ----------                                                 
KING               10       5000                                                 
BLAKE              30       2850                                                 
JONES              20       2975                                                 
ALLEN              30       1600                                                 
FORD               20       3000                                                 
SCOTT              20       3000                                                 
 
6 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 

 
  1  SELECT Ename, OE.Deptno, Sal, SalAvg 
  2  FROM Emp OE, ( 
  3                          SELECT Deptno, AVG(Sal) SalAvg 
  4                          FROM Emp IE 
  5                          GROUP BY Deptno 
  6                         ) IE 
  7  WHERE OE.Deptno = IE.Deptno AND 
  8* OE.Sal > IE.SalAvg 
SQL> / 
 
ENAME          DEPTNO        SAL     SALAVG                                      
---------- ---------- ---------- ----------                                      
KING               10       5000 2916.66667                                      
SCOTT              20       3000       2175                                      
FORD               20       3000       2175                                      
JONES              20       2975       2175                                      
ALLEN              30       1600 1566.66667                                      
BLAKE              30       2850 1566.66667                                      
 
6 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT Empno, Ename, E.Deptno, Sal, MGR 
  2  FROM Emp E 
  3  WHERE E.Sal > ( 
  4      SELECT M.Sal 
  5      FROM Emp M 
  6      WHERE M.Empno  = E.MGR 
  7      ); 
 
     EMPNO ENAME          DEPTNO        SAL        MGR                           
---------- ---------- ---------- ---------- ----------                           
      7902 FORD               20       3000       7566                           
      7788 SCOTT              20       3000       7566                           
 
SQL> SELECT Empno, Ename, Sal, MGR 
  2  FROM Emp 
  3  WHERE Empno = 7566; 
 
     EMPNO ENAME             SAL        MGR                                      
---------- ---------- ---------- ----------                                      
      7566 JONES            2975       7839                                      
 
SQL> cl scr 
 
SQL> SELECT Deptno, Dname 
  2  FROM  Dept D 
  3  WHERE  EXISTS ( 
  4      SELECT * 
  5      FROM Emp E 
  6      WHERE E.Deptno = D.Deptno 
  7      ); 
 
    DEPTNO DNAME                                                                 
 

---------- --------------                                                        
        10 ACCOUNTING                                                            
        30 SALES                                                                 
        20 RESEARCH                                                              
 
SQL> SELECT Deptno, Dname 
  2  FROM Emp, Dept 
  3  WHERE Emp.Deptno = Dept.Deptno; 
SELECT Deptno, Dname 
       * 
ERROR at line 1: 
ORA-00918: column ambiguously defined  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Dept.Deptno, Dname 
  2  FROM Emp, Dept 
  3 WHERE Emp.Deptno = Dept.Deptno 
SQL> / 
 
    DEPTNO DNAME                                                                 
---------- --------------                                                        
        10 ACCOUNTING                                                            
        30 SALES                                                                 
        10 ACCOUNTING                                                            
        20 RESEARCH                                                              
        30 SALES                                                                 
        30 SALES                                                                 
        30 SALES                                                                 
        30 SALES                                                                 
        30 SALES                                                                 
        20 RESEARCH                                                              
        20 RESEARCH                                                              
 
    DEPTNO DNAME                                                                 
---------- --------------                                                        
        20 RESEARCH                                                              
        20 RESEARCH                                                              
        10 ACCOUNTING                                                            
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT DISTINCT Dept.Deptno, Dname 
  2  FROM Emp, Dept 
  3 WHERE Emp.Deptno = Dept.Deptno 
SQL> / 
 
    DEPTNO DNAME                                                                 
---------- --------------                                                        
        10 ACCOUNTING                                                            
        20 RESEARCH                                                              
 

        30 SALES                                                                 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Dept.Deptno, Dname 
  2  FROM Emp, Dept 
  3  WHERE Emp.Deptno = Dept.Deptno 
  4 GROUP BY Dept.Deptno, Dname 
SQL> / 
 
    DEPTNO DNAME                                                                 
---------- --------------                                                        
        10 ACCOUNTING                                                            
        20 RESEARCH                                                              
        30 SALES                                                                 
 
SQL> SELECT Dept.Deptno, Dname 
  2  FROM Dept 
  3  WHERE Dept.Deptno IN ( 
  4     SELECT Deptno 
  5     FROM Emp 
  6    ) 
SQL> / 
 
    DEPTNO DNAME                                                                 
---------- --------------                                                        
        10 ACCOUNTING                                                            
        30 SALES                                                                 
        20 RESEARCH                                                              
 
SQL> cl scr 
 
SQL> SELECT Deptno, Dname 
  2  FROM  Dept D 
  3  WHERE  NOT EXISTS ( 
  4       SELECT * 
  5       FROM Emp E 
  6       WHERE E.Deptno = D.Deptno 
  7       ); 
 
    DEPTNO DNAME                                                                 
---------- --------------                                                        
        40 OPERATIONS                                                            
 
SQL> cl scr 
 
SQL> SELECT E.Ename 
  2  FROM Emp E 
  3  WHERE EXISTS ( 
  4      SELECT * 
  5      FROM Emp E1 
  6      WHERE E1.Empno = E.Mgr 
  7      ); 
 
ENAME                                                                            
 

----------                                                                       
BLAKE                                                                            
CLARK                                                                            
JONES                                                                            
MARTIN                                                                           
ALLEN                                                                            
TURNER                                                                           
JAMES                                                                            
WARD                                                                             
FORD                                                                             
SMITH                                                                            
SCOTT                                                                            
 
ENAME                                                                            
----------                                                                       
ADAMS                                                                            
MILLER                                                                           
 
13 rows selected. 
 
SQL> SELECT Ename FROM Emp WHERE MGR IS NOT NULL; 
 
ENAME                                                                            
----------                                                                       
BLAKE                                                                            
CLARK                                                                            
JONES                                                                            
MARTIN                                                                           
ALLEN                                                                            
TURNER                                                                           
JAMES                                                                            
WARD                                                                             
FORD                                                                             
SMITH                                                                            
SCOTT                                                                            
 
ENAME                                                                            
----------                                                                       
ADAMS                                                                            
MILLER                                                                           
 
13 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT E.Ename 
  2  FROM Emp E 
  3  WHERE NOT EXISTS ( 
  4       SELECT * 
  5       FROM Emp E1 
  6       WHERE E1.Empno = E.Mgr 
  7       ); 
 
ENAME                                                                            
----------                                                                       
KING                                                                             
 

 
SQL> SELECT E.Ename 
  2  FROM Emp E 
  3  WHERE EXISTS ( 
  4      SELECT * 
  5      FROM Emp E1 
  6      WHERE E1.Mgr = E.Empno 
  7      ); 
 
ENAME                                                                            
----------                                                                       
KING                                                                             
BLAKE                                                                            
JONES                                                                            
FORD                                                                             
SCOTT                                                                            
CLARK                                                                            
 
6 rows selected. 
 
SQL> SELECT E.Ename 
  2  FROM Emp E 
  3  WHERE NOT EXISTS ( 
  4       SELECT * 
  5       FROM Emp E1 
  6       WHERE E1.Mgr = E.Empno 
  7       ); 
 
ENAME                                                                            
----------                                                                       
TURNER                                                                           
WARD                                                                             
MARTIN                                                                           
ALLEN                                                                            
MILLER                                                                           
SMITH                                                                            
ADAMS                                                                            
JAMES                                                                            
 
8 rows selected. 

Go Back