БД 11 лаба

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
USE MASTER;
GO
IF db_id (N'lab11') IS NOT NULL
DROP DATABASE lab11;
GO
CREATE DATABASE lab11
ON (NAME = lab11_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab11.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab11_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab11.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab11;
GO
CREATE TABLE country (
countryid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namec nvarchar(30));
GO
CREATE TABLE ruler (
rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namer nvarchar(30),
ruler_country int FOREIGN KEY REFERENCES country(countryid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO
CREATE TABLE technology (
technologyid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namet nvarchar(30),
);
GO
CREATE TABLE country_technology (
countryFK int NOT NULL REFERENCES country(countryid),
technologyFK int NOT NULL REFERENCES technology(technologyid),
);
GO
ALTER TABLE country_technology
ADD PRIMARY KEY (countryFK, technologyFK)
GO
-- представления для связи страна-технология
CREATE VIEW bufview1 WITH SCHEMABINDING AS
SELECT a.namec, b.technologyFK FROM
dbo.country a INNER JOIN dbo.country_technology b ON a.countryid = b.countryFK
GO
CREATE VIEW workCT WITH SCHEMABINDING AS
SELECT a.namec, b.namet FROM dbo.bufview1 a INNER JOIN dbo.technology b ON a.technologyFK = b.technologyid
GO
-- вставка страны/технологии
CREATE TRIGGER insertcountry ON country INSTEAD OF INSERT
AS INSERT country(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
GO
CREATE TRIGGER inserttechnology ON technology INSTEAD OF INSERT
AS INSERT technology(namet) SELECT DISTINCT namet FROM inserted WHERE namet NOT IN (SELECT namet FROM technology);
GO
--вставка страна-технология
CREATE TRIGGER insert_C_T ON country_technology INSTEAD OF INSERT
AS INSERT country_technology(countryFK, technologyFK) SELECT DISTINCT countryFK, technologyFK FROM inserted
GO
CREATE TRIGGER insert_View1 ON bufview1 INSTEAD OF INSERT
AS INSERT country_technology(countryFK, technologyFK) SELECT DISTINCT a.countryid, b.technologyFK FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec --WHERE a.countryid NOT IN (SELECT countryFK FROM country_technology);
GO
CREATE TRIGGER insert_addC ON workCT INSTEAD OF INSERT
AS INSERT bufview1(technologyFK, namec) SELECT DISTINCT a.technologyid, b.namec FROM dbo.technology a INNER JOIN inserted b ON a.namet = b.namet
-- WHERE a.technologyid NOT IN (SELECT technologyFK FROM bufview1);
GO
-- пример работы вставки
/*
INSERT technology (namet) VALUES ('a'), ('b'), ('c'), ('d'), ('e');
INSERT country (namec) VALUES ('a'), ('b'), ('c'), ('d'), ('e');
GO
INSERT workCT(namec, namet) Values ('a','c'), ('b','b'), ('d','e'), ('a','b'), ('d','b');
INSERT workCT(namec, namet) Values ('a','c'), ('b','a'), ('a','b');
SELECT * FROM country_technology
GO*/
-- удаление страна - технология
CREATE TRIGGER delete_C_T ON country_technology INSTEAD OF DELETE
AS
DELETE FROM country_technology WHERE technologyFK IN (SELECT technologyFK FROM deleted )
AND countryFK IN (SELECT countryFK deleted )
--DELETE FROM country WHERE countryid NOT IN (SELECT countryFK FROM country_technology);
GO
CREATE TRIGGER delete_View1 ON bufview1 INSTEAD OF DELETE
AS
DELETE FROM country_technology WHERE technologyFK IN (SELECT b.technologyFK FROM dbo.country a INNER JOIN deleted b ON a.namec = b.namec)
AND countryFK IN (SELECT a.countryid FROM dbo.country a INNER JOIN deleted b ON a.namec = b.namec)
GO
CREATE TRIGGER delete_View2 ON workCT INSTEAD OF DELETE
AS
DELETE FROM bufview1 WHERE technologyFK IN (SELECT a.technologyid FROM dbo.technology a INNER JOIN deleted b ON a.namet = b.namet)
AND namec IN (SELECT b.namec FROM dbo.technology a INNER JOIN deleted b ON a.namet = b.namet)
GO
-- удаление страны/технологии
CREATE TRIGGER deletecountry ON country INSTEAD OF DELETE
AS DELETE FROM workCT WHERE namec IN (SELECT namec FROM deleted);
DELETE FROM country WHERE namec IN (SELECT namec FROM deleted);
GO
CREATE TRIGGER deletetechnology ON technology INSTEAD OF DELETE
AS DELETE FROM workCT WHERE namet IN (SELECT namet FROM deleted);
DELETE FROM technology WHERE namet IN (SELECT namet FROM deleted);
GO
-- пример работы удаления
/*DELETE FROM workCT WHERE namet = 'b';
SELECT*FROM country_technology
GO*/
-- с 9 лабы
CREATE VIEW C_lView WITH SCHEMABINDING AS
SELECT a.namec, b.namer FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
GO
CREATE TRIGGER insert_C_lView ON C_lView INSTEAD OF INSERT
AS
BEGIN
INSERT country(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
INSERT ruler(ruler_country, namer) SELECT DISTINCT a.countryid, b.namer FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.namer NOT IN (SELECT namer FROM ruler);
END;
GO
/*
INSERT C_lView(namec, namer) VALUES ('Egypt', 'Ramses'), ('Egypt', 'Ramses II');
INSERT C_lView(namec, namer) VALUES ('Egypt', 'Ramses II');
INSERT C_lView(namec, namer) VALUES ('Egypt', 'Ramses III'), ('Laplandia', 'Santa'), ('Egypt', 'Ramses V'), ('Egypt', 'Ramses V');
GO*/
/*SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO*/
CREATE TRIGGER delete_C_lView ON C_lView INSTEAD OF DELETE
AS
DELETE FROM ruler WHERE namer IN (SELECT namer FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
/*
DELETE FROM C_lView WHERE namer = 'Caesar';
DELETE FROM C_lView WHERE namec = 'Egypt';
GO*/
/*SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
*/
CREATE TRIGGER update_C_lView ON C_lView INSTEAD OF UPDATE
AS
BEGIN
CREATE TABLE #table1 (id1 int IDENTITY PRIMARY KEY, new_name nvarchar(30));
CREATE TABLE #table2 (id2 int IDENTITY PRIMARY KEY, old_name nvarchar(30));
INSERT #table1 (new_name) SELECT namer FROM inserted;
INSERT #table2 (old_name) SELECT namer FROM deleted;
INSERT country(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
UPDATE ruler SET ruler_country = countryid FROM country a INNER JOIN inserted b ON a.namec =b.namec WHERE ruler.namer = b.namer;
UPDATE ruler SET namer = new_name FROM #table1 INNER JOIN #table2 ON #table1.id1 = #table2.id2 WHERE ruler.namer = #table2.old_name;
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
DROP TABLE #table1;
DROP TABLE #table2;
END;
GO
INSERT C_lView(namec, namer) VALUES ('Egypt', 'Ramses'), ('Rome', 'Octavian'), ('Rome', 'Caesar'), ('Greece', 'Pericles');
INSERT technology (namet) VALUES ('economy'), ('philosophy'), ('astronomy'), ('writing'), ('mathematics');
INSERT workCT(namec, namet) VALUES ('Egypt','philosophy'), ('Egypt','astronomy'), ('Egypt','writing');
INSERT workCT(namec, namet) VALUES ('Rome','economy'), ('Rome','astronomy'), ('Rome','mathematics'), ('Rome','writing');
INSERT workCT(namec, namet) VALUES ('Greece','philosophy'), ('Greece','astronomy'), ('Greece','mathematics'), ('Greece','writing');
GO
--INSERT C_lView(namec, namer) VALUES ('Greece', 'Caligula');
/*SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO*/
/*
UPDATE C_lView SET namec = 'Rome II' WHERE namec = 'Rome';
UPDATE C_lView SET namer = 'Caesar II' WHERE namer = 'Santa';
UPDATE C_lView SET namec = 'Honduras' WHERE namer like 'Ca%'
GO*/
/*SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO*/
--workCT
--SELECT*FROM workCT
--SELECT*FROM C_lView
--GO
SELECT a.namer, b.namer FROM dbo.ruler a /*LEFT*//*RIGHT*/ FULL OUTER JOIN dbo.ruler b ON a.ruler_country = b.ruler_country AND a.rulerid != b.rulerid ORDER BY b.namer desc
GO
SELECT a.namet, b.namer FROM dbo.workCT a INNER JOIN dbo.C_lView b ON a.namec = b.namec WHERE a.namet NOT IN (SELECT namet FROM dbo.workCT WHERE namec = 'Egypt')
GO
SELECT namec FROM C_lView WHERE namec like '%t' UNION/*UNION ALL*/ SELECT namec FROM country WHERE countryid BETWEEN 0 AND 2
GO
SELECT a.namec FROM dbo.workCT a WHERE EXISTS (SELECT namet FROM dbo.workCT WHERE a.namet = 'economy')
GO
SELECT namet, count(*) cnt FROM dbo.workCT GROUP BY namet HAVING (count(*)>1) ORDER BY cnt
GO
SELECT MIN(namer) FROM ruler GROUP BY ruler_country INTERSECT SELECT MAX(namer) FROM ruler GROUP BY ruler_country
GO
SELECT*FROM technology EXCEPT SELECT * FROM technology WHERE technologyid !=3
GO