qwVRZx6N

1USE master
2GO 
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = N'Sedova'
8)
9ALTER DATABASE [Sedova] set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name 
14		FROM sys.databases 
15		WHERE name = N'Sedova'
16)
17DROP DATABASE [Sedova]
18GO
19
20CREATE DATABASE [Sedova]
21GO
22
23USE [Sedova]
24GO
25IF OBJECT_ID('Sedova.NameStation', 'U') IS NOT NULL
26  DROP TABLE  Sedova.NameStation
27GO
28
29
30CREATE TABLE NameStation
31(
32	id_station tinyint NOT NULL,
33	name_station nvarchar(40) NOT NULL,
34    CONSTRAINT PK_NumberSt PRIMARY KEY (id_station) 
35)
36GO
37
38INSERT INTO NameStation 
39(id_station, name_station)
40  VALUES 
41 (1,N'Орегон')
42 , (2,N'ЕВ4')
43 , (3,N'Стадлер')
44 , (4,N'ЕА2')
45
46SELECT * From NameStation
47
48IF OBJECT_ID('Sedova.TypeDimention', 'U') IS NOT NULL
49  DROP TABLE  Sedova.TypeDimention
50GO
51
52
53CREATE TABLE TypeDimention
54(
55	id_dimention tinyint NOT NULL,
56	type_dimention nvarchar(40) NOT NULL,
57	measurement_units nvarchar(40) NOT NULL,
58    CONSTRAINT PK_NumberDim PRIMARY KEY (id_dimention) 
59)
60GO
61
62
63INSERT INTO TypeDimention 
64(id_dimention, type_dimention, measurement_units)
65  VALUES 
66 (1,N'Температура',N'C(Цельсия)')
67 , (2,N'Влажность', N'%')
68 , (3,N'Давление', N'Бары')
69 , (4,N'Направление ветра', N'стороны света')
70 , (5,N'Скорость ветра', N'мм.рт')
71 , (6,N'Количество осадков', N'м')
72 
73
74SELECT * From TypeDimention
75
76
77IF OBJECT_ID('Sedova.StationDimention', 'U') IS NOT NULL
78  DROP TABLE  Sedova.StationDimention
79GO
80
81
82CREATE TABLE StationDimention
83(
84	id_station tinyint NOT NULL,
85	id_dimention tinyint NOT NULL,
86	datet DATE NOT NULL	,
87	dim_count tinyint NOT NULL,
88	value tinyint NOT NULL,
89	CONSTRAINT FK_st FOREIGN KEY (id_station) REFERENCES NameStation(id_station) ON UPDATE CASCADE,
90	CONSTRAINT FK_Dim FOREIGN KEY (id_dimention) REFERENCES TypeDimention (id_dimention) ON UPDATE CASCADE,
91)
92GO
93
94
95INSERT INTO StationDimention 
96(id_station, id_dimention, datet, dim_count, value)
97  VALUES 
98 (1, 1, '20160113', 1, 123)
99 ,(1, 2,'20160113', 1, 15)
100 ,(1, 3,'20160113', 1, 2)
101 ,(1, 4, '20160113', 1, 54)
102 ,(1, 5, '20160113', 1, 65)
103,(1, 6, '20160113', 1, 56)
104,(2, 1, '20160114', 1, 98)
105,(2, 2, '20160113', 1, 90)
106,(2, 3,'20160113', 1, 89)
107,(2, 4,'20160113', 1, 6)
108,(3, 1,'20160113', 1, 29)
109,(4, 1,'20160113', 1, 78)
110,(4, 2,'20160113', 1, 79)
111
112SELECT NameStation.name_station, TypeDimention.type_dimention, 
113       TypeDimention.measurement_units, StationDimention.value, 
114       StationDimention.datet
115FROM   NameStation INNER JOIN
116       StationDimention ON NameStation.id_station = StationDimention.id_station INNER JOIN
117       TypeDimention ON StationDimention.id_dimention = TypeDimention.id_dimention
118
119
120
121SELECT t.type_dimention as "Тип измерения",
122	   s.name_station as "Метеостанция",
123	   CONVERT(varchar, sd.datet, 104) as "Время",
124	   AVG(sd.value/sd.dim_count) as "Среднее значение",
125	   t.measurement_units as "Единицы Измерения"
126
127FROM StationDimention sd
128INNER JOIN TypeDimention t ON t.id_dimention = sd.id_dimention
129INNER JOIN NameStation s ON s.id_station = sd.id_station
130
131GROUP BY sd.datet, t.type_dimention, t.measurement_units, s.name_station
132GO
133 
134SELECT sd.value as "Температура"
135FROM StationDimention sd
136WHERE sd.id_dimention = 1
137GO
138 
139SELECT AVG(sd.value) as "Средняя температура"
140FROM StationDimention sd
141WHERE sd.id_dimention = 1
142GO