AmMMyiLK

1CREATE TABLE IF NOT EXISTS measuring_types (
2    id INT PRIMARY KEY AUTO_INCREMENT,
3    title VARCHAR(255) NOT NULL,
4    units_measuring_id INT
5);
6
7CREATE TABLE IF NOT EXISTS units_measuring (
8    id INT PRIMARY KEY AUTO_INCREMENT,
9    title VARCHAR(255) NOT NULL,
10    measuring_type_id INT
11);
12
13ALTER TABLE measuring_types
14ADD FOREIGN KEY (units_measuring_id) REFERENCES units_measuring(id);
15
16ALTER TABLE units_measuring
17ADD FOREIGN KEY (measuring_type_id) REFERENCES measuring_types(id);
18
19CREATE TABLE IF NOT EXISTS station (
20    id INT PRIMARY KEY AUTO_INCREMENT,
21    station_name VARCHAR(255) NOT NULL,
22    address VARCHAR(255) DEFAULT 'Russia'
23);
24
25CREATE TABLE IF NOT EXISTS measuring (
26    id INT PRIMARY KEY AUTO_INCREMENT,
27    measuring_type_id INT,
28    station_id INT,
29    measuring_value Int,
30    measuring_datetime Datetime,
31
32    FOREIGN KEY (measuring_type_id) REFERENCES measuring_types (id),
33    FOREIGN KEY (station_id) REFERENCES station (id)
34);
35
36INSERT station(station_name)
37VALUES
38       ('name1'),
39       ('name2'),
40       ('name3');
41
42INSERT measuring_types(title)
43VALUES
44       ('Градусы'),
45       ('type2');
46INSERT units_measuring(title)
47VALUES
48       ('Цельсия'),
49       ('unit2');
50
51UPDATE measuring_types t
52SET units_measuring_id = (SELECT id FROM units_measuring u WHERE u.title = 'Цельсия')
53WHERE title = 'Градусы';
54
55UPDATE measuring_types t
56SET units_measuring_id = (SELECT id FROM units_measuring u WHERE u.title = 'unit2')
57WHERE title = 'type2';
58
59UPDATE units_measuring u
60SET measuring_type_id = (SELECT id FROM measuring_types t WHERE  t.title = 'Градусы')
61WHERE title = 'Цельсия';
62
63UPDATE units_measuring u
64SET measuring_type_id = (SELECT id FROM measuring_types t WHERE  t.title = 'type2')
65WHERE title = 'unit2';
66
67INSERT measuring(measuring_type_id, station_id, measuring_value, measuring_datetime)
68VALUES
69       (1, 1, 1, '1989-4-4'),
70       (1, 1, 2, '1989-4-4'),
71       (2, 1, 1, '1989-4-4'),
72       (2, 1, 2, '1989-4-4');
73
74
75SELECT DISTINCT measuring_datetime
76FROM measuring
77ORDER BY measuring_datetime;
78
79
80SELECT measuring_value, measuring_datetime
81FROM measuring
82WHERE measuring_type_id = (SELECT id FROM measuring_types WHERE title = 'Градусы');
83
84
85SELECT measuring_type_id, MAX(measuring_value) max_value, MIN(measuring_value) min_value
86FROM measuring
87GROUP BY measuring_type_id;
88
89
90SELECT measuring_datetime
91FROM measuring m, measuring_types t
92WHERE
93      m.measuring_type_id = t.id AND
94      m.measuring_value = (
95          SELECT MIN(m1.measuring_value)
96          FROM measuring m1
97          WHERE m.measuring_type_id = t.id
98          );
99
100SELECT t.title, u.title
101FROM measuring_types t
102JOIN units_measuring u
103    ON t.units_measuring_id = u.measuring_type_id;
104SELECT t.title, u.title
105FROM measuring_types t, units_measuring u
106WHERE t.units_measuring_id = u.measuring_type_id;
107
108
109SELECT measuring_type_id, AVG(measuring_value), measuring_datetime, station_id
110FROM measuring
111WHERE measuring_datetime = '1989-4-4'
112GROUP BY measuring_type_id, station_id;