6Qhc5agX

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	value tinyint NOT NULL,
88	CONSTRAINT FK_st FOREIGN KEY (id_station) REFERENCES NameStation(id_station) ON UPDATE CASCADE,
89	CONSTRAINT FK_Dim FOREIGN KEY (id_dimention) REFERENCES TypeDimention (id_dimention) ON UPDATE CASCADE,
90)
91GO
92
93
94INSERT INTO StationDimention 
95(id_station, id_dimention, datet, value)
96  VALUES 
97 (1, 1, '20160113',  123)
98 ,(1, 2,'20160113', 15)
99 ,(1, 3,'20160113', 2)
100 ,(1, 4, '20160113', 54)
101 ,(1, 5, '20160113', 65)
102,(1, 6, '20160113', 56)
103,(2, 1, '20160114', 98)
104,(2, 2, '20160113', 90)
105,(2, 3,'20160113', 89)
106,(2, 4,'20160113', 6)
107,(3, 1,'20160113', 29)
108,(4, 1,'20160113', 78)
109,(4, 2,'20160113', 79)
110
111SELECT NameStation.name_station, TypeDimention.type_dimention, 
112       TypeDimention.measurement_units, StationDimention.value, 
113       StationDimention.datet
114FROM   NameStation INNER JOIN
115       StationDimention ON NameStation.id_station = StationDimention.id_station INNER JOIN
116       TypeDimention ON StationDimention.id_dimention = TypeDimention.id_dimention
117
118
119
120SELECT t.type_dimention as "Тип измерения",
121	   s.name_station as "Метеостанция",
122	   CONVERT(varchar, sd.datet, 104) as "Время",
123	   AVG(sd.value) as "Среднее значение",
124	   t.measurement_units as "Единицы Измерения"
125
126FROM StationDimention sd
127INNER JOIN TypeDimention t ON t.id_dimention = sd.id_dimention
128INNER JOIN NameStation s ON s.id_station = sd.id_station
129
130GROUP BY sd.datet, t.type_dimention, t.measurement_units, s.name_station
131GO
132 
133SELECT sd.value as "Температура"
134FROM StationDimention sd
135WHERE sd.id_dimention = 1
136GO
137 
138SELECT AVG(sd.value) as "Средняя температура"
139FROM StationDimention sd
140WHERE sd.id_dimention = 1
141GO