-
Notifications
You must be signed in to change notification settings - Fork 0
/
universe.sql
140 lines (130 loc) · 8.09 KB
/
universe.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
psql --username=freecodecamp --dbname=postgres
create database universe;
\c universe;
CREATE table galaxy(galaxy_id serial not null primary key,
name varchar(30) unique not null,
galaxy_type varchar(20),
galaxy_mass numeric(50,3),
galaxy_description text,
galaxy_velocity int,
galaxy_age int);
create table star(star_id serial not null primary key,
name varchar(30) unique not null,
star_type varchar(20),
star_weight numeric(20,3),
star_description text,
star_radius int,
star_age int,
galaxy_id int,
constraint fk_galaxy_star foreign key (galaxy_id) references galaxy(galaxy_id));
create table planet(planet_id serial not null primary key,
name varchar(30) unique not null,
planet_type varchar(20),
planet_weight numeric(20,3),
planet_description text,
planet_radius int,
planet_age int,
has_life boolean,
has_water boolean,
star_id int,
constraint fk_star_planet FOREIGN KEY (star_id) references star(star_id));
create table moon(moon_id serial not null primary key,
name varchar(30) unique not null,
moon_type varchar(20),
moon_weight numeric(20,3),
moon_description text,
moon_radius int,
moon_age int,
has_life boolean,
has_water boolean,
planet_id int,
constraint fk_planet_moon foreign key (planet_id) references planet(planet_id));
CREATE table blackhole(blackhole_id serial not null primary key,
name varchar(30) unique not null,
blackhole_description text,
blackhole_radius int,
blackhole_age int,
galaxy_id int,
constraint fk_galaxy_blackhole foreign key (galaxy_id) references galaxy(galaxy_id));
insert into galaxy(name,
galaxy_type ,
galaxy_mass,
galaxy_description,
galaxy_velocity,
galaxy_age) values ('G1', 'A', 800155550230, 'Galaxy G1 type A', 550, 39723000),
('G2', 'A', 2342385230, 'Galaxy G2 type A', 530, 156435000),
('G3', 'C', 999222025500, 'Galaxy G3 type C', 450, 23416000),
('G4', 'B', 5818878500000.25, 'Galaxy G4 type B', 950, 32400000),
('G5', 'K', 91312332344.145, 'Galaxy G5 type K', 435, 789784000),
('G6', 'F', 362510149293.22, 'Galaxy G6 type F', 333, 12000000),
('G7', 'VVA', 12515154335489.1, 'Galaxy G7 type VVA', 693, 4545700);
insert into star (name,
star_type ,
star_weight ,
star_description,
star_radius,
star_age,
galaxy_id)
values ('ST1', 'A', 80050230, 'Star ST1 type A', 554350, 31970000, 1),
('ST2', 'F', 344550239, 'Star ST2 type F', 1235550, 5390000, 1),
('ST3', '3', 550230, 'Star ST3 type 3', 554670, 340700, 3),
('ST4', 'Q', 8700150, 'Star ST4 type Q', 234550, 390000, 1),
('ST5', 'A', 8740230, 'Star ST5 type A', 564250, 3974000, 3),
('ST6', '5R', 6520230, 'Star ST6 type 5R', 523450, 369000, 7),
('ST7', '1', 55870, 'Star ST7 type 1', 512350,897000, 5);
insert into planet(name,
planet_type,
planet_weight,
planet_description,
planet_radius,
planet_age,
has_life,
has_water,
star_id) values ('P1', '1', 919439, 'Planet 1', 342452, 134838, CAST(1 AS bit), TRUE, 1),
('P2', '2', 93139, 'Planet 2', 235152, 178838, CAST(0 AS bit), TRUE, 1),
('P3', '1', 213439, 'Planet 3', 29252, 33838, CAST(1 AS bit), TRUE, 2),
('P4', '1', 666439, 'Planet 4', 124452, 342838, CAST(0 AS bit), FALSE, 3),
('P5', '2', 95639, 'Planet 5', 2452, 456838, CAST(0 AS bit), TRUE, 2),
('P6', '1', 246439, 'Planet 6', 244452, 672838, CAST(0 AS bit), TRUE, 4),
('P7', '6', 93439, 'Planet 7', 255452, 532838, CAST(0 AS bit), FALSE, 1),
('P8', '4', 579439, 'Planet 8', 212562, 13438, CAST(1 AS bit), TRUE, 3),
('P9', '1', 564389, 'Planet 9', 883452, 123838, CAST(1 AS bit), FALSE, 1),
('P10', '5', 900239, 'Planet 10', 202452, 34838, CAST(0 AS bit), FALSE, 7),
('P11', '2', 321239, 'Planet 11', 754352, 15038, CAST(1 AS bit), TRUE, 6),
('P12', '3', 348925, 'Planet 12', 734452, 55638, CAST(0 AS bit), TRUE, 1);
insert into moon(name,
moon_type,
moon_weight,
moon_description,
moon_radius,
moon_age,
has_life,
has_water,
planet_id) values ('M1', '3', 45439, 'Moon 1', 29452, 4458, FALSE, FALSE, 2),
('M2', '1', 45439, 'Moon 2', 28452, 6458, FALSE, FALSE, 1),
('M3', '2', 94339, 'Moon 3', 2232, 6438, FALSE, FALSE, 1),
('M4', '1', 23439, 'Moon 4', 22652, NULL, FALSE, FALSE, 1),
('M5', '3', 94549, 'Moon 5', NULL, 64838, FALSE, FALSE, 2),
('M6', '1', 94543, 'Moon 6', 25252, 64838, FALSE, FALSE, 3),
('M7', '2', 94549, 'Moon 7', 2252, NULL, FALSE, FALSE, 9),
('M8', '3', 94543, 'Moon 8', 63052, 3838, FALSE, FALSE, 4),
('M9', '1', 94439, 'Moon 9', 29452, NULL, FALSE, FALSE, 2),
('M10', '1', 94543, 'Moon 10', 22652, 64838, FALSE, FALSE, 1),
('M11', '1', 94539, 'Moon 11', 223452, 64838, FALSE, TRUE, 4),
('M12', '2', 64543, 'Moon 12', NULL, 64838, FALSE, FALSE, 1),
('M13', '2', 94539, 'Moon 13', NULL, 64838, FALSE, FALSE, 1),
('M14', '1', 14539, 'Moon 14', 223452, 64838, FALSE, FALSE, 10),
('M15', '1', 40439, 'Moon 15', 223452, NULL, FALSE, FALSE, 1),
('M16', '1', 94439, 'Moon 16', 223452, 64838, FALSE, FALSE, 1),
('M17', '1', 74439, 'Moon 17', 223452, 64838, TRUE, FALSE, 1),
('M18', '3', 94439, 'Moon 18', NULL, NULL, FALSE, FALSE, 1),
('M19', '1', 94539, 'Moon 19', 223452, 64838, FALSE, FALSE, 11),
('M20', '3', 94539, 'Moon 29', NULL, 64838, FALSE, FALSE, 1);
insert into blackhole(name,
blackhole_description,
blackhole_radius,
blackhole_age,
galaxy_id) values ('BH1', 'Black Hole 1', 2934, 19994, 1),
('BH2', 'Black Hole 2', 467234, 239494, 1),
('BH3', 'Black Hole 3', 2334, 13424, 4),
('BH4', 'Black Hole 4', 434, 199094, 2);