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