w6x25B2n

1USE master
2GO 
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = N'Uliana_lab1'
8)
9ALTER DATABASE [Uliana_lab1] set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name 
14		FROM sys.databases 
15		WHERE name = N'Uliana_lab1'
16)
17DROP DATABASE [Uliana_lab1]
18GO
19
20CREATE DATABASE [Uliana_lab1]
21GO
22
23USE [Uliana_lab1]
24GO
25
26IF EXISTS(
27  SELECT *
28    FROM sys.schemas
29   WHERE name = N'Kvashnina1'
30) 
31 DROP SCHEMA Kvashnina1
32GO
33
34
35CREATE SCHEMA Kvashnina1 
36GO
37
38
39IF OBJECT_ID('[Uliana_lab1].Kvashnina1.izm', 'U') IS NOT NULL
40  DROP TABLE  [Uliana_lab1].Kvashnina1.izm
41GO
42
43
44CREATE TABLE [Uliana_lab1].Kvashnina1.izm
45(
46    id_iz int not NULL,
47	Название_измерения VARCHAR(max) not null,
48	Мера_измерения nvarchar(max) not null, 
49	CONSTRAINT ID_IZ PRIMARY KEY (id_iz),
50)
51GO
52
53 INSERT INTO [Uliana_lab1].Kvashnina1.izm 
54 (id_iz, Название_измерения, Мера_измерения)
55 VALUES 
56 ('1',  N'Температура', N'Цельсий')
57 ,('2',N'Влажность', N'Проценты')
58 ,('3',N'Давление', N'ММ ртутного столба')	
59 ,('4',N'Направление', N'Часть света')	
60 ,('5',N'Скорость', N'Км.ч')	
61 ,('6',N'Осадки', N'мм')
62  ,('7',N'Радиционный_фон', N'эфективная доза')	
63GO	
64
65
66IF OBJECT_ID('[Uliana_lab1].Kvashnina1.station', 'U') IS NOT NULL
67  DROP TABLE  [Uliana_lab1].Kvashnina1.station
68GO
69
70
71CREATE TABLE [Uliana_lab1].Kvashnina1.station
72(
73    id_st int not NULL,
74	Название_станции varchar(max) not null, 
75	CONSTRAINT ID_ST PRIMARY KEY (id_st),
76)
77GO
78
79 INSERT INTO [Uliana_lab1].Kvashnina1.station 
80 (id_st, Название_станции)
81 VALUES 
82 ( '1', N'Им.Э.Т.Кренкеля,ГМО')
83 ,('2', N'Остров_Визе')
84 ,('3',N'Голомянный')
85 ,( '4',N'Баренцбург')	
86 ,('5', N'Русский')
87 ,('6', N'Им.Е.К.Федорова,ГМО')	
88 ,('7', N'мыс_Желания ')	
89 ,( '8',N'Стерлегова')	
90 ,('9', N'Остров_Диксон')	
91GO	
92
93
94CREATE TABLE [Uliana_lab1].Kvashnina1.measure
95(
96	idSt int  NOT NULL, 
97	idIz int  NOT NULL,
98	Дата_измерения date not null,
99	Значение nvarchar(max) not null,
100	CONSTRAINT ID_STAT FOREIGN KEY (idSt)
101	REFERENCES [Uliana_lab1].Kvashnina1.station (id_st),
102	CONSTRAINT ID_MEAS FOREIGN KEY (idIz)
103	REFERENCES [Uliana_lab1].Kvashnina1.izm (id_iz),
104)
105GO
106
107INSERT INTO [Uliana_lab1].Kvashnina1.measure
108(idSt, idIz, Дата_измерения, Значение)
109VALUES
110	(1, 1, convert(date, '10-10-16', 3), 12)
111	,(1, 2, convert(date, '10-10-16', 3), 70)
112	,(1, 3, convert(date, '10-10-16', 3), 760)
113	,(1, 4, convert(date, '10-10-16', 3), 1)
114	,(1, 5, convert(date, '10-10-16', 3), 4)
115	,(1, 6, convert(date, '10-10-16', 3), 10)
116	,(1, 7, convert(date, '10-10-16', 3), 34)
117   ,(2, 1, convert(date, '10-10-16', 3), 12)
118	,(2, 2, convert(date, '10-10-16', 3), 70)
119	,(2, 3, convert(date, '10-10-16', 3), 760)
120	,(2, 4, convert(date, '10-10-16', 3), 1)
121	,(2, 5, convert(date, '10-10-16', 3), 4)
122	,(2, 6, convert(date, '10-10-16', 3), 10)
123	,(2, 7, convert(date, '10-10-16', 3), 34)
124,(3, 1, convert(date, '10-10-16', 3), 12)
125	,(3, 2, convert(date, '10-10-16', 3), 70)
126	,(3, 3, convert(date, '10-10-16', 3), 760)
127	,(3, 4, convert(date, '10-10-16', 3), 1)
128	,(3, 5, convert(date, '10-10-16', 3), 4)
129	,(3, 6, convert(date, '10-10-16', 3), 10)
130	,(3, 7, convert(date, '10-10-16', 3), 34)
131,(4, 1, convert(date, '10-10-16', 3), 12)
132	,(4, 2, convert(date, '10-10-16', 3), 70)
133	,(4, 3, convert(date, '10-10-16', 3), 760)
134	,(4, 4, convert(date, '10-10-16', 3),1)
135	,(4, 5, convert(date, '10-10-16', 3), 4)
136	,(4, 6, convert(date, '10-10-16', 3), 10)
137	,(4, 7, convert(date, '10-10-16', 3), 34)
138   ,(5, 1, convert(date, '10-10-16', 3), 12)
139	,(5, 2, convert(date, '10-10-16', 3), 70)
140	,(5, 3, convert(date, '10-10-16', 3), 760)
141	,(5, 4, convert(date, '10-10-16', 3), 1)
142	,(5, 5, convert(date, '10-10-16', 3), 4)
143	,(5, 6, convert(date, '10-10-16', 3), 10)
144	,(5, 7, convert(date, '10-10-16', 3), 34)
145	   ,(6, 1, convert(date, '10-10-16', 3), 12)
146	,(6, 2, convert(date, '10-10-16', 3), 70)
147	,(6, 3, convert(date, '10-10-16', 3), 760)
148	,(6, 4, convert(date, '10-10-16', 3), 1)
149	,(6, 5, convert(date, '10-10-16', 3), 4)
150	,(6, 6, convert(date, '10-10-16', 3), 10)
151	,(6, 7, convert(date, '10-10-16', 3), 34)
152	   ,(7, 1, convert(date, '10-10-16', 3), 12)
153	,(7, 2, convert(date, '10-10-16', 3), 70)
154	,(7, 3, convert(date, '10-10-16', 3), 760)
155	,(7, 4, convert(date, '10-10-16', 3), 1)
156	,(7, 5, convert(date, '10-10-16', 3), 4)
157	,(7, 6, convert(date, '10-10-16', 3), 10)
158	,(7, 7, convert(date, '10-10-16', 3), 34)
159	   ,(8, 1, convert(date, '10-10-16', 3), 12)
160	,(8, 2, convert(date, '10-10-16', 3), 70)
161	,(8, 3, convert(date, '10-10-16', 3), 760)
162	,(8, 4, convert(date, '10-10-16', 3), 1)
163	,(8, 5, convert(date, '10-10-16', 3), 4)
164	,(8, 6, convert(date, '10-10-16', 3), 10)
165	,(8, 7, convert(date, '10-10-16', 3), 34)
166	   ,(9, 1, convert(date, '10-10-16', 3), 12)
167	,(9, 2, convert(date, '10-10-16', 3), 70)
168	,(9, 3, convert(date, '10-10-16', 3), 760)
169	,(9, 4, convert(date, '10-10-16', 3), 1)
170	,(9, 5, convert(date, '10-10-16', 3), 4)
171	,(9, 6, convert(date, '10-10-16', 3), 10)
172	,(9, 7, convert(varchar,'10-10-16' , 106), 34)
173
174GO
175
176
177SELECT * From [Uliana_lab1].Kvashnina1.station
178SELECT * From [Uliana_lab1].Kvashnina1.izm
179SELECT * From [Uliana_lab1].Kvashnina1.measure
180
181
182SELECT        Kvashnina1.izm.Название_измерения, Kvashnina1.izm.Мера_измерения, Kvashnina1.measure.Значение, Kvashnina1.measure.Дата_измерения, Kvashnina1.station.Название_станции
183FROM            Kvashnina1.izm INNER JOIN
184                         Kvashnina1.measure ON Kvashnina1.izm.id_iz = Kvashnina1.measure.idIz INNER JOIN
185                         Kvashnina1.station ON Kvashnina1.measure.idSt = Kvashnina1.station.id_st