-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathseed.psql
181 lines (148 loc) · 9.79 KB
/
seed.psql
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
CREATE DATABASE animal_sighting_tracker;
\c animal_sighting_tracker;
CREATE TABLE species (
id SERIAL PRIMARY KEY,
common_name VARCHAR ( 50 ) UNIQUE NOT NULL,
scientific_name VARCHAR ( 50 ) NOT NULL,
population integer,
conservation_status VARCHAR (2),
created_on TIMESTAMP WITH TIME zone DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE individuals (
id serial PRIMARY KEY,
nick_name VARCHAR ( 50 ),
species_id integer NOT NULL,
seen_on TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_species
FOREIGN KEY(species_id)
REFERENCES species(id)
);
CREATE TABLE sightings (
id serial PRIMARY KEY,
date_time TIMESTAMP WITH TIME ZONE,
location VARCHAR ( 50 ) NOT NULL,
healthy BOOLEAN NOT NULL,
email VARCHAR(50) UNIQUE,
individual_id INT NOT NULL,
created_on TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_individuals
FOREIGN KEY(individual_id)
REFERENCES individuals(id)
);
ALTER TABLE sightings
DROP COLUMN email;
ALTER TABLE sightings
ADD CONSTRAINT fk_sighter_id
FOREIGN KEY(sighter_id)
REFERENCES sighter(id);
ALTER TABLE sightings
ADD sighter_id integer;
ALTER TABLE sightings ADD CONSTRAINT fk_sighter_id FOREIGN KEY (sighter_id) REFERENCES sighter(id);
CREATE TABLE sighter (
id serial PRIMARY KEY,
email VARCHAR(50) UNIQUE
);
-- SHOW TABLE
\dt
INSERT INTO
species(common_name,scientific_name, population, conservation_status, created_on)
VALUES
('Rhinoceros','Rhinoceros sondaicus', '60', 'CE','2016-06-22 19:10:25-07'),
('Vaquita',' Phocoena sinus', NULL, 'CE','2017-06-22 19:10:25-07'),
('Gorilla','Gorilla beringei beringei', null, 'CE','2016-06-22 20:20:25-07'),
('Tigers','Panthera tigris', null, 'CE','2020-06-22 19:10:25-07');
INSERT INTO
individuals(nick_name, seen_on, species_id)
VALUES ('jocky','2016-06-22 20:20:25-07', 2),
('chintu','2016-06-22 20:20:25-07', 3),
('reya','2016-06-22 20:20:25-07', 1),
('tom','2016-06-22 20:20:25-07', 2);
INSERT INTO
sighter(email)
VALUES ('[email protected]'),
('[email protected]'),
('[email protected]');
INSERT INTO
sightings(date_time ,location, healthy, individual_id, sighter_id)
VALUES ('2016-06-22 20:20:25-07','Olympic National Park', true, 2, 1),
('2016-06-22 20:20:25-07','Zion National Park',false, 3, 2);
ALTER TABLE sighter
RENAME TO sighters;
ALTER TABLE sightings
ADD email VARCHAR(50) UNIQUE;
ALTER TABLE sightings
DROP COLUMN sighter_id;
DROP TABLE IF EXISTS sighter;
create table species (
id SERIAL PRIMARY KEY,
common_name VARCHAR(50),
scientific_name VARCHAR(50),
population VARCHAR(50),
conservation_status VARCHAR(2),
created_on TIMESTAMP WITH TIME zone DEFAULT CURRENT_TIMESTAMP
);
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('Leopard, indian', 'Panthera pardus', '500', 'US', '1/21/2022');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('Vine snake (unidentified)', 'Oxybelis sp.', '985', 'US', '2/15/2022');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('European badger', 'Meles meles', '489', 'US', '10/27/2021');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('Baboon, yellow', 'Papio cynocephalus', '85', 'US', '10/14/2021');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('Black curlew', 'Haematopus ater', '530', 'US', '2/1/2022');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('Brazilian otter', 'Pteronura brasiliensis', '09', 'US', '3/29/2022');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('Ring-necked pheasant', 'Phasianus colchicus', '2529', 'US', '5/29/2021');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('American beaver', 'Castor canadensis', '257', 'US', '7/21/2021');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('European badger', 'Meles meles', '9', 'US', '8/13/2021');
insert into species (common_name, scientific_name, population, conservation_status, created_on) values ('Scaly-breasted lorikeet', 'Trichoglossus chlorolepidotus', '919', 'US', '5/15/2021');
create table individuals (
id SERIAL PRIMARY KEY,
nick_name VARCHAR(50),
seen_on TIMESTAMP,
species_id INT
);
insert into individuals (nick_name, seen_on, species_id) values ('Anny', '1/1/2022', 1);
insert into individuals (nick_name, seen_on, species_id) values ('Gabriell', '10/15/2021', 2);
insert into individuals (nick_name, seen_on, species_id) values ('Nani', '7/2/2021', 1);
insert into individuals (nick_name, seen_on, species_id) values ('Leonanie', '7/11/2021', 2);
insert into individuals (nick_name, seen_on, species_id) values ('Duffie', '4/25/2021', 3);
insert into individuals (nick_name, seen_on, species_id) values ('Marshall', '6/29/2021', 3);
insert into individuals (nick_name, seen_on, species_id) values ('Sophia', '6/1/2021', 4);
insert into individuals (nick_name, seen_on, species_id) values ('Angie', '4/30/2021', 4);
insert into individuals (nick_name, seen_on, species_id) values ('Lion', '12/28/2021', 4);
insert into individuals (nick_name, seen_on, species_id) values ('Robinette', '7/16/2021', 5);
insert into individuals (nick_name, seen_on, species_id) values ('Lindi', '10/3/2021', 6);
insert into individuals (nick_name, seen_on, species_id) values ('Duffy', '8/4/2021', 6);
insert into individuals (nick_name, seen_on, species_id) values ('Ax', '6/13/2021', 7);
insert into individuals (nick_name, seen_on, species_id) values ('Kelly', '6/28/2021', 7);
insert into individuals (nick_name, seen_on, species_id) values ('Ulrika', '4/1/2022', 8);
insert into individuals (nick_name, seen_on, species_id) values ('Helyn', '5/6/2021', 8);
insert into individuals (nick_name, seen_on, species_id) values ('Keefer', '5/14/2021', 9);
insert into individuals (nick_name, seen_on, species_id) values ('Johan', '8/16/2021', 9);
insert into individuals (nick_name, seen_on, species_id) values ('Casey', '2/10/2022', 10);
insert into individuals (nick_name, seen_on, species_id) values ('Udall', '3/8/2022', 10);
create table sightings (
id SERIAL PRIMARY KEY,
date_time TIMESTAMP,
location VARCHAR(50),
healthy BOOLEAN,
individual_id INT,
created_on DATE,
email VARCHAR(50)
);
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('6/25/2021', 'Westport', true, 1, '6/14/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('3/30/2022', 'Express', true, 2, '4/15/2022', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('8/17/2021', 'Homewood', false, 3, '6/12/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('10/31/2021', 'Waywood', true, 4, '4/26/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('10/19/2021', 'Duke', false, 5, '1/10/2022', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('4/6/2022', 'Amoth', false, 6, '2/22/2022', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('10/26/2021', 'Porter', false, 7, '10/3/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('6/27/2021', 'Brentwood', false, 8, '4/4/2022', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('4/5/2022', 'Hayes', true, 9, '12/27/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('9/10/2021', 'Lighthouse Bay', true, 10, '8/31/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('9/23/2021', 'Mariners Cove', false, 11, '4/27/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('5/23/2021', 'Huxley', false, 12, '3/28/2022', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('6/6/2021', 'Mayer', false, 13, '12/31/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('5/26/2021', 'Portage', false, 14, '7/15/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('12/15/2021', 'Vahlen', true, 15, '8/18/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('2/14/2022', 'School', false, 16, '5/2/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('2/21/2022', 'Goodland', true, 17, '9/8/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('2/2/2022', 'Spenser', false, 18, '4/12/2022', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('10/21/2021', 'Hoepker', true, 19, '6/4/2021', '[email protected]');
insert into sightings (date_time, location, healthy, individual_id, created_on, email) values ('3/5/2022', 'Sunnyside', false, 20, '2/28/2022', '[email protected]');