JxmH1WfK

1#12B class, Kaloyan Madjunov, Simeon Chakarov
2DROP DATABASE IF EXISTS Class3;
3CREATE DATABASE Class3;
4USE Class3;
5
6CREATE TABLE Person(
7	id INT PRIMARY KEY AUTO_INCREMENT,
8    name VARCHAR(30) NOT NULL,
9	age INT NOT NULL,
10	average_weight NUMERIC(5, 2) DEFAULT 0 NOT NULL,
11    gender ENUM('M', 'F') NOT NULL
12);
13
14CREATE TABLE Weight_data(
15	id INT PRIMARY KEY AUTO_INCREMENT,
16	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
17	weight NUMERIC(5, 2) NOT NULL,
18    person_id INT NOT NULL,
19    
20    FOREIGN KEY (Person_id) REFERENCES Person(id)
21);
22
23#Task 6
24
25#DROP TRIGGER IF EXISTS calc_average_weight_of_person;
26
27DELIMITER $$
28    
29CREATE TRIGGER calc_average_weight_of_person AFTER INSERT
30ON Weight_data
31FOR EACH ROW
32BEGIN
33    UPDATE Person AS p
34    SET p.average_weight = (
35		SELECT AVG(w.weight)
36        FROM Weight_data AS w
37		WHERE new.person_id = w.person_id
38    )
39    WHERE new.person_id = p.id;
40END$$
41DELIMITER ;
42
43INSERT INTO Person(name, age, gender) VALUES ("Simeon", 18, 'M');
44INSERT INTO Weight_data(weight, person_id) VALUES (80, 1);
45SELECT SLEEP(1);
46INSERT INTO Weight_data(weight, person_id) VALUES (78, 1);
47
48INSERT INTO Person(name, age, gender) VALUES ("Julie", 18, 'F');
49INSERT INTO Weight_data(weight, person_id) VALUES (45, 2);
50
51#Task 7
52SELECT p.name, AVG(w.weight) AS Average_weight
53FROM Person AS p
54LEFT JOIN Weight_data AS w
55ON p.id = w.person_id
56WHERE p.id = 1 AND w.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
57GROUP BY p.id, p.name;
58
59#Task 8
60SELECT (
61	SELECT wd.weight
62	FROM Weight_data AS wd
63	LEFT JOIN Person AS p
64	ON p.id = wd.person_id
65	WHERE p.id = 1
66	ORDER BY wd.created_at ASC
67	LIMIT 1
68) - (
69	SELECT wd.weight
70	FROM Weight_data AS wd
71	LEFT JOIN Person AS p
72	ON p.id = wd.person_id
73	WHERE p.id = 1
74	ORDER BY wd.created_at DESC
75	LIMIT 1
76) AS Weight_difference_from_start_and_end_of_year;
77
78
79#Task 9
80SELECT p.name, AVG(w.weight) AS Average_weight
81FROM Person AS p
82LEFT JOIN Weight_data AS w
83ON p.id = w.person_id
84WHERE p.gender = 'F'
85GROUP BY p.id, p.name;
86
87#Task 10
88SELECT p.name, AVG(w.weight) AS Average_weight
89FROM Person AS p
90LEFT JOIN Weight_data AS w
91ON p.id = w.person_id
92WHERE p.gender = 'M'
93GROUP BY p.id, p.name;