8tVVNYEJ

1USE master
2GO
3
4
5/* ПРОВЕРЯЕМ, СУЩЕСТВУЕТ ЛИ НА СЕРВЕРЕ БАЗА ДАННЫХ
6С ИМЕНЕМ [ИМЯ БАЗЫ], ЕСЛИ ДА, ТО ЗАКРЫВАЕМ ВСЕ ТЕКУЩИЕ
7 СОЕДИНЕНИЯ С ЭТОЙ БАЗОЙ */
8IF EXISTS (
9    SELECT  name
10    FROM    sys.databases
11    WHERE   name = N'Mamonov' )
12ALTER DATABASE [Mamonov] set single_user with rollback immediate
13GO
14
15
16
17/* СНОВА ПРОВЕРЯЕМ, СУЩЕСТВУЕТ ЛИ НА СЕРВЕРЕ БАЗА ДАННЫХ
18С ИМЕНЕМ [ИМЯ БАЗЫ], ЕСЛИ ДА, УДАЛЯЕМ ЕЕ С СЕРВЕРА */
19--Нужно для корректности
20IF EXISTS (
21    SELECT  name
22    FROM    sys.databases
23    WHERE   name = N'Mamonov' )
24DROP DATABASE [Mamonov]
25GO
26
27
28CREATE DATABASE [Mamonov]
29GO
30
31
32USE [Mamonov]
33GO
34
35
36
37-- создаём 3 таблицы
38
39
40--â„–1
41IF OBJECT_ID('Mamonov.MeteoStation', 'U') IS NOT NULL
42    DROP TABLE  Mamonov.MeteoStation
43GO
44
45
46CREATE TABLE MeteoStation (
47    id  TINYINT,
48    Name nvarchar(20),
49    CONSTRAINT PK_Station PRIMARY KEY (id)
50)
51GO
52
53
54INSERT INTO MeteoStation (id, Name) VALUES
55(1, N'Екатеринбургская'),
56(2, N'Московская'),
57(3, N'Верхне Пышминская')
58GO
59
60
61
62
63
64--â„–2
65IF OBJECT_ID('Mamonov.MeasureTypes', 'U') IS NOT NULL
66    DROP TABLE  Mamonov.MeasureTypes
67GO
68
69
70CREATE TABLE MeasureTypes (
71    id  TINYINT,
72    Name nvarchar(20),
73    Measure nvarchar(20),
74    CONSTRAINT PK_MeasureType PRIMARY KEY (id)
75)
76GO
77
78
79INSERT INTO MeasureTypes (id, Name, Measure) VALUES
80(1, N'Влажность', N'г/м3'),
81(2, N'Температура', N'градусов'),
82(3, N'Давление', N'Па')
83GO
84
85
86
87
88
89--â„–3
90IF OBJECT_ID('Mamonov.Measures', 'U') IS NOT NULL
91    DROP TABLE  Mamonov.Measures
92GO
93
94
95
96CREATE TABLE Measures (
97    Station_id  tinyint NOT NULL,
98    Measure_id  tinyint NOT NULL,
99    TimeOfMeasure   DATE    NOT NULL,
100    Value   float   NOT NULL,
101    CONSTRAINT FK_Station FOREIGN KEY (Station_id) REFERENCES MeteoStation(id) ON UPDATE CASCADE,
102    CONSTRAINT FK_MeasureType FOREIGN KEY (Measure_id) REFERENCES MeasureTypes(id) ON UPDATE CASCADE,
103)
104GO
105
106INSERT INTO Measures (Station_id, Measure_id, TimeOfMeasure, Value) VALUES
107	-- â„–1
108    (1, 1, '20160912', 30),
109    (2, 1, '20160912', 25),
110    (3, 1, '20160912', 20),
111    (1, 2, '20160913', 30),
112    (2, 2, '20160913', 20),
113    (3, 2, '20160913', 21),
114    (1, 3, '20160914', 30),
115    (2, 3, '20160914', 32),
116    (3, 3, '20160914', 31.1),
117	-- â„–2
118    (1, 3, '20160915', 30.2),
119    (2, 2, '20160915', 19),
120    (3, 1, '20160915', 30),
121    (1, 1, '20160912', 32),
122    (2, 1, '20160912', 33),
123    (3, 1, '20160912', 34),
124    (1, 2, '20160913', 36),
125    (2, 2, '20160913', 39),
126    (3, 2, '20160913', 37),
127    (1, 3, '20160914', 30.7),
128    (2, 3, '20160914', 30.9),
129    (3, 3, '20160914', 30.1),
130    (1, 3, '20160915', 30.6),
131    (2, 2, '20160915', 19),
132    (3, 1, '20160915', 30)
133GO
134/*
135SELECT type.Name as "Тип измерения",
136       station.Name as "Метеостанция",
137	   -- 106 - полный "русский" формат даты
138       CONVERT(varchar, measure.TimeOfMeasure, 106) as "Время измерения",
139       ROUND(AVG(measure.Value), 1) as "Среднее значение",
140       type.Measure as "Единицы"
141FROM Measures measure
142INNER JOIN MeasureTypes type
143    ON type.id = measure.Measure_id
144INNER JOIN MeteoStation station
145    ON station.id = measure.Station_id
146GROUP BY measure.TimeOfMeasure, type.Name, type.Measure, station.Name
147GO
148*/
149
150/*
151SELECT        dbo.Measures.Value, dbo.Measures.TimeOfMeasure, dbo.MeasureTypes.Name, dbo.MeasureTypes.Measure, dbo.MeteoStation.Name AS Expr1
152FROM            dbo.Measures INNER JOIN
153                         dbo.MeasureTypes ON dbo.Measures.Measure_id = dbo.MeasureTypes.id INNER JOIN
154                         dbo.MeteoStation ON dbo.Measures.Station_id = dbo.MeteoStation.id
155*/
156/*
157SELECT dbo.MeasureTypes.Name as "Тип измерения",
158	   dbo.MeteoStation.Name as "Метеостанция",
159	   CONVERT(varchar, dbo.Measures.TimeOfMeasure, 106) as "Время измерения",
160	   ROUND(AVG(dbo.Measures.Value), 1) as "Среднее значение",
161	   dbo.MeasureTypes.Measure as "Единицы"
162*/
163
164SELECT Measures.Value,
165	   Measures.TimeOfMeasure,
166	   MeasureTypes.Name as "Тип измерения",
167	   MeasureTypes.Measure as "Единицы",
168	   MeteoStation.Name as "Метеостанция"
169FROM Measures
170INNER JOIN
171	dbo.MeasureTypes ON Measures.Measure_id = dbo.MeasureTypes.id
172INNER JOIN
173	dbo.MeteoStation ON Measures.Station_id = dbo.MeteoStation.id
174
175			--where Measures.TimeOfMeasure = '20160912'
176GROUP BY Measures.TimeOfMeasure, Measures.Value
177GO