SQL> cl scr
SQL> SET VERIFY OFF
SQL> cl scr
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> SPOOL OFF
SQL> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> SPOOL OFF
SQL> cl scr
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> cl scr
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> cl scr
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> cl scr
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
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
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
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> cl scr
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> SPOOL OFF
SQL> cl scr
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> cl scr
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
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
JAMES SALES CHICAGO BLAKE SALES
WARD SALES CHICAGO BLAKE SALES
13 rows selected.
SQL> cl scr
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
SMITH 800 FORD 3000 RESEARCH
ADAMS 1100 SCOTT 3000 RESEARCH
13 rows selected.
SQL> cl scr
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
ADAMS 1100 SCOTT 3000 RESEARCH
MILLER 1300 CLARK 2450 ACCOUNTING
13 rows selected.
SQL> cl scr
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
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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.
SQL> SPOOL OFF
SQL> SET VERIFY OFF
SQL> cl scr
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> SPOOL OFF
SQL> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> SPOOL OFF
SQL> cl scr
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> cl scr
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> cl scr
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> cl scr
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
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
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
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> cl scr
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> SPOOL OFF
SQL> cl scr
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> cl scr
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
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
JAMES SALES CHICAGO BLAKE SALES
WARD SALES CHICAGO BLAKE SALES
13 rows selected.
SQL> cl scr
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
SMITH 800 FORD 3000 RESEARCH
ADAMS 1100 SCOTT 3000 RESEARCH
13 rows selected.
SQL> cl scr
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
ADAMS 1100 SCOTT 3000 RESEARCH
MILLER 1300 CLARK 2450 ACCOUNTING
13 rows selected.
SQL> cl scr
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
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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> cl scr
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.
SQL> SPOOL OFF
No comments:
Post a Comment