Lab1

use [Gallyam.Biktashev]

create table measurements
(
    id int not null identity(1,1),
    measurement_value int,
    measurement_date date,
    measurement_time time,
    primary key (id)
)
GO

create table stations (
    id int not null identity(1,1),
    name varchar(30) not null,
    primary key (id)
)
GO

create table measurements_names (
    id int not null identity(1,1),
    name varchar(30) not null,
    primary key (id)
)
GO

alter table measurements
add station int,
foreign key (station) references stations (id);
GO

alter table measurements
add measurement_type int,
foreign key (measurement_type) references measurements_names (id);
GO

create table measurement_units
(
    id int not null identity(1,1),
    name varchar(40),
    shortname varchar(10),
    primary key(id)
)
GO

alter table stations
add address varchar(200) default 'Россия, г.Верхушкино, ул. Пушкина, д. Колотушкина';
GO

alter table measurements_names
add unit int,
foreign key (unit) references measurement_units(id);
GO

insert into measurement_units
values
    ('Цельсий', 'ºC'),
    ('Фаренгейт', 'F'),
    ('Паскаль', 'Па');
GO

insert into stations
values
    ('Окуловский', 'Россия, г.Екатеринбург, ул. Тургенева, д. 4'),
    ('Ватников', 'Россия, г.Екатеринбург, ул. Мира, д. 19'),
    ('Кремлевская', 'Россия, г.Москва, Красная площадь, д. 1');
GO

insert into measurements_names
values
    ('Температура', 1),
    ('Температура', 2),
    ('Давление', 3);
GO

insert into measurements
values
    (35, '5.07.1954', '12:24', 2, 2),
    (123, '5.07.1954', '13:24', 2, 2),

    (35, '24.11.1988', '10:24', 1, 1),
    (51234, '24.11.1988', '13:24', 1, 1),
    (100000, '24.11.1988', '11:24', 1, 3),

    (214, '5.07.2000', '20:24', 3, 2),
    (355, '5.07.2000', '19:24', 3, 2),

    (342, '5.07.2005', '17:24', 3, 1),
    (-123, '5.07.1992', '16:24', 3, 2),
    (-1234, '5.07.95', '18:24', 1, 3),
    (312, '5.07.2004', '02:24', 2, 3),
    (-35, '5.07.1945', '03:24', 3, 2);
GO


select distinct measurement_date from measurements order by measurement_date asc;
GO

select measurement_type, max(measurement_value) as max_value, min(measurement_value) as min_value from measurements
    group by measurement_type;
GO

select *
from measurements as m
where m.measurement_type in(
    select id
    from measurements_names
    where unit in (
        select id
        from measurement_units
        where shortname = 'ºC'
    )
)
GO

select m.measurement_type, m.measurement_date, m.measurement_value
from measurements as m
where m.measurement_value in (
    select min(m1.measurement_value)
    from measurements as m1
    where m.measurement_type = m1.measurement_type
    group by m1.measurement_type
)
GO

select measurement_name as 'Тип измерения', unit_name as 'Обозначение', station_name as 'Станция', avg(measurement_value) as 'Среднее значение', format(measurement_date, 'dd.MMMM.yy(yyyy)') as 'Дата измерения'
from
    (select mn.id as mn_id, mn.name as measurement_name, mu.shortname as unit_name
        from measurements_names as mn
        left join measurement_units as mu
        on mn.unit = mu.id
    ) as mn
    right join measurements as m
    on m.measurement_type = mn.mn_id
    left join (select id, name as station_name from stations as s) as s
    on s.id = m.station
group by measurement_name, unit_name, station_name, measurement_date
order by measurement_name, unit_name, station_name, measurement_date

select mo.measurement_date, mo.measurement_type, mo.station, mo.measurement_value
from measurements as mo
where mo.measurement_value > (
    select avg(m.measurement_value)
    from measurements as m
    where m.measurement_type = mo.measurement_type
)
group by mo.measurement_type, mo.measurement_date, mo.station, mo.measurement_value

select
 m.measurement_date as 'Дата измерения',
measurements_names.name 'Измерение',
stations.name as 'Станция',
m.measurement_value as 'Значение',
measurement_units.shortname as 'Единицы измерения'

from measurements as m

 inner join stations
 on stations.id = m.station

 inner join measurements_names
 on measurements_names.id = measurement_type
 inner join measurement_units
 on measurement_units.id = measurements_names.unit
where m.measurement_value > (
 select avg(measurement_value)
 from measurements
 where measurement_type = m.measurement_type
)
group by m.measurement_date, measurements_names.name, measurement_units.shortname, stations.name, m.measurement_value
order by m.measurement_date, measurement_units.shortname

--drop table measurements
--drop table stations
--drop table measurements_names
--drop table measurement_units