drop table if exists
    weather.measures;

create table weather.measures(
    m_id int unsigned auto_increment,
    m_name varchar(50) not null,
    m_metric varchar(10) not null,

    primary key(m_id)
);


drop table if exists
    weather.stantions;

create table weather.stantions(
    s_id int unsigned auto_increment not null,
    s_name varchar(50) not null,
    s_city varchar(50) not null,

    primary key(s_id)
);


drop table if exists
    weather.observations;


create table observations(
    obs_id int unsigned auto_increment not null,
    meas_type int unsigned not null,
    stantion_id int unsigned not null,
    measure_value decimal(10, 2),
    obs_date date not null,

    primary key(obs_id),

    foreign key(meas_type) references measures(m_id) on delete cascade,
    foreign key(stantion_id) references stantions(s_id) on delete cascade
);

insert into measures values
    (1,'Давление', 'pas'),
    (2,'Температура', 'С'),
    (3,'Влажность', '%'),
    (4,'Сила', 'Н');

insert into stantions values
    (1, 'Восточная', 'Екатеринбург'),
    (2, 'Sunny', 'Berlin'),
    (3, 'Ботаническая', 'Москва');


insert into observations values
    (1, 1, 2, 23.45, curdate()),
    (2, 3, 1, 55, curdate()),
    (3, 1, 2, 77.3, curdate()),
    (4, 1, 3, 33.34, curdate());

insert into observations values
    (5, 2, 1, 23, curdate());

select
       s_name as 'Имя Станции',
       s_city as 'Город Станции',
       m_name as 'Физ. величина',
       m_metric as 'Единица измерения',
       format(avg(measure_value), 2) as 'Среднее значение',
       date_format(obs_date, '%d.%m.%Y') as 'Дата'
    from observations as obs
        inner join stantions as st
            on obs.stantion_id = st.s_id
        inner join measures as ms
            on obs.meas_type = ms.m_id

    group by meas_type, s_name