===============================================
true relational projection of last names, salaries, and
hiredates from the empl relation
EMPL_LAST_NAME SALARY HIREDATE
--------------- ---------- ---------
Michaels 1600 20-FEB-91
James 950 03-DEC-91
Turner 1500 08-SEP-91
Blake 2850 01-MAY-91
Raimi 2450 09-JUN-91
Ward 1250 22-FEB-91
King 5000 17-NOV-91
Jones 2975 02-APR-91
Ford 3000 03-DEC-91
Smith 800 17-DEC-90
Scott 3000 09-NOV-91
EMPL_LAST_NAME SALARY HIREDATE
--------------- ---------- ---------
Adams 1100 23-SEP-91
Martin 1250 28-SEP-91
Miller 1300 23-JAN-92
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