CREATE TABLE IF NOT EXISTS measuring_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    units_measuring_id INT
);

CREATE TABLE IF NOT EXISTS units_measuring (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    measuring_type_id INT
);

ALTER TABLE measuring_types
ADD FOREIGN KEY (units_measuring_id) REFERENCES units_measuring(id);

ALTER TABLE units_measuring
ADD FOREIGN KEY (measuring_type_id) REFERENCES measuring_types(id);

CREATE TABLE IF NOT EXISTS station (
    id INT PRIMARY KEY AUTO_INCREMENT,
    station_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) DEFAULT 'Russia'
);

CREATE TABLE IF NOT EXISTS measuring (
    id INT PRIMARY KEY AUTO_INCREMENT,
    measuring_type_id INT,
    station_id INT,
    measuring_value Int,
    measuring_datetime Datetime,

    FOREIGN KEY (measuring_type_id) REFERENCES measuring_types (id),
    FOREIGN KEY (station_id) REFERENCES station (id)
);

INSERT station(station_name)
VALUES
       ('name1'),
       ('name2'),
       ('name3');

INSERT measuring_types(title)
VALUES
       ('Градусы'),
       ('type2');
INSERT units_measuring(title)
VALUES
       ('Цельсия'),
       ('unit2');

UPDATE measuring_types t
SET units_measuring_id = (SELECT id FROM units_measuring u WHERE u.title = 'Цельсия')
WHERE title = 'Градусы';

UPDATE measuring_types t
SET units_measuring_id = (SELECT id FROM units_measuring u WHERE u.title = 'unit2')
WHERE title = 'type2';

UPDATE units_measuring u
SET measuring_type_id = (SELECT id FROM measuring_types t WHERE  t.title = 'Градусы')
WHERE title = 'Цельсия';

UPDATE units_measuring u
SET measuring_type_id = (SELECT id FROM measuring_types t WHERE  t.title = 'type2')
WHERE title = 'unit2';

INSERT measuring(measuring_type_id, station_id, measuring_value, measuring_datetime)
VALUES
       (1, 1, 1, '1989-4-4'),
       (1, 1, 2, '1989-4-4'),
       (2, 1, 1, '1989-4-4'),
       (2, 1, 2, '1989-4-4');


SELECT DISTINCT measuring_datetime
FROM measuring
ORDER BY measuring_datetime;


SELECT measuring_value, measuring_datetime
FROM measuring
WHERE measuring_type_id = (SELECT id FROM measuring_types WHERE title = 'Градусы');


SELECT measuring_type_id, MAX(measuring_value) max_value, MIN(measuring_value) min_value
FROM measuring
GROUP BY measuring_type_id;


SELECT measuring_datetime
FROM measuring m, measuring_types t
WHERE
      m.measuring_type_id = t.id AND
      m.measuring_value = (
          SELECT MIN(m1.measuring_value)
          FROM measuring m1
          WHERE m.measuring_type_id = t.id
          );

SELECT t.title, u.title
FROM measuring_types t
JOIN units_measuring u
    ON t.units_measuring_id = u.measuring_type_id;
SELECT t.title, u.title
FROM measuring_types t, units_measuring u
WHERE t.units_measuring_id = u.measuring_type_id;


SELECT measuring_type_id, AVG(measuring_value), measuring_datetime, station_id
FROM measuring
WHERE measuring_datetime = '1989-4-4'
GROUP BY measuring_type_id, station_id;