s12QtwQD

1-- Konstantin Mihaylov N12 & Peter Mihaylov N16 XIIb 
2DROP DATABASE demo_db;
3
4CREATE DATABASE demo_db;
5USE demo_db;
6
7-- TABLES
8CREATE TABLE Person (
9	id INTEGER AUTO_INCREMENT NOT NULL, 
10	p_name VARCHAR(50) NOT NULL,
11    age INTEGER,
12    avg_weight FLOAT DEFAULT 0,
13    gender ENUM('M', 'F'),
14    
15    PRIMARY KEY (id)
16);
17
18CREATE TABLE Weight_data (
19	id INTEGER NOT NULL, 
20	created_at DATETIME,
21    weight FLOAT,
22    p_id INTEGER,
23    
24    PRIMARY KEY (id),
25    FOREIGN KEY (p_id) REFERENCES Person(id)
26);
27
28INSERT INTO Person (id, p_name, age, gender) VALUES (1, 'Ivan', 18, 'M');
29INSERT INTO Person (id, p_name, age, gender) VALUES (2, 'Beatris', 18, 'F');
30INSERT INTO Person (id, p_name, age, gender) VALUES (3, 'Fea', 15, 'F');
31
32
33-- INSERT
34
35DROP TRIGGER IF EXISTS person_weight_insert;
36
37CREATE TRIGGER person_weight_insert AFTER INSERT ON Weight_data
38FOR EACH ROW
39	UPDATE Person SET avg_weight = 
40		(SELECT AVG(weight) FROM Weight_data WHERE p_id = NEW.p_id)
41    WHERE NEW.p_id = Person.id;
42    
43INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (1, NOW(), 70, 1);
44INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (2, DATE_FORMAT(NOW() ,'%Y-01-01'), 80, 1);
45INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (6, DATE_FORMAT(NOW() ,'%Y-12-31'), 75, 1);
46INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (7, DATE_FORMAT(NOW() ,'%Y-01-01'), 35, 2);
47INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (8, DATE_FORMAT(NOW() ,'%Y-12-31'), 65, 2);
48INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (3, DATE_SUB(NOW(), INTERVAL 2 MONTH), 60, 1);
49
50INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (4, NOW(), 60, 2);
51INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (5, NOW(), 40, 3);
52
53SELECT * FROM Person;
54
55
56-- UPDATE
57
58DROP TRIGGER IF EXISTS person_weight_update;
59CREATE TRIGGER person_weight_update AFTER UPDATE ON Weight_data
60FOR EACH ROW
61	UPDATE Person SET avg_weight = 
62		(SELECT AVG(weight) FROM Weight_data WHERE p_id = NEW.p_id)
63    WHERE NEW.p_id = Person.id;
64    
65UPDATE Weight_data
66 SET   weight = 90
67 WHERE p_id = 3;
68
69SELECT * FROM Person;
70
71
72-- AVG WEIGHT
73
74SELECT id, AVG(weight) 
75FROM Weight_data 
76WHERE p_id = 1 AND created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
77GROUP BY id;
78
79
80-- WEIGHT FROM BEGINNIG AND END OF YEAR
81-- This example shows the results of using the ABS function on three different numbers.
82SELECT start_of_year.p_id, ABS(start_of_year.weight - end_of_year.weight)
83FROM Weight_data as start_of_year 
84LEFT JOIN Weight_data as end_of_year ON 
85end_of_year.created_at = DATE_FORMAT(NOW() ,'%Y-12-31')
86AND start_of_year.p_id = end_of_year.p_id
87WHERE start_of_year.created_at = DATE_FORMAT(NOW() ,'%Y-01-01')
88GROUP BY start_of_year.p_id, ABS(start_of_year.weight - end_of_year.weight);
89
90
91-- AVG MEN
92
93SELECT AVG(avg_weight) 
94FROM Person
95WHERE gender = 'M';
96
97
98-- AVG FEMALE
99SELECT AVG(avg_weight) 
100FROM Person
101WHERE gender = 'F';