Analytics - First look at Window functions

View All Scripts Login to Run Script

Script Name Analytics - First look at Window functions
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script introduces 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 Monday January 18, 2016

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

Statement 2

create table water ( name varchar2(30) primary key,
                     type varchar2(10),
                     square_km int )

Table created.

Statement 3

insert into water(name,type,square_km) values ('Pacific Ocean','Ocean',155557000)

1 row(s) inserted.

Statement 4

insert into water(name,type,square_km) values ('Atlantic Ocean','Ocean',76762000)

1 row(s) inserted.

Statement 5

insert into water(name,type,square_km) values ('Indian Ocean','Ocean',68556000)

1 row(s) inserted.

Statement 6

insert into water(name,type,square_km) values ('Southern Ocean','Ocean',20327000)

1 row(s) inserted.

Statement 7

insert into water(name,type,square_km) values ('Arctic Ocean','Ocean',14056000)

1 row(s) inserted.

Statement 8

insert into water(name,type,square_km) values ('Mediterranean Sea','Sea',2965800)

1 row(s) inserted.

Statement 9

insert into water(name,type,square_km) values ('Caribbean Sea','Sea',2718200)

1 row(s) inserted.

Statement 10

insert into water(name,type,square_km) values ('South China Sea','Sea',2319000)

1 row(s) inserted.

Statement 11

insert into water(name,type,square_km) values ('Bering Sea','Sea',2291900)

1 row(s) inserted.

Statement 12

insert into water(name,type,square_km) values ('Gulf of Mexico','Gulf',1592800)

1 row(s) inserted.

Statement 13

insert into water(name,type,square_km) values ('Okhotsk Sea','Sea',1589700)

1 row(s) inserted.

Statement 14

insert into water(name,type,square_km) values ('East China Sea','Sea',1249200)

1 row(s) inserted.

Statement 15

insert into water(name,type,square_km) values ('Hudson Bay','Bay',1232300)

1 row(s) inserted.

Statement 16

insert into water(name,type,square_km) values ('Japan Sea','Sea',1007800)

1 row(s) inserted.

Statement 17

insert into water(name,type,square_km) values ('Andaman Sea','Sea',797700)

1 row(s) inserted.

Statement 18

insert into water(name,type,square_km) values ('North Sea','Sea',575200)

1 row(s) inserted.

Statement 19

insert into water(name,type,square_km) values ('Red Sea','Sea',438000)

1 row(s) inserted.

Statement 20

insert into water(name,type,square_km) values ('Baltic Sea','Sea',422200)

1 row(s) inserted.

Statement 21
```
commit
```
```
Statement processed.
```

Statement 22

select * 
from water
order by 3 desc

 
NAME TYPE SQUARE_KM
Pacific Ocean Ocean 155557000
Atlantic Ocean Ocean 76762000
Indian Ocean Ocean 68556000
Southern Ocean Ocean 20327000
Arctic Ocean Ocean 14056000
Mediterranean Sea Sea 2965800
Caribbean Sea Sea 2718200
South China Sea Sea 2319000
Bering Sea Sea 2291900
Gulf of Mexico Gulf 1592800
Okhotsk Sea Sea 1589700
East China Sea Sea 1249200
Hudson Bay Bay 1232300
Japan Sea Sea 1007800
Andaman Sea Sea 797700
North Sea Sea 575200
Red Sea Sea 438000
Baltic Sea Sea 422200

18 rows selected.

NAME	TYPE	SQUARE_KM
Pacific Ocean	Ocean	155557000
Atlantic Ocean	Ocean	76762000
Indian Ocean	Ocean	68556000
Southern Ocean	Ocean	20327000
Arctic Ocean	Ocean	14056000
Mediterranean Sea	Sea	2965800
Caribbean Sea	Sea	2718200
South China Sea	Sea	2319000
Bering Sea	Sea	2291900
Gulf of Mexico	Gulf	1592800
Okhotsk Sea	Sea	1589700
East China Sea	Sea	1249200
Hudson Bay	Bay	1232300
Japan Sea	Sea	1007800
Andaman Sea	Sea	797700
North Sea	Sea	575200
Red Sea	Sea	438000
Baltic Sea	Sea	422200

Statement 23

select name, type, square_km,
       sum(square_km) over ( order by square_km desc 
          rows between unbounded preceding and current row
) as tot
from water
order by 3 desc

 
NAME TYPE SQUARE_KM TOT
Pacific Ocean Ocean 155557000 155557000
Atlantic Ocean Ocean 76762000 232319000
Indian Ocean Ocean 68556000 300875000
Southern Ocean Ocean 20327000 321202000
Arctic Ocean Ocean 14056000 335258000
Mediterranean Sea Sea 2965800 338223800
Caribbean Sea Sea 2718200 340942000
South China Sea Sea 2319000 343261000
Bering Sea Sea 2291900 345552900
Gulf of Mexico Gulf 1592800 347145700
Okhotsk Sea Sea 1589700 348735400
East China Sea Sea 1249200 349984600
Hudson Bay Bay 1232300 351216900
Japan Sea Sea 1007800 352224700
Andaman Sea Sea 797700 353022400
North Sea Sea 575200 353597600
Red Sea Sea 438000 354035600
Baltic Sea Sea 422200 354457800

18 rows selected.

NAME	TYPE	SQUARE_KM	TOT
Pacific Ocean	Ocean	155557000	155557000
Atlantic Ocean	Ocean	76762000	232319000
Indian Ocean	Ocean	68556000	300875000
Southern Ocean	Ocean	20327000	321202000
Arctic Ocean	Ocean	14056000	335258000
Mediterranean Sea	Sea	2965800	338223800
Caribbean Sea	Sea	2718200	340942000
South China Sea	Sea	2319000	343261000
Bering Sea	Sea	2291900	345552900
Gulf of Mexico	Gulf	1592800	347145700
Okhotsk Sea	Sea	1589700	348735400
East China Sea	Sea	1249200	349984600
Hudson Bay	Bay	1232300	351216900
Japan Sea	Sea	1007800	352224700
Andaman Sea	Sea	797700	353022400
North Sea	Sea	575200	353597600
Red Sea	Sea	438000	354035600
Baltic Sea	Sea	422200	354457800

Additional Information

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