Analytical Exercises and Solutions Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Exercises

1.      OVER

-- Id     : $Id: tabletax.sql,v 1.3 2015/07/10 08:50:54 Laurent Exp $
-- Author : $Author: Laurent $
-- Date   : $Date: 2015/07/10 08:50:54 $
--
-- Create TAX and TAX_OPTION Tables in current schema
--
-- Current user needs CREATE VIEW privilege

WHENEVER SQLERROR EXIT FAILURE
EXEC BEGIN EXECUTE IMMEDIATE 'DROP TABLE TAX_EMP'; EXCEPTION WHEN OTHERS THEN NULL; END
EXEC BEGIN EXECUTE IMMEDIATE 'DROP TABLE TAX_OPTION'; EXCEPTION WHEN OTHERS THEN NULL; END

CREATE TABLE
   TAX_EMP
(
   EMPNO NUMBER REFERENCES EMP,
   YEAR NUMBER,
   TAX NUMBER,
   PRIMARY KEY (EMPNO, YEAR)
);
INSERT INTO
   TAX_EMP
(
   EMPNO,
   YEAR,
   TAX
)
VALUES
(
   7788,
   2015,
   5000
);
INSERT INTO
   TAX_EMP
(
   EMPNO,
   YEAR,
   TAX
)
VALUES
(
   7839,
   2015,
   10000
);
CREATE TABLE
   TAX_OPTION
(
   OPTION_ID NUMBER,
   DUEMONTH NUMBER,
   DUEDAY NUMBER,
   PRIMARY KEY (OPTION_ID, DUEMONTH, DUEDAY),
   CHECK (TO_DATE(2001*10000+DUEMONTH*100+DUEDAY,'YYYYMMDD') IS NOT NULL)
   -- checks that it is a valid day and month
);

INSERT INTO
   TAX_OPTION
(
   OPTION_ID,
   DUEMONTH,
   DUEDAY
)
VALUES
(
   1,
   9,
   30
);
INSERT INTO
   TAX_OPTION
(
   OPTION_ID,
   DUEMONTH,
   DUEDAY
)
VALUES
(
   2,
   6,
   30
);
INSERT INTO
   TAX_OPTION
(
   OPTION_ID,
   DUEMONTH,
   DUEDAY
)
VALUES
(
   2,
   9,
   30
);
INSERT INTO
   TAX_OPTION
(
   OPTION_ID,
   DUEMONTH,
   DUEDAY
)
VALUES
(
   2,
   12,
   31
);
COMMIT;
CREATE OR REPLACE VIEW
   TAX
AS
SELECT
   e.ENAME,
   t.YEAR,
   t.TAX,
   o.OPTION_ID,
   TO_DATE(t.YEAR||'.'||o.DUEMONTH||'.'||o.DUEDAY, 'YYYY.MM.DD') DUEDATE
FROM
   EMP e
   JOIN TAX_EMP t USING (EMPNO)
   CROSS JOIN TAX_OPTION o;
SELECT * FROM TAX;

ENAME    YEAR    TAX  OPTION_ID DUEDATE
------ ------ ------ ---------- ---------
SCOTT    2015   5000          1 30-SEP-08
SCOTT    2015   5000          2 30-JUN-08
SCOTT    2015   5000          2 30-SEP-08
SCOTT    2015   5000          2 31-DEC-08
KING     2015  10000          1 30-SEP-09
KING     2015  10000          2 30-JUN-09
KING     2015  10000          2 30-SEP-09
KING     2015  10000          2 31-DEC-09

The tax could be paid either in one or three installments.

Determine the due amount for each date. Round the value to 1$ and, if necessary, correct the final payment to keep the overall total accurate. The expected result is:

ENAME   OPTION_ID DUEDATE       AMOUNT
------ ---------- --------- ----------
SCOTT           1 30-SEP-08       5000
SCOTT           2 30-JUN-08       1667
SCOTT           2 30-SEP-08       1667
SCOTT           2 31-DEC-08       1666
KING            1 30-SEP-09      10000
KING            2 30-JUN-09       3333
KING            2 30-SEP-09       3333
KING            2 31-DEC-09       3334

2.      RANGE

For each employee, retrieve the previous and next hire dates. Example: James was hired on 1981-12-03, the previous hire date is 1981-11-17 and the next hire date is 1982-01-23.

3.      Moving average

Using a window of three months preceding and three months following, retrieve the average salary per hire date.

4.      Cumulative totals

Return the cumulative salary of clerks and the cumulative salary of managers for each employee from the longest-serving to the most recently hired.

Solutions

1.      OVER

SELECT
   ENAME,
   OPTION_ID,
   DUEDATE,
   CASE
      WHEN
         COUNT(*) OVER (PARTITION BY ENAME, YEAR, OPTION_ID)=1
      THEN
         TAX
      WHEN
         ROW_NUMBER() OVER (PARTITION BY ENAME, YEAR, OPTION_ID
            ORDER BY DUEDATE DESC)=1
      THEN
         TAX-(COUNT(*) OVER (PARTITION BY ENAME, YEAR, OPTION_ID)-1)*
            ROUND(TAX/COUNT(*) OVER (PARTITION BY ENAME, YEAR, OPTION_ID))
      ELSE
         ROUND(TAX/COUNT(*) OVER (PARTITION BY ENAME, YEAR, OPTION_ID))
   END AMOUNT
FROM
   TAX;

ENAME   OPTION_ID DUEDATE       AMOUNT
------ ---------- --------- ----------
SCOTT           1 30-SEP-08       5000
SCOTT           2 30-JUN-08       1667
SCOTT           2 30-SEP-08       1667
SCOTT           2 31-DEC-08       1666
KING            1 30-SEP-09      10000
KING            2 30-JUN-09       3333
KING            2 30-SEP-09       3333
KING            2 31-DEC-09       3334

COUNT retrieves the number of periods. For one period, the result is trivial. For more than one period, the first amounts are equals to the tax divided by the count. The last amount is the difference between the tax and all previous amounts.

2.      RANGE

RANGE windowing is required to retrieve an employee that was hired before and not on the same date.

SELECT
   ENAME,
   HIREDATE,
   LAST_VALUE(HIREDATE) OVER
   (
      ORDER BY HIREDATE
      RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '1' DAY PRECEDING
   ) PREVIOUS,
   FIRST_VALUE(HIREDATE) OVER
   (
      ORDER BY HIREDATE
      RANGE BETWEEN INTERVAL '1' DAY FOLLOWING AND UNBOUNDED FOLLOWING
   ) NEXT
FROM
   EMP;

 ENAME      HIREDATE  PREVIOUS  NEXT
---------- --------- --------- ---------
SMITH      17-DEC-80           20-FEB-81
ALLEN      20-FEB-81 17-DEC-80 22-FEB-81
WARD       22-FEB-81 20-FEB-81 02-APR-81
JONES      02-APR-81 22-FEB-81 01-MAY-81
BLAKE      01-MAY-81 02-APR-81 09-JUN-81
CLARK      09-JUN-81 01-MAY-81 08-SEP-81
TURNER     08-SEP-81 09-JUN-81 28-SEP-81
MARTIN     28-SEP-81 08-SEP-81 17-NOV-81
KING       17-NOV-81 28-SEP-81 03-DEC-81
JAMES      03-DEC-81 17-NOV-81 23-JAN-82
FORD       03-DEC-81 17-NOV-81 23-JAN-82
MILLER     23-JAN-82 03-DEC-81 19-APR-87
SCOTT      19-APR-87 23-JAN-82 23-MAY-87
ADAMS      23-MAY-87 19-APR-87

Both James and Ford were hired on Dec 3. The previous hire date is Nov 17 and the next is Jan 23. 

3.      Moving average

SELECT
   ENAME,
   SAL,
   HIREDATE,
   AVG(SAL)
   OVER
   (
      ORDER BY
         HIREDATE
      RANGE BETWEEN
         INTERVAL '3' MONTH PRECEDING
         AND
         INTERVAL '3' MONTH FOLLOWING
    ) MOVINGAVG
FROM
   EMP;

ENAME         SAL HIREDATE  MOVINGAVG
---------- ------ --------- ---------
SMITH         800 17-DEC-80      1217
ALLEN        1600 20-FEB-81      1895
WARD         1250 22-FEB-81      1895
JONES        2975 02-APR-81      2225
BLAKE        2850 01-MAY-81      2225
CLARK        2450 09-JUN-81      2444
TURNER       1500 08-SEP-81      2358
MARTIN       1250 28-SEP-81      2340
KING         5000 17-NOV-81      2167
JAMES         950 03-DEC-81      2167
FORD         3000 03-DEC-81      2167
MILLER       1300 23-JAN-82      2563
SCOTT        3000 19-APR-87      2050
ADAMS        1100 23-MAY-87      2050

The average is evaluated over a 6 month period. For instance, the moving average of Scott is the average salary of employees hired between Jan 19, 1987 and July 19, 1987.

4.      Cumulative totals

SELECT
   ENAME,
   JOB,
   SAL,
   HIREDATE,
   SUM(DECODE(JOB,'CLERK',SAL)) OVER (ORDER BY HIREDATE) SALCLERK,
   SUM(DECODE(JOB,'MANAGER',SAL)) OVER (ORDER BY HIREDATE) SALMGR
FROM
   EMP;

ENAME      JOB          SAL HIREDATE    SALCLERK     SALMGR
---------- --------- ------ --------- ---------- ----------
SMITH      CLERK        800 17-DEC-80        800
ALLEN      SALESMAN    1600 20-FEB-81        800
WARD       SALESMAN    1250 22-FEB-81        800
JONES      MANAGER     2975 02-APR-81        800       2975
BLAKE      MANAGER     2850 01-MAY-81        800       5825
CLARK      MANAGER     2450 09-JUN-81        800       8275
TURNER     SALESMAN    1500 08-SEP-81        800       8275
MARTIN     SALESMAN    1250 28-SEP-81        800       8275
KING       PRESIDENT   5000 17-NOV-81        800       8275
JAMES      CLERK        950 03-DEC-81       1750       8275
FORD       ANALYST     3000 03-DEC-81       1750       8275
MILLER     CLERK       1300 23-JAN-82       3050       8275
SCOTT      ANALYST     3000 19-APR-87       3050       8275
ADAMS      CLERK       1100 23-MAY-87       4150       8275

The clerks? cumulative total sums the salaries of the clerks; other salaries are set to NULL by DECODE and ignored by SUM.

The same mechanism is used for SALMGR.