Oracle Live SQL - Script: Analytics - LISTAGG with the OVER clause

Statement 1

drop table planets cascade constraints purge

ORA-00942: table or view does not exist

Statement 2

create table planets ( name varchar2(20) primary key, class varchar2(20) )

Table created.

Statement 3

insert into planets values ('Mercury','Inner')

1 row(s) inserted.

Statement 4

insert into planets values ('Venus','Inner')

1 row(s) inserted.

Statement 5

insert into planets values ('Earth','Earth')

1 row(s) inserted.

Statement 6

insert into planets values ('Eris','Dwarf')

1 row(s) inserted.

Statement 7

insert into planets values ('Haumea','Dwarf')

1 row(s) inserted.

Statement 8

insert into planets values ('Jupiter','Superior')

1 row(s) inserted.

Statement 9

insert into planets values ('Mars','Superior')

1 row(s) inserted.

Statement 10

insert into planets values ('Neptune','Superior')

1 row(s) inserted.

Statement 11

insert into planets values ('Pluto','Superior')

1 row(s) inserted.

Statement 12

insert into planets values ('Saturn','Superior')

1 row(s) inserted.

Statement 13

insert into planets values ('Uranus','Superior')

1 row(s) inserted.

Statement 14

drop table moons purge

ORA-00942: table or view does not exist

Statement 15

create table moons ( planet_name varchar2(20) references planets ( name ), name varchar2(20) primary key , radius number(12,2) )

Table created.

Statement 16

insert into moons values ('','',)

ORA-00936: missing expression

Statement 17

insert into moons values ('Earth','Moon',1737.1)

1 row(s) inserted.

Statement 18

insert into moons values ('Eris','Dysnomia',342)

1 row(s) inserted.

Statement 19

insert into moons values ('Haumea','Hiiaka',195)

1 row(s) inserted.

Statement 20

insert into moons values ('Haumea','Namaka',100)

1 row(s) inserted.

Statement 21

insert into moons values ('Jupiter','Adrastea',8.2 )

1 row(s) inserted.

Statement 22

insert into moons values ('Jupiter','Aitne',1.5)

1 row(s) inserted.

Statement 23

insert into moons values ('Jupiter','Amalthea',83.45 )

1 row(s) inserted.

Statement 24

insert into moons values ('Jupiter','Ananke',14)

1 row(s) inserted.

Statement 25

insert into moons values ('Jupiter','Aoede',2)

1 row(s) inserted.

Statement 26

insert into moons values ('Jupiter','Arche',1.5)

1 row(s) inserted.

Statement 27

insert into moons values ('Jupiter','Autonoe',2)

1 row(s) inserted.

Statement 28

insert into moons values ('Jupiter','Callirrhoe',4.3)

1 row(s) inserted.

Statement 29

insert into moons values ('Jupiter','Callisto',2408.4 )

1 row(s) inserted.

Statement 30

insert into moons values ('Jupiter','Carme',23)

1 row(s) inserted.

Statement 31

insert into moons values ('Jupiter','Carpo',1.5)

1 row(s) inserted.

Statement 32

insert into moons values ('Jupiter','Chaldene',1.9)

1 row(s) inserted.

Statement 33

insert into moons values ('Jupiter','Cyllene',1)

1 row(s) inserted.

Statement 34

insert into moons values ('Jupiter','Dia',2)

1 row(s) inserted.

Statement 35

insert into moons values ('Jupiter','Elara',43)

1 row(s) inserted.

Statement 36

insert into moons values ('Jupiter','Erinome',1.6)

1 row(s) inserted.

Statement 37

insert into moons values ('Jupiter','Euanthe',1.5)

1 row(s) inserted.

Statement 38

insert into moons values ('Jupiter','Eukelade',2)

1 row(s) inserted.

Statement 39

insert into moons values ('Jupiter','Euporie',1)

1 row(s) inserted.

Statement 40

insert into moons values ('Jupiter','Europa',1560.7 )

1 row(s) inserted.

Statement 41

insert into moons values ('Jupiter','Eurydome',1.5)

1 row(s) inserted.

Statement 42

insert into moons values ('Jupiter','Ganymede',2634.1 )

1 row(s) inserted.

Statement 43

insert into moons values ('Jupiter','Harpalyke',2.2)

1 row(s) inserted.

Statement 44

insert into moons values ('Jupiter','Hegemone',1.5)

1 row(s) inserted.

Statement 45

insert into moons values ('Jupiter','Helike',2)

1 row(s) inserted.

Statement 46

insert into moons values ('Jupiter','Hermippe',2)

1 row(s) inserted.

Statement 47

insert into moons values ('Jupiter','Herse',1)

1 row(s) inserted.

Statement 48

insert into moons values ('Jupiter','Himalia',67 )

1 row(s) inserted.

Statement 49

insert into moons values ('Jupiter','Io',1818.1 )

1 row(s) inserted.

Statement 50

insert into moons values ('Jupiter','Iocaste',2.6)

1 row(s) inserted.

Statement 51

insert into moons values ('Jupiter','Isonoe',1.9)

1 row(s) inserted.

Statement 52

insert into moons values ('Jupiter','Kale',1)

1 row(s) inserted.

Statement 53

insert into moons values ('Jupiter','Kallichore',1)

1 row(s) inserted.

Statement 54

insert into moons values ('Jupiter','Kalyke',2.6)

1 row(s) inserted.

Statement 55

insert into moons values ('Jupiter','Kore',1)

1 row(s) inserted.

Statement 56

insert into moons values ('Jupiter','Leda',10)

1 row(s) inserted.

Statement 57

insert into moons values ('Jupiter','Lysithea',18)

1 row(s) inserted.

Statement 58

insert into moons values ('Jupiter','Megaclite',2.7)

1 row(s) inserted.

Statement 59

insert into moons values ('Jupiter','Metis',21.5 )

1 row(s) inserted.

Statement 60

insert into moons values ('Jupiter','Mneme',1)

1 row(s) inserted.

Statement 61

insert into moons values ('Jupiter','Orthosie',1)

1 row(s) inserted.

Statement 62

insert into moons values ('Jupiter','Pasiphae',30)

1 row(s) inserted.

Statement 63

insert into moons values ('Jupiter','Pasithee',1)

1 row(s) inserted.

Statement 64

insert into moons values ('Jupiter','Praxidike',3.4)

1 row(s) inserted.

Statement 65

insert into moons values ('Jupiter','S/2003 J 10',1)

1 row(s) inserted.

Statement 66

insert into moons values ('Jupiter','S/2003 J 12',0.5)

1 row(s) inserted.

Statement 67

insert into moons values ('Jupiter','S/2003 J 15',1)

1 row(s) inserted.

Statement 68

insert into moons values ('Jupiter','S/2003 J 16',1)

1 row(s) inserted.

Statement 69

insert into moons values ('Jupiter','S/2003 J 18',1)

1 row(s) inserted.

Statement 70

insert into moons values ('Jupiter','S/2003 J 19',1)

1 row(s) inserted.

Statement 71

insert into moons values ('Jupiter','S/2003 J 2',1)

1 row(s) inserted.

Statement 72

insert into moons values ('Jupiter','S/2003 J 23',1)

1 row(s) inserted.

Statement 73

insert into moons values ('Jupiter','S/2003 J 3',1)

1 row(s) inserted.

Statement 74

insert into moons values ('Jupiter','S/2003 J 4',1)

1 row(s) inserted.

Statement 75

insert into moons values ('Jupiter','S/2003 J 5',2)

1 row(s) inserted.

Statement 76

insert into moons values ('Jupiter','S/2003 J 9',0.5)

1 row(s) inserted.

Statement 77

insert into moons values ('Jupiter','S/2010 J 1',1)

1 row(s) inserted.

Statement 78

insert into moons values ('Jupiter','S/2010 J 2',0.5)

1 row(s) inserted.

Statement 79

insert into moons values ('Jupiter','S/2011 J 1',0.5)

1 row(s) inserted.

Statement 80

insert into moons values ('Jupiter','S/2011 J 2',0.5)

1 row(s) inserted.

Statement 81

insert into moons values ('Jupiter','Sinope',19)

1 row(s) inserted.

Statement 82

insert into moons values ('Jupiter','Sponde',1)

1 row(s) inserted.

Statement 83

insert into moons values ('Jupiter','Taygete',2.5)

1 row(s) inserted.

Statement 84

insert into moons values ('Jupiter','Thebe',49.3 )

1 row(s) inserted.

Statement 85

insert into moons values ('Jupiter','Thelxinoe',1)

1 row(s) inserted.

Statement 86

insert into moons values ('Jupiter','Themisto',4)

1 row(s) inserted.

Statement 87

insert into moons values ('Jupiter','Thyone',2)

1 row(s) inserted.

Statement 88

insert into moons values ('Mars','Deimos',6.2 )

1 row(s) inserted.

Statement 89

insert into moons values ('Mars','Phobos',11.1 )

1 row(s) inserted.

Statement 90

insert into moons values ('Neptune','Despina',75 )

1 row(s) inserted.

Statement 91

insert into moons values ('Neptune','Galatea',88 )

1 row(s) inserted.

Statement 92

insert into moons values ('Neptune','Halimede',31)

1 row(s) inserted.

Statement 93

insert into moons values ('Neptune','Laomedeia',21)

1 row(s) inserted.

Statement 94

insert into moons values ('Neptune','Larissa',97 )

1 row(s) inserted.

Statement 95

insert into moons values ('Neptune','Naiad',33 )

1 row(s) inserted.

Statement 96

insert into moons values ('Neptune','Nereid',170 )

1 row(s) inserted.

Statement 97

insert into moons values ('Neptune','Neso',30)

1 row(s) inserted.

Statement 98

insert into moons values ('Neptune','Proteus',210 )

1 row(s) inserted.

Statement 99

insert into moons values ('Neptune','Psamathe',20)

1 row(s) inserted.

Statement 100

insert into moons values ('Neptune','S/2004 N 1',8)

1 row(s) inserted.

Statement 101

insert into moons values ('Neptune','Sao',22)

1 row(s) inserted.

Statement 102

insert into moons values ('Neptune','Thalassa',41 )

1 row(s) inserted.

Statement 103

insert into moons values ('Neptune','Triton',1353.4 )

1 row(s) inserted.

Statement 104

insert into moons values ('Pluto','Charon',603.6 )

1 row(s) inserted.

Statement 105

insert into moons values ('Pluto','Hydra',30.5)

1 row(s) inserted.

Statement 106

insert into moons values ('Pluto','Kerberos',6.5)

1 row(s) inserted.

Statement 107

insert into moons values ('Pluto','Nix',23)

1 row(s) inserted.

Statement 108

insert into moons values ('Pluto','Styx',5)

1 row(s) inserted.

Statement 109

insert into moons values ('Saturn','Aegaeon',0.25)

1 row(s) inserted.

Statement 110

insert into moons values ('Saturn','Aegir',3)

1 row(s) inserted.

Statement 111

insert into moons values ('Saturn','Albiorix',16)

1 row(s) inserted.

Statement 112

insert into moons values ('Saturn','Anthe',1)

1 row(s) inserted.

Statement 113

insert into moons values ('Saturn','Atlas',15.3 )

1 row(s) inserted.

Statement 114

insert into moons values ('Saturn','Bebhionn',3)

1 row(s) inserted.

Statement 115

insert into moons values ('Saturn','Bergelmir',3)

1 row(s) inserted.

Statement 116

insert into moons values ('Saturn','Bestla',3.5)

1 row(s) inserted.

Statement 117

insert into moons values ('Saturn','Calypso',9.5 )

1 row(s) inserted.

Statement 118

insert into moons values ('Saturn','Daphnis',3)

1 row(s) inserted.

Statement 119

insert into moons values ('Saturn','Dione',562.5 )

1 row(s) inserted.

Statement 120

insert into moons values ('Saturn','Enceladus',252.3 )

1 row(s) inserted.

Statement 121

insert into moons values ('Saturn','Epimetheus',58.3 )

1 row(s) inserted.

Statement 122

insert into moons values ('Saturn','Erriapus',5)

1 row(s) inserted.

Statement 123

insert into moons values ('Saturn','Farbauti',2.5)

1 row(s) inserted.

Statement 124

insert into moons values ('Saturn','Fenrir',2)

1 row(s) inserted.

Statement 125

insert into moons values ('Saturn','Fornjot',3)

1 row(s) inserted.

Statement 126

insert into moons values ('Saturn','Greip',3)

1 row(s) inserted.

Statement 127

insert into moons values ('Saturn','Hati',3)

1 row(s) inserted.

Statement 128

insert into moons values ('Saturn','Helene',16 )

1 row(s) inserted.

Statement 129

insert into moons values ('Saturn','Hyperion',133.0 )

1 row(s) inserted.

Statement 130

insert into moons values ('Saturn','Hyrrokkin',4)

1 row(s) inserted.

Statement 131

insert into moons values ('Saturn','Iapetus',734.5 )

1 row(s) inserted.

Statement 132

insert into moons values ('Saturn','Ijiraq',6)

1 row(s) inserted.

Statement 133

insert into moons values ('Saturn','Janus',90.4 )

1 row(s) inserted.

Statement 134

insert into moons values ('Saturn','Jarnsaxa',3)

1 row(s) inserted.

Statement 135

insert into moons values ('Saturn','Kari',3.5)

1 row(s) inserted.

Statement 136

insert into moons values ('Saturn','Kiviuq',8)

1 row(s) inserted.

Statement 137

insert into moons values ('Saturn','Loge',3)

1 row(s) inserted.

Statement 138

insert into moons values ('Saturn','Methone',1.6)

1 row(s) inserted.

Statement 139

insert into moons values ('Saturn','Mimas',198.2 )

1 row(s) inserted.

Statement 140

insert into moons values ('Saturn','Mundilfari',3.5)

1 row(s) inserted.

Statement 141

insert into moons values ('Saturn','Narvi',3.5)

1 row(s) inserted.

Statement 142

insert into moons values ('Saturn','Paaliaq',11)

1 row(s) inserted.

Statement 143

insert into moons values ('Saturn','Pallene',2)

1 row(s) inserted.

Statement 144

insert into moons values ('Saturn','Pan',12.8)

1 row(s) inserted.

Statement 145

insert into moons values ('Saturn','Pandora',40.6 )

1 row(s) inserted.

Statement 146

insert into moons values ('Saturn','Phoebe',106.6 )

1 row(s) inserted.

Statement 147

insert into moons values ('Saturn','Polydeuces',1.25)

1 row(s) inserted.

Statement 148

insert into moons values ('Saturn','Prometheus',46.8 )

1 row(s) inserted.

Statement 149

insert into moons values ('Saturn','Rhea',764.5 )

1 row(s) inserted.

Statement 150

insert into moons values ('Saturn','S/2004 S 12',2.5)

1 row(s) inserted.

Statement 151

insert into moons values ('Saturn','S/2004 S 13',3)

1 row(s) inserted.

Statement 152

insert into moons values ('Saturn','S/2004 S 17',2)

1 row(s) inserted.

Statement 153

insert into moons values ('Saturn','S/2004 S 7',3)

1 row(s) inserted.

Statement 154

insert into moons values ('Saturn','S/2006 S 1',3)

1 row(s) inserted.

Statement 155

insert into moons values ('Saturn','S/2006 S 3',3)

1 row(s) inserted.

Statement 156

insert into moons values ('Saturn','S/2007 S 2',3)

1 row(s) inserted.

Statement 157

insert into moons values ('Saturn','S/2007 S 3',3)

1 row(s) inserted.

Statement 158

insert into moons values ('Saturn','S/2009 S 1',0.15)

1 row(s) inserted.

Statement 159

insert into moons values ('Saturn','Siarnaq',20)

1 row(s) inserted.

Statement 160

insert into moons values ('Saturn','Skathi',4)

1 row(s) inserted.

Statement 161

insert into moons values ('Saturn','Skoll',3)

1 row(s) inserted.

Statement 162

insert into moons values ('Saturn','Surtur',3)

1 row(s) inserted.

Statement 163

insert into moons values ('Saturn','Suttungr',3.5)

1 row(s) inserted.

Statement 164

insert into moons values ('Saturn','Tarqeq',3.5)

1 row(s) inserted.

Statement 165

insert into moons values ('Saturn','Tarvos',7.5)

1 row(s) inserted.

Statement 166

insert into moons values ('Saturn','Telesto',12 )

1 row(s) inserted.

Statement 167

insert into moons values ('Saturn','Tethys',536.3 )

1 row(s) inserted.

Statement 168

insert into moons values ('Saturn','Thrymr',3.5)

1 row(s) inserted.

Statement 169

insert into moons values ('Saturn','Titan',2575.5 )

1 row(s) inserted.

Statement 170

insert into moons values ('Saturn','Ymir',9)

1 row(s) inserted.

Statement 171

insert into moons values ('Uranus','Ariel',578.9 )

1 row(s) inserted.

Statement 172

insert into moons values ('Uranus','Belinda',40.3 )

1 row(s) inserted.

Statement 173

insert into moons values ('Uranus','Bianca',25.7 )

1 row(s) inserted.

Statement 174

insert into moons values ('Uranus','Caliban',49)

1 row(s) inserted.

Statement 175

insert into moons values ('Uranus','Cordelia',20.1 )

1 row(s) inserted.

Statement 176

insert into moons values ('Uranus','Cressida',39.8 )

1 row(s) inserted.

Statement 177

insert into moons values ('Uranus','Cupid',5)

1 row(s) inserted.

Statement 178

insert into moons values ('Uranus','Desdemona',32.0 )

1 row(s) inserted.

Statement 179

insert into moons values ('Uranus','Ferdinand',6)

1 row(s) inserted.

Statement 180

insert into moons values ('Uranus','Francisco',6)

1 row(s) inserted.

Statement 181

insert into moons values ('Uranus','Juliet',46.8 )

1 row(s) inserted.

Statement 182

insert into moons values ('Uranus','Mab',5)

1 row(s) inserted.

Statement 183

insert into moons values ('Uranus','Margaret',5.5)

1 row(s) inserted.

Statement 184

insert into moons values ('Uranus','Miranda',235.8 )

1 row(s) inserted.

Statement 185

insert into moons values ('Uranus','Oberon',761.4 )

1 row(s) inserted.

Statement 186

insert into moons values ('Uranus','Ophelia',21.4 )

1 row(s) inserted.

Statement 187

insert into moons values ('Uranus','Perdita',10)

1 row(s) inserted.

Statement 188

insert into moons values ('Uranus','Portia',67.6 )

1 row(s) inserted.

Statement 189

insert into moons values ('Uranus','Prospero',15)

1 row(s) inserted.

Statement 190

insert into moons values ('Uranus','Puck',81 )

1 row(s) inserted.

Statement 191

insert into moons values ('Uranus','Rosalind',36 )

1 row(s) inserted.

Statement 192

insert into moons values ('Uranus','Setebos',15)

1 row(s) inserted.

Statement 193

insert into moons values ('Uranus','Stephano',10)

1 row(s) inserted.

Statement 194

insert into moons values ('Uranus','Sycorax',75)

1 row(s) inserted.

Statement 195

insert into moons values ('Uranus','Titania',788.9 )

1 row(s) inserted.

Statement 196

insert into moons values ('Uranus','Trinculo',5)

1 row(s) inserted.

Statement 197

insert into moons values ('Uranus','Umbriel',584.7 )

1 row(s) inserted.

Statement 198

commit

Statement processed.

Statement 199

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/%'

View created.

Statement 200

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

157 rows selected.

Statement 201

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

11 rows selected.

Statement 202

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

11 rows selected.

Analytics - LISTAGG with the OVER clause

Additional Information