тбд

create database [Anastasia.Svalova]
go

-----------------------------------

CREATE SCHEMA lab1
GO

-----------------------------------

create table [measure_type.Svalova.ut_students]
(
	name varchar(30) NOT NULL,
	id int NOT NULL primary key IDENTITY(1,1)
)
go

create table [gstate.Svalova.ut_students]
(
	name varchar(30) NOT NULL,
	id int NOT NULL primary key IDENTITY(1,1)
)
go

create table [measures_on_st.Svalova.ut_students]
(
	state_id int NOt NULL foreign key references [gstate.Svalova.ut_students](id),
	measure_type_id int NOt NULL foreign key references [measure_type.Svalova.ut_students](id),
	value int NOt NULL,
	data_of_measure datetime NOt NULL
)
go

-----------------------------------------

insert into [measure_type.Svalova.ut_students] (name)
values ('влажность')
go

insert into [measure_type.Svalova.ut_students] (name)
values ('давление')
go


insert into [measure_type.Svalova.ut_students] (name)
values ('температура')
go

----------------------------------------

insert into [gstate.Svalova.ut_students] (name)
values ('Москва')
go
insert into [gstate.Svalova.ut_students] (name)
values ('Псков')
go
insert into [gstate.Svalova.ut_students] (name)
values ('Кировград')
go
insert into [gstate.Svalova.ut_students] (name)
values ('Тюмень')
go

----------------------------------------

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (1, 1, 13, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (1, 2, 543, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (1, 3, 63, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (2, 1, 45, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (2, 2, 6768, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (2, 3, 234, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (3, 1, 45, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (3, 2, 57, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (3, 3, 7686, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (4, 3, 77, '19950330')
go

insert into [measures_on_st.Svalova.ut_students] (state_id, measure_type_id, value, data_of_measure)
values (4, 1, 57, '19950330')
go

--------------------------------------

alter table [gstate.Svalova.ut_students]
add address varchar(70) default 'Россия, г. Екатеринбург' not null

--------------------------------------

create table [measures_units.Svalova.ut_students]
(
	name varchar(30) NOT NULL,
	id int NOT NULL primary key IDENTITY(1,1)
)
go

--------------------------------------

insert into [measures_units.Svalova.ut_students] (name)
values ('%')
go

insert into [measures_units.Svalova.ut_students] (name)
values ('атм')
go

insert into [measures_units.Svalova.ut_students] (name)
values ('градусы_Цельсия')
go

--------------------------------------

alter table [measure_type.Svalova.ut_students]
add [measures_unit] int foreign key references [measures_units.Svalova.ut_students](id)
go

update [measure_type.Svalova.ut_students]
set [measures_unit] = 1
where name = 'влажность'
go

update [measure_type.Svalova.ut_students]
set [measures_unit] = 2
where name = 'давление'
go

update [measure_type.Svalova.ut_students]
set [measures_unit] = 3
where name = 'температура'
go

--------------------------------------

select distinct FORMAT(CONVERT(date, data_of_measure), 'dd.MM.yy(yyyy)') as 'дата' from [measures_on_st.Svalova.ut_students]
go

select (select name from [measure_type.Svalova.ut_students] where id = [measure_type_id]) as 'тип',
	max(value) as 'max', min(value) as 'min' FROM [measures_on_st.Svalova.ut_students]
group by measure_type_id

select value from [measures_on_st.Svalova.ut_students]
where measure_type_id in
(
	select id from [measure_type.Svalova.ut_students]
	where measures_unit in
	(
		select id from [measures_units.Svalova.ut_students]
		where name like 'градусы%'
	)
)

--------------------------------------

SELECT x.value as 'минимальное значение', FORMAT(CONVERT(date, data_of_measure), 'dd.MM.yy(yyyy)') as 'дата',
        name as 'тип'
FROM [measures_on_st.Svalova.ut_students] as x join [measure_type.Svalova.ut_students]
	on x.measure_type_id = [measure_type.Svalova.ut_students].id
WHERE EXISTS
        (SELECT * FROM [measures_on_st.Svalova.ut_students] as y
        WHERE x.measure_type_id = y.measure_type_id
        HAVING x.value = MIN(y.value))

--select value as 'минимальное значение', FORMAT(CONVERT(date, data_of_measure), 'dd.MM.yy(yyyy)') as 'дата' , name as 'тип'
--from [measures_on_st.Svalova.ut_students]  join [measure_type.Svalova.ut_students] on measure_type_id=id
--where value in
--(
--	select min(value)
--	from [measures_on_st.Svalova.ut_students]
--	group by measure_type_id
--)
--проблема что мин не связан с валью
--------------------------------------

select avg(value) as 'среднее значение', FORMAT(CONVERT(date, data_of_measure), 'dd.MM.yy(yyyy)') as 'дата', [measure_type.Svalova.ut_students].name as 'тип',
	[gstate.Svalova.ut_students].name as 'имя', [measures_units.Svalova.ut_students].name as 'единицы'
from [measures_on_st.Svalova.ut_students] join [measure_type.Svalova.ut_students] on measure_type_id=id
join [gstate.Svalova.ut_students] on [gstate.Svalova.ut_students].id = [measures_on_st.Svalova.ut_students].state_id
join [measures_units.Svalova.ut_students] on [measures_units.Svalova.ut_students].id = [measure_type.Svalova.ut_students].measures_unit
group by [gstate.Svalova.ut_students].name, data_of_measure, measure_type_id, [measure_type.Svalova.ut_students].name, [measures_units.Svalova.ut_students].name

--------------------------------------

select * from [measures_on_st.Svalova.ut_students];
go
select * from [gstate.Svalova.ut_students];
go
select * from [measure_type.Svalova.ut_students];
go
select * from [measures_units.Svalova.ut_students];
go