rT4BmM78

1USE master
2/*ОБРАЩЕНИЕ К СИСТЕМНОЙ БАЗЕ SQL СЕРВЕРА
3ДЛЯ СОЗДАНИЯ ПОЛЬЗОВАТЕЛЬСКОЙ БАЗЫ ДАННЫХ*/
4GO --РАЗДЕЛИТЕЛЬ БАТЧЕЙ (BATH)
5
6IF  EXISTS (
7	SELECT name 
8		FROM sys.databases 
9		WHERE name = N'lab1'
10)
11ALTER DATABASE [lab1] set single_user with rollback immediate
12GO
13/* ПРОВЕРЯЕМ, СУЩЕСТВУЕТ ЛИ НА СЕРВЕРЕ БАЗА ДАННЫХ
14С ИМЕНЕМ [ИМЯ БАЗЫ], ЕСЛИ ДА, ТО ЗАКРЫВАЕМ ВСЕ ТЕКУЩИЕ
15 СОЕДИНЕНИЯ С ЭТОЙ БАЗОЙ */
16
17IF  EXISTS (
18	SELECT name 
19		FROM sys.databases 
20		WHERE name = N'lab1'
21)
22DROP DATABASE lab1
23GO
24/* СНОВА ПРОВЕРЯЕМ, СУЩЕСТВУЕТ ЛИ НА СЕРВЕРЕ БАЗА ДАННЫХ
25С ИМЕНЕМ [ИМЯ БАЗЫ], ЕСЛИ ДА, УДАЛЯЕМ ЕЕ С СЕРВЕРА */
26
27/* ДАННЫЙ БЛОК НЕОБХОДИМ ДЛЯ КОРРЕКТНОГО ПЕРЕСОЗДАНИЯ БАЗЫ
28ДАННЫХ С ИМЕНЕМ [ИМЯ БАЗЫ] ПРИ НЕОБХОДИМОСТИ */
29
30CREATE DATABASE lab1
31GO
32-- СОЗДАЕМ БАЗУ ДАННЫХ
33
34USE lab1
35GO
36/* ПЕРЕХОДИМ К СОЗДАННОЙ БАЗЕ ДАННЫХ ДЛЯ ПОСЛЕДУЮЩЕЙ РАБОТЫ С НЕЙ 
37ИЛИ С ЭТИХ КОМАНД ПРОДОЛЖАЕМ РАБОТУ С БАЗОЙ ДАННЫХ ЕСЛИ ОНА
38 УЖЕ СУЩЕСТВУЕТ НА СЕРВЕРЕ */
39
40IF EXISTS(
41  SELECT *
42    FROM sys.schemas
43   WHERE name = N'ilyenko'
44) 
45 DROP SCHEMA ilyenko
46GO
47/*ПРОВЕРЯЕМ, СУЩЕСТВУЕТ ЛИ В БАЗЕ ДАННЫХ
48 [ИМЯ БАЗЫ] СХЕМА С ИМЕНЕМ Фамилия, ЕСЛИ ДА,
49  ТО ПРЕДВАРИТЕЛЬНО УДАЛЯЕМ ЕЕ ИЗ БАЗЫ
50  ЕСЛИ ВЫ УАЛЯЕТЕ ВСЮ БАЗУ ЦЕЛИКОМ - ЭТА ЧАСТЬ СКРИПТА НЕ НУЖНА */
51
52CREATE SCHEMA ilyenko 
53GO
54/*СОЗДАЕМ В БАЗЕ ДАННЫХ
55 [ИМЯ БАЗЫ] СХЕМУ С ИМЕНЕМ Фамилия */
56
57
58 IF OBJECT_ID('lab1.ilyenko.measures', 'U') IS NOT NULL
59  DROP TABLE  lab1.ilyenko.measures
60GO
61
62/*ПРОВЕРЯЕМ, СУЩЕСТВУЕТ ЛИ В БАЗЕ ДАННЫХ
63 [ИМЯ БАЗЫ] И СХЕМЕ С ИМЕНЕМ Фамилия ТАБЛИЦА ut_students ЕСЛИ ДА, 
64  ТО ПРЕДВАРИТЕЛЬНО УДАЛЯЕМ ЕЕ ИЗ БАЗЫ И СХЕМЫ.
65  ЕСЛИ ВЫ УАЛЯЕТЕ ВСЮ БАЗУ ЦЕЛИКОМ - ЭТА ЧАСТЬ СКРИПТА НЕ НУЖНА */
66
67CREATE TABLE lab1.ilyenko.measures
68( /*влажность, давление, температура*/
69	Humidity smallint NOT NULL, 
70	Pressure smallint NOT NULL, 
71	Temperature smallint NOT NULL,
72	id_measure tinyint NOT NULL,
73	Day datetime NOT NULL
74)
75GO
76
77CREATE TABLE lab1.ilyenko.stations
78( /*название гидром станций*/
79	Name nvarchar(100) NOT NULL,
80	id_station tinyint NOT NULL
81	CONSTRAINT PK_station PRIMARY KEY (id_station)
82)
83GO
84
85CREATE TABLE lab1.ilyenko.measures_of_stations
86( /*измерениях определенных типов, производимых в определенные моменты времени на определенных станциях*/
87	Humidity smallint NOT NULL, 
88	Pressure smallint NOT NULL, 
89	Temperature smallint NOT NULL,
90	Name nvarchar(100) NOT NULL,
91	Day datetime NOT NULL
92)
93GO
94
95INSERT INTO lab1.ilyenko.measures
96 (Humidity, Pressure, Temperature, id_measure, day)
97 VALUES 
98 (60, 760, 10, 1, '30/09/2016'),
99 (40, 740, 16, 1, '30/09/2016'),
100 (70, 765, 12, 1, '28/09/2016'),
101 (80, 765, 10, 1, '28/09/2016'),
102 (40, 760, 9, 1, '25/09/2016'),
103 (50, 760, 13, 1, '25/09/2016'),
104 (40, 760, 8, 2, '25/09/2016'),
105 (30, 755, 10, 2, '25/09/2016'),
106 (50, 760, 10, 2, '30/09/2016'),
107 (30, 740, 15, 2, '30/09/2016'),
108 (40, 760, 10, 1, '27/09/2016'),
109 (40, 760, 11, 1, '27/09/2016')
110GO	
111
112SELECT * From lab1.ilyenko.measures
113--ПРОСМАТРИВАЕМ СОДЕРЖИМОЕ ТАБЛИЦЫ measures
114
115INSERT INTO lab1.ilyenko.stations
116 (Name, id_station)
117 VALUES 
118 ('Gismeteo', 1),
119 ('Yandex', 2)
120GO	
121
122SELECT * From lab1.ilyenko.stations
123--ПРОСМАТРИВАЕМ СОДЕРЖИМОЕ ТАБЛИЦЫ stations
124
125
126INSERT INTO lab1.ilyenko.measures_of_stations
127 (Humidity, Pressure, Temperature, Name, Day)
128 VALUES 
129 (60, 760, 10, 'Gismeteo', '30/09/2016'),
130 (40, 740, 16, 'Gismeteo', '30/09/2016'),
131
132 (80, 765, 10, 'Gismeteo', '28/09/2016'),
133 (70, 765, 12, 'Gismeteo', '28/09/2016'),
134 
135
136 (40, 760, 9, 'Gismeteo', '25/09/2016'),
137 (50, 760, 13, 'Gismeteo', '25/09/2016'),
138
139 (40, 760, 8, 'Yandex', '25/09/2016'),
140 (30, 755, 10, 'Yandex', '25/09/2016'),
141
142 (50, 760, 10, 'Yandex', '30/09/2016'),
143 (30, 740, 15, 'Yandex', '30/09/2016'),
144
145 (40, 760, 10, 'Gismeteo', '27/09/2016'),
146 (40, 760, 11, 'Gismeteo', '27/09/2016')
147GO	
148
149SELECT * From lab1.ilyenko.measures_of_stations
150--ПРОСМАТРИВАЕМ СОДЕРЖИМОЕ ТАБЛИЦЫ stations
151
152/* Связать таблицы */
153 SELECT lab1.ilyenko.stations.Name,
154		lab1.ilyenko.measures.Pressure,
155		lab1.ilyenko.measures.Temperature,
156        lab1.ilyenko.measures.Humidity
157     FROM
158            lab1.ilyenko.stations, lab1.ilyenko.measures
159     WHERE 
160             lab1.ilyenko.stations.id_station =
161             lab1.ilyenko.measures.id_measure;
162
163
164/*Выдать средние значения разных типов измерений на определенную дату по каждой станции. 
165Заголовки полей итоговых таблиц должны быть на русском языке, даты в виде 
166dd.month.yy(yyyy). */
167
168
169 SELECT  FORMAT(CONVERT(DATE, day), 'dd.MM.yyyy') AS "Дата", name AS "Станция", AVG(Humidity) AS "Средняя влажность", AVG(Pressure) AS "Среднее давление", AVG(Temperature) AS "Средняя температура"
170       FROM lab1.ilyenko.measures_of_stations
171	   GROUP BY name, day;