0LQKc7Zp

1DROP DATABASE IF EXISTS WeightDB;
2CREATE DATABASE WeightDB;
3
4USE WeightDB;
5
6CREATE TABLE person(
7	id INTEGER AUTO_INCREMENT PRIMARY KEY,
8    p_name VARCHAR(50) NOT NULL,
9    average_weight INTEGER DEFAULT 0,
10    gender ENUM('M', 'F') NOT NULL
11);
12
13CREATE TABLE weight_data(
14	id INTEGER AUTO_INCREMENT PRIMARY KEY,
15    person_id INTEGER,
16    weight INTEGER DEFAULT 0,
17    FOREIGN KEY (person_id) REFERENCES person(id),
18    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
19);
20
21
22DELIMITER $$
23CREATE TRIGGER calculate_avg 
24	AFTER INSERT ON weight_data
25	FOR EACH ROW
26BEGIN
27	UPDATE person
28    SET average_weight = (SELECT AVG(weight) FROM weight_data
29						  WHERE person.id = weight_data.person_id)
30    WHERE id = New.person_id;
31END$$
32DELIMITER ;
33
34INSERT INTO person(p_name, gender) VALUES('Ivancho', 'M');
35INSERT INTO person(p_name, gender) VALUES('Mariika', 'F');
36
37INSERT INTO weight_data(person_id, weight) VALUES(1, 70);
38INSERT INTO weight_data(person_id, weight) VALUES(1, 80);
39INSERT INTO weight_data(person_id, weight) VALUES(1, 80);
40
41INSERT INTO weight_data(person_id, weight) VALUES(2, 45);
42INSERT INTO weight_data(person_id, weight) VALUES(2, 50);
43
44SELECT * FROM weight_data;
45SELECT * FROM person;
46
47SELECT AVG(wd.weight) as 'Average Weight(1 month)' FROM person p
48JOIN weight_data wd
49WHERE wd.created_at >= DATE_SUB(NOW(), INTERVAL 30 day) AND p.id = 1;
50
51SELECT ABS(
52	(SELECT weight FROM weight_data WHERE person_id = 1 ORDER BY weight ASC LIMIT 1) - 
53    (SELECT weight FROM weight_data WHERE person_id = 1 ORDER BY weight DESC LIMIT 1)
54);
55
56SELECT AVG(p.average_weight) as 'Avg female' FROM person p
57WHERE p.gender = 'F';
58
59SELECT AVG(p.average_weight) as 'Avg male' FROM person p
60WHERE p.gender = 'M';