see employee rows in order of salary
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-90 800 200
7900 James Clerk 7698 03-DEC-91 950 300
7876 Adams Clerk 7788 23-SEP-91 1100 400
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7782 Raimi Manager 7839 09-JUN-91 2450 100
7698 Blake Manager 7839 01-MAY-91 2850 300
7566 Jones Manager 7839 02-APR-91 2975 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7788 Scott Analyst 7566 09-NOV-91 3000 200
7902 Ford Analyst 7566 03-DEC-91 3000 200
7839 King President 17-NOV-91 5000 500
14 rows selected.
see employee rows in order of hiredate
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-90 800 200
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
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
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7876 Adams Clerk 7788 23-SEP-91 1100 400
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7788 Scott Analyst 7566 09-NOV-91 3000 200
7839 King President 17-NOV-91 5000 500
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7902 Ford Analyst 7566 03-DEC-91 3000 200
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
14 rows selected.
can order these employee last names by salary without projecting
those salaries:
EMPL_LAST_NAME
---------------
Smith
James
Adams
Martin
Ward
Miller
Turner
Michaels
Raimi
Blake
Jones
EMPL_LAST_NAME
---------------
Scott
Ford
King
14 rows selected.
see the salaries and last names just of those with job title
of Manager, in order of their last names:
SALARY EMPL_LAST_NAME
---------- ---------------
2850 Blake
2975 Jones
2450 Raimi
employee info in order of job title:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7902 Ford Analyst 7566 03-DEC-91 3000 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
7369 Smith Clerk 7902 17-DEC-90 800 200
7934 Miller Clerk 7782 23-JAN-92 1300 100
7876 Adams Clerk 7788 23-SEP-91 1100 400
7900 James Clerk 7698 03-DEC-91 950 300
7698 Blake Manager 7839 01-MAY-91 2850 300
7782 Raimi Manager 7839 09-JUN-91 2450 100
7566 Jones Manager 7839 02-APR-91 2975 200
7839 King President 17-NOV-91 5000 500
7844 Turner Sales 7698 08-SEP-91 1500 0 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
14 rows selected.
employee info in order of job title,
and for those with the same job title, in order
of mgr value:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7902 Ford Analyst 7566 03-DEC-91 3000 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7876 Adams Clerk 7788 23-SEP-91 1100 400
7369 Smith Clerk 7902 17-DEC-90 800 200
7698 Blake Manager 7839 01-MAY-91 2850 300
7782 Raimi Manager 7839 09-JUN-91 2450 100
7566 Jones Manager 7839 02-APR-91 2975 200
7839 King President 17-NOV-91 5000 500
7844 Turner Sales 7698 08-SEP-91 1500 0 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
14 rows selected.
employee info in order of job title,
and for those with the same job title, in order
of mgr value,
and for those with the same job title and mgr value,
in order of hiredate:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7788 Scott Analyst 7566 09-NOV-91 3000 200
7902 Ford Analyst 7566 03-DEC-91 3000 200
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7876 Adams Clerk 7788 23-SEP-91 1100 400
7369 Smith Clerk 7902 17-DEC-90 800 200
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
7839 King President 17-NOV-91 5000 500
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
14 rows selected.
notice what happens with NULL values when order by commission:
EMPL_LAST_NAME JOB_TITLE COMMISSION
--------------- ---------- ----------
Turner Sales 0
Michaels Sales 300
Ward Sales 500
Martin Sales 1400
Ford Analyst
Smith Clerk
Miller Clerk
Jones Manager
Raimi Manager
Scott Analyst
King President
EMPL_LAST_NAME JOB_TITLE COMMISSION
--------------- ---------- ----------
Adams Clerk
James Clerk
Blake Manager
14 rows selected.
...and when order by mgr value (note where employee King appears):
EMPL_LAST_NAME JOB_TITLE MGR
--------------- ---------- ----
Scott Analyst 7566
Ford Analyst 7566
Michaels Sales 7698
James Clerk 7698
Turner Sales 7698
Martin Sales 7698
Ward Sales 7698
Miller Clerk 7782
Adams Clerk 7788
Jones Manager 7839
Raimi Manager 7839
EMPL_LAST_NAME JOB_TITLE MGR
--------------- ---------- ----
Blake Manager 7839
Smith Clerk 7902
King President
14 rows selected.
order in descending order of salary:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-91 5000 500
7902 Ford Analyst 7566 03-DEC-91 3000 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
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
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-91 1100 400
7900 James Clerk 7698 03-DEC-91 950 300
7369 Smith Clerk 7902 17-DEC-90 800 200
14 rows selected.
order in descending order of job_title,
and for those with the same job title in
ascending order of mgr number,
and for those with the same job title and mgr
in descending order of hiredate:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7839 King President 17-NOV-91 5000 500
7782 Raimi Manager 7839 09-JUN-91 2450 100
7698 Blake Manager 7839 01-MAY-91 2850 300
7566 Jones Manager 7839 02-APR-91 2975 200
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7876 Adams Clerk 7788 23-SEP-91 1100 400
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-90 800 200
7902 Ford Analyst 7566 03-DEC-91 3000 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
14 rows selected.
you get the same result when explicitly giving the default ASC for mgr:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7839 King President 17-NOV-91 5000 500
7782 Raimi Manager 7839 09-JUN-91 2450 100
7698 Blake Manager 7839 01-MAY-91 2850 300
7566 Jones Manager 7839 02-APR-91 2975 200
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7876 Adams Clerk 7788 23-SEP-91 1100 400
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-90 800 200
7902 Ford Analyst 7566 03-DEC-91 3000 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
14 rows selected.
display in order of (increasing) salary,
and for those with the same salary, in descending order of hiredate:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-90 800 200
7900 James Clerk 7698 03-DEC-91 950 300
7876 Adams Clerk 7788 23-SEP-91 1100 400
7654 Martin Sales 7698 28-SEP-91 1250 1400 300
7521 Ward Sales 7698 22-FEB-91 1250 500 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
7844 Turner Sales 7698 08-SEP-91 1500 0 300
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7782 Raimi Manager 7839 09-JUN-91 2450 100
7698 Blake Manager 7839 01-MAY-91 2850 300
7566 Jones Manager 7839 02-APR-91 2975 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7902 Ford Analyst 7566 03-DEC-91 3000 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
7839 King President 17-NOV-91 5000 500
14 rows selected.
PROPERLY gives an error for badly-placed order by in a subselect:
order by salary)
*
ERROR at line 7:
ORA-00907: missing right parenthesis
now properly placing the order by in the outer select,
to display these employees making more than the
lowest-paid manager in order of their salaries
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7698 Blake Manager 7839 01-MAY-91 2850 300
7566 Jones Manager 7839 02-APR-91 2975 200
7788 Scott Analyst 7566 09-NOV-91 3000 200
7902 Ford Analyst 7566 03-DEC-91 3000 200
7839 King President 17-NOV-91 5000 500
show the average salary for all employees
AVG(SALARY)
-----------
2073.21429
show the average salary for those with job title of Clerk:
AVG(SALARY)
-----------
1037.5
group the selected rows by job_title,
and project the avg salary of each
group
AVG(SALARY)
-----------
2758.33333
3000
1037.5
5000
1400
group the selected rows by job_title,
and project the job title and the avg salary of each
group (OK since grouping by job_title!):
JOB_TITLE AVG(SALARY)
---------- -----------
Manager 2758.33333
Analyst 3000
Clerk 1037.5
President 5000
Sales 1400
this will fail, can only project
an aggregate function call or
grouped-by attribute:
select mgr, job_title, avg(salary)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
show the dept name and the average salary for those working
in that department:
DEPT_NAME AVG(SALARY)
--------------- -----------
Operations 1100
Sales 1566.66667
Accounting 1875
Research 2443.75
Management 5000
FAILS, cannot project dept_num unless group by it
select d.dept_num, dept_name, min(salary), max(salary),
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
now grouping by BOTH d.dept_num AND dept_name:
DEP DEPT_NAME MIN(SALARY) MAX(SALARY) MIN(HIRED MAX(HIRED
--- --------------- ----------- ----------- --------- ---------
200 Research 800 3000 17-DEC-90 03-DEC-91
500 Management 5000 5000 17-NOV-91 17-NOV-91
100 Accounting 1300 2450 09-JUN-91 23-JAN-92
400 Operations 1100 1100 23-SEP-91 23-SEP-91
300 Sales 950 2850 20-FEB-91 03-DEC-91
note that grouping by job title AND mgr value
(so a group is each distinct PAIR of these values):
JOB_TITLE MGR AVG(SALARY) COUNT(*)
---------- ---- ----------- ----------
Analyst 7566 3000 2
Clerk 7782 1300 1
Manager 7839 2758.33333 3
Sales 7698 1400 4
President 5000 1
Clerk 7902 800 1
Clerk 7788 1100 1
Clerk 7698 950 1
8 rows selected.
can have a group by in a sub-select, also
here, project last names and salaries of
employees making >= the minimum of the
average salaries of those in each department:
EMPL_LAST_NAME SALARY
--------------- ----------
King 5000
Jones 2975
Blake 2850
Raimi 2450
Ford 3000
Michaels 1600
Ward 1250
Martin 1250
Scott 3000
Turner 1500
Adams 1100
EMPL_LAST_NAME SALARY
--------------- ----------
Miller 1300
12 rows selected.
you can do aggregate computations of the
grouped-by aggregate computations
what is the minimum of the average salaries of
each department?
MIN(AVG(SALARY))
----------------
1100
project the average salary for each department
DEP AVG(SALARY)
--- -----------
100 1875
200 2443.75
300 1566.66667
500 5000
400 1100
project the average salary only for departments whose
average salary is more than 1500:
DEP AVG(SALARY)
--- -----------
100 1875
200 2443.75
300 1566.66667
500 5000
this FAILS, you are dealing with ROWS
from the FROM clause in the WHERE clause:
where avg(salary) > 1600
*
ERROR at line 3:
ORA-00934: group function is not allowed here