drop table planets cascade constraints purge
create table planets ( name varchar2(20) primary key )
insert into planets values ('Mercury')
insert into planets values ('Venus')
insert into planets values ('Earth')
insert into planets values ('Eris')
insert into planets values ('Haumea')
insert into planets values ('Jupiter')
insert into planets values ('Mars')
insert into planets values ('Neptune')
insert into planets values ('Pluto')
insert into planets values ('Saturn')
insert into planets values ('Uranus')
drop table moons purge
create table moons ( planet_name varchar2(20) references planets ( name ), name varchar2(20) primary key , radius number(12,2) )
insert into moons values ('','',)
insert into moons values ('Earth','Moon',1737.1)
insert into moons values ('Eris','Dysnomia',342)
insert into moons values ('Haumea','Hi?iaka',195)
insert into moons values ('Haumea','Namaka',100)
insert into moons values ('Jupiter','Adrastea',8.2 )
insert into moons values ('Jupiter','Aitne',1.5)
insert into moons values ('Jupiter','Amalthea',83.45 )
insert into moons values ('Jupiter','Ananke',14)
insert into moons values ('Jupiter','Aoede',2)
insert into moons values ('Jupiter','Arche',1.5)
insert into moons values ('Jupiter','Autonoe',2)
insert into moons values ('Jupiter','Callirrhoe',4.3)
insert into moons values ('Jupiter','Callisto',2408.4 )
insert into moons values ('Jupiter','Carme',23)
insert into moons values ('Jupiter','Carpo',1.5)
insert into moons values ('Jupiter','Chaldene',1.9)
insert into moons values ('Jupiter','Cyllene',1)
insert into moons values ('Jupiter','Dia',2)
insert into moons values ('Jupiter','Elara',43)
insert into moons values ('Jupiter','Erinome',1.6)
insert into moons values ('Jupiter','Euanthe',1.5)
insert into moons values ('Jupiter','Eukelade',2)
insert into moons values ('Jupiter','Euporie',1)
insert into moons values ('Jupiter','Europa',1560.7 )
insert into moons values ('Jupiter','Eurydome',1.5)
insert into moons values ('Jupiter','Ganymede',2634.1 )
insert into moons values ('Jupiter','Harpalyke',2.2)
insert into moons values ('Jupiter','Hegemone',1.5)
insert into moons values ('Jupiter','Helike',2)
insert into moons values ('Jupiter','Hermippe',2)
insert into moons values ('Jupiter','Herse',1)
insert into moons values ('Jupiter','Himalia',67 )
insert into moons values ('Jupiter','Io',1818.1 )
insert into moons values ('Jupiter','Iocaste',2.6)
insert into moons values ('Jupiter','Isonoe',1.9)
insert into moons values ('Jupiter','Kale',1)
insert into moons values ('Jupiter','Kallichore',1)
insert into moons values ('Jupiter','Kalyke',2.6)
insert into moons values ('Jupiter','Kore',1)
insert into moons values ('Jupiter','Leda',10)
insert into moons values ('Jupiter','Lysithea',18)
insert into moons values ('Jupiter','Megaclite',2.7)
insert into moons values ('Jupiter','Metis',21.5 )
insert into moons values ('Jupiter','Mneme',1)
insert into moons values ('Jupiter','Orthosie',1)
insert into moons values ('Jupiter','Pasiphae',30)
insert into moons values ('Jupiter','Pasithee',1)
insert into moons values ('Jupiter','Praxidike',3.4)
insert into moons values ('Jupiter','S/2003 J 10',1)
insert into moons values ('Jupiter','S/2003 J 12',0.5)
insert into moons values ('Jupiter','S/2003 J 15',1)
insert into moons values ('Jupiter','S/2003 J 16',1)
insert into moons values ('Jupiter','S/2003 J 18',1)
insert into moons values ('Jupiter','S/2003 J 19',1)
insert into moons values ('Jupiter','S/2003 J 2',1)
insert into moons values ('Jupiter','S/2003 J 23',1)
insert into moons values ('Jupiter','S/2003 J 3',1)
insert into moons values ('Jupiter','S/2003 J 4',1)
insert into moons values ('Jupiter','S/2003 J 5',2)
insert into moons values ('Jupiter','S/2003 J 9',0.5)
insert into moons values ('Jupiter','S/2010 J 1',1)
insert into moons values ('Jupiter','S/2010 J 2',0.5)
insert into moons values ('Jupiter','S/2011 J 1',0.5)
insert into moons values ('Jupiter','S/2011 J 2',0.5)
insert into moons values ('Jupiter','Sinope',19)
insert into moons values ('Jupiter','Sponde',1)
insert into moons values ('Jupiter','Taygete',2.5)
insert into moons values ('Jupiter','Thebe',49.3 )
insert into moons values ('Jupiter','Thelxinoe',1)
insert into moons values ('Jupiter','Themisto',4)
insert into moons values ('Jupiter','Thyone',2)
insert into moons values ('Mars','Deimos',6.2 )
insert into moons values ('Mars','Phobos',11.1 )
insert into moons values ('Neptune','Despina',75 )
insert into moons values ('Neptune','Galatea',88 )
insert into moons values ('Neptune','Halimede',31)
insert into moons values ('Neptune','Laomedeia',21)
insert into moons values ('Neptune','Larissa',97 )
insert into moons values ('Neptune','Naiad',33 )
insert into moons values ('Neptune','Nereid',170 )
insert into moons values ('Neptune','Neso',30)
insert into moons values ('Neptune','Proteus',210 )
insert into moons values ('Neptune','Psamathe',20)
insert into moons values ('Neptune','S/2004 N 1',8)
insert into moons values ('Neptune','Sao',22)
insert into moons values ('Neptune','Thalassa',41 )
insert into moons values ('Neptune','Triton',1353.4 )
insert into moons values ('Pluto','Charon',603.6 )
insert into moons values ('Pluto','Hydra',30.5)
insert into moons values ('Pluto','Kerberos',6.5)
insert into moons values ('Pluto','Nix',23)
insert into moons values ('Pluto','Styx',5)
insert into moons values ('Saturn','Aegaeon',0.25)
insert into moons values ('Saturn','Aegir',3)
insert into moons values ('Saturn','Albiorix',16)
insert into moons values ('Saturn','Anthe',1)
insert into moons values ('Saturn','Atlas',15.3 )
insert into moons values ('Saturn','Bebhionn',3)
insert into moons values ('Saturn','Bergelmir',3)
insert into moons values ('Saturn','Bestla',3.5)
insert into moons values ('Saturn','Calypso',9.5 )
insert into moons values ('Saturn','Daphnis',3)
insert into moons values ('Saturn','Dione',562.5 )
insert into moons values ('Saturn','Enceladus',252.3 )
insert into moons values ('Saturn','Epimetheus',58.3 )
insert into moons values ('Saturn','Erriapus',5)
insert into moons values ('Saturn','Farbauti',2.5)
insert into moons values ('Saturn','Fenrir',2)
insert into moons values ('Saturn','Fornjot',3)
insert into moons values ('Saturn','Greip',3)
insert into moons values ('Saturn','Hati',3)
insert into moons values ('Saturn','Helene',16 )
insert into moons values ('Saturn','Hyperion',133.0 )
insert into moons values ('Saturn','Hyrrokkin',4)
insert into moons values ('Saturn','Iapetus',734.5 )
insert into moons values ('Saturn','Ijiraq',6)
insert into moons values ('Saturn','Janus',90.4 )
insert into moons values ('Saturn','Jarnsaxa',3)
insert into moons values ('Saturn','Kari',3.5)
insert into moons values ('Saturn','Kiviuq',8)
insert into moons values ('Saturn','Loge',3)
insert into moons values ('Saturn','Methone',1.6)
insert into moons values ('Saturn','Mimas',198.2 )
insert into moons values ('Saturn','Mundilfari',3.5)
insert into moons values ('Saturn','Narvi',3.5)
insert into moons values ('Saturn','Paaliaq',11)
insert into moons values ('Saturn','Pallene',2)
insert into moons values ('Saturn','Pan',12.8)
insert into moons values ('Saturn','Pandora',40.6 )
insert into moons values ('Saturn','Phoebe',106.6 )
insert into moons values ('Saturn','Polydeuces',1.25)
insert into moons values ('Saturn','Prometheus',46.8 )
insert into moons values ('Saturn','Rhea',764.5 )
insert into moons values ('Saturn','S/2004 S 12',2.5)
insert into moons values ('Saturn','S/2004 S 13',3)
insert into moons values ('Saturn','S/2004 S 17',2)
insert into moons values ('Saturn','S/2004 S 7',3)
insert into moons values ('Saturn','S/2006 S 1',3)
insert into moons values ('Saturn','S/2006 S 3',3)
insert into moons values ('Saturn','S/2007 S 2',3)
insert into moons values ('Saturn','S/2007 S 3',3)
insert into moons values ('Saturn','S/2009 S 1',0.15)
insert into moons values ('Saturn','Siarnaq',20)
insert into moons values ('Saturn','Skathi',4)
insert into moons values ('Saturn','Skoll',3)
insert into moons values ('Saturn','Surtur',3)
insert into moons values ('Saturn','Suttungr',3.5)
insert into moons values ('Saturn','Tarqeq',3.5)
insert into moons values ('Saturn','Tarvos',7.5)
insert into moons values ('Saturn','Telesto',12 )
insert into moons values ('Saturn','Tethys',536.3 )
insert into moons values ('Saturn','Thrymr',3.5)
insert into moons values ('Saturn','Titan',2575.5 )
insert into moons values ('Saturn','Ymir',9)
insert into moons values ('Uranus','Ariel',578.9 )
insert into moons values ('Uranus','Belinda',40.3 )
insert into moons values ('Uranus','Bianca',25.7 )
insert into moons values ('Uranus','Caliban',49)
insert into moons values ('Uranus','Cordelia',20.1 )
insert into moons values ('Uranus','Cressida',39.8 )
insert into moons values ('Uranus','Cupid',5)
insert into moons values ('Uranus','Desdemona',32.0 )
insert into moons values ('Uranus','Ferdinand',6)
insert into moons values ('Uranus','Francisco',6)
insert into moons values ('Uranus','Juliet',46.8 )
insert into moons values ('Uranus','Mab',5)
insert into moons values ('Uranus','Margaret',5.5)
insert into moons values ('Uranus','Miranda',235.8 )
insert into moons values ('Uranus','Oberon',761.4 )
insert into moons values ('Uranus','Ophelia',21.4 )
insert into moons values ('Uranus','Perdita',10)
insert into moons values ('Uranus','Portia',67.6 )
insert into moons values ('Uranus','Prospero',15)
insert into moons values ('Uranus','Puck',81 )
insert into moons values ('Uranus','Rosalind',36 )
insert into moons values ('Uranus','Setebos',15)
insert into moons values ('Uranus','Stephano',10)
insert into moons values ('Uranus','Sycorax',75)
insert into moons values ('Uranus','Titania',788.9 )
insert into moons values ('Uranus','Trinculo',5)
insert into moons values ('Uranus','Umbriel',584.7 )
commit
select p.name planet, m.name, m.radius,
row_number() over ( order by radius desc ) as size_rank
from planets p, moons m
where p.name = m.planet_name(+)
and p.name in ('Mars','Venus','Pluto','Mercury','Neptune')
order by size_rank
PLANET | NAME | RADIUS | SIZE_RANK | Venus | - | - | 1 | Mercury | - | - | 2 | Neptune | Triton | 1353.4 | 3 | Pluto | Charon | 603.6 | 4 | Neptune | Proteus | 210 | 5 | Neptune | Nereid | 170 | 6 | Neptune | Larissa | 97 | 7 | Neptune | Galatea | 88 | 8 | Neptune | Despina | 75 | 9 | Neptune | Thalassa | 41 | 10 | Neptune | Naiad | 33 | 11 | Neptune | Halimede | 31 | 12 | Pluto | Hydra | 30.5 | 13 | Neptune | Neso | 30 | 14 | Pluto | Nix | 23 | 15 | Neptune | Sao | 22 | 16 | Neptune | Laomedeia | 21 | 17 | Neptune | Psamathe | 20 | 18 | Mars | Phobos | 11.1 | 19 | Neptune | S/2004 N 1 | 8 | 20 | Pluto | Kerberos | 6.5 | 21 | Mars | Deimos | 6.2 | 22 | Pluto | Styx | 5 | 23 |
---|
select p.name planet, m.name, m.radius,
row_number() over ( order by radius desc NULLS LAST ) as size_rank
from planets p, moons m
where p.name = m.planet_name(+)
and p.name in ('Mars','Venus','Pluto','Mercury','Neptune')
PLANET | NAME | RADIUS | SIZE_RANK | Neptune | Triton | 1353.4 | 1 | Pluto | Charon | 603.6 | 2 | Neptune | Proteus | 210 | 3 | Neptune | Nereid | 170 | 4 | Neptune | Larissa | 97 | 5 | Neptune | Galatea | 88 | 6 | Neptune | Despina | 75 | 7 | Neptune | Thalassa | 41 | 8 | Neptune | Naiad | 33 | 9 | Neptune | Halimede | 31 | 10 | Pluto | Hydra | 30.5 | 11 | Neptune | Neso | 30 | 12 | Pluto | Nix | 23 | 13 | Neptune | Sao | 22 | 14 | Neptune | Laomedeia | 21 | 15 | Neptune | Psamathe | 20 | 16 | Mars | Phobos | 11.1 | 17 | Neptune | S/2004 N 1 | 8 | 18 | Pluto | Kerberos | 6.5 | 19 | Mars | Deimos | 6.2 | 20 | Pluto | Styx | 5 | 21 | Mercury | - | - | 22 | Venus | - | - | 23 |
---|
select p.name planet, m.name, m.radius,
rank() over ( order by radius desc ) as size_rank
from planets p, moons m
where p.name = m.planet_name(+)
and p.name in ('Mars','Venus','Pluto','Mercury','Neptune')
order by size_rank
PLANET | NAME | RADIUS | SIZE_RANK | Venus | - | - | 1 | Mercury | - | - | 1 | Neptune | Triton | 1353.4 | 3 | Pluto | Charon | 603.6 | 4 | Neptune | Proteus | 210 | 5 | Neptune | Nereid | 170 | 6 | Neptune | Larissa | 97 | 7 | Neptune | Galatea | 88 | 8 | Neptune | Despina | 75 | 9 | Neptune | Thalassa | 41 | 10 | Neptune | Naiad | 33 | 11 | Neptune | Halimede | 31 | 12 | Pluto | Hydra | 30.5 | 13 | Neptune | Neso | 30 | 14 | Pluto | Nix | 23 | 15 | Neptune | Sao | 22 | 16 | Neptune | Laomedeia | 21 | 17 | Neptune | Psamathe | 20 | 18 | Mars | Phobos | 11.1 | 19 | Neptune | S/2004 N 1 | 8 | 20 | Pluto | Kerberos | 6.5 | 21 | Mars | Deimos | 6.2 | 22 | Pluto | Styx | 5 | 23 |
---|