========
project empl last names and salaries:
EMPL_LAST_NAME SALARY
--------------- ----------
King 5000
Jones 2975
Blake 2850
Raimi 2450
Ford 3000
Smith 800
Michaels 1600
Ward 1250
Martin 1250
Scott 3000
Turner 1500
EMPL_LAST_NAME SALARY
--------------- ----------
Adams 1100
James 950
Miller 1300
14 rows selected.
========
project empl last names and 2 * salaries:
EMPL_LAST_NAME SALARY*2
--------------- ----------
King 10000
Jones 5950
Blake 5700
Raimi 4900
Ford 6000
Smith 1600
Michaels 3200
Ward 2500
Martin 2500
Scott 6000
Turner 3000
EMPL_LAST_NAME SALARY*2
--------------- ----------
Adams 2200
James 1900
Miller 2600
14 rows selected.
========
project empl last names and salaries
(note that projecting a computed column does not change
that column!)
EMPL_LAST_NAME SALARY
--------------- ----------
King 5000
Jones 2975
Blake 2850
Raimi 2450
Ford 3000
Smith 800
Michaels 1600
Ward 1250
Martin 1250
Scott 3000
Turner 1500
EMPL_LAST_NAME SALARY
--------------- ----------
Adams 1100
James 950
Miller 1300
14 rows selected.
========
playing with column aliases!
EMPL_LAST_NAME IF_RAISE
--------------- ----------
King 10000
Jones 5950
Blake 5700
Raimi 4900
Ford 6000
Smith 1600
Michaels 3200
Ward 2500
Martin 2500
Scott 6000
Turner 3000
EMPL_LAST_NAME IF_RAISE
--------------- ----------
Adams 2200
James 1900
Miller 2600
14 rows selected.
EMPL_LAST_NAME if Raise
--------------- ----------
King 10000
Jones 5950
Blake 5700
Raimi 4900
Ford 6000
Smith 1600
Michaels 3200
Ward 2500
Martin 2500
Scott 6000
Turner 3000
EMPL_LAST_NAME if Raise
--------------- ----------
Adams 2200
James 1900
Miller 2600
14 rows selected.
Employee if 10% raise
--------------- ------------
King 5500
Jones 3272.5
Blake 3135
Raimi 2695
Ford 3300
Smith 880
Michaels 1760
Ward 1375
Martin 1375
Scott 3300
Turner 1650
Employee if 10% raise
--------------- ------------
Adams 1210
James 1045
Miller 1430
14 rows selected.
========
WILL GET ERROR, column alias must be in NO quotes or DOUBLE quotes:
select empl_last_name, salary * 2 'if Raise'
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
========
computations will NOT be done on NULL values:
EMPL_LAST_NAME Total pay
--------------- ----------
King
Jones
Blake
Raimi
Ford
Smith
Michaels 1900
Ward 1750
Martin 2650
Scott
Turner 1500
EMPL_LAST_NAME Total pay
--------------- ----------
Adams
James
Miller
14 rows selected.
========
empl last names and dept names, NO table aliases:
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.
========
empl last names and dept names, WITH table aliases
(no difference in the output!):
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; once you have a table alias,
you must USE it THROUGHOUT that select,
EVEN in its SELECT clause...!
select empl_last_name, dept.dept_num, dept_name
*
ERROR at line 1:
ORA-00904: "DEPT"."DEPT_NUM": invalid identifier
========
now using the needed table alias in select clause:
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.
========
average salary of, and number of rows selected,
with job_title of Manager
AVG(SALARY) COUNT(*)
----------- ----------
2758.33333 3
========
note difference between count(*) and count(commission),
and again note that computations are NOT done with NULL values:
COUNT(*) COUNT(COMMISSION) SUM(COMMISSION)
---------- ----------------- ---------------
14 4 2200