Analytics - Windows and null values

View All Scripts Login to Run Script

Script Name Analytics - Windows and null values
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script looks at how to deal with nulls when using the window clause. Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Wednesday February 24, 2016

Statement 1
```
drop table external_emp
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table external_emp (
SEQ int,
EMPNO    NUMBER(4)    ,
ENAME             VARCHAR2(10) ,
JOB               VARCHAR2(9)  ,
MGR               NUMBER(4)    ,
HIREDATE          DATE         ,
SAL               NUMBER(7,2)  ,
COMM              NUMBER(7,2)  ,
DEPTNO            NUMBER(2)    
)

Table created.

Statement 3

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (1,7782,'CLARK','MANAGER',7839,to_date('09/JUN/81','DD/MON/RR'),2450,null,10)

1 row(s) inserted.

Statement 4

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (2,7839,'KING','PRESIDENT',null,to_date('17/NOV/81','DD/MON/RR'),5000,null,null)

1 row(s) inserted.

Statement 5

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (3,7934,'MILLER','CLERK',7782,to_date('23/JAN/82','DD/MON/RR'),1300,null,null)

1 row(s) inserted.

Statement 6

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (4,7369,'SMITH','CLERK',7902,to_date('17/DEC/80','DD/MON/RR'),800,null,20)

1 row(s) inserted.

Statement 7

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (5,7876,'ADAMS','CLERK',7788,to_date('12/JAN/83','DD/MON/RR'),1100,null,null)

1 row(s) inserted.

Statement 8

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (6,7902,'FORD','ANALYST',7566,to_date('03/DEC/81','DD/MON/RR'),3000,null,null)

1 row(s) inserted.

Statement 9

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7,7566,'JONES','MANAGER',7839,to_date('02/APR/81','DD/MON/RR'),2975,null,null)

1 row(s) inserted.

Statement 10

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (8,7788,'SCOTT','ANALYST',7566,to_date('09/DEC/82','DD/MON/RR'),3000,null,null)

1 row(s) inserted.

Statement 11

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (9,7499,'ALLEN','SALESMAN',7698,to_date('20/FEB/81','DD/MON/RR'),1600,300,30)

1 row(s) inserted.

Statement 12

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (10,7521,'WARD','SALESMAN',7698,to_date('22/FEB/81','DD/MON/RR'),1250,500,null)

1 row(s) inserted.

Statement 13

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (11,7654,'MARTIN','SALESMAN',7698,to_date('28/SEP/81','DD/MON/RR'),1250,1400,null)

1 row(s) inserted.

Statement 14

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (12,7698,'BLAKE','MANAGER',7839,to_date('01/MAY/81','DD/MON/RR'),2850,null,null)

1 row(s) inserted.

Statement 15

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (13,7844,'TURNER','SALESMAN',7698,to_date('08/SEP/81','DD/MON/RR'),1500,null,null)

1 row(s) inserted.

Statement 16

insert into external_emp (SEQ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (14,7900,'JAMES','CLERK',7698,to_date('03/DEC/81','DD/MON/RR'),950,null,null)

1 row(s) inserted.

Statement 17
```
commit
```
```
Statement processed.
```

Statement 18

select empno, ename, job, hiredate, sal , deptno
from external_emp 
order by seq

 
EMPNO ENAME JOB HIREDATE SAL DEPTNO
7782 CLARK MANAGER 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000  - 
7934 MILLER CLERK 23-JAN-82 1300  - 
7369 SMITH CLERK 17-DEC-80 800 20
7876 ADAMS CLERK 12-JAN-83 1100  - 
7902 FORD ANALYST 03-DEC-81 3000  - 
7566 JONES MANAGER 02-APR-81 2975  - 
7788 SCOTT ANALYST 09-DEC-82 3000  - 
7499 ALLEN SALESMAN 20-FEB-81 1600 30
7521 WARD SALESMAN 22-FEB-81 1250  - 
7654 MARTIN SALESMAN 28-SEP-81 1250  - 
7698 BLAKE MANAGER 01-MAY-81 2850  - 
7844 TURNER SALESMAN 08-SEP-81 1500  - 
7900 JAMES CLERK 03-DEC-81 950  - 

14 rows selected.

EMPNO	ENAME	JOB	HIREDATE	SAL	DEPTNO
7782	CLARK	MANAGER	09-JUN-81	2450	10
7839	KING	PRESIDENT	17-NOV-81	5000	-
7934	MILLER	CLERK	23-JAN-82	1300	-
7369	SMITH	CLERK	17-DEC-80	800	20
7876	ADAMS	CLERK	12-JAN-83	1100	-
7902	FORD	ANALYST	03-DEC-81	3000	-
7566	JONES	MANAGER	02-APR-81	2975	-
7788	SCOTT	ANALYST	09-DEC-82	3000	-
7499	ALLEN	SALESMAN	20-FEB-81	1600	30
7521	WARD	SALESMAN	22-FEB-81	1250	-
7654	MARTIN	SALESMAN	28-SEP-81	1250	-
7698	BLAKE	MANAGER	01-MAY-81	2850	-
7844	TURNER	SALESMAN	08-SEP-81	1500	-
7900	JAMES	CLERK	03-DEC-81	950	-

Statement 19

select empno, ename, job, hiredate, sal , 
       last_value(deptno) over (order by SEQ)  deptno
from external_emp 
order by seq

 
EMPNO ENAME JOB HIREDATE SAL DEPTNO
7782 CLARK MANAGER 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000  - 
7934 MILLER CLERK 23-JAN-82 1300  - 
7369 SMITH CLERK 17-DEC-80 800 20
7876 ADAMS CLERK 12-JAN-83 1100  - 
7902 FORD ANALYST 03-DEC-81 3000  - 
7566 JONES MANAGER 02-APR-81 2975  - 
7788 SCOTT ANALYST 09-DEC-82 3000  - 
7499 ALLEN SALESMAN 20-FEB-81 1600 30
7521 WARD SALESMAN 22-FEB-81 1250  - 
7654 MARTIN SALESMAN 28-SEP-81 1250  - 
7698 BLAKE MANAGER 01-MAY-81 2850  - 
7844 TURNER SALESMAN 08-SEP-81 1500  - 
7900 JAMES CLERK 03-DEC-81 950  - 

14 rows selected.

EMPNO	ENAME	JOB	HIREDATE	SAL	DEPTNO
7782	CLARK	MANAGER	09-JUN-81	2450	10
7839	KING	PRESIDENT	17-NOV-81	5000	-
7934	MILLER	CLERK	23-JAN-82	1300	-
7369	SMITH	CLERK	17-DEC-80	800	20
7876	ADAMS	CLERK	12-JAN-83	1100	-
7902	FORD	ANALYST	03-DEC-81	3000	-
7566	JONES	MANAGER	02-APR-81	2975	-
7788	SCOTT	ANALYST	09-DEC-82	3000	-
7499	ALLEN	SALESMAN	20-FEB-81	1600	30
7521	WARD	SALESMAN	22-FEB-81	1250	-
7654	MARTIN	SALESMAN	28-SEP-81	1250	-
7698	BLAKE	MANAGER	01-MAY-81	2850	-
7844	TURNER	SALESMAN	08-SEP-81	1500	-
7900	JAMES	CLERK	03-DEC-81	950	-

Statement 20

select empno, ename, job, hiredate, sal , 
  last_value(deptno IGNORE NULLS) over (order by SEQ)  deptno
from external_emp 
order by seq

 
EMPNO ENAME JOB HIREDATE SAL DEPTNO
7782 CLARK MANAGER 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 23-JAN-82 1300 10
7369 SMITH CLERK 17-DEC-80 800 20
7876 ADAMS CLERK 12-JAN-83 1100 20
7902 FORD ANALYST 03-DEC-81 3000 20
7566 JONES MANAGER 02-APR-81 2975 20
7788 SCOTT ANALYST 09-DEC-82 3000 20
7499 ALLEN SALESMAN 20-FEB-81 1600 30
7521 WARD SALESMAN 22-FEB-81 1250 30
7654 MARTIN SALESMAN 28-SEP-81 1250 30
7698 BLAKE MANAGER 01-MAY-81 2850 30
7844 TURNER SALESMAN 08-SEP-81 1500 30
7900 JAMES CLERK 03-DEC-81 950 30

14 rows selected.

EMPNO	ENAME	JOB	HIREDATE	SAL	DEPTNO
7782	CLARK	MANAGER	09-JUN-81	2450	10
7839	KING	PRESIDENT	17-NOV-81	5000	10
7934	MILLER	CLERK	23-JAN-82	1300	10
7369	SMITH	CLERK	17-DEC-80	800	20
7876	ADAMS	CLERK	12-JAN-83	1100	20
7902	FORD	ANALYST	03-DEC-81	3000	20
7566	JONES	MANAGER	02-APR-81	2975	20
7788	SCOTT	ANALYST	09-DEC-82	3000	20
7499	ALLEN	SALESMAN	20-FEB-81	1600	30
7521	WARD	SALESMAN	22-FEB-81	1250	30
7654	MARTIN	SALESMAN	28-SEP-81	1250	30
7698	BLAKE	MANAGER	01-MAY-81	2850	30
7844	TURNER	SALESMAN	08-SEP-81	1500	30
7900	JAMES	CLERK	03-DEC-81	950	30

Statement 21

select deptno, count(*)
from
( select
    last_value(deptno IGNORE NULLS) 
      over (order by SEQ)  deptno
  from external_emp
)
group by deptno
order by deptno

 
DEPTNO COUNT(*)
10 3
20 5
30 6

3 rows selected.

DEPTNO	COUNT(*)
10	3
20	5
30	6

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database