===============================================
true relational projection of last names, salaries, and
hiredates from the empl relation
EMPL_LAST_NAME HIREDATE JOB_TITLE
--------------- --------- ----------
Jones 02-APR-91 Manager
Turner 08-SEP-91 Sales
Miller 23-JAN-92 Clerk
King 17-NOV-91 President
James 03-DEC-91 Clerk
Blake 01-MAY-91 Manager
Martin 28-SEP-91 Sales
Raimi 09-JUN-91 Manager
Ward 22-FEB-91 Sales
Ford 03-DEC-91 Analyst
Smith 17-DEC-90 Clerk
EMPL_LAST_NAME HIREDATE JOB_TITLE
--------------- --------- ----------
Michaels 20-FEB-91 Sales
Scott 09-NOV-91 Analyst
Adams 23-SEP-91 Clerk
14 rows selected.
===============================================
true relational projection of job titles
from the empl relation
JOB_TITLE
----------
Manager
Analyst
Clerk
President
Sales
===============================================
you can project the columns in the order desired
here, projecting salary then empl_last_name:
SALARY EMPL_LAST_NAME
---------- ---------------
1300 Miller
5000 King
3000 Ford
1600 Michaels
950 James
800 Smith
1250 Martin
3000 Scott
2450 Raimi
1500 Turner
1250 Ward
SALARY EMPL_LAST_NAME
---------- ---------------
2975 Jones
2850 Blake
1100 Adams
14 rows selected.
===============================================
notice the duplicate rows here...(NO distinct!)
JOB_TITLE
----------
President
Manager
Manager
Manager
Analyst
Clerk
Sales
Sales
Sales
Analyst
Sales
JOB_TITLE
----------
Clerk
Clerk
Clerk
14 rows selected.
===============================================
pure relational projection of job titles, dept nums
JOB_TITLE DEP
---------- ---
Manager 100
Clerk 100
Sales 300
Manager 200
Manager 300
Clerk 400
President 500
Analyst 200
Clerk 200
Clerk 300
10 rows selected.
===============================================
not pure projection, has duplicates ROWS (no distinct!)
JOB_TITLE DEP
---------- ---
President 500
Manager 200
Manager 300
Manager 100
Analyst 200
Clerk 200
Sales 300
Sales 300
Sales 300
Analyst 200
Sales 300
JOB_TITLE DEP
---------- ---
Clerk 400
Clerk 300
Clerk 100
14 rows selected.
===============================================
pure relational selection of empl rows with a job title
of 'manager' (to demo that case matters in string *literals*)
no rows selected
===============================================
pure relational selection of empl rows with a job title
of 'Manager'
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7566 Jones Manager 7839 02-APR-91 2975 200
7698 Blake Manager 7839 01-MAY-91 2850 300
7782 Raimi Manager 7839 09-JUN-91 2450 100
===============================================
pure relational selection of empl rows with a salary
that is more than 4000
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-91 5000 500
===============================================
pure relational selection of empl rows with a job title
of 'Manager' AND a salary more than 2500
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7566 Jones Manager 7839 02-APR-91 2975 200
7698 Blake Manager 7839 01-MAY-91 2850 300
===============================================
TWO true equi-joins of the empl and dept tables with
the join condition (empl.dept_num = dept.dept_num)
(using both "old" and ANSI SQL/99 versions)
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7934 Miller Clerk 7782 23-JAN-92 1300 100 100
Accounting New York
7782 Raimi Manager 7839 09-JUN-91 2450 100 100
Accounting New York
7788 Scott Analyst 7566 09-NOV-91 3000 200 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7566 Jones Manager 7839 02-APR-91 2975 200 200
Research Dallas
7902 Ford Analyst 7566 03-DEC-91 3000 200 200
Research Dallas
7369 Smith Clerk 7902 17-DEC-90 800 200 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7654 Martin Sales 7698 28-SEP-91 1250 1400 300 300
Sales Chicago
7521 Ward Sales 7698 22-FEB-91 1250 500 300 300
Sales Chicago
7698 Blake Manager 7839 01-MAY-91 2850 300 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7499 Michaels Sales 7698 20-FEB-91 1600 300 300 300
Sales Chicago
7900 James Clerk 7698 03-DEC-91 950 300 300
Sales Chicago
7844 Turner Sales 7698 08-SEP-91 1500 0 300 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7876 Adams Clerk 7788 23-SEP-91 1100 400 400
Operations Boston
7839 King President 17-NOV-91 5000 500 500
Management New York
14 rows selected.
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7934 Miller Clerk 7782 23-JAN-92 1300 100 100
Accounting New York
7782 Raimi Manager 7839 09-JUN-91 2450 100 100
Accounting New York
7788 Scott Analyst 7566 09-NOV-91 3000 200 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7566 Jones Manager 7839 02-APR-91 2975 200 200
Research Dallas
7902 Ford Analyst 7566 03-DEC-91 3000 200 200
Research Dallas
7369 Smith Clerk 7902 17-DEC-90 800 200 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7654 Martin Sales 7698 28-SEP-91 1250 1400 300 300
Sales Chicago
7521 Ward Sales 7698 22-FEB-91 1250 500 300 300
Sales Chicago
7698 Blake Manager 7839 01-MAY-91 2850 300 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7499 Michaels Sales 7698 20-FEB-91 1600 300 300 300
Sales Chicago
7900 James Clerk 7698 03-DEC-91 950 300 300
Sales Chicago
7844 Turner Sales 7698 08-SEP-91 1500 0 300 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7876 Adams Clerk 7788 23-SEP-91 1100 400 400
Operations Boston
7839 King President 17-NOV-91 5000 500 500
Management New York
14 rows selected.
===============================================
TWO true natural joins of the empl and dept tables with
the join condition (empl.dept_num = dept.dept_num)
(using both "old" and ANSI SQL/99 versions)
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7934 Miller Clerk 7782 23-JAN-92 1300 100
Accounting New York
7782 Raimi Manager 7839 09-JUN-91 2450 100
Accounting New York
7788 Scott Analyst 7566 09-NOV-91 3000 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7566 Jones Manager 7839 02-APR-91 2975 200
Research Dallas
7902 Ford Analyst 7566 03-DEC-91 3000 200
Research Dallas
7369 Smith Clerk 7902 17-DEC-90 800 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
Sales Chicago
7521 Ward Sales 7698 22-FEB-91 1250 500 300
Sales Chicago
7698 Blake Manager 7839 01-MAY-91 2850 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
Sales Chicago
7900 James Clerk 7698 03-DEC-91 950 300
Sales Chicago
7844 Turner Sales 7698 08-SEP-91 1500 0 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7876 Adams Clerk 7788 23-SEP-91 1100 400
Operations Boston
7839 King President 17-NOV-91 5000 500
Management New York
14 rows selected.
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7934 Miller Clerk 7782 23-JAN-92 1300 100
Accounting New York
7782 Raimi Manager 7839 09-JUN-91 2450 100
Accounting New York
7788 Scott Analyst 7566 09-NOV-91 3000 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7566 Jones Manager 7839 02-APR-91 2975 200
Research Dallas
7902 Ford Analyst 7566 03-DEC-91 3000 200
Research Dallas
7369 Smith Clerk 7902 17-DEC-90 800 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
Sales Chicago
7521 Ward Sales 7698 22-FEB-91 1250 500 300
Sales Chicago
7698 Blake Manager 7839 01-MAY-91 2850 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
Sales Chicago
7900 James Clerk 7698 03-DEC-91 950 300
Sales Chicago
7844 Turner Sales 7698 08-SEP-91 1500 0 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7876 Adams Clerk 7788 23-SEP-91 1100 400
Operations Boston
7839 King President 17-NOV-91 5000 500
Management New York
14 rows selected.
===============================================
here is the error you get if you JUST project dept_num
hiredate, salary, commission, dept_num,
*
ERROR at line 2:
ORA-00918: column ambiguously defined