=====
the salary of the highest-paid clerk:
=====
MAX(SALARY)
-----------
1300
=====
the name, salary, and mgr number of the highest-paid clerk:
=====
EMPL_LAST_NAME SALARY MGR
--------------- ---------- ----
Miller 1300 7782
=====
the mgr number of the highest-paid clerk:
=====
MGR
----
7782
=====
the last name and mgr number of the highest-paid clerk:
=====
EMPL_LAST_NAME EMPL
--------------- ----
Raimi 7782
=====
I want to know which employees work in Dallas.
=====
=====
I *could* start by asking: what departments are in Dallas?
=====
DEP
---
200
=====
then I could use this select as a sub-select in another
query, to find out the last names of employees who work
in Dallas:
=====
EMPL_LAST_NAME
---------------
Scott
Jones
Ford
Smith
=====
you have to be careful where you use aggregate function calls
THIS causes an error:
=====
and salary = max(salary)
*
ERROR at line 4:
ORA-00934: group function is not allowed here
=====
what if you want to know the names of Clerks making
more than the salary of the average-paid Clerk?
=====
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
Adams Clerk 1100
Miller Clerk 1300
=====
be careful; you need both instances of job_title = 'Clerk' here!
=====
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Scott Analyst 3000
Turner Sales 1500
Adams Clerk 1100
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
Miller Clerk 1300
12 rows selected.
=====
be careful; you need both instances of job_title = 'Clerk' here!
=====
no rows selected
=====
error you will see if you use a subquery that returns more than
one row with the = operator:
=====
where empl_num = (select mgr
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
=====
but if I use IN instead of = here, this query works:
=====
EMPL_LAST_NAME
---------------
Blake
=====
rather silly example of a sub-select in a FROM clause:
=====
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Adams Operations
=====
another rather silly example of a sub-select in a FROM clause:
=====
ENAME
---------------
Adams
=====
this causes an error; outer select only knows what is projected
from subselect in its FROM clause:
=====
select empl_last_name
*
ERROR at line 1:
ORA-00904: "EMPL_LAST_NAME": invalid identifier
=====
this causes an error; outer select only knows what is projected
from subselect in its FROM clause:
=====
where dept_name = 'Operations'
*
ERROR at line 5:
ORA-00904: "DEPT_NAME": invalid identifier
=====
this causes an error; outer select only knows what is projected
from subselect in its FROM clause:
=====
select ename, salary
*
ERROR at line 1:
ORA-00904: "SALARY": invalid identifier
=====
rather silly example of projecting a literal!
=====
'MO
---
moo
moo
moo
moo
moo
moo
moo
moo
moo
moo
moo
'MO
---
moo
moo
moo
14 rows selected.
=====
another rather silly example of projecting a literal!
=====
35
----------
35
35
35
=====
concatenating empl_last_name and salary:
=====
EMPL_LAST_NAME||SALARY
-------------------------------------------------------
King5000
Jones2975
Blake2850
Raimi2450
Ford3000
Smith800
Michaels1600
Ward1250
Martin1250
Scott3000
Turner1500
EMPL_LAST_NAME||SALARY
-------------------------------------------------------
Adams1100
James950
Miller1300
14 rows selected.
=====
concatenating empl_last_name, a dash, and salary:
=====
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.
=====
concatenating empl_last_name, a dash and $, and salary:
=====
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.
=====
concatenating empl_last_name, a dash and $, and salary,
now with a column alias, also:
=====
Empl-Salary
---------------------------------------------------------
King-$5000
Jones-$2975
Blake-$2850
Raimi-$2450
Ford-$3000
Smith-$800
Michaels-$1600
Ward-$1250
Martin-$1250
Scott-$3000
Turner-$1500
Empl-Salary
---------------------------------------------------------
Adams-$1100
James-$950
Miller-$1300
14 rows selected.
=====
quick example of using concatenation to generate "CSV",
comma-separated-values, output from a database:
=====
csv output
-------------------------------------------------------------------
King,President,5000
Jones,Manager,2975
Blake,Manager,2850
Raimi,Manager,2450
Ford,Analyst,3000
Smith,Clerk,800
Michaels,Sales,1600
Ward,Sales,1250
Martin,Sales,1250
Scott,Analyst,3000
Turner,Sales,1500
csv output
-------------------------------------------------------------------
Adams,Clerk,1100
James,Clerk,950
Miller,Clerk,1300
14 rows selected.
=====
temporarily inserting a new department 600, a Computer department
in Arcata (new, so has not employees yet):
=====
1 row created.
=====
the names and locations of departments WITH employees
=====
DEPT_LOC DEPT_NAME
--------------- ---------------
New York Accounting
Dallas Research
Chicago Sales
Boston Operations
New York Management
=====
the names and locations of departments WITHOUT employees
=====
DEPT_LOC DEPT_NAME
--------------- ---------------
Arcata Computer
=====
ERROR: does NOT correctly return
the names and locations of departments WITHOUT employees
(it neeeeeeds a correlation condition!)
=====
no rows selected
=====
ERROR: does NOT correctly return
the names and locations of departments WITHOUT employees
(it has a join condition instead of a correlation condition!)
=====
no rows selected
=====
can use table aliases to make sure you have a correlation
condition when a table is in the outer and an inner subquery:
=====
DEPT_LOC DEPT_NAME
--------------- ---------------
Arcata Computer
=====
(I am UNDOING, rolling-back, the insertion of the Computer department
into the dept table)
=====
Rollback complete.