=====
see empls ordered by salary (lowest to highest)
=====
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-12 800 200
7900 James Clerk 7698 03-DEC-17 950 300
7876 Adams Clerk 7788 23-SEP-18 1100 400
7654 Martin Sales 7698 28-SEP-18 1250 1400 300
7521 Ward Sales 7698 22-FEB-19 1250 500 300
7934 Miller Clerk 7782 23-JAN-16 1300 100
7844 Turner Sales 7698 08-SEP-19 1500 0 300
7499 Michaels Sales 7698 20-FEB-18 1600 300 300
7782 Raimi Manager 7839 09-JUN-12 2450 100
7698 Blake Manager 7839 01-MAY-13 2850 300
7566 Jones Manager 7839 02-APR-12 2975 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7788 Scott Analyst 7566 09-NOV-18 3000 200
7902 Ford Analyst 7566 03-DEC-12 3000 200
7839 King President 17-NOV-11 5000 500
14 rows selected.
=====
see empls ordered by salary (highest to lowest)
=====
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-11 5000 500
7902 Ford Analyst 7566 03-DEC-12 3000 200
7788 Scott Analyst 7566 09-NOV-18 3000 200
7566 Jones Manager 7839 02-APR-12 2975 200
7698 Blake Manager 7839 01-MAY-13 2850 300
7782 Raimi Manager 7839 09-JUN-12 2450 100
7499 Michaels Sales 7698 20-FEB-18 1600 300 300
7844 Turner Sales 7698 08-SEP-19 1500 0 300
7934 Miller Clerk 7782 23-JAN-16 1300 100
7521 Ward Sales 7698 22-FEB-19 1250 500 300
7654 Martin Sales 7698 28-SEP-18 1250 1400 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-18 1100 400
7900 James Clerk 7698 03-DEC-17 950 300
7369 Smith Clerk 7902 17-DEC-12 800 200
14 rows selected.
=====
order empls by salary THEN by empl_last_name
=====
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-12 800 200
7900 James Clerk 7698 03-DEC-17 950 300
7876 Adams Clerk 7788 23-SEP-18 1100 400
7654 Martin Sales 7698 28-SEP-18 1250 1400 300
7521 Ward Sales 7698 22-FEB-19 1250 500 300
7934 Miller Clerk 7782 23-JAN-16 1300 100
7844 Turner Sales 7698 08-SEP-19 1500 0 300
7499 Michaels Sales 7698 20-FEB-18 1600 300 300
7782 Raimi Manager 7839 09-JUN-12 2450 100
7698 Blake Manager 7839 01-MAY-13 2850 300
7566 Jones Manager 7839 02-APR-12 2975 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7902 Ford Analyst 7566 03-DEC-12 3000 200
7788 Scott Analyst 7566 09-NOV-18 3000 200
7839 King President 17-NOV-11 5000 500
14 rows selected.
=====
in 1st-order by DESCending salary, in 2nd-order by ASCending empl_last_name
=====
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-11 5000 500
7902 Ford Analyst 7566 03-DEC-12 3000 200
7788 Scott Analyst 7566 09-NOV-18 3000 200
7566 Jones Manager 7839 02-APR-12 2975 200
7698 Blake Manager 7839 01-MAY-13 2850 300
7782 Raimi Manager 7839 09-JUN-12 2450 100
7499 Michaels Sales 7698 20-FEB-18 1600 300 300
7844 Turner Sales 7698 08-SEP-19 1500 0 300
7934 Miller Clerk 7782 23-JAN-16 1300 100
7654 Martin Sales 7698 28-SEP-18 1250 1400 300
7521 Ward Sales 7698 22-FEB-19 1250 500 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-18 1100 400
7900 James Clerk 7698 03-DEC-17 950 300
7369 Smith Clerk 7902 17-DEC-12 800 200
14 rows selected.
=====
in 1st-order by ASCending salary, in 2nd-order by DESCending empl_last_name
=====
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7369 Smith Clerk 7902 17-DEC-12 800 200
7900 James Clerk 7698 03-DEC-17 950 300
7876 Adams Clerk 7788 23-SEP-18 1100 400
7521 Ward Sales 7698 22-FEB-19 1250 500 300
7654 Martin Sales 7698 28-SEP-18 1250 1400 300
7934 Miller Clerk 7782 23-JAN-16 1300 100
7844 Turner Sales 7698 08-SEP-19 1500 0 300
7499 Michaels Sales 7698 20-FEB-18 1600 300 300
7782 Raimi Manager 7839 09-JUN-12 2450 100
7698 Blake Manager 7839 01-MAY-13 2850 300
7566 Jones Manager 7839 02-APR-12 2975 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7788 Scott Analyst 7566 09-NOV-18 3000 200
7902 Ford Analyst 7566 03-DEC-12 3000 200
7839 King President 17-NOV-11 5000 500
14 rows selected.
=====
in 1st-order by DESCending salary, in 2nd-order by DESCending empl_last_name
=====
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7839 King President 17-NOV-11 5000 500
7788 Scott Analyst 7566 09-NOV-18 3000 200
7902 Ford Analyst 7566 03-DEC-12 3000 200
7566 Jones Manager 7839 02-APR-12 2975 200
7698 Blake Manager 7839 01-MAY-13 2850 300
7782 Raimi Manager 7839 09-JUN-12 2450 100
7499 Michaels Sales 7698 20-FEB-18 1600 300 300
7844 Turner Sales 7698 08-SEP-19 1500 0 300
7934 Miller Clerk 7782 23-JAN-16 1300 100
7521 Ward Sales 7698 22-FEB-19 1250 500 300
7654 Martin Sales 7698 28-SEP-18 1250 1400 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-18 1100 400
7900 James Clerk 7698 03-DEC-17 950 300
7369 Smith Clerk 7902 17-DEC-12 800 200
14 rows selected.
=====
showing last names of Sales employees, lowest to highest salary
(yes, you can order by a column you are not projecting...!)
=====
EMPL_LAST_NAME
---------------
Ward
Martin
Turner
Michaels
=====
look what happens if you order by a column with null values in it:
=====
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.
=====
average salary of all employees:
=====
AVG(SALARY)
-----------
2073.21429
=====
average salary of managers:
=====
AVG(SALARY)
-----------
2758.33333
=====
average salary of analysts:
=====
AVG(SALARY)
-----------
3000
=====
with GROUP BY, can get average salary for employees based on
on their job title, for ALL job titles:
=====
JOB_TITLE AVG(SALARY)
---------- -----------
Manager 2758.33333
Analyst 3000
Clerk 1037.5
President 5000
Sales 1400
=====
with GROUP BY, can get average salary for employees based on
their dept_num, for ALL dept_nums:
=====
DEP AVG(SALARY)
--- -----------
100 1875
200 2443.75
300 1566.66667
500 5000
400 1100
=====
BUT note: WHERE clause selects rows BEFORE group-by groups them
this gives, JUST for employees hired AFTER January 1, 2015,
the maximum salary and count by job title:
=====
JOB_TITLE MAX(SALARY) COUNT(*)
---------- ----------- ----------
Analyst 3000 1
Clerk 1300 3
Sales 1600 4
=====
if your group by more than one column, there is a group for each
different combination of those columns
=====
here, get average salary and count for each different pair of
job title and mgr columns:
=====
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.
=====
average salary for each dept_num (with the help of group by):
=====
DEP AVG(SALARY)
--- -----------
100 1875
200 2443.75
300 1566.66667
500 5000
400 1100
=====
having (used WITH group by) lets you specify WHICH groups you want included
here, want average salary for each dept_num,
BUT only for dept_num groups whose average salary is MORE than 1500:
=====
DEP AVG(SALARY)
--- -----------
100 1875
200 2443.75
300 1566.66667
500 5000
=====
JUST for empls hired BEFORE July 15, 2017,
want average salary and count by dept_num
BUT only for dept_num values whose average salary for these particular employees
exceeds 1500, and order these rows from highest to lowest average salary...!
=====
DEP AVG(SALARY) COUNT(*)
--- ----------- ----------
500 5000 1
300 2850 1
200 2258.33333 3
100 1875 2