PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

SQL's using SUBQUERIES

Using Oracle Pre Defined Functions

Lower Function:


SQL> SET VERIFY OFF 
 
SQL> SELECT Ename, Sal, Job, Dname, Loc 
  2  FROM Emp, Dept; 
 
ENAME             SAL JOB       DNAME          LOC                               
---------- ---------- --------- -------------- -------------                     
KING             5000 PRESIDENT ACCOUNTING     NEW YORK                          
BLAKE            2850 MANAGER   ACCOUNTING     NEW YORK                          
CLARK            2450 MANAGER   ACCOUNTING     NEW YORK                          
JONES            2975 MANAGER   ACCOUNTING     NEW YORK                          
MARTIN           1250 SALESMAN  ACCOUNTING     NEW YORK                          
ALLEN            1600 SALESMAN  ACCOUNTING     NEW YORK                          
TURNER           1500 SALESMAN  ACCOUNTING     NEW YORK                          
JAMES             950 CLERK     ACCOUNTING     NEW YORK                          
WARD             1250 SALESMAN  ACCOUNTING     NEW YORK                          
FORD             3000 ANALYST   ACCOUNTING     NEW YORK                          
SMITH             800 CLERK     ACCOUNTING     NEW YORK                          
 
ENAME             SAL JOB       DNAME          LOC                               
---------- ---------- --------- -------------- -------------                     
SCOTT            3000 ANALYST   ACCOUNTING     NEW YORK                          
ADAMS            1100 CLERK     ACCOUNTING     NEW YORK                          
MILLER           1300 CLERK     ACCOUNTING     NEW YORK                          
KING             5000 PRESIDENT RESEARCH       DALLAS                            
BLAKE            2850 MANAGER   RESEARCH       DALLAS                            
CLARK            2450 MANAGER   RESEARCH       DALLAS                            
JONES            2975 MANAGER   RESEARCH       DALLAS                            
MARTIN           1250 SALESMAN  RESEARCH       DALLAS                            
ALLEN            1600 SALESMAN  RESEARCH       DALLAS                            
TURNER           1500 SALESMAN  RESEARCH       DALLAS                            
JAMES             950 CLERK     RESEARCH       DALLAS                            
 
ENAME             SAL JOB       DNAME          LOC                               
---------- ---------- --------- -------------- -------------                     
WARD             1250 SALESMAN  RESEARCH       DALLAS                            
FORD             3000 ANALYST   RESEARCH       DALLAS                            
SMITH             800 CLERK     RESEARCH       DALLAS                            
SCOTT            3000 ANALYST   RESEARCH       DALLAS                            
ADAMS            1100 CLERK     RESEARCH       DALLAS                            
MILLER           1300 CLERK     RESEARCH       DALLAS                            
KING             5000 PRESIDENT SALES          CHICAGO                           
BLAKE            2850 MANAGER   SALES          CHICAGO                           
CLARK            2450 MANAGER   SALES          CHICAGO                           
JONES            2975 MANAGER   SALES          CHICAGO                           
MARTIN           1250 SALESMAN  SALES          CHICAGO                           
 
ENAME             SAL JOB       DNAME          LOC                               
---------- ---------- --------- -------------- -------------                     
ALLEN            1600 SALESMAN  SALES          CHICAGO                           
TURNER           1500 SALESMAN  SALES          CHICAGO                           
JAMES             950 CLERK     SALES          CHICAGO                           
WARD             1250 SALESMAN  SALES          CHICAGO                           
 

FORD             3000 ANALYST   SALES          CHICAGO                           
SMITH             800 CLERK     SALES          CHICAGO                           
SCOTT            3000 ANALYST   SALES          CHICAGO                           
ADAMS            1100 CLERK     SALES          CHICAGO                           
MILLER           1300 CLERK     SALES          CHICAGO                           
KING             5000 PRESIDENT OPERATIONS     BOSTON                            
BLAKE            2850 MANAGER   OPERATIONS     BOSTON                            
 
ENAME             SAL JOB       DNAME          LOC                               
---------- ---------- --------- -------------- -------------                     
CLARK            2450 MANAGER   OPERATIONS     BOSTON                            
JONES            2975 MANAGER   OPERATIONS     BOSTON                            
MARTIN           1250 SALESMAN  OPERATIONS     BOSTON                            
ALLEN            1600 SALESMAN  OPERATIONS     BOSTON                            
TURNER           1500 SALESMAN  OPERATIONS     BOSTON                            
JAMES             950 CLERK     OPERATIONS     BOSTON                            
WARD             1250 SALESMAN  OPERATIONS     BOSTON                            
FORD             3000 ANALYST   OPERATIONS     BOSTON                            
SMITH             800 CLERK     OPERATIONS     BOSTON                            
SCOTT            3000 ANALYST   OPERATIONS     BOSTON                            
ADAMS            1100 CLERK     OPERATIONS     BOSTON                            
 
ENAME             SAL JOB       DNAME          LOC                               
---------- ---------- --------- -------------- -------------                     
MILLER           1300 CLERK     OPERATIONS     BOSTON                            
 
56 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Job 
  2 FROM Emp, Dept 
SQL> / 
 
ENAME             SAL JOB                                                        
---------- ---------- ---------                                                  
KING             5000 PRESIDENT                                                  
KING             5000 PRESIDENT                                                  
KING             5000 PRESIDENT                                                  
KING             5000 PRESIDENT                                                  
BLAKE            2850 MANAGER                                                    
BLAKE            2850 MANAGER                                                    
BLAKE            2850 MANAGER                                                    
BLAKE            2850 MANAGER                                                    
CLARK            2450 MANAGER                                                    
CLARK            2450 MANAGER                                                    
CLARK            2450 MANAGER                                                    
 
ENAME             SAL JOB                                                        
---------- ---------- ---------                                                  
CLARK            2450 MANAGER                                                    
JONES            2975 MANAGER                                                    
JONES            2975 MANAGER                                                    
JONES            2975 MANAGER                                                    
JONES            2975 MANAGER                                                    
 

MARTIN           1250 SALESMAN                                                   
MARTIN           1250 SALESMAN                                                   
MARTIN           1250 SALESMAN                                                   
MARTIN           1250 SALESMAN                                                   
ALLEN            1600 SALESMAN                                                   
ALLEN            1600 SALESMAN                                                   
 
ENAME             SAL JOB                                                        
---------- ---------- ---------                                                  
ALLEN            1600 SALESMAN                                                   
ALLEN            1600 SALESMAN                                                   
TURNER           1500 SALESMAN                                                   
TURNER           1500 SALESMAN                                                   
TURNER           1500 SALESMAN                                                   
TURNER           1500 SALESMAN                                                   
JAMES             950 CLERK                                                      
JAMES             950 CLERK                                                      
JAMES             950 CLERK                                                      
JAMES             950 CLERK                                                      
WARD             1250 SALESMAN                                                   
 
ENAME             SAL JOB                                                        
---------- ---------- ---------                                                  
WARD             1250 SALESMAN                                                   
WARD             1250 SALESMAN                                                   
WARD             1250 SALESMAN                                                   
FORD             3000 ANALYST                                                    
FORD             3000 ANALYST                                                    
FORD             3000 ANALYST                                                    
FORD             3000 ANALYST                                                    
SMITH             800 CLERK                                                      
SMITH             800 CLERK                                                      
SMITH             800 CLERK                                                      
SMITH             800 CLERK                                                      
 
ENAME             SAL JOB                                                        
---------- ---------- ---------                                                  
SCOTT            3000 ANALYST                                                    
SCOTT            3000 ANALYST                                                    
SCOTT            3000 ANALYST                                                    
SCOTT            3000 ANALYST                                                    
ADAMS            1100 CLERK                                                      
ADAMS            1100 CLERK                                                      
ADAMS            1100 CLERK                                                      
ADAMS            1100 CLERK                                                      
MILLER           1300 CLERK                                                      
MILLER           1300 CLERK                                                      
MILLER           1300 CLERK                                                      
 
ENAME             SAL JOB                                                        
---------- ---------- ---------                                                  
MILLER           1300 CLERK                                                      
 
56 rows selected. 
 
SQL> ED 
 

Wrote file afiedt.buf 
 
  1  SELECT Dname, Loc 
  2 FROM Emp, Dept 
SQL> / 
 
DNAME          LOC                                                               
-------------- -------------                                                     
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
 
DNAME          LOC                                                               
-------------- -------------                                                     
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
ACCOUNTING     NEW YORK                                                          
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
 
DNAME          LOC                                                               
-------------- -------------                                                     
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
RESEARCH       DALLAS                                                            
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
 
DNAME          LOC                                                               
-------------- -------------                                                     
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
 

SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
SALES          CHICAGO                                                           
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
 
DNAME          LOC                                                               
-------------- -------------                                                     
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
OPERATIONS     BOSTON                                                            
 
DNAME          LOC                                                               
-------------- -------------                                                     
OPERATIONS     BOSTON                                                            
 
56 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Deptno, Dname, Loc 
  2 FROM Emp, Dept 
SQL> / 
SELECT Ename, Job, Deptno, Dname, Loc 
                    
ERROR at line 1: 
ORA-00918: column ambiguously defined  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Emp.Deptno, Dname, Loc 
  2 FROM Emp, Dept 
SQL> / 
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
KING       PRESIDENT         10 ACCOUNTING     NEW YORK                          
BLAKE      MANAGER           30 ACCOUNTING     NEW YORK                          
CLARK      MANAGER           10 ACCOUNTING     NEW YORK                          
JONES      MANAGER           20 ACCOUNTING     NEW YORK                          
MARTIN     SALESMAN          30 ACCOUNTING     NEW YORK                          
ALLEN      SALESMAN          30 ACCOUNTING     NEW YORK                          
TURNER     SALESMAN          30 ACCOUNTING     NEW YORK                          
JAMES      CLERK             30 ACCOUNTING     NEW YORK                          
WARD       SALESMAN          30 ACCOUNTING     NEW YORK                          
 

FORD       ANALYST           20 ACCOUNTING     NEW YORK                          
SMITH      CLERK             20 ACCOUNTING     NEW YORK                          
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
SCOTT      ANALYST           20 ACCOUNTING     NEW YORK                          
ADAMS      CLERK             20 ACCOUNTING     NEW YORK                          
MILLER     CLERK             10 ACCOUNTING     NEW YORK                          
KING       PRESIDENT         10 RESEARCH       DALLAS                            
BLAKE      MANAGER           30 RESEARCH       DALLAS                            
CLARK      MANAGER           10 RESEARCH       DALLAS                            
JONES      MANAGER           20 RESEARCH       DALLAS                            
MARTIN     SALESMAN          30 RESEARCH       DALLAS                            
ALLEN      SALESMAN          30 RESEARCH       DALLAS                            
TURNER     SALESMAN          30 RESEARCH       DALLAS                            
JAMES      CLERK             30 RESEARCH       DALLAS                            
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
WARD       SALESMAN          30 RESEARCH       DALLAS                            
FORD       ANALYST           20 RESEARCH       DALLAS                            
SMITH      CLERK             20 RESEARCH       DALLAS                            
SCOTT      ANALYST           20 RESEARCH       DALLAS                            
ADAMS      CLERK             20 RESEARCH       DALLAS                            
MILLER     CLERK             10 RESEARCH       DALLAS                            
KING       PRESIDENT         10 SALES          CHICAGO                           
BLAKE      MANAGER           30 SALES          CHICAGO                           
CLARK      MANAGER           10 SALES          CHICAGO                           
JONES      MANAGER           20 SALES          CHICAGO                           
MARTIN     SALESMAN          30 SALES          CHICAGO                           
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
ALLEN      SALESMAN          30 SALES          CHICAGO                           
TURNER     SALESMAN          30 SALES          CHICAGO                           
JAMES      CLERK             30 SALES          CHICAGO                           
WARD       SALESMAN          30 SALES          CHICAGO                           
FORD       ANALYST           20 SALES          CHICAGO                           
SMITH      CLERK             20 SALES          CHICAGO                           
SCOTT      ANALYST           20 SALES          CHICAGO                           
ADAMS      CLERK             20 SALES          CHICAGO                           
MILLER     CLERK             10 SALES          CHICAGO                           
KING       PRESIDENT         10 OPERATIONS     BOSTON                            
BLAKE      MANAGER           30 OPERATIONS     BOSTON                            
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
CLARK      MANAGER           10 OPERATIONS     BOSTON                            
JONES      MANAGER           20 OPERATIONS     BOSTON                            
MARTIN     SALESMAN          30 OPERATIONS     BOSTON                            
ALLEN      SALESMAN          30 OPERATIONS     BOSTON                            
TURNER     SALESMAN          30 OPERATIONS     BOSTON                            
JAMES      CLERK             30 OPERATIONS     BOSTON                            
WARD       SALESMAN          30 OPERATIONS     BOSTON                            
FORD       ANALYST           20 OPERATIONS     BOSTON                            
SMITH      CLERK             20 OPERATIONS     BOSTON                            
 

SCOTT      ANALYST           20 OPERATIONS     BOSTON                            
ADAMS      CLERK             20 OPERATIONS     BOSTON                            
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
MILLER     CLERK             10 OPERATIONS     BOSTON                            
 
56 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Dept.Deptno, Dname, Loc 
  2 FROM Emp, Dept 
SQL> / 
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
KING       PRESIDENT         10 ACCOUNTING     NEW YORK                          
BLAKE      MANAGER           10 ACCOUNTING     NEW YORK                          
CLARK      MANAGER           10 ACCOUNTING     NEW YORK                          
JONES      MANAGER           10 ACCOUNTING     NEW YORK                          
MARTIN     SALESMAN          10 ACCOUNTING     NEW YORK                          
ALLEN      SALESMAN          10 ACCOUNTING     NEW YORK                          
TURNER     SALESMAN          10 ACCOUNTING     NEW YORK                          
JAMES      CLERK             10 ACCOUNTING     NEW YORK                          
WARD       SALESMAN          10 ACCOUNTING     NEW YORK                          
FORD       ANALYST           10 ACCOUNTING     NEW YORK                          
SMITH      CLERK             10 ACCOUNTING     NEW YORK                          
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
SCOTT      ANALYST           10 ACCOUNTING     NEW YORK                          
ADAMS      CLERK             10 ACCOUNTING     NEW YORK                          
MILLER     CLERK             10 ACCOUNTING     NEW YORK                          
KING       PRESIDENT         20 RESEARCH       DALLAS                            
BLAKE      MANAGER           20 RESEARCH       DALLAS                            
CLARK      MANAGER           20 RESEARCH       DALLAS                            
JONES      MANAGER           20 RESEARCH       DALLAS                            
MARTIN     SALESMAN          20 RESEARCH       DALLAS                            
ALLEN      SALESMAN          20 RESEARCH       DALLAS                            
TURNER     SALESMAN          20 RESEARCH       DALLAS                            
JAMES      CLERK             20 RESEARCH       DALLAS                            
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
WARD       SALESMAN          20 RESEARCH       DALLAS                            
FORD       ANALYST           20 RESEARCH       DALLAS                            
SMITH      CLERK             20 RESEARCH       DALLAS                            
SCOTT      ANALYST           20 RESEARCH       DALLAS                            
ADAMS      CLERK             20 RESEARCH       DALLAS                            
MILLER     CLERK             20 RESEARCH       DALLAS                            
KING       PRESIDENT         30 SALES          CHICAGO                           
BLAKE      MANAGER           30 SALES          CHICAGO                           
CLARK      MANAGER           30 SALES          CHICAGO                           
JONES      MANAGER           30 SALES          CHICAGO                           
 

MARTIN     SALESMAN          30 SALES          CHICAGO                           
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
ALLEN      SALESMAN          30 SALES          CHICAGO                           
TURNER     SALESMAN          30 SALES          CHICAGO                           
JAMES      CLERK             30 SALES          CHICAGO                           
WARD       SALESMAN          30 SALES          CHICAGO                           
FORD       ANALYST           30 SALES          CHICAGO                           
SMITH      CLERK             30 SALES          CHICAGO                           
SCOTT      ANALYST           30 SALES          CHICAGO                           
ADAMS      CLERK             30 SALES          CHICAGO                           
MILLER     CLERK             30 SALES          CHICAGO                           
KING       PRESIDENT         40 OPERATIONS     BOSTON                            
BLAKE      MANAGER           40 OPERATIONS     BOSTON                            
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
CLARK      MANAGER           40 OPERATIONS     BOSTON                            
JONES      MANAGER           40 OPERATIONS     BOSTON                            
MARTIN     SALESMAN          40 OPERATIONS     BOSTON                            
ALLEN      SALESMAN          40 OPERATIONS     BOSTON                            
TURNER     SALESMAN          40 OPERATIONS     BOSTON                            
JAMES      CLERK             40 OPERATIONS     BOSTON                            
WARD       SALESMAN          40 OPERATIONS     BOSTON                            
FORD       ANALYST           40 OPERATIONS     BOSTON                            
SMITH      CLERK             40 OPERATIONS     BOSTON                            
SCOTT      ANALYST           40 OPERATIONS     BOSTON                            
ADAMS      CLERK             40 OPERATIONS     BOSTON                            
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
MILLER     CLERK             40 OPERATIONS     BOSTON                            
 
56 rows selected. 
 


 
SQL> SELECT Ename, Emp.Deptno, Dname, Loc 
  2  FROM Emp, Dept 
  3  WHERE Emp.Deptno = Dept.Deptno; 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
BLAKE              30 SALES          CHICAGO                                     
CLARK              10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
FORD               20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
 

 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
MILLER             10 ACCOUNTING     NEW YORK                                    
 
14 rows selected. 
 
SQL> SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
 

 
SQL> Select 
  2  Empno, 
  3  Ename, 
  4  Sal, 
  5  Sal * 12 AnnSal, 
  6  Emp.Deptno, 
  7  Loc 
  8  FROM Emp, Dept 
  9  WHERE Emp.Deptno = Dept.Deptno; 
 
     EMPNO ENAME             SAL     ANNSAL     DEPTNO LOC                       
---------- ---------- ---------- ---------- ---------- -------------             
      7839 KING             5000      60000         10 NEW YORK                  
      7782 CLARK            2450      29400         10 NEW YORK                  
      7934 MILLER           1300      15600         10 NEW YORK                  
      7566 JONES            2975      35700         20 DALLAS                    
      7788 SCOTT            3000      36000         20 DALLAS                    
      7876 ADAMS            1100      13200         20 DALLAS                    
      7369 SMITH             800       9600         20 DALLAS                    
      7902 FORD             3000      36000         20 DALLAS                    
      7698 BLAKE            2850      34200         30 CHICAGO                   
      7654 MARTIN           1250      15000         30 CHICAGO                   
      7499 ALLEN            1600      19200         30 CHICAGO                   
 
     EMPNO ENAME             SAL     ANNSAL     DEPTNO LOC                       
---------- ---------- ---------- ---------- ---------- -------------             
      7844 TURNER           1500      18000         30 CHICAGO                   
      7900 JAMES             950      11400         30 CHICAGO                   
      7521 WARD             1250      15000         30 CHICAGO                   
 
14 rows selected. 
 

 
SQL> Select 
  2  Dept.Deptno, 
 

  3  Dname, 
  4  Loc, 
  5  SUM(Sal) 
  6  FROM Emp, Dept 
  7  WHERE Emp.Deptno = Dept.Deptno 
  8  GROUP BY Dept.Deptno, Dname, Loc; 
 
    DEPTNO DNAME          LOC             SUM(SAL)                               
---------- -------------- ------------- ----------                               
        10 ACCOUNTING     NEW YORK            8750                               
        20 RESEARCH       DALLAS             10875                               
        30 SALES          CHICAGO             9400                               
 
SQL> Select 
  2  TO_CHAR(HireDate, 'YYYY') Year, 
  3  Dept.Deptno, 
  4  Dname, 
  5  SUM(Sal) 
  6  FROM Emp, Dept 
  7  WHERE Emp.Deptno = Dept.Deptno 
  8  GROUP BY TO_CHAR(HireDate, 'YYYY'), Dept.Deptno, Dname 
  9  ORDER BY Year; 
 
YEAR     DEPTNO DNAME            SUM(SAL)                                        
---- ---------- -------------- ----------                                        
1980         20 RESEARCH              800                                        
1981         10 ACCOUNTING           7450                                        
1981         20 RESEARCH             5975                                        
1981         30 SALES                9400                                        
1982         10 ACCOUNTING           1300                                        
1982         20 RESEARCH             3000                                        
1983         20 RESEARCH             1100                                        
 
7 rows selected. 
 

 
SQL> Select 
  2  TO_CHAR(HireDate, 'YYYY') Year, 
  3  Dept.Deptno, 
  4  Dname, 
  5  SUM(Sal) 
  6  FROM Emp, Dept 
  7  WHERE Emp.Deptno = Dept.Deptno 
  8  GROUP BY TO_CHAR(HireDate, 'YYYY'), Dept.Deptno, Dname 
  9  HAVING COUNT(TO_CHAR(HireDate, 'YYYY')) > 2 
 10  ORDER BY Year; 
 
YEAR     DEPTNO DNAME            SUM(SAL)                                        
---- ---------- -------------- ----------                                        
1981         30 SALES                9400                                        
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  TO_CHAR(HireDate, 'YYYY') Year, 
  3  Dept.Deptno, 
  4  Dname, 
  5  SUM(Sal) 
  6  FROM Emp, Dept 
  7  WHERE Emp.Deptno = Dept.Deptno 
  8  GROUP BY TO_CHAR(HireDate, 'YYYY'), Dept.Deptno, Dname 
  9 ORDER BY Year 
SQL> / 
 
YEAR     DEPTNO DNAME            SUM(SAL)                                        
---- ---------- -------------- ----------                                        
1980         20 RESEARCH              800                                        
1981         10 ACCOUNTING           7450                                        
1981         20 RESEARCH             5975                                        
1981         30 SALES                9400                                        
1982         10 ACCOUNTING           1300                                        
1982         20 RESEARCH             3000                                        
1983         20 RESEARCH             1100                                        
 
7 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  Select 
  2  TO_CHAR(HireDate, 'YYYY') Year, 
  3  Dept.Deptno, 
  4  Dname, 
  5  COUNT(*) Cnt, 
  6  SUM(Sal) 
  7  FROM Emp, Dept 
  8  WHERE Emp.Deptno = Dept.Deptno 
  9  GROUP BY TO_CHAR(HireDate, 'YYYY'), Dept.Deptno, Dname 
 10 ORDER BY Year 
SQL> / 
 
YEAR     DEPTNO DNAME                 CNT   SUM(SAL)                             
---- ---------- -------------- ---------- ----------                             
1980         20 RESEARCH                1        800                             
1981         10 ACCOUNTING              2       7450                             
1981         20 RESEARCH                2       5975                             
1981         30 SALES                   6       9400                             
1982         10 ACCOUNTING              1       1300                             
1982         20 RESEARCH                1       3000                             
1983         20 RESEARCH                1       1100                             
 
7 rows selected. 
 

 
SQL> SELECT Ename, Ename 
  2  FROM Emp; 
 
ENAME      ENAME                                                                 
---------- ----------                                                            
 

KING       KING                                                                  
BLAKE      BLAKE                                                                 
CLARK      CLARK                                                                 
JONES      JONES                                                                 
MARTIN     MARTIN                                                                
ALLEN      ALLEN                                                                 
TURNER     TURNER                                                                
JAMES      JAMES                                                                 
WARD       WARD                                                                  
FORD       FORD                                                                  
SMITH      SMITH                                                                 
 
ENAME      ENAME                                                                 
---------- ----------                                                            
SCOTT      SCOTT                                                                 
ADAMS      ADAMS                                                                 
MILLER     MILLER                                                                
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Job, Sal 
  2 FROM Emp, Emp 
SQL> / 
SELECT Ename, Job, Sal 
                    
ERROR at line 1: 
ORA-00918: column ambiguously defined  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 125 
  2 FROM Emp 
SQL> / 
 
       125                                                                       
----------                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
       125                                                                       
 
       125                                                                       
----------                                                                       
       125                                                                       
 

       125                                                                       
       125                                                                       
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 125 
  2 FROM Emp, Emp 
SQL> / 
 

 
SQL> SELECT Ename 
  2  FROM Emp, Emp 
  3   
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename 
  2 FROM Emp E1, Emp E2 
SQL> / 
SELECT Ename 
        
ERROR at line 1: 
ORA-00918: column ambiguously defined  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT E1.Ename 
  2 FROM Emp E1, Emp E2 
SQL> / 
 
  
SQL> SELECT 
  2  E.Empno, 
  3  E.Ename, 
  4  D.Deptno, 
  5  D.Dname 
  6  FROM Emp E, Dept D 
  7  WHERE E.Deptno = D.Deptno; 
 
     EMPNO ENAME          DEPTNO DNAME                                           
---------- ---------- ---------- --------------                                  
      7839 KING               10 ACCOUNTING                                      
      7782 CLARK              10 ACCOUNTING                                      
      7934 MILLER             10 ACCOUNTING                                      
      7566 JONES              20 RESEARCH                                        
      7788 SCOTT              20 RESEARCH                                        
      7876 ADAMS              20 RESEARCH                                        
      7369 SMITH              20 RESEARCH                                        
 

      7902 FORD               20 RESEARCH                                        
      7698 BLAKE              30 SALES                                           
      7654 MARTIN             30 SALES                                           
      7499 ALLEN              30 SALES                                           
 
     EMPNO ENAME          DEPTNO DNAME                                           
---------- ---------- ---------- --------------                                  
      7844 TURNER             30 SALES                                           
      7900 JAMES              30 SALES                                           
      7521 WARD               30 SALES                                           
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  E.Empno, 
  3  E.Ename, 
  4  Dept.Deptno, 
  5  D.Dname 
  6  FROM Emp E, Dept D 
  7 WHERE E.Deptno = D.Deptno 
SQL> / 
Dept.Deptno, 
 
ERROR at line 4: 
ORA-00904: "DEPT"."DEPTNO": invalid identifier  
 
 
SQL> SELECT 
  2  E.Ename, 
  3  E.Job, 
  4  D.Deptno, 
  5  D.Dname, 
  6  D.Loc 
  7  FROM Emp E, Dept D 
  8  WHERE E.Deptno = D.Deptno AND 
  9  E.Job IN('ANALYST', 'MANAGER' ); 
 
ENAME      JOB           DEPTNO DNAME          LOC                               
---------- --------- ---------- -------------- -------------                     
CLARK      MANAGER           10 ACCOUNTING     NEW YORK                          
JONES      MANAGER           20 RESEARCH       DALLAS                            
FORD       ANALYST           20 RESEARCH       DALLAS                            
SCOTT      ANALYST           20 RESEARCH       DALLAS                            
BLAKE      MANAGER           30 SALES          CHICAGO                           
 
 
SQL> SELECT 
  2  Ename, 
  3  DName, 
  4  Loc 
  5  FROM Emp Employees, Dept Departments 
  6  WHERE Employees.Deptno = Departments.Deptno; 
 
ENAME      DNAME          LOC                                                    
---------- -------------- -------------                                          
KING       ACCOUNTING     NEW YORK                                               
CLARK      ACCOUNTING     NEW YORK                                               
MILLER     ACCOUNTING     NEW YORK                                               
JONES      RESEARCH       DALLAS                                                 
SCOTT      RESEARCH       DALLAS                                                 
ADAMS      RESEARCH       DALLAS                                                 
SMITH      RESEARCH       DALLAS                                                 
FORD       RESEARCH       DALLAS                                                 
BLAKE      SALES          CHICAGO                                                
MARTIN     SALES          CHICAGO                                                
ALLEN      SALES          CHICAGO                                                
 
ENAME      DNAME          LOC                                                    
---------- -------------- -------------                                          
TURNER     SALES          CHICAGO                                                
JAMES      SALES          CHICAGO                                                
WARD       SALES          CHICAGO                                                
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  Ename, 
  3  DName, 
  4  Loc 
  5  FROM Emp E, Dept D 
  6 WHERE E.Deptno = D.Deptno 
SQL> / 
 
ENAME      DNAME          LOC                                                    
---------- -------------- -------------                                          
KING       ACCOUNTING     NEW YORK                                               
CLARK      ACCOUNTING     NEW YORK                                               
MILLER     ACCOUNTING     NEW YORK                                               
JONES      RESEARCH       DALLAS                                                 
SCOTT      RESEARCH       DALLAS                                                 
ADAMS      RESEARCH       DALLAS                                                 
SMITH      RESEARCH       DALLAS                                                 
FORD       RESEARCH       DALLAS                                                 
BLAKE      SALES          CHICAGO                                                
MARTIN     SALES          CHICAGO                                                
ALLEN      SALES          CHICAGO                                                
 
ENAME      DNAME          LOC                                                    
---------- -------------- -------------                                          
TURNER     SALES          CHICAGO                                                
JAMES      SALES          CHICAGO                                                
WARD       SALES          CHICAGO                                                
 
14 rows selected. 

 
SQL> SELECT Empno, Ename, MGR 
  2  FROM Emp; 
 
     EMPNO ENAME             MGR                                                 
---------- ---------- ----------                                                 
      7839 KING                                                                  
      7698 BLAKE            7839                                                 
      7782 CLARK            7839                                                 
      7566 JONES            7839                                                 
      7654 MARTIN           7698                                                 
      7499 ALLEN            7698                                                 
      7844 TURNER           7698                                                 
      7900 JAMES            7698                                                 
      7521 WARD             7698                                                 
      7902 FORD             7566                                                 
      7369 SMITH            7902                                                 
 
     EMPNO ENAME             MGR                                                 
---------- ---------- ----------                                                 
      7788 SCOTT            7566                                                 
      7876 ADAMS            7788                                                 
      7934 MILLER           7782                                                 
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Empno, Ename, Sal, MGR 
  2 FROM Emp 
SQL> / 
 
     EMPNO ENAME             SAL        MGR                                      
---------- ---------- ---------- ----------                                      
      7839 KING             5000                                                 
      7698 BLAKE            2850       7839                                      
      7782 CLARK            2450       7839                                      
      7566 JONES            2975       7839                                      
      7654 MARTIN           1250       7698                                      
      7499 ALLEN            1600       7698                                      
      7844 TURNER           1500       7698                                      
      7900 JAMES             950       7698                                      
      7521 WARD             1250       7698                                      
      7902 FORD             3000       7566                                      
      7369 SMITH             800       7902                                      
 
     EMPNO ENAME             SAL        MGR                                      
---------- ---------- ---------- ----------                                      
      7788 SCOTT            3000       7566                                      
      7876 ADAMS            1100       7788                                      
      7934 MILLER           1300       7782                                      
 
14 rows selected. 
 
SQL> SELECT 
  2  E1.Ename "Employees", 
  3  E2. Ename "Managers" 
  4  FROM Emp E1, Emp E2 
  5  WHERE E1.Mgr = E2.Empno; 
 
Employees  Managers                                                              
---------- ----------                                                            
FORD       JONES                                                                 
SCOTT      JONES                                                                 
MARTIN     BLAKE                                                                 
ALLEN      BLAKE                                                                 
JAMES      BLAKE                                                                 
TURNER     BLAKE                                                                 
WARD       BLAKE                                                                 
MILLER     CLARK                                                                 
ADAMS      SCOTT                                                                 
BLAKE      KING                                                                  
CLARK      KING                                                                  
 
Employees  Managers                                                              
---------- ----------                                                            
JONES      KING                                                                  
SMITH      FORD                                                                  
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  E1.Ename, 
  3  E2. Ename 
  4  FROM Emp E1, Emp E2 
  5 WHERE E1.Mgr = E2.Empno 
SQL> / 
 
ENAME      ENAME                                                                 
---------- ----------                                                            
FORD       JONES                                                                 
SCOTT      JONES                                                                 
MARTIN     BLAKE                                                                 
ALLEN      BLAKE                                                                 
JAMES      BLAKE                                                                 
TURNER     BLAKE                                                                 
WARD       BLAKE                                                                 
MILLER     CLARK                                                                 
ADAMS      SCOTT                                                                 
BLAKE      KING                                                                  
CLARK      KING                                                                  
 
ENAME      ENAME                                                                 
---------- ----------                                                            
JONES      KING                                                                  
 

SMITH      FORD                                                                  
 
13 rows selected. 

SQL> SELECT 
  2  Employees.Ename "Employees", 
  3  Managers.Ename "Managers" 
  4  FROM Emp Employees, Emp Managers 
  5  WHERE Employees.Mgr = Managers.Empno; 
 
Employees  Managers                                                              
---------- ----------                                                            
FORD       JONES                                                                 
SCOTT      JONES                                                                 
MARTIN     BLAKE                                                                 
ALLEN      BLAKE                                                                 
JAMES      BLAKE                                                                 
TURNER     BLAKE                                                                 
WARD       BLAKE                                                                 
MILLER     CLARK                                                                 
ADAMS      SCOTT                                                                 
BLAKE      KING                                                                  
CLARK      KING                                                                  
 
Employees  Managers                                                              
---------- ----------                                                            
JONES      KING                                                                  
SMITH      FORD                                                                  
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT 
  2  Employees.Ename "Employees", 
  3  Managers.Ename "Managers" 
  4  FROM Emp Employees, Emp Managers 
  5 WHERE Managers.Mgr = Employees.Empno 
SQL> / 
 
Employees  Managers                                                              
---------- ----------                                                            
JONES      FORD                                                                  
JONES      SCOTT                                                                 
BLAKE      MARTIN                                                                
BLAKE      ALLEN                                                                 
BLAKE      JAMES                                                                 
BLAKE      TURNER                                                                
BLAKE      WARD                                                                  
CLARK      MILLER                                                                
SCOTT      ADAMS                                                                 
KING       BLAKE                                                                 
KING       CLARK                                                                 
 

 
Employees  Managers                                                              
---------- ----------                                                            
KING       JONES                                                                 
FORD       SMITH                                                                 
 
13 rows selected. 

SQL> SELECT 
  2  E1.Ename||'''s Manager is '|| 
  3  E2.Ename  "Employees And Managers" 
  4  FROM  Emp E1, Emp E2 
  5  WHERE E1.Mgr = E2.Empno; 
 
Employees And Managers                                                           
----------------------------------                                               
FORD's Manager is JONES                                                          
SCOTT's Manager is JONES                                                         
MARTIN's Manager is BLAKE                                                        
ALLEN's Manager is BLAKE                                                         
JAMES's Manager is BLAKE                                                         
TURNER's Manager is BLAKE                                                        
WARD's Manager is BLAKE                                                          
MILLER's Manager is CLARK                                                        
ADAMS's Manager is SCOTT                                                         
BLAKE's Manager is KING                                                          
CLARK's Manager is KING                                                          
 
Employees And Managers                                                           
----------------------------------                                               
JONES's Manager is KING                                                          
SMITH's Manager is FORD                                                          
 
13 rows selected. 
' 

 
SQL> SELECT Empno, Ename, Sal 
  2  FROM Emp; 
 
     EMPNO ENAME             SAL                                                 
---------- ---------- ----------                                                 
      7839 KING             5000                                                 
      7698 BLAKE            2850                                                 
      7782 CLARK            2450                                                 
      7566 JONES            2975                                                 
      7654 MARTIN           1250                                                 
      7499 ALLEN            1600                                                 
      7844 TURNER           1500                                                 
      7900 JAMES             950                                                 
      7521 WARD             1250                                                 
      7902 FORD             3000                                                 
      7369 SMITH             800                                                 
 

 
     EMPNO ENAME             SAL                                                 
---------- ---------- ----------                                                 
      7788 SCOTT            3000                                                 
      7876 ADAMS            1100                                                 
      7934 MILLER           1300                                                 
 
14 rows selected. 
 
SQL> SELECT LoSal, HiSal, Grade 
  2  FROM DalGrade; 
FROM DalGrade 
      
ERROR at line 2: 
ORA-00942: table or view does not exist  
 
 
SQL> Ed 
Wrote file afiedt.buf 
 
  1  SELECT LoSal, HiSal, Grade 
  2 FROM SalGrade 
SQL> / 
 
     LOSAL      HISAL      GRADE                                                 
---------- ---------- ----------                                                 
       700       1200          1                                                 
      1201       1400          2                                                 
      1401       2000          3                                                 
      2001       3000          4                                                 
      3001       9999          5                                                 
 
SQL> SELECT Empno, Ename, Sal 
  2  FROM Emp; 
 
     EMPNO ENAME             SAL                                                 
---------- ---------- ----------                                                 
      7839 KING             5000                                                 
      7698 BLAKE            2850                                                 
      7782 CLARK            2450                                                 
      7566 JONES            2975                                                 
      7654 MARTIN           1250                                                 
      7499 ALLEN            1600                                                 
      7844 TURNER           1500                                                 
      7900 JAMES             950                                                 
      7521 WARD             1250                                                 
      7902 FORD             3000                                                 
      7369 SMITH             800                                                 
 
     EMPNO ENAME             SAL                                                 
---------- ---------- ----------                                                 
      7788 SCOTT            3000                                                 
      7876 ADAMS            1100                                                 
      7934 MILLER           1300                                                 
 
14 rows selected. 
 
SQL> SELECT Ename, Emp.Deptno, Dname, Loc 
  2  FROM Emp, Dept 
  3  WHERE Emp.Deptno = Dept.Deptno; 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
BLAKE              30 SALES          CHICAGO                                     
CLARK              10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
FORD               20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
MILLER             10 ACCOUNTING     NEW YORK                                    
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Emp.Deptno, Dname, Loc 
  2  FROM Emp, Dept 
  3 WHERE Emp.Deptno > Dept.Deptno 
SQL> / 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
JONES              20 ACCOUNTING     NEW YORK                                    
SCOTT              20 ACCOUNTING     NEW YORK                                    
ADAMS              20 ACCOUNTING     NEW YORK                                    
SMITH              20 ACCOUNTING     NEW YORK                                    
FORD               20 ACCOUNTING     NEW YORK                                    
BLAKE              30 ACCOUNTING     NEW YORK                                    
MARTIN             30 ACCOUNTING     NEW YORK                                    
ALLEN              30 ACCOUNTING     NEW YORK                                    
TURNER             30 ACCOUNTING     NEW YORK                                    
JAMES              30 ACCOUNTING     NEW YORK                                    
WARD               30 ACCOUNTING     NEW YORK                                    
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
BLAKE              30 RESEARCH       DALLAS                                      
MARTIN             30 RESEARCH       DALLAS                                      
ALLEN              30 RESEARCH       DALLAS                                      
 

TURNER             30 RESEARCH       DALLAS                                      
JAMES              30 RESEARCH       DALLAS                                      
WARD               30 RESEARCH       DALLAS                                      
 
17 rows selected. 
 
SQL> SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
 

 
SQL> SELECT Ename, Deptno, Job, Sal, Grade 
  2  FROM Emp, SalGrade 
  3  WHERE Emp.Sal >= SalGrade.LoSal AND 
  4        Emp.Sal <= SalGrade.HiSal; 
 
ENAME          DEPTNO JOB              SAL      GRADE                            
---------- ---------- --------- ---------- ----------                            
SMITH              20 CLERK            800          1                            
JAMES              30 CLERK            950          1                            
ADAMS              20 CLERK           1100          1                            
MARTIN             30 SALESMAN        1250          2                            
WARD               30 SALESMAN        1250          2                            
MILLER             10 CLERK           1300          2                            
TURNER             30 SALESMAN        1500          3                            
ALLEN              30 SALESMAN        1600          3                            
CLARK              10 MANAGER         2450          4                            
BLAKE              30 MANAGER         2850          4                            
JONES              20 MANAGER         2975          4                            
 
ENAME          DEPTNO JOB              SAL      GRADE                            
---------- ---------- --------- ---------- ----------                            
FORD               20 ANALYST         3000          4                            
SCOTT              20 ANALYST         3000          4                            
KING               10 PRESIDENT       5000          5                            
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Job, Sal, Grade 
  2  FROM Emp, SalGrade 
  3 WHERE Emp.Sal BETWEEN SalGrade.LoSal AND SalGrade.HiSal 
SQL> / 
 
ENAME          DEPTNO JOB              SAL      GRADE                            
---------- ---------- --------- ---------- ----------                            
SMITH              20 CLERK            800          1                            
JAMES              30 CLERK            950          1                            
 

ADAMS              20 CLERK           1100          1                            
MARTIN             30 SALESMAN        1250          2                            
WARD               30 SALESMAN        1250          2                            
MILLER             10 CLERK           1300          2                            
TURNER             30 SALESMAN        1500          3                            
ALLEN              30 SALESMAN        1600          3                            
CLARK              10 MANAGER         2450          4                            
BLAKE              30 MANAGER         2850          4                            
JONES              20 MANAGER         2975          4                            
 
ENAME          DEPTNO JOB              SAL      GRADE                            
---------- ---------- --------- ---------- ----------                            
FORD               20 ANALYST         3000          4                            
SCOTT              20 ANALYST         3000          4                            
KING               10 PRESIDENT       5000          5                            
 
14 rows selected. 
 

 
SQL> SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
 
SQL> INSERT INTO Dept 
  2  VALUES(50, 'SHIPPING', 'CHENNAI'); 
 
1 row created. 
 
SQL> INSERT INTO Dept 
  2  VALUES(60, 'CARGO', 'MUMBAI'); 
 
1 row created. 
 
SQL> INSERT INTO Dept 
  2  VALUES(70, 'COURIER', 'DELHI'); 
 
1 row created. 
 
SQL> SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
        50 SHIPPING       CHENNAI                                                
        60 CARGO          MUMBAI                                                 
        70 COURIER        DELHI                                                  
 
 

7 rows selected. 
 
SQL> COLUMN Empno FORMAT 9999 
SQL> COLUMN Sal FORMAT 9999 
SQL> COLUMN Comm FORMAT 9999 
SQL> COLUMN Deptno FORMAT 99 
SQL> SELECT * FROM Emp ORDER BY Deptno; 
 
EMPNO ENAME      JOB              MGR HIREDATE    SAL  COMM DEPTNO               
----- ---------- --------- ---------- --------- ----- ----- ------               
 7839 KING       PRESIDENT            17-NOV-81  5000           10               
 7782 CLARK      MANAGER         7839 09-JUN-81  2450           10               
 7934 MILLER     CLERK           7782 23-JAN-82  1300           10               
 7566 JONES      MANAGER         7839 02-APR-81  2975           20               
 7788 SCOTT      ANALYST         7566 09-DEC-82  3000           20               
 7876 ADAMS      CLERK           7788 12-JAN-83  1100           20               
 7369 SMITH      CLERK           7902 17-DEC-80   800           20               
 7902 FORD       ANALYST         7566 03-DEC-81  3000           20               
 7698 BLAKE      MANAGER         7839 01-MAY-81  2850           30               
 7654 MARTIN     SALESMAN        7698 28-SEP-81  1250  1400     30               
 7499 ALLEN      SALESMAN        7698 20-FEB-81  1600   300     30               
 
EMPNO ENAME      JOB              MGR HIREDATE    SAL  COMM DEPTNO               
----- ---------- --------- ---------- --------- ----- ----- ------               
 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               
 
14 rows selected. 
 
SQL> SELECT Ename, Sal, Emp.Deptno Deptno, Dname, Loc 
  2  FROM Emp, Dept 
  3  WHERE Emp.Deptno = Dept.Deptno; 
 
ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
KING        5000     10 ACCOUNTING     NEW YORK                                  
CLARK       2450     10 ACCOUNTING     NEW YORK                                  
MILLER      1300     10 ACCOUNTING     NEW YORK                                  
JONES       2975     20 RESEARCH       DALLAS                                    
SCOTT       3000     20 RESEARCH       DALLAS                                    
ADAMS       1100     20 RESEARCH       DALLAS                                    
SMITH        800     20 RESEARCH       DALLAS                                    
FORD        3000     20 RESEARCH       DALLAS                                    
BLAKE       2850     30 SALES          CHICAGO                                   
MARTIN      1250     30 SALES          CHICAGO                                   
ALLEN       1600     30 SALES          CHICAGO                                   
 
ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
TURNER      1500     30 SALES          CHICAGO                                   
JAMES        950     30 SALES          CHICAGO                                   
WARD        1250     30 SALES          CHICAGO                                   
 
14 rows selected. 
 
 

SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Emp.Deptno Deptno, Dname, Loc 
  2  FROM Emp, Dept 
  3 WHERE Emp.Deptno(+) = Dept.Deptno 
SQL> / 
 
ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
KING        5000     10 ACCOUNTING     NEW YORK                                  
CLARK       2450     10 ACCOUNTING     NEW YORK                                  
MILLER      1300     10 ACCOUNTING     NEW YORK                                  
JONES       2975     20 RESEARCH       DALLAS                                    
SCOTT       3000     20 RESEARCH       DALLAS                                    
ADAMS       1100     20 RESEARCH       DALLAS                                    
SMITH        800     20 RESEARCH       DALLAS                                    
FORD        3000     20 RESEARCH       DALLAS                                    
BLAKE       2850     30 SALES          CHICAGO                                   
MARTIN      1250     30 SALES          CHICAGO                                   
ALLEN       1600     30 SALES          CHICAGO                                   
 
ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
TURNER      1500     30 SALES          CHICAGO                                   
JAMES        950     30 SALES          CHICAGO                                   
WARD        1250     30 SALES          CHICAGO                                   
                        OPERATIONS     BOSTON                                    
                        SHIPPING       CHENNAI                                   
                        CARGO          MUMBAI                                    
                        COURIER        DELHI                                     
 
18 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Dept.Deptno Deptno, Dname, Loc 
  2  FROM Emp, Dept 
  3 WHERE Emp.Deptno(+) = Dept.Deptno 
SQL> / 
 
ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
KING        5000     10 ACCOUNTING     NEW YORK                                  
CLARK       2450     10 ACCOUNTING     NEW YORK                                  
MILLER      1300     10 ACCOUNTING     NEW YORK                                  
JONES       2975     20 RESEARCH       DALLAS                                    
SCOTT       3000     20 RESEARCH       DALLAS                                    
ADAMS       1100     20 RESEARCH       DALLAS                                    
SMITH        800     20 RESEARCH       DALLAS                                    
FORD        3000     20 RESEARCH       DALLAS                                    
BLAKE       2850     30 SALES          CHICAGO                                   
MARTIN      1250     30 SALES          CHICAGO                                   
ALLEN       1600     30 SALES          CHICAGO                                   
 
 

ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
TURNER      1500     30 SALES          CHICAGO                                   
JAMES        950     30 SALES          CHICAGO                                   
WARD        1250     30 SALES          CHICAGO                                   
                     40 OPERATIONS     BOSTON                                    
                     50 SHIPPING       CHENNAI                                   
                     60 CARGO          MUMBAI                                    
                     70 COURIER        DELHI                                     
 
18 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Dept.Deptno Deptno, Dname, Loc 
  2  FROM Emp, Dept 
  3 WHERE Emp.Deptno = Dept.Deptno(+) 
SQL> / 
 
ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
MILLER      1300     10 ACCOUNTING     NEW YORK                                  
CLARK       2450     10 ACCOUNTING     NEW YORK                                  
KING        5000     10 ACCOUNTING     NEW YORK                                  
ADAMS       1100     20 RESEARCH       DALLAS                                    
SCOTT       3000     20 RESEARCH       DALLAS                                    
SMITH        800     20 RESEARCH       DALLAS                                    
FORD        3000     20 RESEARCH       DALLAS                                    
JONES       2975     20 RESEARCH       DALLAS                                    
WARD        1250     30 SALES          CHICAGO                                   
JAMES        950     30 SALES          CHICAGO                                   
TURNER      1500     30 SALES          CHICAGO                                   
 
ENAME        SAL DEPTNO DNAME          LOC                                       
---------- ----- ------ -------------- -------------                             
ALLEN       1600     30 SALES          CHICAGO                                   
MARTIN      1250     30 SALES          CHICAGO                                   
BLAKE       2850     30 SALES          CHICAGO                                   
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  
  2  NVL(Ename, 'NOT RECRUITED') Ename, 
  3  Dept.Deptno Deptno,  
  4  Dname,  
  5  Loc 
  6  FROM Emp, Dept 
  7 WHERE Emp.Deptno(+) = Dept.Deptno 
SQL> / 
 
ENAME         DEPTNO DNAME          LOC                                          
------------- ------ -------------- -------------                                
 

KING              10 ACCOUNTING     NEW YORK                                     
CLARK             10 ACCOUNTING     NEW YORK                                     
MILLER            10 ACCOUNTING     NEW YORK                                     
JONES             20 RESEARCH       DALLAS                                       
SCOTT             20 RESEARCH       DALLAS                                       
ADAMS             20 RESEARCH       DALLAS                                       
SMITH             20 RESEARCH       DALLAS                                       
FORD              20 RESEARCH       DALLAS                                       
BLAKE             30 SALES          CHICAGO                                      
MARTIN            30 SALES          CHICAGO                                      
ALLEN             30 SALES          CHICAGO                                      
 
ENAME         DEPTNO DNAME          LOC                                          
------------- ------ -------------- -------------                                
TURNER            30 SALES          CHICAGO                                      
JAMES             30 SALES          CHICAGO                                      
WARD              30 SALES          CHICAGO                                      
NOT RECRUITED     40 OPERATIONS     BOSTON                                       
NOT RECRUITED     50 SHIPPING       CHENNAI                                      
NOT RECRUITED     60 CARGO          MUMBAI                                       
NOT RECRUITED     70 COURIER        DELHI                                        
 
18 rows selected. 
 

 
SQL> SELECT E.Ename, D.Deptno, D.Dname 
  2  FROM Emp E, Dept D 
  3  WHERE E.Deptno(+) = D.Deptno 
  4  AND E.Deptno = 10 
  5  ORDER BY E.Deptno; 
 
ENAME      DEPTNO DNAME                                                          
---------- ------ --------------                                                 
KING           10 ACCOUNTING                                                     
CLARK          10 ACCOUNTING                                                     
MILLER         10 ACCOUNTING                                                     
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT E.Ename, D.Deptno, D.Dname, D.Loc 
  2  FROM Emp E, Dept D 
  3  WHERE E.Deptno(+) = D.Deptno 
  4  AND E.Deptno = 10 
  5 ORDER BY E.Deptno 
SQL> / 
 
ENAME      DEPTNO DNAME          LOC                                             
---------- ------ -------------- -------------                                   
KING           10 ACCOUNTING     NEW YORK                                        
CLARK          10 ACCOUNTING     NEW YORK                                        
MILLER         10 ACCOUNTING     NEW YORK                                        
 
SQL> ED 
Wrote file afiedt.buf 
 

 
  1  SELECT E.Ename, D.Deptno, D.Dname, D.Loc 
  2  FROM Emp E, Dept D 
  3  WHERE E.Deptno(+) = D.Deptno 
  4  AND E.Deptno(+) = 10 
  5 ORDER BY E.Deptno 
SQL> / 
 
ENAME      DEPTNO DNAME          LOC                                             
---------- ------ -------------- -------------                                   
KING           10 ACCOUNTING     NEW YORK                                        
CLARK          10 ACCOUNTING     NEW YORK                                        
MILLER         10 ACCOUNTING     NEW YORK                                        
               20 RESEARCH       DALLAS                                          
               30 SALES          CHICAGO                                         
               40 OPERATIONS     BOSTON                                          
               50 SHIPPING       CHENNAI                                         
               60 CARGO          MUMBAI                                          
               70 COURIER        DELHI                                           
 
9 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT E.Ename, D.Deptno, D.Dname, D.Loc 
  2  FROM Emp E, Dept D 
  3  WHERE E.Deptno(+) = D.Deptno 
  4  AND D.Deptno(+) = 10 
  5 ORDER BY E.Deptno 
SQL> / 
 
ENAME      DEPTNO DNAME          LOC                                             
---------- ------ -------------- -------------                                   
KING           10 ACCOUNTING     NEW YORK                                        
CLARK          10 ACCOUNTING     NEW YORK                                        
MILLER         10 ACCOUNTING     NEW YORK                                        
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT  
  2  NVL(E.Ename,  'NOT RECRUITED OR REQUESTED') Ename, 
  3  D.Deptno,  
  4  D.Dname,  
  5  D.Loc 
  6  FROM Emp E, Dept D 
  7  WHERE E.Deptno(+) = D.Deptno 
  8  AND E.Deptno(+) = 10 
  9 ORDER BY E.Deptno 
SQL> / 
 
ENAME                      DEPTNO DNAME          LOC                             
-------------------------- ------ -------------- -------------                   
KING                           10 ACCOUNTING     NEW YORK                        
CLARK                          10 ACCOUNTING     NEW YORK                        
 

MILLER                         10 ACCOUNTING     NEW YORK                        
NOT RECRUITED OR REQUESTED     20 RESEARCH       DALLAS                          
NOT RECRUITED OR REQUESTED     30 SALES          CHICAGO                         
NOT RECRUITED OR REQUESTED     40 OPERATIONS     BOSTON                          
NOT RECRUITED OR REQUESTED     50 SHIPPING       CHENNAI                         
NOT RECRUITED OR REQUESTED     60 CARGO          MUMBAI                          
NOT RECRUITED OR REQUESTED     70 COURIER        DELHI                           
 
9 rows selected. 
 

SQL> SELECT 
  2  C.Name, O.OrdID, 
  3  I.ItemID, I.Itemtot, O.Total 
  4  FROM Customer C, Ord O, Item I 
  5  WHERE C.CustID = O.CustID 
  6  AND O.OrdID = I.OrdID 
  7  AND C.Name = 'TKB SPORT SHOP'; 
 
NAME                                               ORDID     ITEMID    ITEMTOT   
--------------------------------------------- ---------- ---------- ----------   
     TOTAL                                                                       
----------                                                                       
TKB SPORT SHOP                                       610          2        8.4   
     101.4                                                                       
                                                                                 
TKB SPORT SHOP                                       610          1         35   
     101.4                                                                       
                                                                                 
TKB SPORT SHOP                                       610          3         58   
     101.4                                                                       
                                                                                 
 

 
SQL> SELECT 
  2  E.Ename Employee, 
  3  E.Deptno Department, 
  4  M.Ename Manager, 
  5  M.Deptno "Manager's Dept" 
  6  FROM Emp E, Dept D, Emp M 
  7  WHERE E.MGR = M.Empno AND 
  8               E.Deptno = D.Deptno; 
 
EMPLOYEE   DEPARTMENT MANAGER    Manager's Dept                                  
---------- ---------- ---------- --------------                                  
FORD               20 JONES                  20                                  
SCOTT              20 JONES                  20                                  
MARTIN             30 BLAKE                  30                                  
ALLEN              30 BLAKE                  30                                  
JAMES              30 BLAKE                  30                                  
TURNER             30 BLAKE                  30                                  
WARD               30 BLAKE                  30                                  
MILLER             10 CLARK                  10                                  
 

ADAMS              20 SCOTT                  20                                  
BLAKE              30 KING                   10                                  
CLARK              10 KING                   10                                  
 
EMPLOYEE   DEPARTMENT MANAGER    Manager's Dept                                  
---------- ---------- ---------- --------------                                  
JONES              20 KING                   10                                  
SMITH              20 FORD                   20                                  
 
13 rows selected. 
 
SQL> SELECT 
  2  E.Ename Employee, 
  3  Dname Department, 
  4  Loc Place, 
  5  M.Ename Manager, 
  6  Dname "Manager's Dept" 
  7  FROM Emp E, Dept D, Emp M 
  8  WHERE E.MGR = M.Empno AND 
  9               E.Deptno = D.Deptno; 
 
EMPLOYEE   DEPARTMENT     PLACE         MANAGER    Manager's Dept                
---------- -------------- ------------- ---------- --------------                
BLAKE      SALES          CHICAGO       KING       SALES                         
JONES      RESEARCH       DALLAS        KING       RESEARCH                      
CLARK      ACCOUNTING     NEW YORK      KING       ACCOUNTING                    
WARD       SALES          CHICAGO       BLAKE      SALES                         
JAMES      SALES          CHICAGO       BLAKE      SALES                         
TURNER     SALES          CHICAGO       BLAKE      SALES                         
ALLEN      SALES          CHICAGO       BLAKE      SALES                         
MARTIN     SALES          CHICAGO       BLAKE      SALES                         
MILLER     ACCOUNTING     NEW YORK      CLARK      ACCOUNTING                    
SCOTT      RESEARCH       DALLAS        JONES      RESEARCH                      
FORD       RESEARCH       DALLAS        JONES      RESEARCH                      
 
EMPLOYEE   DEPARTMENT     PLACE         MANAGER    Manager's Dept                
---------- -------------- ------------- ---------- --------------                
SMITH      RESEARCH       DALLAS        FORD       RESEARCH                      
ADAMS      RESEARCH       DALLAS        SCOTT      RESEARCH                      
 
13 rows selected. 
 
SQL> SELECT 
  2  E.Ename Employee, 
  3  DE.Dname Department, 
  4  DE.Loc Place, 
  5  M.Ename Manager, 
  6  DM.Dname "Manager's Dept" 
  7  FROM Emp E, Dept DE, Emp M, Dept DM 
  8  WHERE E.MGR = M.Empno AND 
  9              E.Deptno = DE.Deptno AND 
 10              M.Deptno = DM.Deptno 
 11  ORDER BY E.Deptno; 
 
EMPLOYEE   DEPARTMENT     PLACE         MANAGER    Manager's Dept                
---------- -------------- ------------- ---------- --------------                
 

MILLER     ACCOUNTING     NEW YORK      CLARK      ACCOUNTING                    
CLARK      ACCOUNTING     NEW YORK      KING       ACCOUNTING                    
JONES      RESEARCH       DALLAS        KING       ACCOUNTING                    
SMITH      RESEARCH       DALLAS        FORD       RESEARCH                      
SCOTT      RESEARCH       DALLAS        JONES      RESEARCH                      
FORD       RESEARCH       DALLAS        JONES      RESEARCH                      
ADAMS      RESEARCH       DALLAS        SCOTT      RESEARCH                      
BLAKE      SALES          CHICAGO       KING       ACCOUNTING                    
MARTIN     SALES          CHICAGO       BLAKE      SALES                         
ALLEN      SALES          CHICAGO       BLAKE      SALES                         
TURNER     SALES          CHICAGO       BLAKE      SALES                         
 
EMPLOYEE   DEPARTMENT     PLACE         MANAGER    Manager's Dept                
---------- -------------- ------------- ---------- --------------                
JAMES      SALES          CHICAGO       BLAKE      SALES                         
WARD       SALES          CHICAGO       BLAKE      SALES                         
 
13 rows selected. 
 

 
SQL> SELECT 
  2  E.Ename Employee, 
  3  E.Sal "Employee's Salary", 
  4  M.Ename Manager, 
  5  M.Sal "Manager's Salary", 
  6  Dname "Manager's Dept" 
  7  FROM Emp E, Dept D, Emp M 
  8  WHERE E.Deptno = D.Deptno  AND 
  9  E.MGR = M.Empno; 
 
EMPLOYEE   Employee's Salary MANAGER    Manager's Salary Manager's Dept          
---------- ----------------- ---------- ---------------- --------------          
BLAKE                   2850 KING                   5000 SALES                   
JONES                   2975 KING                   5000 RESEARCH                
CLARK                   2450 KING                   5000 ACCOUNTING              
WARD                    1250 BLAKE                  2850 SALES                   
JAMES                    950 BLAKE                  2850 SALES                   
TURNER                  1500 BLAKE                  2850 SALES                   
ALLEN                   1600 BLAKE                  2850 SALES                   
MARTIN                  1250 BLAKE                  2850 SALES                   
MILLER                  1300 CLARK                  2450 ACCOUNTING              
SCOTT                   3000 JONES                  2975 RESEARCH                
FORD                    3000 JONES                  2975 RESEARCH                
 
EMPLOYEE   Employee's Salary MANAGER    Manager's Salary Manager's Dept          
---------- ----------------- ---------- ---------------- --------------          
SMITH                    800 FORD                   3000 RESEARCH                
ADAMS                   1100 SCOTT                  3000 RESEARCH                
 
13 rows selected. 
 

 
SQL> SELECT 
  2  E.Ename Employee, 
 

  3  E.Sal "Employee's Salary", 
  4  M.Ename Manager, 
  5  M.Sal "Manager's Salary", 
  6  DM.Dname "Manager's Dept" 
  7  FROM Emp E, Dept DE, Emp M, Dept DM 
  8  WHERE E.Deptno = DE.Deptno  AND 
  9  M.Deptno = DM.Deptno AND 
 10  E.MGR = M.Empno; 
 
EMPLOYEE   Employee's Salary MANAGER    Manager's Salary Manager's Dept          
---------- ----------------- ---------- ---------------- --------------          
BLAKE                   2850 KING                   5000 ACCOUNTING              
CLARK                   2450 KING                   5000 ACCOUNTING              
JONES                   2975 KING                   5000 ACCOUNTING              
MARTIN                  1250 BLAKE                  2850 SALES                   
ALLEN                   1600 BLAKE                  2850 SALES                   
TURNER                  1500 BLAKE                  2850 SALES                   
JAMES                    950 BLAKE                  2850 SALES                   
WARD                    1250 BLAKE                  2850 SALES                   
FORD                    3000 JONES                  2975 RESEARCH                
SMITH                    800 FORD                   3000 RESEARCH                
SCOTT                   3000 JONES                  2975 RESEARCH                
 
EMPLOYEE   Employee's Salary MANAGER    Manager's Salary Manager's Dept          
---------- ----------------- ---------- ---------------- --------------          
ADAMS                   1100 SCOTT                  3000 RESEARCH                
MILLER                  1300 CLARK                  2450 ACCOUNTING              
 
13 rows selected. 
 
SQL> COLUMN Employee FORMAT A14 
SQL> COLUMN "Employee's Dept" FORMAT A10 
SQL> COLUMN Manager FORMAT A15 
SQL> COLUMN "Manager's Dept" FORMAT A15 
SQL> SELECT 
  2  NVL(E.Ename, 'Not Recruited') Employee, 
  3  DE.Dname "Employee's Dept", 
  4  NVL(M.Ename, 'NULL Manager') Manager, 
  5  NVL(DM.Dname, 'Not Recruited') "Manager's Dept", 
  6  DE.Deptno 
  7  FROM Emp E, Dept DE, Emp M, Dept DM 
  8  WHERE E.Deptno(+) = DE.Deptno  AND 
  9  M.Deptno = DM.Deptno(+) AND 
 10  E.MGR = M.Empno(+); 
 
EMPLOYEE       Employee's MANAGER         Manager's Dept      DEPTNO             
-------------- ---------- --------------- --------------- ----------             
MILLER         ACCOUNTING CLARK           ACCOUNTING              10             
CLARK          ACCOUNTING KING            ACCOUNTING              10             
JONES          RESEARCH   KING            ACCOUNTING              20             
BLAKE          SALES      KING            ACCOUNTING              30             
ADAMS          RESEARCH   SCOTT           RESEARCH                20             
SMITH          RESEARCH   FORD            RESEARCH                20             
SCOTT          RESEARCH   JONES           RESEARCH                20             
 

FORD           RESEARCH   JONES           RESEARCH                20             
MARTIN         SALES      BLAKE           SALES                   30             
ALLEN          SALES      BLAKE           SALES                   30             
TURNER         SALES      BLAKE           SALES                   30             
 
EMPLOYEE       Employee's MANAGER         Manager's Dept      DEPTNO             
-------------- ---------- --------------- --------------- ----------             
JAMES          SALES      BLAKE           SALES                   30             
WARD           SALES      BLAKE           SALES                   30             
KING           ACCOUNTING NULL Manager    Not Recruited           10             
Not Recruited  OPERATIONS NULL Manager    Not Recruited           40             
 
15 rows selected. 
 

 
SQL> COLUMN "Employee's Salary" FORMAT 9999 
SQL> COLUMN EMPGRADE FORMAT 99 
SQL> COLUMN "Manager's Salary" FORMAT 9999 
SQL> COLUMN MGRGRADE FORMAT 99 
SQL> SELECT 
  2  E.Ename Employee, 
  3  E.Sal "Employee's Salary", 
  4  SE.Grade EmpGrade, 
  5  M.Sal "Manager's Salary", 
  6  SM.Grade MGRGrade, 
  7  Dname 
  8  FROM Emp E, Dept D, Emp M, SalGrade SE, SalGrade SM 
  9  WHERE E.Deptno = D.Deptno  AND 
 10  E.MGR = M.Empno AND 
 11  E.Sal BETWEEN SE.LoSal AND SE.HiSal AND 
 12  M.Sal BETWEEN SM.LoSal AND SM.HiSal; 
 
EMPLOYEE       Employee's Salary EMPGRADE Manager's Salary MGRGRADE              
-------------- ----------------- -------- ---------------- --------              
DNAME                                                                            
--------------                                                                   
SMITH                        800        1             3000        4              
RESEARCH                                                                         
                                                                                 
JAMES                        950        1             2850        4              
SALES                                                                            
                                                                                 
ADAMS                       1100        1             3000        4              
RESEARCH                                                                         
                                                                                 
 
EMPLOYEE       Employee's Salary EMPGRADE Manager's Salary MGRGRADE              
-------------- ----------------- -------- ---------------- --------              
DNAME                                                                            
--------------                                                                   
MARTIN                      1250        2             2850        4              
SALES                                                                            
                                                                                 
WARD                        1250        2             2850        4              
SALES                                                                            
 

                                                                                 
MILLER                      1300        2             2450        4              
ACCOUNTING                                                                       
                                                                                 
 
EMPLOYEE       Employee's Salary EMPGRADE Manager's Salary MGRGRADE              
-------------- ----------------- -------- ---------------- --------              
DNAME                                                                            
--------------                                                                   
TURNER                      1500        3             2850        4              
SALES                                                                            
                                                                                 
ALLEN                       1600        3             2850        4              
SALES                                                                            
                                                                                 
CLARK                       2450        4             5000        5              
ACCOUNTING                                                                       
                                                                                 
 
EMPLOYEE       Employee's Salary EMPGRADE Manager's Salary MGRGRADE              
-------------- ----------------- -------- ---------------- --------              
DNAME                                                                            
--------------                                                                   
BLAKE                       2850        4             5000        5              
SALES                                                                            
                                                                                 
JONES                       2975        4             5000        5              
RESEARCH                                                                         
                                                                                 
FORD                        3000        4             2975        4              
RESEARCH                                                                         
                                                                                 
 
EMPLOYEE       Employee's Salary EMPGRADE Manager's Salary MGRGRADE              
-------------- ----------------- -------- ---------------- --------              
DNAME                                                                            
--------------                                                                   
SCOTT                       3000        4             2975        4              
RESEARCH                                                                         
                                                                                 
 
13 rows selected. 
  
SQL> SELECT Ename, Dept.Deptno, Dname, Loc 
  2  FROM Emp CROSS JOIN Dept 
  3  / 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
BLAKE              10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
JONES              10 ACCOUNTING     NEW YORK                                    
MARTIN             10 ACCOUNTING     NEW YORK                                    
 

ALLEN              10 ACCOUNTING     NEW YORK                                    
TURNER             10 ACCOUNTING     NEW YORK                                    
JAMES              10 ACCOUNTING     NEW YORK                                    
WARD               10 ACCOUNTING     NEW YORK                                    
FORD               10 ACCOUNTING     NEW YORK                                    
SMITH              10 ACCOUNTING     NEW YORK                                    
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
SCOTT              10 ACCOUNTING     NEW YORK                                    
ADAMS              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
KING               20 RESEARCH       DALLAS                                      
BLAKE              20 RESEARCH       DALLAS                                      
CLARK              20 RESEARCH       DALLAS                                      
JONES              20 RESEARCH       DALLAS                                      
MARTIN             20 RESEARCH       DALLAS                                      
ALLEN              20 RESEARCH       DALLAS                                      
TURNER             20 RESEARCH       DALLAS                                      
JAMES              20 RESEARCH       DALLAS                                      
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
WARD               20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
MILLER             20 RESEARCH       DALLAS                                      
KING               30 SALES          CHICAGO                                     
BLAKE              30 SALES          CHICAGO                                     
CLARK              30 SALES          CHICAGO                                     
JONES              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
ALLEN              30 SALES          CHICAGO                                     
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
FORD               30 SALES          CHICAGO                                     
SMITH              30 SALES          CHICAGO                                     
SCOTT              30 SALES          CHICAGO                                     
ADAMS              30 SALES          CHICAGO                                     
MILLER             30 SALES          CHICAGO                                     
KING               40 OPERATIONS     BOSTON                                      
BLAKE              40 OPERATIONS     BOSTON                                      
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
CLARK              40 OPERATIONS     BOSTON                                      
JONES              40 OPERATIONS     BOSTON                                      
MARTIN             40 OPERATIONS     BOSTON                                      
ALLEN              40 OPERATIONS     BOSTON                                      
TURNER             40 OPERATIONS     BOSTON                                      
 

JAMES              40 OPERATIONS     BOSTON                                      
WARD               40 OPERATIONS     BOSTON                                      
FORD               40 OPERATIONS     BOSTON                                      
SMITH              40 OPERATIONS     BOSTON                                      
SCOTT              40 OPERATIONS     BOSTON                                      
ADAMS              40 OPERATIONS     BOSTON                                      
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
MILLER             40 OPERATIONS     BOSTON                                      
 
56 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Dept.Deptno, Dname, Loc 
  2 FROM Emp, Dept 
SQL> / 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
BLAKE              10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
JONES              10 ACCOUNTING     NEW YORK                                    
MARTIN             10 ACCOUNTING     NEW YORK                                    
ALLEN              10 ACCOUNTING     NEW YORK                                    
TURNER             10 ACCOUNTING     NEW YORK                                    
JAMES              10 ACCOUNTING     NEW YORK                                    
WARD               10 ACCOUNTING     NEW YORK                                    
FORD               10 ACCOUNTING     NEW YORK                                    
SMITH              10 ACCOUNTING     NEW YORK                                    
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
SCOTT              10 ACCOUNTING     NEW YORK                                    
ADAMS              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
KING               20 RESEARCH       DALLAS                                      
BLAKE              20 RESEARCH       DALLAS                                      
CLARK              20 RESEARCH       DALLAS                                      
JONES              20 RESEARCH       DALLAS                                      
MARTIN             20 RESEARCH       DALLAS                                      
ALLEN              20 RESEARCH       DALLAS                                      
TURNER             20 RESEARCH       DALLAS                                      
JAMES              20 RESEARCH       DALLAS                                      
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
WARD               20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
MILLER             20 RESEARCH       DALLAS                                      
 

KING               30 SALES          CHICAGO                                     
BLAKE              30 SALES          CHICAGO                                     
CLARK              30 SALES          CHICAGO                                     
JONES              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
ALLEN              30 SALES          CHICAGO                                     
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
FORD               30 SALES          CHICAGO                                     
SMITH              30 SALES          CHICAGO                                     
SCOTT              30 SALES          CHICAGO                                     
ADAMS              30 SALES          CHICAGO                                     
MILLER             30 SALES          CHICAGO                                     
KING               40 OPERATIONS     BOSTON                                      
BLAKE              40 OPERATIONS     BOSTON                                      
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
CLARK              40 OPERATIONS     BOSTON                                      
JONES              40 OPERATIONS     BOSTON                                      
MARTIN             40 OPERATIONS     BOSTON                                      
ALLEN              40 OPERATIONS     BOSTON                                      
TURNER             40 OPERATIONS     BOSTON                                      
JAMES              40 OPERATIONS     BOSTON                                      
WARD               40 OPERATIONS     BOSTON                                      
FORD               40 OPERATIONS     BOSTON                                      
SMITH              40 OPERATIONS     BOSTON                                      
SCOTT              40 OPERATIONS     BOSTON                                      
ADAMS              40 OPERATIONS     BOSTON                                      
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
MILLER             40 OPERATIONS     BOSTON                                      
 
56 rows selected. 
 

 
SQL> SELECT Ename, Dept.Deptno, Dname, Loc 
  2  FROM Emp CROSS JOIN Dept 
  3  WHERE Emp.Deptno = Dept.Deptno; 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
 

BLAKE              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
 
14 rows selected. 
 

 
SQL> SELECT Ename, Deptno, Dname, Loc 
  2  FROM Emp NATURAL JOIN Dept; 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
BLAKE              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, D.Deptno, Dname, Loc 
  2 FROM Emp NATURAL JOIN Dept 
SQL> / 
SELECT Ename, D.Deptno, Dname, Loc 
               
ERROR at line 1: 
ORA-00904: "D"."DEPTNO": invalid identifier  
 
 

 
SQL> SELECT Ename, Deptno, Dname, Loc 
  2  FROM Emp JOIN Dept 
  3  USING(Deptno); 
 

 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
BLAKE              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Deptno, Dname, Loc 
  2  FROM Emp INNER JOIN Dept 
  3 USING(Deptno) 
SQL> / 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
BLAKE              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
 
14 rows selected. 
 

 
SQL> SELECT Ename, Dept.Deptno, Dname, Loc 
 

  2  FROM Emp JOIN Dept 
  3  ON Emp.Deptno = Dept.Deptno; 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
BLAKE              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Dept.Deptno, Dname, Loc 
  2  FROM Emp INNER JOIN Dept 
  3 ON Emp.Deptno = Dept.Deptno 
SQL> / 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
BLAKE              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
 
14 rows selected. 
 
SQL> ED 
 

Wrote file afiedt.buf 
 
  1  SELECT Ename, Dept.Deptno, Dname, Loc 
  2  FROM Emp INNER JOIN Dept 
  3 WHERE Emp.Deptno = Dept.Deptno 
SQL> / 
WHERE Emp.Deptno = Dept.Deptno 
 
ERROR at line 3: 
ORA-00905: missing keyword  
 
 

 
SQL> SELECT 
  2  E.Ename Employee, 
  3  M.Ename Manager 
  4  FROM 
  5  Emp E INNER JOIN Emp M 
  6  ON(E.MGR = M.Empno) 
  7  / 
 
EMPLOYEE       MANAGER                                                           
-------------- ---------------                                                   
FORD           JONES                                                             
SCOTT          JONES                                                             
MARTIN         BLAKE                                                             
ALLEN          BLAKE                                                             
JAMES          BLAKE                                                             
TURNER         BLAKE                                                             
WARD           BLAKE                                                             
MILLER         CLARK                                                             
ADAMS          SCOTT                                                             
BLAKE          KING                                                              
CLARK          KING                                                              
 
EMPLOYEE       MANAGER                                                           
-------------- ---------------                                                   
JONES          KING                                                              
SMITH          FORD                                                              
 
13 rows selected. 
 
SQL> SELECT Ename, Sal, Grade, Dept.Deptno, Dname 
  2  FROM Emp JOIN Dept 
  3  ON Emp.Deptno = Dept.Deptno 
  4  JOIN SalGrade 
  5  ON Emp.Sal BETWEEN LoSal AND HiSal; 
 
ENAME             SAL      GRADE     DEPTNO DNAME                                
---------- ---------- ---------- ---------- --------------                       
SMITH             800          1         20 RESEARCH                             
JAMES             950          1         30 SALES                                
ADAMS            1100          1         20 RESEARCH                             
MARTIN           1250          2         30 SALES                                
WARD             1250          2         30 SALES                                
 

MILLER           1300          2         10 ACCOUNTING                           
TURNER           1500          3         30 SALES                                
ALLEN            1600          3         30 SALES                                
CLARK            2450          4         10 ACCOUNTING                           
BLAKE            2850          4         30 SALES                                
JONES            2975          4         20 RESEARCH                             
 
ENAME             SAL      GRADE     DEPTNO DNAME                                
---------- ---------- ---------- ---------- --------------                       
FORD             3000          4         20 RESEARCH                             
SCOTT            3000          4         20 RESEARCH                             
KING             5000          5         10 ACCOUNTING                           
 
14 rows selected. 
 
SQL> SELECT E.Ename, M.Ename, Sal, Grade, D.Deptno, Dname 
  2  FROM 
  3  Emp E INNER JOIN Dept D 
  4  ON E.Deptno = D.Deptno 
  5  INNER JOIN Emp M 
  6  ON E.Empno = M.MGR 
  7  INNER JOIN SalGrade S 
  8  ON E.Sal BETWEEN LoSal AND HiSal; 
 
ENAME      ENAME             SAL      GRADE     DEPTNO DNAME                     
---------- ---------- ---------- ---------- ---------- --------------            
KING       BLAKE            2850          5         10 ACCOUNTING                
KING       CLARK            2450          5         10 ACCOUNTING                
KING       JONES            2975          5         10 ACCOUNTING                
SCOTT      ADAMS            1100          4         20 RESEARCH                  
FORD       SMITH             800          4         20 RESEARCH                  
JONES      FORD             3000          4         20 RESEARCH                  
JONES      SCOTT            3000          4         20 RESEARCH                  
BLAKE      MARTIN           1250          4         30 SALES                     
BLAKE      ALLEN            1600          4         30 SALES                     
BLAKE      JAMES             950          4         30 SALES                     
BLAKE      TURNER           1500          4         30 SALES                     
 
ENAME      ENAME             SAL      GRADE     DEPTNO DNAME                     
---------- ---------- ---------- ---------- ---------- --------------            
BLAKE      WARD             1250          4         30 SALES                     
CLARK      MILLER           1300          4         10 ACCOUNTING                
 
13 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Grade, Dept.Deptno, Dname 
  2  FROM Emp NATURAL JOIN Dept 
  3  JOIN SalGrade 
  4 ON Emp.Sal BETWEEN LoSal AND HiSal 
SQL> / 
SELECT Ename, Sal, Grade, Dept.Deptno, Dname 
                           
ERROR at line 1: 
 

ORA-25155: column used in NATURAL join cannot have qualifier  
 
 
SQL> Ed 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Sal, Grade, Deptno, Dname 
  2  FROM Emp NATURAL JOIN Dept 
  3  JOIN SalGrade 
  4 ON Emp.Sal BETWEEN LoSal AND HiSal 
SQL> / 
 
ENAME             SAL      GRADE     DEPTNO DNAME                                
---------- ---------- ---------- ---------- --------------                       
SMITH             800          1         20 RESEARCH                             
JAMES             950          1         30 SALES                                
ADAMS            1100          1         20 RESEARCH                             
MARTIN           1250          2         30 SALES                                
WARD             1250          2         30 SALES                                
MILLER           1300          2         10 ACCOUNTING                           
TURNER           1500          3         30 SALES                                
ALLEN            1600          3         30 SALES                                
CLARK            2450          4         10 ACCOUNTING                           
BLAKE            2850          4         30 SALES                                
JONES            2975          4         20 RESEARCH                             
 
ENAME             SAL      GRADE     DEPTNO DNAME                                
---------- ---------- ---------- ---------- --------------                       
FORD             3000          4         20 RESEARCH                             
SCOTT            3000          4         20 RESEARCH                             
KING             5000          5         10 ACCOUNTING                           
 
14 rows selected. 
 

 
SQL> SELECT Ename, Dept.Deptno, Dname, Loc 
  2  FROM Emp RIGHT JOIN Dept 
  3  ON Emp.Deptno = Dept.Deptno; 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
BLAKE              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
 

TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
                   40 OPERATIONS     BOSTON                                      
 
15 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Dept.Deptno, Dname, Loc 
  2  FROM Emp LEFT JOIN Dept 
  3 ON Emp.Deptno = Dept.Deptno 
SQL> / 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
MILLER             10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
KING               10 ACCOUNTING     NEW YORK                                    
ADAMS              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
JONES              20 RESEARCH       DALLAS                                      
WARD               30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
TURNER             30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
ALLEN              30 SALES          CHICAGO                                     
MARTIN             30 SALES          CHICAGO                                     
BLAKE              30 SALES          CHICAGO                                     
 
14 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Ename, Dept.Deptno, Dname, Loc 
  2  FROM Dept LEFT JOIN Emp 
  3 ON Emp.Deptno = Dept.Deptno 
SQL> / 
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
KING               10 ACCOUNTING     NEW YORK                                    
CLARK              10 ACCOUNTING     NEW YORK                                    
MILLER             10 ACCOUNTING     NEW YORK                                    
JONES              20 RESEARCH       DALLAS                                      
SCOTT              20 RESEARCH       DALLAS                                      
ADAMS              20 RESEARCH       DALLAS                                      
SMITH              20 RESEARCH       DALLAS                                      
FORD               20 RESEARCH       DALLAS                                      
BLAKE              30 SALES          CHICAGO                                     
 

MARTIN             30 SALES          CHICAGO                                     
ALLEN              30 SALES          CHICAGO                                     
 
ENAME          DEPTNO DNAME          LOC                                         
---------- ---------- -------------- -------------                               
TURNER             30 SALES          CHICAGO                                     
JAMES              30 SALES          CHICAGO                                     
WARD               30 SALES          CHICAGO                                     
                   40 OPERATIONS     BOSTON                                      
 
15 rows selected. 

Go Back