· 5 years ago · Oct 10, 2020, 02:42 AM
1AUTO INCREMENT (1)
2/*
3Creating Establishment Table
4*/
5CREATE TABLE IF NOT EXISTS `mydb`.`establishment` (
6 `id` INT NOT NULL AUTO_INCREMENT,
7 `name` VARCHAR(45) NULL,
8 `duration` INT NULL,
9 `is_airconditioned` TINYINT NULL,
10 `location_id` INT NOT NULL,
11 `establishment_category_id` INT NOT NULL,
12 PRIMARY KEY (`id`),
13 INDEX `fk_establishment_location1_idx` (`location_id` ASC) VISIBLE,
14 INDEX `fk_establishment_establishment_category1_idx` (`establishment_category_id` ASC) VISIBLE,
15 CONSTRAINT `fk_establishment_location1`
16 FOREIGN KEY (`location_id`)
17 REFERENCES `mydb`.`location` (`id`)
18 ON DELETE NO ACTION
19 ON UPDATE NO ACTION,
20 CONSTRAINT `fk_establishment_establishment_category1`
21 FOREIGN KEY (`establishment_category_id`)
22 REFERENCES `mydb`.`establishment_category` (`id`)
23 ON DELETE NO ACTION
24 ON UPDATE NO ACTION)
25ENGINE = InnoDB;
26------------------------------------------------------------------------------
27CREATE INDEX (1)
28
29/*
30Creating Address Table
31*/
32CREATE TABLE IF NOT EXISTS `mydb`.`address` (
33 `house_no` INT NULL,
34 `lot` VARCHAR(45) NULL,
35 `barangay` VARCHAR(45) NULL,
36 `street` VARCHAR(45) NULL,
37 `building` VARCHAR(45) NULL,
38 `municipality` VARCHAR(45) NULL,
39 `city` VARCHAR(45) NULL,
40 `province` VARCHAR(45) NULL,
41 `region` VARCHAR(45) NULL,
42 `person_id` INT NOT NULL,
43 INDEX `fk_address_person_idx` (`person_id` ASC) VISIBLE,
44 CONSTRAINT `fk_address_person`
45 FOREIGN KEY (`person_id`)
46 REFERENCES `mydb`.`person` (`id`)
47 ON DELETE NO ACTION
48 ON UPDATE NO ACTION)
49ENGINE = InnoDB;
50------------------------------------------------------------------------------
51/*
52FULLTEXT (1)
53
54Creating Person Table
55*/
56CREATE TABLE IF NOT EXISTS `mydb`.`person` (
57 `id` INT NOT NULL AUTO_INCREMENT,
58 `first_name` VARCHAR(45) NULL,
59 `last_name` VARCHAR(45) NULL,
60 `suffix` VARCHAR(45) NULL,
61 `nationality` VARCHAR(45) NULL,
62 `birthday` DATE NULL,
63 `gender` VARCHAR(45) NULL,
64 `civil_status` VARCHAR(45) NULL,
65 PRIMARY KEY (`id`),
66 FULLTEXT INDEX `full_name` (`first_name`, `last_name`, `suffix`) INVISIBLE)
67ENGINE = InnoDB;
68
69/* Searching */
70SELECT * FROM person WHERE MATCH(first_name, last_name, suffix) AGAINST('Mabilin')
71------------------------------------------------------------------------------
72/*
73JOINS (INNER,LEFT OUTER, RIGHT OUTER, SELF) (4)
74
75Maximum number of establishment occuring in the same place
76*/
77SELECT MAX(establishment.location_id)
78FROM location
79INNER JOIN establishment
80ON location.id = establishment.location_id
81HAVING COUNT(location.id) > 1;
82
83/* Check the number of current recorded disease */
84SELECT disease.name
85FROM disease
86LEFT JOIN person_disease
87ON disease.id = person_disease.disease_id
88GROUP BY disease.name
89
90/* Display all person that has Novel Coronavirus Cases */
91SELECT COUNT(*)
92FROM person
93LEFT JOIN person_disease
94ON person.id = person_disease.person_id
95RIGHT JOIN disease
96ON person_disease.disease_id = disease.id
97WHERE disease.name = 'Novel Coronavirus'
98AND person_disease.datetime <= DATE_SUB(NOW(), INTERVAL 6 MONTH);
99
100/* Check all person with the same last name */
101SELECT A.first_name , A.last_name
102FROM person A, person B
103WHERE A.id <> B.id
104AND A.last_name = B.last_name
105GROUP BY A.first_name
106------------------------------------------------------------------------------
107/*
108UNION (1)
109
110Get All Travel History
111*/
112SELECT all_person.person_id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
113FROM
114(SELECT person_id, datetime, temperature, duration
115FROM person_establishment
116UNION
117SELECT person_id, datetime, temperature, duration
118FROM person_event
119UNION
120SELECT person_id, datetime, temperature, duration
121FROM person_transportation) AS all_person
122INNER JOIN person
123ON all_person.person_id = person.id
124LEFT JOIN person_disease
125ON person.id = person_disease.person_id
126LEFT JOIN disease
127ON person_disease.disease_id = disease.id
128LEFT JOIN classification
129ON person_disease.classification_id = classification.id
130GROUP BY all_person.person_id
131------------------------------------------------------------------------------
132/*
133MINUS/NOT IN (1)
134
135Display person that has no disease
136*/
137SELECT person.first_name, person.last_name
138FROM person
139WHERE person.id NOT IN (SELECT person_disease.person_id FROM person_disease)
140------------------------------------------------------------------------------
141/*
142EXISTS/IN (1)
143
144Get All Person that attended an event with temperature of greater than 36
145*/
146SELECT person.id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
147FROM person
148LEFT JOIN person_disease
149ON person.id = person_disease.person_id
150LEFT JOIN disease
151ON person_disease.disease_id = disease.id
152LEFT JOIN classification
153ON person_disease.classification_id = classification.id
154WHERE EXISTS (SELECT * FROM person_event WHERE person_event.person_id = person.id AND temperature > 36)
155------------------------------------------------------------------------------
156/*
157SUBQUERY (FROM Clause, WHERE clause ) (2)
158
159Get All Travel History
160*/
161SELECT all_person.person_id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
162FROM
163(SELECT person_id, datetime, temperature, duration
164FROM person_establishment
165UNION
166SELECT person_id, datetime, temperature, duration
167FROM person_event
168UNION
169SELECT person_id, datetime, temperature, duration
170FROM person_transportation) AS all_person
171INNER JOIN person
172ON all_person.person_id = person.id
173LEFT JOIN person_disease
174ON person.id = person_disease.person_id
175LEFT JOIN disease
176ON person_disease.disease_id = disease.id
177LEFT JOIN classification
178ON person_disease.classification_id = classification.id
179GROUP BY all_person.person_id
180
181/* Get All Person that attended an event with temperature of greater than 36 */
182SELECT person.id, person.first_name, person.last_name, person.suffix, disease.name as disease, classification.name as classification
183FROM person
184LEFT JOIN person_disease
185ON person.id = person_disease.person_id
186LEFT JOIN disease
187ON person_disease.disease_id = disease.id
188LEFT JOIN classification
189ON person_disease.classification_id = classification.id
190WHERE EXISTS (SELECT * FROM person_event WHERE person_event.person_id = person.id AND temperature > 36)
191------------------------------------------------------------------------------
192/*
193CASE (1)
194
195Checking temperature status of a person who entered the establishment
196*/
197SELECT person.first_name, person.last_name, person.suffix,
198 CASE
199 WHEN person_establishment.temperature > 36 THEN "Not normal"
200 ELSE "Normal"
201 END AS temperature_status
202FROM person
203LEFT JOIN person_establishment
204ON person.id = person_establishment.person_id
205RIGHT JOIN establishment
206ON person_establishment.establishment_id = establishment.id
207------------------------------------------------------------------------------
208/*
209SELECT INTO/INSERT INTO SELECT (1)
210
211Clone Location to Person Address
212*/
213INSERT INTO `mydb`.`address` (`house_no`, `lot`, `barangay`, `street`, `building`, `municipality`, `city`, `province`, `region`, `person_id`)
214SELECT house_no, lot, barangay, street, building, municipality, city, province, region, person.id as person_id
215FROM location, person
216WHERE location.id = location_id AND person.id = person_id;
217------------------------------------------------------------------------------
218/*
219VIEWS (2)
220
221Views for all Suspected Person
222*/
223USE `mydb`;
224CREATE OR REPLACE VIEW `suspected_person` AS
225SELECT person.first_name, person.last_name, person.suffix
226FROM person
227LEFT JOIN person_disease
228ON person.id = person_disease.person_id
229LEFT JOIN classification
230ON person_disease.classification_id = classification.id
231WHERE classification.name = 'Suspected';
232
233USE `mydb`;
234CREATE OR REPLACE VIEW `probable_person` AS
235SELECT person.first_name, person.last_name, person.suffix
236FROM person
237LEFT JOIN person_disease
238ON person.id = person_disease.person_id
239LEFT JOIN classification
240ON person_disease.classification_id = classification.id
241WHERE classification.name = 'Probable';
242
243USE `mydb`;
244CREATE OR REPLACE VIEW `confirmed_person` AS
245SELECT person.first_name, person.last_name, person.suffix
246FROM person
247LEFT JOIN person_disease
248ON person.id = person_disease.person_id
249LEFT JOIN classification
250ON person_disease.classification_id = classification.id
251WHERE classification.name = 'Confirmed';
252------------------------------------------------------------------------------
253/* TRIGGERS (4) */
254
255DELIMITER $$
256USE `mydb`$$
257CREATE DEFINER=`root`@`localhost` TRIGGER `person_BEFORE_DELETE` BEFORE DELETE ON `person` FOR EACH ROW BEGIN
258DELETE FROM `mydb`.`person_disease` WHERE (`person_disease`.`person_id` = old.id);
259DELETE FROM `mydb`.`person_establishment` WHERE (`person_establishment`.`person_id` = old.id);
260DELETE FROM `mydb`.`person_event` WHERE (`person_event`.`person_id` = old.id);
261DELETE FROM `mydb`.`person_transportation` WHERE (`person_transportation`.`person_id` = old.id);
262END$$
263DELIMITER ;
264
265DELIMITER $$
266USE `mydb`$$
267CREATE DEFINER=`root`@`localhost` TRIGGER `disease_BEFORE_DELETE` BEFORE DELETE ON `disease` FOR EACH ROW BEGIN
268 DELETE FROM `mydb`.`person_disease` WHERE (`person_disease`.`disease_id` = old.id);
269END$$
270DELIMITER ;
271
272DELIMITER $$
273USE `mydb`$$
274CREATE DEFINER=`root`@`localhost` TRIGGER `establishment_BEFORE_DELETE` BEFORE DELETE ON `establishment` FOR EACH ROW BEGIN
275 DELETE FROM `mydb`.`person_establishment` WHERE (`person_establishment`.`establishment_id` = old.id);
276END$$
277DELIMITER ;
278
279DELIMITER $$
280USE `mydb`$$
281CREATE DEFINER=`root`@`localhost` TRIGGER `event_BEFORE_DELETE` BEFORE DELETE ON `event` FOR EACH ROW BEGIN
282 DELETE FROM `mydb`.`person_event` WHERE (`person_event`.`event_id` = old.id);
283END$$
284DELIMITER ;
285
286------------------------------------------------------------------------------
287/* STORED PROCEDURES (4)
288
289Clone Location to Person Address
290*/
291DELIMITER $$
292USE `mydb`$$
293CREATE DEFINER=`root`@`localhost` PROCEDURE `clone_location_to_person_address`(IN location_id int(45), person_id int(45))
294BEGIN
295 INSERT INTO `mydb`.`address` (`house_no`, `lot`, `barangay`, `street`, `building`, `municipality`, `city`, `province`, `region`, `person_id`)
296 SELECT house_no, lot, barangay, street, building, municipality, city, province, region, person.id as person_id
297 FROM location, person
298 WHERE location.id = location_id AND person.id = person_id;
299END$$
300
301/* Generate Daily Reports */
302DELIMITER $$
303USE `mydb`$$
304CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_daily_report`()
305BEGIN
306 SELECT disease.name as disease, classification.name as classification, COUNT(person_disease.person_id) as total
307 FROM person
308 LEFT JOIN person_disease
309 ON person.id = person_disease.person_id
310 RIGHT JOIN disease
311 ON person_disease.disease_id = disease.id
312 RIGHT JOIN classification
313 ON person_disease.classification_id = classification.id
314 WHERE DATE(person_disease.datetime) = CURDATE()
315 GROUP BY classification.id;
316END$$
317
318
319/* Generate Disease Reports */
320DELIMITER $$
321USE `mydb`$$
322CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_disease_report`()
323BEGIN
324 SELECT disease.name as disease, classification.name as classification, COUNT(person_disease.person_id) as total
325 FROM person
326 LEFT JOIN person_disease
327 ON person.id = person_disease.person_id
328 RIGHT JOIN disease
329 ON person_disease.disease_id = disease.id
330 RIGHT JOIN classification
331 ON person_disease.classification_id = classification.id
332 GROUP BY classification.id;
333END$$
334
335
336/* Generate Disease Reports by Municipality */
337DELIMITER $$
338USE `mydb`$$
339CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_disease_report_municipality`()
340BEGIN
341 SELECT address.municipality as municipality, disease.name as disease, classification.name as classification, COUNT(classification.id) as total
342 FROM person_disease
343 RIGHT JOIN disease
344 ON person_disease.disease_id = disease.id
345 RIGHT JOIN classification
346 ON person_disease.classification_id = classification.id
347 RIGHT JOIN address
348 ON person_disease.person_id = address.person_id
349 GROUP BY municipality, classification
350 ORDER BY municipality;
351END$$
352------------------------------------------------------------------------------
353/*
354STORED FUNCTIONS/USER-DEFINED FUNCTIONS (UDFs) (4)
355
356Person Diseases
357*/
358USE `mydb`;
359DROP procedure IF EXISTS `person_disease`;
360
361DELIMITER $$
362USE `mydb`$$
363CREATE DEFINER=`root`@`localhost` PROCEDURE `person_disease`(IN disease char(45), classification char(45))
364BEGIN
365 SELECT person.first_name, person.last_name, person.suffix, disease.name AS disease, classification.name AS classification
366 FROM person
367 LEFT JOIN person_disease
368 ON person.id = person_disease.person_id
369 RIGHT JOIN disease
370 ON person_disease.disease_id = disease.id
371 RIGHT JOIN classification
372 ON person_disease.classification_id = classification.id
373 WHERE disease.name = disease AND classification.name = classification;
374END$$
375
376DELIMITER ;
377
378/* Disease Classification */
379USE `mydb`;
380DROP procedure IF EXISTS `disease_classification`;
381
382DELIMITER $$
383USE `mydb`$$
384CREATE PROCEDURE `disease_classification`
385(IN disease char(45))
386BEGIN
387 SELECT disease.name AS disease, classification.name as classification
388 FROM disease
389 LEFT JOIN disease_classification
390 ON disease.id = disease_classification.disease_id
391 RIGHT JOIN classification
392 ON disease_classification.classification_id = classification.id
393WHERE disease.name = disease;
394END$$
395
396DELIMITER ;
397
398/* Event */
399USE `mydb`;
400DROP procedure IF EXISTS `person_event`;
401
402DELIMITER $$
403USE `mydb`$$
404CREATE PROCEDURE `person_event` (IN event char(45))
405BEGIN
406 SELECT person_event.datetime, person.first_name, person.last_name, person.suffix, event.name AS event, event.duration AS event_duration, person_event.temperature, person_event.duration
407 FROM person
408 LEFT JOIN person_event
409 ON person.id = person_event.person_id
410 RIGHT JOIN event
411 ON person_event.event_id = event.id
412 WHERE event.name = event;
413END$$
414
415DELIMITER ;
416
417/* Establishment */
418USE `mydb`;
419DROP procedure IF EXISTS `person_establishment`;
420
421DELIMITER $$
422USE `mydb`$$
423CREATE PROCEDURE `person_establishment` (IN establishment char(45))
424BEGIN
425 SELECT person_establishment.datetime,
426 person.first_name,
427 person.last_name,
428 person.suffix,
429 establishment.name AS establishment,
430 establishment_category.name AS establishment_category,
431 establishment_category_type.name AS establishment_category_type,
432 person_establishment.temperature,
433 person_establishment.duration
434 FROM person
435 LEFT JOIN person_establishment
436 ON person.id = person_establishment.person_id
437 RIGHT JOIN establishment
438 ON person_establishment.establishment_id = establishment.id
439 LEFT JOIN establishment_category_type
440 ON establishment.establishment_category_type_id = establishment_category_type.id
441 LEFT JOIN establishment_category
442 ON establishment_category_type.establishment_category_id = establishment_category.id
443 WHERE establishment.name = establishment;
444END$$
445
446DELIMITER ;
447------------------------------------------------------------------------------
448/*
449NATIVE FUNCTIONS (COUNT(),MAX()/MIN(),UCASE/LCASE,NOW()) (4)
450
451Display the total number of Novel Coronavirus Cases
452*/
453SELECT COUNT(*)
454FROM person
455LEFT JOIN person_disease
456ON person.id = person_disease.person_id
457RIGHT JOIN disease
458ON person_disease.disease_id = disease.id
459WHERE disease.name = 'Novel Coronavirus'
460AND person_disease.datetime <= DATE_SUB(NOW(), INTERVAL 6 MONTH);
461
462/* Maximum number of establishment occuring in the same place */
463SELECT MAX(establishment.location_id)
464FROM location
465INNER JOIN establishment
466ON location.id = establishment.location_id
467HAVING COUNT(location.id) > 1;
468
469/* Display classification in uppercase */
470SELECT UCASE(classification.name)
471FROM classification;
472
473/* Display all person that has Novel Coronavirus Cases in the 6 months */
474SELECT COUNT(*)
475FROM person
476LEFT JOIN person_disease
477ON person.id = person_disease.person_id
478RIGHT JOIN disease
479ON person_disease.disease_id = disease.id
480WHERE disease.name = 'Novel Coronavirus'
481AND person_disease.datetime <= DATE_SUB(NOW(), INTERVAL 6 MONTH);
482------------------------------------------------------------------------------
483GROUP BY (1)
484
485/* Check the current recorded disease */
486SELECT disease.name, COUNT(disease.id) AS cases
487FROM disease
488LEFT JOIN person_disease
489ON disease.id = person_disease.disease_id
490GROUP BY disease.name
491------------------------------------------------------------------------------
492HAVING (1)
493
494/* Check the count of establishment occuring on the same location */
495SELECT establishment.name
496FROM establishment
497INNER JOIN location
498ON establishment.location_id = location.id
499HAVING COUNT(location.id) > 1;
500------------------------------------------------------------------------------