=====
THIS DOES NOT WORK, there ARE empls whose commission is null
=====
no rows selected
=====
use IS NULL to get empls whose commission is null
=====
EMPL_LAST_NAME
---------------
King
Jones
Blake
Raimi
Ford
Smith
Scott
Adams
James
Miller
10 rows selected.
=====
THIS DOES NOT WORK, there ARE empls whose commission is NOT null
=====
no rows selected
=====
use IS NOT NULL to get empls whose commission is NOT null
=====
EMPL_LAST_NAME COMMISSION
--------------- ----------
Michaels 300
Ward 500
Martin 1400
Turner 0
=====
want this info about empls who are Analysts or Managers
=====
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Scott Analyst 3000
=====
projecting JUST empl_last_name and dept_name from the equi-join
of empl and dept
=====
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Miller Accounting
Raimi Accounting
Scott Research
Jones Research
Ford Research
Smith Research
Martin Sales
Ward Sales
Blake Sales
Michaels Sales
James Sales
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Turner Sales
Adams Operations
King Management
14 rows selected.
========
WILL GET ERROR!
dept_num is in dept and empl, MUST say which table's version you want
========
select empl_last_name, dept_num, dept_name
*
ERROR at line 1:
ORA-00918: column ambiguously defined
=====
indicate which table's version of dept_num you want, and that works:
=====
EMPL_LAST_NAME DEP DEPT_NAME
--------------- --- ---------------
Miller 100 Accounting
Raimi 100 Accounting
Scott 200 Research
Jones 200 Research
Ford 200 Research
Smith 200 Research
Martin 300 Sales
Ward 300 Sales
Blake 300 Sales
Michaels 300 Sales
James 300 Sales
EMPL_LAST_NAME DEP DEPT_NAME
--------------- --- ---------------
Turner 300 Sales
Adams 400 Operations
King 500 Management
14 rows selected.
=====
projecting JUST empl_last_name and salary for empls
whose salary is between 1100 and 1600 inclusive:
=====
EMPL_LAST_NAME SALARY
--------------- ----------
Michaels 1600
Ward 1250
Martin 1250
Turner 1500
Adams 1100
Miller 1300
6 rows selected.
=====
asking for employees whose empl_num ENDS with a 9:
=====
EMPL EMPL_LAST_NAME
---- ---------------
7839 King
7369 Smith
7499 Michaels
=====
asking for employees whose job_title is ONE letter followed by
anager
=====
JOB_TITLE EMPL_LAST_NAME
---------- ---------------
Manager Jones
Manager Blake
Manager Raimi