5dkamdRi

1DROP DATABASE IF EXISTS database_name;
2CREATE DATABASE database_name;
3USE database_name;
4
5CREATE TABLE person (
6	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    firstname VARCHAR(30) NOT NULL,
8    age INT UNSIGNED NOT NULL,
9    average_weight FLOAT(3, 1) DEFAULT 0, 
10    gender ENUM('M', 'F')
11);
12
13CREATE TABLE weight_data (
14	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
15    person_id INT UNSIGNED NOT NULL,
16    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
17	weight FLOAT(3, 1) NOT NULL,
18    FOREIGN KEY (person_id) REFERENCES person(id)
19);
20
21INSERT INTO person (firstname, age, gender) VALUES ('Peter Geshov', 18, 'M'), ('Maria Koceva', 21, 'F');
22
23INSERT INTO weight_data (person_id, weight) VALUES (1, 72.5), (2, 55.3);
24
25SELECT * FROM person;
26
27SELECT * FROM weight_data;
28
29SELECT AVG(weight) AS AverageWeight FROM weight_data
30INNER JOIN person
31WHERE person.id = weight_data.person_id && person.gender = 'M';