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