Skip to content

Instantly share code, notes, and snippets.

@seancolsen

seancolsen/celestial_bodies.sql

Created April 20, 2022 14:42
Show Gist options
  • Save seancolsen/2f196027b43f40ea664777857ea4c0ba to your computer and use it in GitHub Desktop.
Save seancolsen/2f196027b43f40ea664777857ea4c0ba to your computer and use it in GitHub Desktop.
Download ZIP
Celestial Bodies
Raw
celestial_bodies.sql
DROP TABLE IF EXISTS "star";
CREATE TABLE "star" (
"id" serial NOT NULL,
"name" VARCHAR(255) NOT NULL,
CONSTRAINT "star_pkey" PRIMARY KEY ("id")
);
INSERT INTO "star" ("id", "name") VALUES
(1, 'Sun');
DROP TABLE IF EXISTS "planet";
CREATE TABLE "planet" (
"id" SERIAL NOT NULL,
"name" VARCHAR(255) NOT NULL,
"star_id" INTEGER NOT NULL,
"aphelion_au" FLOAT NOT NULL,
"perihelion_au" FLOAT NOT NULL,
"type" VARCHAR(255) NOT NULL,
CONSTRAINT "planet_pkey" PRIMARY KEY ("id"),
CONSTRAINT "planet_star_id_fkey"
FOREIGN KEY ("star_id") REFERENCES "star" ("id")
);
INSERT INTO "planet" (
"id",
"name",
"star_id",
"aphelion_au",
"perihelion_au",
"type"
) VALUES
(1, 'Mercury', 1, 0.466697, 0.307499, 'terrestrial'),
(2, 'Venus', 1, 0.728213, 0.718440, 'terrestrial'),
(3, 'Earth', 1, 1.016725701, 0.983269343, 'terrestrial'),
(4, 'Mars', 1, 1.382, 1.666, 'terrestrial'),
(5, 'Jupiter', 1, 5.4570, 4.9506, 'gas giant'),
(6, 'Saturn', 1, 10.1238, 9.0412, 'gas giant'),
(7, 'Uranus', 1, 20.0965, 18.2861, 'ice giant'),
(8, 'Neptune', 1, 30.33, 29.81, 'ice giant');
DROP TABLE IF EXISTS "moon";
CREATE TABLE "moon" (
"id" serial NOT NULL,
"name" VARCHAR(255) NOT NULL,
"planet_id" INTEGER NOT NULL,
"mean_radius_km" FLOAT,
"semi_major_axis_km" FLOAT,
"year_discovered" INTEGER,
CONSTRAINT "moon_pkey" PRIMARY KEY ("id"),
CONSTRAINT "moon_planet_id_fkey"
FOREIGN KEY ("planet_id") REFERENCES "planet" ("id")
);
INSERT INTO "moon" (
"planet_id",
"name",
"mean_radius_km",
"semi_major_axis_km",
"year_discovered"
) VALUES
( 3, 'Moon' , 1738 , 384399 , NULL),
( 4, 'Phobos' , 11.267 , 9380 , 1877),
( 4, 'Deimos' , 6.2 , 23460 , 1877),
( 5, 'Io' , 1821.6 , 421800 , 1610),
( 5, 'Europa' , 1560.8 , 671100 , 1610),
( 5, 'Ganymede' , 2634.1 , 1070400 , 1610),
( 5, 'Callisto' , 2410.3 , 1882700 , 1610),
( 5, 'Amalthea' , 83.5 , 181400 , 1892),
( 5, 'Himalia' , 69.8 , 11461000 , 1904),
( 5, 'Elara' , 43 , 11741000 , 1905),
( 5, 'Pasiphae' , 30 , 23624000 , 1908),
( 5, 'Sinope' , 19 , 23939000 , 1914),
( 5, 'Lysithea' , 18 , 11717000 , 1938),
( 5, 'Carme' , 23 , 23404000 , 1938),
( 5, 'Ananke' , 14 , 21276000 , 1951),
( 5, 'Leda' , 10 , 11165000 , 1974),
( 5, 'Thebe' , 49.3 , 221900 , 1979),
( 5, 'Adrastea' , 8.2 , 129000 , 1979),
( 5, 'Metis' , 21.5 , 128000 , 1979),
( 5, 'Callirrhoe' , 4.5 , 24103000 , 2000),
( 5, 'Themisto' , 4 , 7284000 , 1975),
( 5, 'Megaclite' , 2.7 , 23493000 , 2000),
( 5, 'Taygete' , 2.5 , 23280000 , 2000),
( 5, 'Chaldene' , 1.9 , 23100000 , 2000),
( 5, 'Harpalyke' , 2.2 , 20858000 , 2000),
( 5, 'Kalyke' , 2.6 , 23483000 , 2000),
( 5, 'Iocaste' , 2.6 , 21060000 , 2000),
( 5, 'Erinome' , 1.6 , 23196000 , 2000),
( 5, 'Isonoe' , 2 , 23155000 , 2000),
( 5, 'Praxidike' , 3.5 , 20908000 , 2000),
( 5, 'Autonoe' , 2 , 24046000 , 2001),
( 5, 'Thyone' , 2 , 20939000 , 2001),
( 5, 'Hermippe' , 2 , 21131000 , 2001),
( 5, 'Aitne' , 1.5 , 23229000 , 2001),
( 5, 'Eurydome' , 1.5 , 22865000 , 2001),
( 5, 'Euanthe' , 1.5 , 20797000 , 2001),
( 5, 'Euporie' , 1 , 19304000 , 2001),
( 5, 'Orthosie' , 1 , 20720000 , 2001),
( 5, 'Sponde' , 1 , 23487000 , 2001),
( 5, 'Kale' , 1 , 23217000 , 2001),
( 5, 'Pasithee' , 1 , 23004000 , 2001),
( 5, 'Hegemone' , 1.5 , 23577000 , 2003),
( 5, 'Mneme' , 1 , 21035000 , 2003),
( 5, 'Aoede' , 2 , 23980000 , 2003),
( 5, 'Thelxinoe' , 1 , 21164000 , 2003),
( 5, 'Arche' , 1.5 , 23355000 , 2002),
( 5, 'Kallichore' , 1 , 23288000 , 2003),
( 5, 'Helike' , 2 , 21069000 , 2003),
( 5, 'Carpo' , 1.5 , 17058000 , 2003),
( 5, 'Eukelade' , 2 , 23328000 , 2003),
( 5, 'Cyllene' , 1 , 23809000 , 2003),
( 5, 'Kore' , 1 , 24543000 , 2003),
( 5, 'Herse' , 1 , 22983000 , 2003),
( 5, 'Dia' , 2 , 12118000 , 2000),
( 5, 'Eirene' , 2 , 23731800 , 2003),
( 5, 'Philophrosyne', 1 , 22820000 , 2003),
( 5, 'Eupheme' , 1 , 21199710 , 2003),
( 5, 'Valetudo' , 0.5 , 18928100 , 2016),
( 5, 'Pandia' , 1.5 , 11494800 , 2017),
( 5, 'Ersa' , 1.5 , 11453000 , 2018),
( 6, 'Mimas' , 198.2 , 185540 , 1789),
( 6, 'Enceladus' , 252.1 , 238040 , 1789),
( 6, 'Tethys' , 533.1 , 294670 , 1684),
( 6, 'Dione' , 561.4 , 377420 , 1684),
( 6, 'Rhea' , 763.8 , 527070 , 1672),
( 6, 'Titan' , 2574.73 , 1221870 , 1655),
( 6, 'Hyperion' , 135 , 1500880 , 1848),
( 6, 'Iapetus' , 735.6 , 3560840 , 1671),
( 6, 'Phoebe' , 106.5 , 12947780 , 1899),
( 6, 'Janus' , 89.5 , 151460 , 1966),
( 6, 'Epimetheus' , 58.1 , 151410 , 1966),
( 6, 'Helene' , 17.6 , 377420 , 1980),
( 6, 'Telesto' , 12.4 , 294710 , 1980),
( 6, 'Calypso' , 10.7 , 294710 , 1980),
( 6, 'Atlas' , 15.1 , 137670 , 1980),
( 6, 'Prometheus' , 43.1 , 139380 , 1980),
( 6, 'Pandora' , 40.7 , 141720 , 1980),
( 6, 'Pan' , 14.1 , 133580 , 1990),
( 6, 'Ymir' , 9 , 23140400 , 2000),
( 6, 'Paaliaq' , 11 , 15200000 , 2000),
( 6, 'Tarvos' , 7.5 , 17983000 , 2000),
( 6, 'Ijiraq' , 6 , 11124000 , 2000),
( 6, 'Suttungr' , 3.5 , 19459000 , 2000),
( 6, 'Kiviuq' , 8 , 11110000 , 2000),
( 6, 'Mundilfari' , 3.5 , 18628000 , 2000),
( 6, 'Albiorix' , 16 , 16182000 , 2000),
( 6, 'Skathi' , 4 , 15540000 , 2000),
( 6, 'Erriapus' , 5 , 17343000 , 2000),
( 6, 'Siarnaq' , 20 , 18015400 , 2000),
( 6, 'Thrymr' , 3.5 , 20314000 , 2000),
( 6, 'Narvi' , 3.5 , 19007000 , 2003),
( 6, 'Methone' , 1.45 , 194440 , 2004),
( 6, 'Pallene' , 2.22 , 212280 , 2004),
( 6, 'Polydeuces' , 1.3 , 377200 , 2004),
( 6, 'Daphnis' , 3.8 , 136500 , 2005),
( 6, 'Aegir' , 3 , 20751000 , 2004),
( 6, 'Bebhionn' , 3 , 17119000 , 2004),
( 6, 'Bergelmir' , 3 , 19336000 , 2004),
( 6, 'Bestla' , 3.5 , 20192000 , 2004),
( 6, 'Farbauti' , 2.5 , 20377000 , 2004),
( 6, 'Fenrir' , 2 , 22454000 , 2004),
( 6, 'Fornjot' , 3 , 25146000 , 2004),
( 6, 'Hati' , 3 , 19846000 , 2004),
( 6, 'Hyrrokkin' , 4 , 18437000 , 2004),
( 6, 'Kari' , 3.5 , 22089000 , 2006),
( 6, 'Loge' , 3 , 23058000 , 2006),
( 6, 'Skoll' , 3 , 17665000 , 2006),
( 6, 'Surtur' , 3 , 22704000 , 2006),
( 6, 'Anthe' , 0.9 , 197700 , 2007),
( 6, 'Jarnsaxa' , 3 , 18811000 , 2006),
( 6, 'Greip' , 3 , 18206000 , 2006),
( 6, 'Tarqeq' , 3.5 , 18009000 , 2007),
( 6, 'Aegaeon' , 0.33 , 167500 , 2008),
( 7, 'Ariel' , 578.9 , 190900 , 1851),
( 7, 'Umbriel' , 584.7 , 266000 , 1851),
( 7, 'Titania' , 788.9 , 436300 , 1787),
( 7, 'Oberon' , 761.4 , 583500 , 1787),
( 7, 'Miranda' , 235.8 , 129900 , 1948),
( 7, 'Cordelia' , 20.1 , 49800 , 1986),
( 7, 'Ophelia' , 21.4 , 53800 , 1986),
( 7, 'Bianca' , 25.7 , 59200 , 1986),
( 7, 'Cressida' , 39.8 , 61800 , 1986),
( 7, 'Desdemona' , 32 , 62700 , 1986),
( 7, 'Juliet' , 46.8 , 64400 , 1986),
( 7, 'Portia' , 67.6 , 66100 , 1986),
( 7, 'Rosalind' , 36 , 69900 , 1986),
( 7, 'Belinda' , 40.3 , 75300 , 1986),
( 7, 'Puck' , 81 , 86000 , 1985),
( 7, 'Caliban' , 36.4 , 7231100 , 1997),
( 7, 'Sycorax' , 93 , 12179400 , 1997),
( 7, 'Prospero' , 25 , 16256000 , 1999),
( 7, 'Setebos' , 24 , 17418000 , 1999),
( 7, 'Stephano' , 16 , 8004000 , 1999),
( 7, 'Trinculo' , 9.5 , 8504000 , 2001),
( 7, 'Francisco' , 11 , 4276000 , 2001),
( 7, 'Margaret' , 10 , 14345000 , 2003),
( 7, 'Ferdinand' , 10 , 20901000 , 2001),
( 7, 'Perdita' , 15 , 76417 , 1999),
( 7, 'Mab' , 12 , 97736 , 2003),
( 7, 'Cupid' , 9 , 74392 , 2003),
( 7, 'Triton' , 1353.4 , 354800 , 1846),
( 7, 'Nereid' , 170 , 5513820 , 1949),
( 7, 'Naiad' , 33 , 48224 , 1989),
( 7, 'Thalassa' , 41 , 50075 , 1989),
( 7, 'Despina' , 78 , 52526 , 1989),
( 7, 'Galatea' , 88 , 61953 , 1989),
( 7, 'Larissa' , 97 , 73548 , 1981),
( 7, 'Proteus' , 210 , 117647 , 1989),
( 7, 'Halimede' , 31 , 15728000 , 2002),
( 7, 'Psamathe' , 20 , 46695000 , 2003),
( 7, 'Sao' , 22 , 22422000 , 2002),
( 7, 'Laomedeia' , 21 , 23571000 , 2002),
( 7, 'Neso' , 30 , 48387000 , 2002),
( 7, 'Hippocamp' , 17.4 , 105283 , 2013);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment