drop table water purge
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table water ( name varchar2(30) primary key,
type varchar2(10),
square_km int )
Table created.
insert into water(name,type,square_km) values ('Pacific Ocean','Ocean',155557000)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Atlantic Ocean','Ocean',76762000)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Indian Ocean','Ocean',68556000)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Southern Ocean','Ocean',20327000)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Arctic Ocean','Ocean',14056000)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Mediterranean Sea','Sea',2965800)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Caribbean Sea','Sea',2718200)
1 row(s) inserted.
insert into water(name,type,square_km) values ('South China Sea','Sea',2319000)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Bering Sea','Sea',2291900)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Gulf of Mexico','Gulf',1592800)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Okhotsk Sea','Sea',1589700)
1 row(s) inserted.
insert into water(name,type,square_km) values ('East China Sea','Sea',1249200)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Hudson Bay','Bay',1232300)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Japan Sea','Sea',1007800)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Andaman Sea','Sea',797700)
1 row(s) inserted.
insert into water(name,type,square_km) values ('North Sea','Sea',575200)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Red Sea','Sea',438000)
1 row(s) inserted.
insert into water(name,type,square_km) values ('Baltic Sea','Sea',422200)
1 row(s) inserted.
commit
Statement processed.
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 |
---|
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 |
---|