· 9 years ago · Oct 17, 2016, 08:50 AM
1USE master
2GO
3
4IF EXISTS (
5 SELECT name
6 FROM sys.databases
7 WHERE name = N'Makhaev'
8)
9ALTER DATABASE [Makhaev] set single_user with rollback immediate
10GO
11
12IF EXISTS (
13 SELECT name
14 FROM sys.databases
15 WHERE name = N'Makhaev'
16)
17DROP DATABASE [Makhaev]
18GO
19
20CREATE DATABASE [Makhaev]
21GO
22
23USE [Makhaev]
24GO
25
26/*Ñîçäà Ãèå òà áëèöû ñ èçìåðåÃèÿìè*/
27
28IF EXISTS(
29 SELECT *
30 FROM sys.schemas
31 WHERE name = N'Òèïû èçìåðåÃèé'
32)
33
34DROP SCHEMA [ÈçìåðåÃèÿ]
35GO
36
37CREATE SCHEMA [ÈçìåðåÃèÿ]
38GO
39
40IF OBJECT_ID('[Makhaev].[ÈçìåðåÃèÿ].measure_types', 'U') IS NOT NULL
41 DROP TABLE [Makhaev].[ÈçìåðåÃèÿ].measure_types
42GO
43
44CREATE TABLE [Makhaev].[ÈçìåðåÃèÿ].measure_types
45(
46 id_measure tinyint NOT NULL,
47 MeasureType nvarchar(20) NOT NULL,
48 Measure nvarchar(20) NOT NULL /* Ìåðà */
49 CONSTRAINT PK_MeasureType PRIMARY KEY (id_measure)
50)
51GO
52
53 INSERT INTO [Makhaev].[ÈçìåðåÃèÿ].measure_types
54 (id_measure, MeasureType, Measure)
55 VALUES
56 (1, N'Òåìïåðà òóðà ', N'Ãðà äóñû Öåëüñèÿ')
57 ,(2, N'Âëà æÃîñòü', N'%')
58 ,(3, N'Äà âëåÃèå', N'ìì ðò ñò')
59 ,(4, N'Ãà ïðà âëåÃèå âåòðà ', N'ì/ñ')
60 ,(5, N'Îñà äêè', N'êã')
61GO
62
63SELECT MeasureType AS 'Òèï èçìåðåÃèÿ', Measure AS 'Ìåðà '
64From [Makhaev].[ÈçìåðåÃèÿ].measure_types
65
66/*Ñîçäà Ãèå òà áëèöû ñî ñòà Ãöèÿìè*/
67
68IF OBJECT_ID('[Makhaev].[ÈçìåðåÃèÿ].stations', 'U') IS NOT NULL
69 DROP TABLE [Makhaev].[ÈçìåðåÃèÿ].stations
70GO
71
72CREATE TABLE [Makhaev].[ÈçìåðåÃèÿ].stations
73(
74 id_station tinyint NOT NULL,
75 Name nvarchar(20) NOT NULL
76 CONSTRAINT PK_Station PRIMARY KEY (id_station)
77)
78GO
79
80 INSERT INTO [Makhaev].[ÈçìåðåÃèÿ].stations
81 (id_station, Name)
82 VALUES
83 (1, N'Ãåðâà ÿ')
84 ,(2, N'Âòîðà ÿ')
85 ,(3, N'Òðåòüÿ')
86 ,(4, N'×åòâ¸ðòà ÿ')
87 ,(5, N'Ãÿòà ÿ')
88GO
89
90SELECT Name AS 'Ãà çâà Ãèå' From [Makhaev].[ÈçìåðåÃèÿ].stations
91
92
93/*Ñîçäà Ãèå òà áëèöû ñ äà ÃÃûìè îá èçìåðåÃèÿõ*/
94
95IF OBJECT_ID('[Makhaev].[ÈçìåðåÃèÿ].measuring', 'U') IS NOT NULL
96 DROP TABLE [Makhaev].[ÈçìåðåÃèÿ].measuring
97GO
98
99CREATE TABLE [Makhaev].[ÈçìåðåÃèÿ].measuring
100(
101 id_measure_info tinyint NOT NULL
102 ,id_measure tinyint NOT NULL
103 ,id_station tinyint NOT NULL
104 ,MeasureDateTime datetime NOT NULL
105 ,value int NOT NULL
106 CONSTRAINT PK_Measure_Info PRIMARY KEY (id_measure_info)
107 ,CONSTRAINT FK_Measure FOREIGN KEY (id_measure)
108 REFERENCES [Makhaev].[ÈçìåðåÃèÿ].measure_types(id_measure)
109 ,CONSTRAINT FK_StationId FOREIGN KEY (id_station)
110 REFERENCES [Makhaev].[ÈçìåðåÃèÿ].stations(id_station)
111)
112GO
113
114INSERT INTO [Makhaev].[ÈçìåðåÃèÿ].measuring
115(id_measure_info, id_measure, id_station, MeasureDateTime, value)
116VALUES
117 (1, 1, 1, convert(datetime, '23-10-10 10:00:00 AM', 5), 12)
118 ,(2, 2, 1, convert(datetime, '23-10-10 10:01:00 AM', 5), 43535)
119 ,(3, 3, 1, convert(datetime, '23-10-10 10:02:00 AM', 5), 12312)
120 ,(4, 4, 1, convert(datetime, '23-10-10 10:03:00 AM', 5), 6456)
121 ,(5, 5, 1, convert(datetime, '23-10-10 10:04:00 AM', 5), 4567)
122 ,(6, 1, 2, convert(datetime, '23-10-10 10:05:00 AM', 5), 879)
123 ,(7, 2, 2, convert(datetime, '23-10-10 10:06:00 AM', 5), 5)
124 ,(8, 3, 2, convert(datetime, '23-10-10 10:07:00 AM', 5), 78)
125 ,(9, 4, 2, convert(datetime, '23-10-10 10:08:00 AM', 5), 123)
126 ,(10, 5, 2, convert(datetime, '23-10-10 10:09:00 AM', 5), 128)
127 ,(11, 1, 3, convert(datetime, '23-10-10 10:10:00 AM', 5), 980)
128 ,(12, 2, 3, convert(datetime, '23-10-10 10:11:00 AM', 5), 90)
129 ,(13, 3, 3, convert(datetime, '23-10-10 10:12:00 AM', 5), 123)
130 ,(14, 4, 3, convert(datetime, '23-10-10 10:13:00 AM', 5), 9618976)
131 ,(15, 5, 3, convert(datetime, '23-10-10 10:14:00 AM', 5), 97)
132 ,(16, 1, 4, convert(datetime, '23-10-10 10:15:00 AM', 5), 8765)
133 ,(17, 2, 4, convert(datetime, '23-10-10 10:16:00 AM', 5), 754)
134 ,(18, 3, 4, convert(datetime, '23-10-10 10:17:00 AM', 5), 123)
135 ,(19, 4, 4, convert(datetime, '23-10-10 10:18:00 AM', 5), 345)
136 ,(20, 5, 4, convert(datetime, '23-10-10 10:19:00 AM', 5), 75)
137 ,(21, 1, 5, convert(datetime, '23-10-10 10:20:00 AM', 5), 71625)
138 ,(22, 2, 5, convert(datetime, '23-10-10 10:21:00 AM', 5), 1625)
139 ,(23, 3, 5, convert(datetime, '23-10-10 10:22:00 AM', 5), 715)
140 ,(24, 4, 5, convert(datetime, '23-10-10 10:23:00 AM', 5), 1762)
141 ,(25, 5, 5, convert(datetime, '23-10-10 10:24:00 AM', 5), 187)
142GO
143
144SELECT id_measure AS 'ÈÄ òèïà èçìåðåÃèÿ'
145 ,id_station AS 'ÈÄ ñòà Ãöèè'
146 ,MeasureDateTime AS 'Äà òà è âðåìÿ'
147 ,value AS 'Ãåçóëüòà ò èçìåðåÃèé'
148 From [Makhaev].[ÈçìåðåÃèÿ].measuring
149
150SELECT measuring.MeasureDateTime AS 'Äà òà è âðåìÿ',
151 stations.Name AS 'Ñòà Ãöèÿ',
152 (measure_types.MeasureType + ', (' + measure_types.Measure + ')') AS 'Òèï èçìåðåÃèÿ',
153 measuring.value AS 'Ãåçóëüòà ò èçìåðåÃèé'
154FROM [Makhaev].[ÈçìåðåÃèÿ].measuring AS measuring
155INNER JOIN [Makhaev].[ÈçìåðåÃèÿ].measure_types AS measure_types ON
156 measuring.id_measure = measure_types.id_measure
157INNER JOIN [Makhaev].[ÈçìåðåÃèÿ].stations AS stations ON
158 measuring.id_station = stations.id_station