· 6 years ago · Nov 29, 2019, 02:07 AM
1-- -----------------------------
2-- Creation de la base de donnee
3-- -----------------------------
4
5-- database for GarderieG2B
6CREATE DATABASE IF NOT EXISTS garderieg2b;
7USE garderieg2b;
8
9-- ---------------------------------
10-- Fin creation de la base de donnee
11-- ---------------------------------
12
13
14-- -------------------
15-- Creation des tables
16-- -------------------
17
18-- Garderie
19CREATE TABLE Garderie (
20 id INT NOT NULL PRIMARY KEY,
21 nom TEXT NOT NULL,
22 adresse TEXT NOT NULL
23);
24
25-- Actionnaire
26CREATE TABLE Actionnaire (
27 id INT NOT NULL PRIMARY KEY,
28 nom TEXT NOT NULL,
29 prenom TEXT NOT NULL,
30 pourcentage NUMBER NOT NULL
31);
32
33-- Service
34CREATE TABLE Service (
35 id INT NOT NULL PRIMARY KEY,
36 nom TEXT NOT NULL,
37 frais NUMBER NOT NULL
38);
39
40-- Garderie-Service
41CREATE TABLE GarderieService (
42 id INT NOT NULL PRIMARY KEY,
43 garderie_id INT FOREIGN KEY REFERENCES Garderie(id),
44 service_id INT FOREIGN KEY REFERENCES Service(id)
45);
46
47-- Employe
48CREATE TABLE Employe (
49 id INT NOT NULL PRIMARY KEY,
50 garderie_id INT FOREIGN KEY REFERENCES Garderie(id),
51 nom TEXT NOT NULL,
52 prenom TEXT NOT NULL,
53 salaire NUMBER NOT NULL,
54 telephone TEXT NOT NULL
55);
56
57-- Classe
58CREATE TABLE Classe (
59 id INT NOT NULL PRIMARY KEY,
60 employe_id INT FOREIGN KEY REFERENCES Employe(id),
61 capacite INT NOT NULL,
62 local TEXT NOT NULL
63);
64
65-- Enfant
66CREATE TABLE Enfant (
67 id INT NOT NULL PRIMARY KEY,
68 classe_id INT FOREIGN KEY REFERENCES Classe(id),
69 nom TEXT NOT NULL,
70 prenom TEXT NOT NULL,
71 age INT NOT NULL,
72 subvention INT DEFAULT 0
73);
74
75-- Parent
76CREATE TABLE Parent (
77 id INT NOT NULL PRIMARY KEY,
78 nom TEXT NOT NULL,
79 prenom TEXT NOT NULL,
80 telephone TEXT NOT NULL
81);
82
83-- Enfant-Parent
84CREATE TABLE EnfantParent (
85 id INT NOT NULL PRIMARY KEY,
86 enfant_id INT FOREIGN KEY REFERENCES Enfant(id),
87 parent_id INT FOREIGN KEY REFERENCES Parent(id)
88);
89
90-- Inscription
91CREATE TABLE Inscription (
92 id INT NOT NULL PRIMARY KEY,
93 enfant_id INT FOREIGN KEY REFERENCES Enfant(id),
94 date DATE NOT NULL
95);
96
97-- Presence
98CREATE TABLE Presence (
99 id INT NOT NULL PRIMARY KEY,
100 enfant_id INT FOREIGN KEY REFERENCES Enfant(id),
101 date DATE DEFAULT CURRENT_DATE,
102 entree DATE,
103 sortie DATE,
104 frais NUMBER DEFAULT 0
105);
106
107-- Absence
108CREATE TABLE Absence (
109 id INT NOT NULL PRIMARY KEY,
110 enfant_id INT FOREIGN KEY REFERENCES Enfant(id),
111 date DATE DEFAULT CURRENT_DATE
112);
113
114-- -----------------------
115-- Fin creation des tables
116-- -----------------------
117
118
119-- ---------------------
120-- Insertion des donnees
121-- ---------------------
122
123
124-- Type de services offert par les garderies
125INSERT INTO Service
126VALUES
127 (0, "subventionnée", 8.25),
128 (1, "non subventionnée jour 18mois à 5ans", 55),
129 (2, "jour 3 à 18 mois", 60),
130 (3, "nuit", 21);
131
132
133-- 7
134INSERT INTO Garderie
135VALUES
136 (0, "Perdu dans l'esapce", "Montreal 335 rue des Pains"),
137 (1, "Gagner au sol", "Montreal 112 rue des Baguettes"),
138 (2, "Le Bonheur", "Laval 223 rue des Muffins"),
139 (3, "Garderie les prêtes", "Laval 612 rue Desverges"),
140 (4, "Garderie Chez Steurks", "Boucherville 669 rue Rodeo"),
141 (5, "Peurks", "Boucherville 123 rue Dauzé"),
142 (6, "La soukisserie", "Longueuil 456 rue Souks"),
143 (7, "Les gentils", "Longueuil 354 rue des Abordables");
144
145INSERT INTO Employe
146VALUES
147 (0, 0, "Painchaud", "Yvan D.", 15, "555-243-7242"),
148 (1, 1, "Trudeau", "Simon", 15, "555-423-2378"),
149 (2, 2, "Trudelle", " Simon", 15, "555-547-1920"),
150 (3, 2, "Matte", "Martin", 15, "555-348-1702"),
151 (4, 3, "Guilbaut", "Alphonse", 15, "555-585-3805"),
152 (5, 3, "Bernier", "Herménégild", 15, "555-312-3122"),
153 (6, 4, "Steurks", "Antonio", 18, "555-130-1380"),
154 (7, 5, "Lalonde", "Rémi", 18, "555-312-1232"),
155 (8, 6, "Michel", "Olivier", 15, "555-513-1535"),
156 (9, 7, "Fillion", "Rémi", 15, "555-375-3792");
157
158INSERT INTO Classe
159VALUES
160 (0, 0, 15, "Levure"),
161 (1, 1, 15, "Salle comble"),
162 (2, 2, 15, "Cage"),
163 (3, 3, 15, "Honda"),
164 (4, 4, 15, "Vicieux"),
165 (5, 5, 15, "Détraquer"),
166 (6, 6, 15, "Italie"),
167 (7, 7, 15, "Séduction"),
168 (8, 8, 5, "Jack Daniel"),
169 (9, 9, 15, "Obsession");
170
171INSERT INTO Enfant
172VALUES
173 (0, 0, "Dupont", "Sylvain", 3, 0),
174 (1, 0, "Beauchamp", "Mike", 2, 0),
175 (2, 0, "Deschamps", "Mike", 3, 0),
176 (3, 0, "Demers", "Mike", 2, 0),
177 (4, 0, "Deschaines", "Mike", 2, 0),
178 (5, 0, "Desche", "Mike", 1, 0),
179 (6, 0, "Dupont", "Mike", 2, 0),
180 (7, 1, "Leblanc", "Philippe", 4, 0),
181 (8, 1, "Labatt", "Philippe", 4, 0),
182 (9, 1, "Legolas", "Philippe", 4, 0),
183 (10, 1, "Letendre", "Philippe", 5, 0),
184 (11, 1, "Legendre", "Philippe", 2, 0),
185 (12, 1, "Légende", "Philippe", 5, 0),
186 (13, 1, "Langlois", "Philippe", 4, 0),
187 (14, 1, "Lalande", "Philippe", 3, 0),
188 (15, 2, "Bastien", "Gabriel", 1, 0),
189 (16, 2, "Benoit", "Gabriel", 1, 0),
190 (17, 2, "Baris", "Gabriel", 1, 0),
191 (18, 2, "Ballou", "Gabriel", 2, 0),
192 (19, 3, "Chamberland", "Kevin", 1, 0),
193 (20, 3, "Coquasse", "Kevin", 1, 0),
194 (21, 3, "Chalet", "Kevin", 3, 0),
195 (22, 3, "Chaud", "Kevin", 5, 0),
196 (23, 3, "Chaleur", "Kevin", 5, 1),
197 (24, 3, "Chiot", "Kevin", 4, 1),
198 (25, 3, "Casta", "Kevin", 1, 0),
199 (26, 3, "Coucou", "Kevin", 4, 1),
200 (27, 3, "Canberry", "Kevin", 5, 1),
201 (28, 3, "Cho", "Kevin", 2, 0),
202 (29, 3, "Cacon", "Kevin", 2, 0),
203 (30, 4, "Tremblay", "Olivier", 4, 0),
204 (31, 4, "Tetard", "Olivier", 5, 0),
205 (32, 4, "Toto", "Olivier", 2, 0),
206 (33, 4, "Tarla", "Olivier", 4, 0),
207 (34, 4, "Tattou", "Olivier", 5, 0),
208 (35, 5, "L'hermite", "Bernard", 5, 0),
209 (36, 5, "Leblanc", "Bernard", 2, 0),
210 (37, 5, "Roberge", "Bernard", 2, 0),
211 (38, 5, "Gravel", "Bernard", 2, 0),
212 (39, 5, "Bernadel", "Bernard", 2, 0),
213 (40, 5, "Bernier", "Bernard", 2, 0),
214 (41, 6, "Terrien", "Gaston", 2, 0),
215 (42, 6, "Chipie", "Gaston", 4, 0),
216 (43, 6, "Levac", "Gaston", 4, 0),
217 (44, 6, "Matte", "Gaston", 4, 0),
218 (45, 7, "Houde", "Denis", 5, 0),
219 (46, 7, "Rapide", "Denis", 2, 0),
220 (47, 7, "Corvette", "Denis", 1, 0),
221 (48, 7, "Fusée", "Denis", 1, 0),
222 (49, 7, "Lambo", "Denis", 1, 0),
223 (50, 8, "Taps", "Maxime", 3, 0),
224 (51, 8, "Marquis", "Maxime", 3, 0),
225 (52, 8, "Lajeunesse", "Maxime", 2, 0),
226 (53, 8, "Beaudry", "Maxime", 2, 0),
227 (54, 8, "Campeau", "Maxime", 4, 0),
228 (55, 8, "Voyer", "Maxime", 2, 0),
229 (56, 9, "Coldgate", "Benjamin", 1, 0),
230 (57, 9, "Crest", "Benjamin", 4, 0),
231 (58, 9, "Scope", "Benjamin", 3, 0),
232 (59, 9, "Listerine", "Benjamin", 5, 0),
233 (60, 9, "GUM", "Benjamin", 2, 0),
234 (61, 9, "Soie", "Benjamin", 1, 0),
235 (62, 9, "Dentaire", "Benjamin", 1, 0);
236
237INSERT INTO Parents
238VALUES
239 (0, "Dupont", "Pierre", "555-555-5555"),
240 (1, "Beauchamp", "Roger", "555-555-5555"),
241 (2, "Deschamps", "Roger", "555-555-5555"),
242 (3, "Demers", "Roger", "555-555-5555"),
243 (4, "Deschaines", "Roger", "555-555-5555"),
244 (5, "Desche", "Roger", "555-555-5555"),
245 (6, "Dupont", "Roger", "555-555-5555"),
246 (7, "Leblanc", "Mike", "555-555-5555"),
247 (8, "Labatt", "Mike", "555-555-5555"),
248 (9, "Legolas", "Mike", "555-555-5555"),
249 (10, "Letendre", "Mike", "555-555-5555"),
250 (11, "Legendre", "Mike", "555-555-5555"),
251 (12, "Légende", "Mike", "555-555-5555"),
252 (13, "Langlois", "Mike", "555-555-5555"),
253 (14, "Lalande", "Mike", "555-555-5555"),
254 (15, "Bastien", "Denis", "555-555-5555"),
255 (16, "Benoit", "Denis", "555-555-5555"),
256 (17, "Baris", "Denis", "555-555-5555"),
257 (18, "Ballou", "Denis", "555-555-5555"),
258 (19, "Chamberland", "Pierre", "555-555-5555"),
259 (20, "Coquasse", "Pierre", "555-555-5555"),
260 (21, "Chalet", "Pierre", "555-555-5555"),
261 (22, "Chaud", "Pierre", "555-555-5555"),
262 (23, "Chaleur", "Pierre", "555-555-5555"),
263 (24, "Chiot", "Pierre", "555-555-5555"),
264 (25, "Casta", "Pierre", "555-555-5555"),
265 (26, "Coucou", "Pierre", "555-555-5555"),
266 (27, "Canberry", "Pierre", "555-555-5555"),
267 (28, "Cho", "Pierre", "555-555-5555"),
268 (29, "Cacon", "Pierre", "555-555-5555"),
269 (30, "Tremblay", "Serge", "555-555-5555"),
270 (31, "Tetard", "Serge", "555-555-5555"),
271 (32, "Toto", "Serge", "555-555-5555"),
272 (33, "Tarla", "Serge", "555-555-5555"),
273 (34, "Tattou", "Serge", "555-555-5555"),
274 (35, "L'hermite", "Sylvain", "555-555-5555"),
275 (36, "Leblanc", "Sylvain", "555-555-5555"),
276 (37, "Roberge", "Sylvain", "555-555-5555"),
277 (38, "Gravel", "Sylvain", "555-555-5555"),
278 (39, "Bernadel", "Sylvain", "555-555-5555"),
279 (40, "Bernier", "Sylvain", "555-555-5555"),
280 (41, "Terrien", "Antoine", "555-555-5555"),
281 (42, "Chipie", "Antoine", "555-555-5555"),
282 (43, "Levac", "Antoine", "555-555-5555"),
283 (44, "Matte", "Antoine", "555-555-5555"),
284 (45, "Houde", "Gaetane", "555-555-5555"),
285 (46, "Rapide", "Gaetane", "555-555-5555"),
286 (47, "Corvette", "Gaetane", "555-555-5555"),
287 (48, "Fusée", "Gaetane", "555-555-5555"),
288 (49, "Lambo", "Gaetane", "555-555-5555"),
289 (50, "Taps", "Benoit", "555-555-5555"),
290 (51, "Marquis", "Benoit", "555-555-5555"),
291 (52, "Lajeunesse", "Benoit", "555-555-5555"),
292 (53, "Beaudry", "Benoit", "555-555-5555"),
293 (54, "Campeau", "Benoit", "555-555-5555"),
294 (55, "Voyer", "Benoit", "555-555-5555"),
295 (56, "Coldgate", "Josée", "555-555-5555"),
296 (57, "Crest", "Josée", "555-555-5555"),
297 (58, "Scope", "Josée", "555-555-5555"),
298 (59, "Listerine", "Josée", "555-555-5555"),
299 (60, "GUM", "Josée", "555-555-5555"),
300 (61, "Soie", "Josée", "555-555-5555"),
301 (62, "Dentaire", "Josée", "555-555-5555");
302
303INSERT INTO EnfantParent
304VALUES
305 (0, 0, 0),
306 (1, 1, 1),
307 (2, 2, 2),
308 (3, 3, 3),
309 (4, 4, 4),
310 (5, 5, 5),
311 (6, 6, 6),
312 (7, 7, 7),
313 (8, 8, 8),
314 (9, 9, 9),
315 (10, 10, 10),
316 (11, 11, 11),
317 (12, 12, 12),
318 (13, 13, 13),
319 (14, 14, 14),
320 (15, 15, 15),
321 (16, 16, 16),
322 (17, 17, 17),
323 (18, 18, 18),
324 (19, 19, 19),
325 (20, 20, 20),
326 (21, 21, 21),
327 (22, 22, 22),
328 (23, 23, 23),
329 (24, 24, 24),
330 (25, 25, 25),
331 (26, 26, 26),
332 (27, 27, 27),
333 (28, 28, 28),
334 (29, 29, 29),
335 (30, 30, 30),
336 (31, 31, 31),
337 (32, 32, 32),
338 (33, 33, 33),
339 (34, 34, 34),
340 (35, 35, 35),
341 (36, 36, 36),
342 (37, 37, 37),
343 (38, 38, 38),
344 (39, 39, 39),
345 (40, 40, 40),
346 (41, 41, 41),
347 (42, 42, 42),
348 (43, 43, 43),
349 (44, 44, 44),
350 (45, 45, 45),
351 (46, 46, 46),
352 (47, 47, 47),
353 (48, 48, 48),
354 (49, 49, 49),
355 (50, 50, 50),
356 (51, 51, 51),
357 (52, 52, 52),
358 (53, 53, 53),
359 (54, 54, 54),
360 (55, 55, 55),
361 (56, 56, 56),
362 (57, 57, 57),
363 (58, 58, 58),
364 (59, 59, 59),
365 (60, 60, 60),
366 (61, 61, 61),
367 (62, 62, 62);
368
369INSERT INTO Actionnaire
370VALUES
371 (0, "Roberge", "Ouimet", 0.25),
372 (1, "Rauson", "Gilbert", 0.25),
373 (2, "Salvail", "Éric", 0.25),
374 (3, "Harrison", "Francois", 0.25);
375
376
377
378-- -------------------------
379-- Fin Insertion des donnees
380-- -------------------------
381
382-- ---------------------
383-- Creation des requetes
384-- ---------------------
385
386--4
387SELECT e.prenom, e.nom, e.age
388FROM Enfant e
389LEFT JOIN Classe c ON c.id = e.classe_id
390LEFT JOIN Employe em ON em.id = c.employe_id
391LEFT JOIN Garderie g ON g.id = em.garderie_id
392WHERE g.nom = "Le Bonheur" AND e.subvention = 1 AND e.age >= 3 AND e.age <= 5;
393
394--5
395SELECT em.prenom, em.nom, em.salaire, em.telephone
396FROM Employe em
397LEFT JOIN Garderie g ON g.id = em.garderie_id
398WHERE g.adresse LIKE "%Montreal%";
399
400--6
401SELECT g.nom, c.local, g.adresse, COUNT(e.id) AS enfants
402FROM Enfant e
403LEFT JOIN Classe c ON c.id = e.classe_id
404LEFT JOIN Employe em ON em.id = c.employe_id
405LEFT JOIN Garderie g ON g.id = em.garderie_id
406WHERE COUNT(e.id) > 10 AND g.adresse LIKE "%Laval%"
407GROUP BY c.id;
408
409--7
410SELECT p.date, p.entree, p.sortie, em.prenom, em.nom, p.frais
411FROM Presence p
412LEFT JOIN Enfant e ON e.id = p.enfant_id
413LEFT JOIN Classe c ON c.id = e.classe_id
414LEFT JOIN Employe em ON em.id = c.employe_id
415LEFT JOIN Garderie g ON g.id = em.garderie_id
416WHERE e.prenom = "Sylvain" AND e.nom = "Dupont" AND g.nom = "Perdu dans l'espace" AND p.date > "31-08-19" AND p.date < "01-10-19";
417
418--9
419SELECT e.prenom, e.nom, pa.telephone, p.date, CONCAT(p.frais, "$")
420FROM Presence p
421LEFT JOIN Enfant e ON e.id = p.enfant_id
422LEFT JOIN EnfantParent ep ON ep.enfant_id = e.id
423LEFT JOIN Parent pa ON pa.id = ep.parent_id
424LEFT JOIN Classe c ON c.id = e.classe_id
425LEFT JOIN Employe em ON em.id = c.employe_id
426LEFT JOIN Garderie g ON g.id = em.garderie_id
427WHERE g.adresse LIKE "%Longueuil%" AND p.sortie = null;
428
429--10
430SELECT pa.prenom, pa.nom, pa.telephone, COUNT(ep.id) AS enfants
431FROM Parent pa
432LEFT JOIN EnfantParent ep ON ep.parent_id = pa.id
433LEFT JOIN Enfant e ON e.id = ep.enfant_id
434LEFT JOIN Classe c ON c.id = e.classe_id
435LEFT JOIN Employe em ON em.id = c.employe_id
436LEFT JOIN Garderie g ON g.id = em.garderie_id
437WHERE COUNT(ep.id) > 2 AND g.adresse LIKE "%Boucherville%";
438
439--11
440SELECT e.prenom, e.nom, g.nom
441FROM Absence ab
442LEFT JOIN Enfant e ON e.id = ab.enfant_id
443LEFT JOIN Classe c ON c.id = e.classe_id
444LEFT JOIN Employe em ON em.id = c.employe_id
445LEFT JOIN Garderie g ON g.id = em.garderie_id
446WHERE ab.date = CURRENT_DATE;
447
448
449-- -----------------
450-- Fin des requetes
451-- -----------------