Oracle PL/SQL/Analytical Functions/Analytical Functions Basics

Материал из SQL эксперт
Перейти к: навигация, поиск

Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2               values (101,"Jason",to_date("19960725","YYYYMMDD"), 34000,        44000        "W")
  3  /
             values (101,"Jason",to_date("19960725","YYYYMMDD"), 34000,        44000        "W")
                                                                                            *
ERROR at line 2:
ORA-00917: missing comma

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
SQL>
SQL> SELECT e.empno, e.ename, j.jobtitle, e.orig_salary,
  2    RANK() OVER(ORDER BY e.orig_salary desc) rankorder
  3  FROM employee e, job j
  4  WHERE e.orig_salary < 43000
  5    AND e.empno = j.empno
  6  ORDER BY orig_salary desc;
Hit a key to continue
     EMPNO ENAME      JOBTITLE   ORIG_SALARY  RANKORDER
---------- ---------- ---------- ----------- ----------
       105 Robert     Accountant       31000          1
       123 James      Dediator         23000          2
       108 Jode       Developer        21000          3
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>



Adding an Analytical Function to the GROUP BY with ORDER BY Version

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer
8 rows selected.
SQL>
SQL> -- Adding an Analytical Function to the GROUP BY with ORDER BY Version
SQL>
SQL> -- When the analytical function RANK is added to the statement, the RANK function is applied last,
SQL> -- just before the ordering:
SQL>
SQL> SELECT j.jobtitle, COUNT(*),
  2    MAX(orig_salary) maxsalary,
  3    MIN(orig_salary) minsalary,
  4    RANK() OVER(ORDER BY MAX(orig_salary)) rankorder
  5  FROM employee e, job j
  6  WHERE e.orig_salary < 43000
  7    AND e.empno = j.empno
  8  GROUP BY j.jobtitle
  9  ORDER BY rankorder;
Hit a key to continue
JOBTITLE     COUNT(*)  MAXSALARY  MINSALARY  RANKORDER
---------- ---------- ---------- ---------- ----------
Developer           1      21000      21000          1
Dediator            1      23000      23000          2
Accountant          1      31000      31000          3
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>



Changing the Final Ordering after Having Added an Analytical Function

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer
8 rows selected.
SQL>
SQL> -- Changing the Final Ordering after Having Added an Analytical Function
SQL>
SQL>
SQL>
SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,
  2    MIN(orig_salary) minsalary,
  3    RANK() OVER(ORDER BY MAX(orig_salary)) rankorder
  4  FROM employee e, job j
  5  WHERE e.orig_salary < 43000
  6    AND e.empno = j.empno
  7  GROUP BY j.jobtitle
  8  ORDER BY j.jobtitle desc;
Hit a key to continue
JOBTITLE     COUNT(*)  MAXSALARY  MINSALARY  RANKORDER
---------- ---------- ---------- ---------- ----------
Developer           1      21000      21000          1
Dediator            1      23000      23000          2
Accountant          1      31000      31000          3
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>
SQL>



Column alias for the analytical function is used in the final ORDER BY

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                   orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       NULL,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), NULL,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      NULL,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), NULL,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       NULL,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000             E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82                   58000 E
       105 Robert     15-JAN-84       31000             W
       116 Linda      30-JUL-87                   53000 E
       117 David      31-DEC-90       78000             W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL>
SQL>
SQL> -- column alias for the analytical function is used in the final ORDER BY
SQL>
SQL> SELECT empno, ename, NVL(curr_salary,44444),
  2    RANK()
  3    OVER(ORDER BY NVL(curr_salary,44444) desc) salary
  4  FROM employee
  5  ORDER BY salary;
     EMPNO ENAME      NVL(CURR_SALARY,44444)     SALARY
---------- ---------- ---------------------- ----------
       104 Celia                       58000          1
       116 Linda                       53000          2
       122 Alison                      44444          3
       105 Robert                      44444          3
       117 David                       44444          3
       123 James                       32000          6
       108 Jode                        29000          7
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>



Combine analytical function with count(*)

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE                    SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ------------------------ ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06                  1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86                  6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90                  6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99                  2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98                  2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96                  4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98                  7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02                  1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> select id, avg(salary), sum(salary), max(salary), count(*) from Employee group by id;
ID                AVG(SALARY)              SUM(SALARY)              MAX(SALARY)                 COUNT(*)
---- ------------------------ ------------------------ ------------------------ ------------------------
04                    2344.78                  2344.78                  2344.78                     1.00
08                    1232.78                  1232.78                  1232.78                     1.00
07                    7897.78                  7897.78                  7897.78                     1.00
01                    1234.56                  1234.56                  1234.56                     1.00
02                    6661.78                  6661.78                  6661.78                     1.00
03                    6544.78                  6544.78                  6544.78                     1.00
05                    2334.78                  2334.78                  2334.78                     1.00
06                    4322.78                  4322.78                  4322.78                     1.00
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>



If more analytical functions are added, yet more sorting may result

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- If more analytical functions are added, yet more sorting may result
SQL>
SQL> SELECT empno, ename, orig_salary, curr_salary,
  2    RANK() OVER(ORDER BY orig_salary) rank,
  3    DENSE_RANK() OVER(ORDER BY curr_salary) d_rank
  4  FROM employee
  5  WHERE orig_salary > 38000
  6  ORDER BY ename;
Hit a key to continue
     EMPNO ENAME      ORIG_SALARY CURR_SALARY       RANK     D_RANK
---------- ---------- ----------- ----------- ---------- ----------
       122 Alison           45000       48000          2          1
       104 Celia            53000       58000          3          3
       117 David            78000       85000          4          4
       116 Linda            43000       53000          1          2
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>



More Than One Analytical Function May Be Used in a Single Statement

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer
8 rows selected.
SQL>
SQL>
SQL> -- More Than One Analytical Function May Be Used in a Single Statement
SQL>
SQL>
SQL>
SQL> SELECT empno, ename, curr_salary,
  2    ROW_NUMBER() OVER(ORDER BY curr_salary) rnum,
  3    RANK() OVER(ORDER BY curr_salary) rank,
  4    DENSE_RANK() OVER(ORDER BY curr_salary) drank
  5  FROM employee
  6  ORDER BY ename;
Hit a key to continue
     EMPNO ENAME      CURR_SALARY       RNUM       RANK      DRANK
---------- ---------- ----------- ---------- ---------- ----------
       122 Alison           48000          4          4          4
       104 Celia            58000          6          6          6
       117 David            85000          7          7          7
       123 James            32000          2          2          2
       108 Jode             29000          1          1          1
       116 Linda            53000          5          5          5
       105 Robert           36000          3          3          3
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>



Nulls could also be handled with a default value using the NVL function in the analytical function

SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                   orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       NULL,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), NULL,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      NULL,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), NULL,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       NULL,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000             E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82                   58000 E
       105 Robert     15-JAN-84       31000             W
       116 Linda      30-JUL-87                   53000 E
       117 David      31-DEC-90       78000             W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL>
SQL>
SQL> -- Nulls could also be handled with a default value using the NVL function in the analytical
SQL> -- function like this:
SQL>
SQL> SELECT empno, ename, NVL(curr_salary,44444),
  2    RANK()
  3    OVER(ORDER BY NVL(curr_salary,44444) desc NULLS LAST)
  4        salary
  5  FROM employee
  6  ORDER BY curr_salary desc NULLS LAST;
     EMPNO ENAME      NVL(CURR_SALARY,44444)     SALARY
---------- ---------- ---------------------- ----------
       104 Celia                       58000          1
       116 Linda                       53000          2
       123 James                       32000          6
       108 Jode                        29000          7
       117 David                       44444          3
       122 Alison                      44444          3
       105 Robert                      44444          3
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>



See the difference between a person"s salary and the average for his or her region

SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL>
SQL> select * from employee;
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL>
SQL> -- See the difference between a person"s salary and the average for his or her region
SQL>
SQL>
SQL>
SQL> SELECT empno, ename, region, curr_salary,
  2    orig_salary,
  3    ROUND(AVG(orig_salary) OVER(PARTITION BY region))
  4        "Avg-group",
  5    ROUND(orig_salary - AVG(orig_salary) OVER(PARTITION
  6        BY region)) "Diff."
  7  FROM employee
  8  ORDER BY region, ename;
     EMPNO ENAME      R CURR_SALARY ORIG_SALARY  Avg-group      Diff.
---------- ---------- - ----------- ----------- ---------- ----------
       122 Alison     E       48000       45000      40500       4500
       104 Celia      E       58000       53000      40500      12500
       108 Jode       E       29000       21000      40500     -19500
       116 Linda      E       53000       43000      40500       2500
       117 David      W       85000       78000      44000      34000
       123 James      W       32000       23000      44000     -21000
       105 Robert     W       36000       31000      44000     -13000
7 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>



The direct application of a WHERE clause in the query is not allowed

SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(20 BYTE),
  4    Last_Name          VARCHAR2(20 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(20 BYTE),
  9    Description        VARCHAR2(80 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 2234.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 2324.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 3334.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 4334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 5334.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 6334.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 7334.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY                 DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- -------------------- --------------------------------------------------------------------------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto              Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    2234.78 Vancouver            Tester
03   James                Smith                12-DEC-78 15-MAR-90    2324.78 Vancouver            Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    3334.78 Vancouver            Manager
05   Robert               Black                15-JAN-84 08-AUG-98    4334.78 Vancouver            Tester
06   Linda                Green                30-JUL-87 04-JAN-96    5334.78 New York             Tester
07   David                Larry                31-DEC-90 12-FEB-98    6334.78 New York             Manager
08   James                Cat                  17-SEP-96 15-APR-02    7334.78 Vancouver            Tester
8 rows selected.
SQL>
SQL>
SQL> -- the direct application of a WHERE clause in the query is not allowed:
SQL>
SQL> SELECT id, first_name, salary,
  2    DENSE_RANK() OVER(ORDER BY salary desc) toprank
  3  FROM employee
  4  WHERE DENSE_RANK() OVER(ORDER BY salary desc) <= 5;
WHERE DENSE_RANK() OVER(ORDER BY salary desc) <= 5
      *
ERROR at line 4:
ORA-30483: window  functions are not allowed here

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>
SQL>
SQL>



Use an analytical function in a WHERE clause

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /
Table created.
SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       48000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000,       58000,        "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      36000,        "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000,       53000,       "E")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       85000,       "W")
  3  /
1 row created.
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
  3  /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   "Painter");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   "Tester");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   "Dediator");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   "Chemist");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   "Accountant");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   "Manager");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   "Programmer");
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   "Developer");
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue
     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E
7 rows selected.
SQL> select * from job;
Hit a key to continue
     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- Use an analytical function in a WHERE clause
SQL>
SQL>
SQL> SELECT *
  2  FROM
  3    (SELECT empno, ename, orig_salary,
  4    DENSE_RANK() OVER(ORDER BY orig_salary) d_rank
  5    FROM employee) x
  6  WHERE x.d_rank = 3;
Hit a key to continue
     EMPNO ENAME      ORIG_SALARY     D_RANK
---------- ---------- ----------- ----------
       105 Robert           31000          3
SQL>
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee;
Table dropped.
SQL>
SQL>



Use multiple analytical functions

SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(20 BYTE),
  4    Last_Name          VARCHAR2(20 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(20 BYTE),
  9    Description        VARCHAR2(80 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 2234.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 2324.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 3334.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 4334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 5334.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 6334.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 7334.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY                 DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- -------------------- --------------------------------------------------------------------------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto              Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    2234.78 Vancouver            Tester
03   James                Smith                12-DEC-78 15-MAR-90    2324.78 Vancouver            Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    3334.78 Vancouver            Manager
05   Robert               Black                15-JAN-84 08-AUG-98    4334.78 Vancouver            Tester
06   Linda                Green                30-JUL-87 04-JAN-96    5334.78 New York             Tester
07   David                Larry                31-DEC-90 12-FEB-98    6334.78 New York             Manager
08   James                Cat                  17-SEP-96 15-APR-02    7334.78 Vancouver            Tester
8 rows selected.
SQL>
SQL>
SQL> -- multiple analytical functions
SQL>
SQL> SELECT id, first_name, salary,
  2    RANK() OVER(ORDER BY salary desc) toprank_orig,
  3      salary,
  4    RANK() OVER(ORDER BY Start_Date desc) toprank_curr
  5  FROM employee
  6  ORDER BY first_name;
ID   FIRST_NAME               SALARY TOPRANK_ORIG     SALARY TOPRANK_CURR
---- -------------------- ---------- ------------ ---------- ------------
02   Alison                  2234.78            7    2234.78            8
04   Celia                   3334.78            5    3334.78            6
07   David                   6334.78            2    6334.78            3
08   James                   7334.78            1    7334.78            1
03   James                   2324.78            6    2324.78            7
01   Jason                   1234.56            8    1234.56            2
06   Linda                   5334.78            3    5334.78            4
05   Robert                  4334.78            4    4334.78            5
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>
SQL>