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 last name, job title, mgr, and hiredate
in order of job title:
EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
--------------- ---------- ---- ---------
Ford Analyst 7566 03-DEC-91
Scott Analyst 7566 09-NOV-91
Smith Clerk 7902 17-DEC-90
Miller Clerk 7782 23-JAN-92
Adams Clerk 7788 23-SEP-91
James Clerk 7698 03-DEC-91
Blake Manager 7839 01-MAY-91
Raimi Manager 7839 09-JUN-91
Jones Manager 7839 02-APR-91
King President 17-NOV-91
Turner Sales 7698 08-SEP-91
EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
--------------- ---------- ---- ---------
Martin Sales 7698 28-SEP-91
Ward Sales 7698 22-FEB-91
Michaels Sales 7698 20-FEB-91
14 rows selected.
employee last name, job title, mgr, and hiredate
in order of job title,
and for those with the same job title, in order
of mgr value:
EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
--------------- ---------- ---- ---------
Ford Analyst 7566 03-DEC-91
Scott Analyst 7566 09-NOV-91
James Clerk 7698 03-DEC-91
Miller Clerk 7782 23-JAN-92
Adams Clerk 7788 23-SEP-91
Smith Clerk 7902 17-DEC-90
Blake Manager 7839 01-MAY-91
Raimi Manager 7839 09-JUN-91
Jones Manager 7839 02-APR-91
King President 17-NOV-91
Turner Sales 7698 08-SEP-91
EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
--------------- ---------- ---- ---------
Martin Sales 7698 28-SEP-91
Ward Sales 7698 22-FEB-91
Michaels Sales 7698 20-FEB-91
14 rows selected.
employee last name, job title, mgr, and hiredate
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_LAST_NAME JOB_TITLE MGR HIREDATE
--------------- ---------- ---- ---------
Scott Analyst 7566 09-NOV-91
Ford Analyst 7566 03-DEC-91
James Clerk 7698 03-DEC-91
Miller Clerk 7782 23-JAN-92
Adams Clerk 7788 23-SEP-91
Smith Clerk 7902 17-DEC-90
Jones Manager 7839 02-APR-91
Blake Manager 7839 01-MAY-91
Raimi Manager 7839 09-JUN-91
King President 17-NOV-91
Michaels Sales 7698 20-FEB-91
EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
--------------- ---------- ---- ---------
Ward Sales 7698 22-FEB-91
Turner Sales 7698 08-SEP-91
Martin Sales 7698 28-SEP-91
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 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
7499 Michaels Sales 7698 20-FEB-91 1600 300 300
7900 James Clerk 7698 03-DEC-91 950 300
7844 Turner Sales 7698 08-SEP-91 1500 0 300
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
7876 Adams Clerk 7788 23-SEP-91 1100 400
7566 Jones Manager 7839 02-APR-91 2975 200
7782 Raimi Manager 7839 09-JUN-91 2450 100
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7698 Blake Manager 7839 01-MAY-91 2850 300
7369 Smith Clerk 7902 17-DEC-90 800 200
7839 King President 17-NOV-91 5000 500
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.
this FAILS, order by in a subselect does not
really make sense
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 Manager:
AVG(SALARY)
-----------
2758.33333
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
FAILS, with group by you CANNOT project
attributes you are not grouping by:
select job_title, mgr, 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, with GROUP BY you CANNOT project
an attribute NOT being grouped-by
select d.dept_num, dept_name, avg(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 AVG(SALARY)
--- --------------- -----------
400 Operations 1100
300 Sales 1566.66667
100 Accounting 1875
200 Research 2443.75
500 Management 5000
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
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