Analytics - More window clause examples

View All Scripts Login to Run Script

Script Name Analytics - More window clause examples
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script shows more window clause examples. 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 ( 
          partition by type
          order by square_km desc 
          rows between 1 preceding and 1 following
) as "3_ROW"
from water
order by decode(type,'Ocean',1,'Sea',2,3), square_km desc

 
NAME TYPE SQUARE_KM 3_ROW
Pacific Ocean Ocean 155557000 232319000
Atlantic Ocean Ocean 76762000 300875000
Indian Ocean Ocean 68556000 165645000
Southern Ocean Ocean 20327000 102939000
Arctic Ocean Ocean 14056000 34383000
Mediterranean Sea Sea 2965800 5684000
Caribbean Sea Sea 2718200 8003000
South China Sea Sea 2319000 7329100
Bering Sea Sea 2291900 6200600
Okhotsk Sea Sea 1589700 5130800
East China Sea Sea 1249200 3846700
Japan Sea Sea 1007800 3054700
Andaman Sea Sea 797700 2380700
North Sea Sea 575200 1810900
Red Sea Sea 438000 1435400
Baltic Sea Sea 422200 860200
Gulf of Mexico Gulf 1592800 1592800
Hudson Bay Bay 1232300 1232300

18 rows selected.

NAME	TYPE	SQUARE_KM	3_ROW
Pacific Ocean	Ocean	155557000	232319000
Atlantic Ocean	Ocean	76762000	300875000
Indian Ocean	Ocean	68556000	165645000
Southern Ocean	Ocean	20327000	102939000
Arctic Ocean	Ocean	14056000	34383000
Mediterranean Sea	Sea	2965800	5684000
Caribbean Sea	Sea	2718200	8003000
South China Sea	Sea	2319000	7329100
Bering Sea	Sea	2291900	6200600
Okhotsk Sea	Sea	1589700	5130800
East China Sea	Sea	1249200	3846700
Japan Sea	Sea	1007800	3054700
Andaman Sea	Sea	797700	2380700
North Sea	Sea	575200	1810900
Red Sea	Sea	438000	1435400
Baltic Sea	Sea	422200	860200
Gulf of Mexico	Gulf	1592800	1592800
Hudson Bay	Bay	1232300	1232300

Additional Information

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