========
further projection from an equi-join:
employee last names, dept names, and dept locations:
EMPL_LAST_NAME DEPT_NAME DEPT_LOC
--------------- --------------- ---------------
King Management New York
Jones Research Dallas
Blake Sales Chicago
Raimi Accounting New York
Ford Research Dallas
Smith Research Dallas
Michaels Sales Chicago
Ward Sales Chicago
Martin Sales Chicago
Scott Research Dallas
Turner Sales Chicago
EMPL_LAST_NAME DEPT_NAME DEPT_LOC
--------------- --------------- ---------------
Adams Operations Boston
James Sales Chicago
Miller Accounting New York
14 rows selected.
========
last names and salaries of managers
EMPL_LAST_NAME SALARY
--------------- ----------
Jones 2975
Blake 2850
Raimi 2450
========
get job_titles and hiredates for employees
with commissions greater than 0
JOB_TITLE HIREDATE
---------- ---------
Sales 20-FEB-91
Sales 22-FEB-91
Sales 28-SEP-91
========
THIS does not work like you might expect!! (using = with NULL)
does NOT show last names of employees with null commissions:
no rows selected
========
WILL give you what you want (using IS with NULL)
NOW shows last names of employees with null commissions:
EMPL_LAST_NAME
---------------
King
Jones
Blake
Raimi
Ford
Smith
Scott
Adams
James
Miller
10 rows selected.
========
also a problem (using != or <> with NULL)
does NOT show employees with NON-null commissions:
no rows selected
========
need IS NOT here (to select rows with non-NULL attribute)
NOW shows employees with NON-null commissions:
EMPL_LAST_NAME
---------------
Michaels
Ward
Martin
Turner
========
(oh, you can use not with an IS expression, too):
EMPL_LAST_NAME
---------------
Michaels
Ward
Martin
Turner
========
using IN: if I want last names, job titles, and salaries
of managers and analysts
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Scott Analyst 3000
========
providing the same info using OR
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Scott Analyst 3000
========
I just want names, dept names, and locations
for employees hired AFTER 1991-12-01
EMPL_LAST_NAME DEPT_NAME DEPT_LOC
--------------- --------------- ---------------
Ford Research Dallas
James Sales Chicago
Miller Accounting New York
========
(now using ANSI-99 join style - no AND needed in this case)
EMPL_LAST_NAME DEPT_NAME DEPT_LOC
--------------- --------------- ---------------
Ford Research Dallas
James Sales Chicago
Miller Accounting New York
========
DOES NOT work, need to say WHICH table version
of dept_num (to project last names, dept nums,
dept names, and dept locations):
select empl_last_name, dept_num,
*
ERROR at line 1:
ORA-00918: column ambiguously defined
========
WORKS when indicate which dept_num:
EMPL_LAST_NAME DEP DEPT_NAME DEPT_LOC
--------------- --- --------------- ---------------
King 500 Management New York
Jones 200 Research Dallas
Blake 300 Sales Chicago
Raimi 100 Accounting New York
Ford 200 Research Dallas
Smith 200 Research Dallas
Michaels 300 Sales Chicago
Ward 300 Sales Chicago
Martin 300 Sales Chicago
Scott 200 Research Dallas
Turner 300 Sales Chicago
EMPL_LAST_NAME DEP DEPT_NAME DEPT_LOC
--------------- --- --------------- ---------------
Adams 400 Operations Boston
James 300 Sales Chicago
Miller 100 Accounting New York
14 rows selected.
========
get last names of employees who are in Sales
OR have salary >= 1500
EMPL_LAST_NAME
---------------
King
Jones
Blake
Raimi
Ford
Michaels
Ward
Martin
Scott
Turner
10 rows selected.
========
get last names and hiredates of employees
hired after March 1, 1991, AND
(who are in Sales OR have salary >= 1500)
EMPL_LAST_NAME HIREDATE
--------------- ---------
King 17-NOV-91
Jones 02-APR-91
Blake 01-MAY-91
Raimi 09-JUN-91
Ford 03-DEC-91
Martin 28-SEP-91
Scott 09-NOV-91
Turner 08-SEP-91
8 rows selected.
========
THREE ways to ask for employees whose job title is NOT 'Sales':
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-91 5000 500
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
7902 Ford Analyst 7566 03-DEC-91 3000 200
7369 Smith Clerk 7902 17-DEC-90 800 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
7876 Adams Clerk 7788 23-SEP-91 1100 400
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
10 rows selected.
========
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-91 5000 500
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
7902 Ford Analyst 7566 03-DEC-91 3000 200
7369 Smith Clerk 7902 17-DEC-90 800 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
7876 Adams Clerk 7788 23-SEP-91 1100 400
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
10 rows selected.
========
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-91 5000 500
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
7902 Ford Analyst 7566 03-DEC-91 3000 200
7369 Smith Clerk 7902 17-DEC-90 800 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
7876 Adams Clerk 7788 23-SEP-91 1100 400
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
10 rows selected.
========
employees whose salary is in [1100, 1600], TWO ways:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7876 Adams Clerk 7788 23-SEP-91 1100 400
7934 Miller Clerk 7782 23-JAN-92 1300 100
6 rows selected.
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7876 Adams Clerk 7788 23-SEP-91 1100 400
7934 Miller Clerk 7782 23-JAN-92 1300 100
6 rows selected.
========
employees whose empl_nums end with a 9?
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-91 5000 500
7369 Smith Clerk 7902 17-DEC-90 800 200
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
========
employees with a 9 ANYWHERE in their empl_num?
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-91 5000 500
7698 Blake Manager 7839 01-MAY-91 2850 300
7902 Ford Analyst 7566 03-DEC-91 3000 200
7369 Smith Clerk 7902 17-DEC-90 800 200
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7 rows selected.
========
employees whose job_title is 1 character
followed by anager:
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
========
if you want a 6 in the 2nd position
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7698 Blake Manager 7839 01-MAY-91 2850 300
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
========
you can project salaries if multiplied by two:
(notice the default column heading)
SALARY*2
----------
10000
5950
5700
4900
6000
1600
3200
2500
2500
6000
3000
SALARY*2
----------
2200
1900
2600
14 rows selected.
========
using a column alias for the salary-times-2
DOUBLED_SAL
-----------
10000
5950
5700
4900
6000
1600
3200
2500
2500
6000
3000
DOUBLED_SAL
-----------
2200
1900
2600
14 rows selected.
========
putting column alias in double quotes so can contain
a blank, and can get specified case:
doubled sal
-----------
10000
5950
5700
4900
6000
1600
3200
2500
2500
6000
3000
doubled sal
-----------
2200
1900
2600
14 rows selected.
========
NOTE that null values do NOT participate
in computed column computations
(trying to project salary + commission):
SALARY+COMMISSION
-----------------
1900
1750
2650
1500
SALARY+COMMISSION
-----------------
14 rows selected.
========
using a table alias in a further projection
from an equi-join, projecting employee
last names, dept nums, and dept names
EMPL_LAST_NAME DEP DEPT_NAME
--------------- --- ---------------
King 500 Management
Jones 200 Research
Blake 300 Sales
Raimi 100 Accounting
Ford 200 Research
Smith 200 Research
Michaels 300 Sales
Ward 300 Sales
Martin 300 Sales
Scott 200 Research
Turner 300 Sales
EMPL_LAST_NAME DEP DEPT_NAME
--------------- --- ---------------
Adams 400 Operations
James 300 Sales
Miller 100 Accounting
14 rows selected.
========
project avg, min, max, sum, and count
of (non-null) salaries
AVG(SALARY) MIN(SALARY) MAX(SALARY) SUM(SALARY) COUNT(SALARY)
----------- ----------- ----------- ----------- -------------
2073.21429 800 5000 29025 14
========
...of salaries JUST of those in Sales:
AVG(SALARY) MIN(SALARY) MAX(SALARY) SUM(SALARY) COUNT(SALARY)
----------- ----------- ----------- ----------- -------------
1400 1250 1600 5600 4