salary of the highest paid clerk(s)?
MAX(SALARY)
-----------
1300
names of clerk(s) with the highest clerk salary
EMPL_LAST_NAME
---------------
Miller
the id of the manager who manages the highest-paid clerk(s)
MGR
----
7782
name of the manager(s) of the highest paid clerk(s)
Clerk Manager
---------------
Raimi
dept nums whose location is Dallas
DEP
---
200
last names and salaries of employees who work in Dallas
EMPL_LAST_NAME SALARY
--------------- ----------
Jones 2975
Ford 3000
Smith 800
Scott 3000
WILL NOT WORK, must called aggregate/group
function max appropriately; for example, in a select clause!
and salary = max(salary)
*
ERROR at line 4:
ORA-00934: group function is not allowed here
WILL work: put the call to max in a nested select
EMPL
----
7934
ALSO WILL NOT WORK, MUST have parentheses
around the sub-select
and salary = select max(salary)
*
ERROR at line 4:
ORA-00936: missing expression
ALSO WILL NOT WORK, cannot project
"plain" attributes along with aggregate
function calls
select empl_num, max(salary)
*
ERROR at line 1:
ORA-00937: not a single-group group function
WILL NOT WORK, = cannot have >1 value on RHS
where empl_num = (select mgr
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
when a sub-select might project more than
one value, IN works where = does not:
EMPL_LAST_NAME
---------------
Blake
Raimi
Scott
Ford
selecting employee last names and dept names with
the help of a nested select in a FROM clause:
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Blake Sales
Michaels Sales
Ward Sales
Martin Sales
Turner Sales
James Sales
6 rows selected.
demo: if sub-select in FROM uses a column alias,
that alias MUST also be used in the outer select!
ENAME DNAME
--------------- ---------------
Blake Sales
Michaels Sales
Ward Sales
Martin Sales
Turner Sales
James Sales
6 rows selected.
minimum salary for anyone in Sales
MIN(SALARY)
-----------
1250
info for clerks making more than the lowest-paid Sales person
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7934 Miller Clerk 7782 23-JAN-92 1300 100
who is/are the manager(s) of such clerks?
EMPL_LAST_NAME
---------------
Raimi
example joining THREE tables using TWO join conditions,
customer last names, the employee who reps them,
and that employee's department:
CUST_LNAME EMPL_LAST_NAME DEPT_NAME
-------------------- --------------- ---------------
Firstly Michaels Sales
Thirdly Michaels Sales
Secondly Martin Sales