-
Notifications
You must be signed in to change notification settings - Fork 2
/
data.sql
170 lines (134 loc) · 6.59 KB
/
data.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
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
/* Populate database with sample data. */
/* Data for first step*/
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Agumon', '2020-02-3', 0, TRUE, 10.23);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Gabumon', '2018-11-15', 2, TRUE, 8);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Pikachu', '2021-01-7', 1, FALSE, 15.04);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Devimon', '2017-05-12', 5, TRUE, 11);
/* Data for second step*/
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Charmander', '2020-02-8', 0, FALSE, -11);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Plantmon', '2022-11-15', 2, TRUE, -5.7);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Squirtle', '1993-04-2', 3, FALSE, -12.13);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Angemon', '2005-06-12', 1, TRUE, -45);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Boarmon', '2005-06-7', 7, TRUE, 20.4);
INSERT INTO animals(name, date_of_birth, escape_attempts, neutered, weight_kg)
VALUES ('Blossom', '1998-10-13', 3, TRUE, 17);
/* Data for third step*/
/*Data for owners table*/
INSERT INTO owners (full_name, age)
VALUES('Sam Smith', 34);
INSERT INTO owners (full_name, age)
VALUES('Jennifer Orwell', 19);
INSERT INTO owners (full_name, age)
VALUES('Bob', 45);
INSERT INTO owners (full_name, age)
VALUES('Melody Pond', 77);
INSERT INTO owners (full_name, age)
VALUES('Dean Winchester', 14);
INSERT INTO owners (full_name, age)
VALUES('Jodie Whittaker', 38);
/*Data for species table*/
INSERT INTO species (name)
VALUES('Pokemon');
INSERT INTO species (name)
VALUES('Digimon');
/*Modify species_id in animals table*/
UPDATE animals SET species_id= '2' WHERE name LIKE '%mon';
UPDATE animals SET species_id= '1' WHERE species_id IS NULL;
/*Modify owner_id in animals table*/
UPDATE animals SET owner_id = owners.id FROM owners WHERE name = 'Agumon' AND owners.full_name= 'Sam Smith' ;
UPDATE animals SET owner_id = owners.id FROM owners WHERE name IN ('Gabumon','Pikachu') AND owners.full_name= 'Jennifer Orwell';
UPDATE animals SET owner_id = owners.id FROM owners WHERE name IN ('Devimon','Plantmon') AND owners.full_name= 'Bob';
UPDATE animals SET owner_id = owners.id FROM owners WHERE name IN ('Charmander','Squirtle', 'Blossom') AND owners.full_name= 'Melody Pond';
UPDATE animals SET owner_id = owners.id FROM owners WHERE name IN ('Angemon','Boarmon') AND owners.full_name= 'Dean Winchester';
/*Add new data to vets table*/
INSERT INTO vets (name, age, date_of_graduation)
VALUES('William Tatcher', 45, '2000-04-23');
INSERT INTO vets (name, age, date_of_graduation)
VALUES('Maisy Smith', 26, '2019-01-17');
INSERT INTO vets (name, age, date_of_graduation)
VALUES('Stephanie Mendez', 64, '1981-05-4');
INSERT INTO vets (name, age, date_of_graduation)
VALUES('Jack Harkness', 38, '2008-06-8');
/*Add specialities vets-species*/
INSERT INTO specializations (vets_id, species_id)
VALUES(1,1);
INSERT INTO specializations (vets_id, species_id)
VALUES(3,1);
INSERT INTO specializations (vets_id, species_id)
VALUES(4,2);
/*Add visits data*/
/*Agumon visited William Tatcher on May 24th, 2020.
Agumon visited Stephanie Mendez on Jul 22th, 2020.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(1,1,'2020-05-24');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(1,3,'2020-07-22');
/*Gabumon visited Jack Harkness on Feb 2nd, 2021.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(2,4,'2021-02-2');
/*Pikachu visited Maisy Smith on Jan 5th, 2020.
Pikachu visited Maisy Smith on Mar 8th, 2020.
Pikachu visited Maisy Smith on May 14th, 2020.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(3,2,'2020-01-5');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(3,2,'2020-03-8');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(3,2,'2020-05-14');
/*Devimon visited Stephanie Mendez on May 4th, 2021.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(4,3,'2021-05-4');
/*Charmander visited Jack Harkness on Feb 24th, 2021.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(5,4,'2021-02-24');
/*Plantmon visited Maisy Smith on Dec 21st, 2019.
Plantmon visited William Tatcher on Aug 10th, 2020.
Plantmon visited Maisy Smith on Apr 7th, 2021.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(6,2,'2019-12-21');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(6,1,'2020-08-10');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(6,2,'2021-04-7');
/*Squirtle visited Stephanie Mendez on Sep 29th, 2019.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(7,3,'2019-09-29');
/*Angemon visited Jack Harkness on Oct 3rd, 2020.
Angemon visited Jack Harkness on Nov 4th, 2020.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(8,4,'2020-10-3');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(8,4,'2020-11-4');
/*Boarmon visited Maisy Smith on Jan 24th, 2019.
Boarmon visited Maisy Smith on May 15th, 2019.
Boarmon visited Maisy Smith on Feb 27th, 2020.
Boarmon visited Maisy Smith on Aug 3rd, 2020.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(9,2,'2019-01-24');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(9,2,'2019-05-15');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(9,2,'2020-02-27');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(9,2,'2020-08-3');
/*Blossom visited Stephanie Mendez on May 24th, 2020.
Blossom visited William Tatcher on Jan 11th, 2021.*/
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(10,3,'2020-05-24');
INSERT INTO visits (animals_id, vets_id, date_of_visit)
VALUES(10,1,'2021-01-11');
/*Week 2*/
/*Day 1*/
-- This will add 3.594.280 visits considering you have 10 animals, 4 vets, and it will use around ~87.000 timestamps (~4min approx.)
INSERT INTO visits (animal_id, vet_id, date_of_visit) SELECT * FROM (SELECT id FROM animals) animal_ids, (SELECT id FROM vets) vets_ids, generate_series('1980-01-01'::timestamp, '2021-01-01', '4 hours') visit_timestamp;
-- This will add 2.500.000 owners with full_name = 'Owner <X>' and email = 'owner_<X>@email.com' (~2min approx.)
insert into owners (full_name, email) select 'Owner ' || generate_series(1,2500000), 'owner_' || generate_series(1,2500000) || '@mail.com';