7F1F0k3u

1USE master;
2GO
3--удаление БД
4IF DB_ID (N'Lab7') IS NOT NULL
5DROP DATABASE Lab7;
6GO
7
8--создание БД
9CREATE DATABASE Lab7
10ON ( NAME = Lab7_dat,
11FILENAME = 'C:\DB\Nika\lab7dat.mdf',
12SIZE = 10, MAXSIZE = 150, FILEGROWTH = 5% )
13LOG ON ( NAME = Lab7_log,
14FILENAME = 'C:\DB\Nika\lab7dat.ldf',
15SIZE = 5MB, MAXSIZE = 150MB, FILEGROWTH = 5MB )
16GO
17
18
19USE Lab7
20GO
21
22--удаление таблицы
23IF OBJECT_ID (N'Audiobook') IS NOT NULL
24DROP TABLE Audiobook;
25GO
26
27--создание таблицы с автоинкрементным первичным ключом
28CREATE TABLE Audiobook (
29ABookID int IDENTITY(1,1) NOT NULL,
30ABookName VARCHAR(50) NOT NULL,
31ReaderLastName VARCHAR(30) NULL,
32ReaderFirstName VARCHAR(30) NULL,
33BookYear INT NULL,
34CONSTRAINT checkValue CHECK (BookYear > 1700),
35Year INT NULL
36CHECK (Year > 1950)
37)
38
39INSERT INTO Audiobook (ABookName, ReaderLastName, ReaderFirstName, BookYear)
40VALUES ('Inferno', 'Unknown', 'Unknown', 2013),
41('Excalibur','Unknown','Unknown', 1997);
42GO
43INSERT INTO Audiobook (ABookName, ReaderLastName, ReaderFirstName, BookYear)
44VALUES ('Adventures of Tomek', 'Unknown', 'Unknown', 2013);
45GO
46
47--Добавление поля, для которого используются значения по умолчанию(DEFAULT)
48ALTER TABLE Audiobook
49ADD Description VARCHAR(1000) DEFAULT ('Unknown');
50GO
51
52INSERT INTO Audiobook (ABookName, ReaderLastName, ReaderFirstName, BookYear, Year)
53VALUES ('Robinson Crusoe', 'Shishmareva', 'Maria', 1719, 2010),
54('1984','Litvinov','Ivan', 1949, 2013);
55GO
56INSERT INTO Audiobook (ABookName, ReaderLastName, ReaderFirstName, BookYear, Year)
57VALUES ('Inferno 2', 'Unknown', 'Unknown', 2013, 2013),
58('Excalibur 2','Unknown','Unknown', 1997, 2018);
59GO
60
61SELECT * FROM Audiobook
62GO
63
64IF OBJECT_ID (N'Book') IS NOT NULL
65DROP TABLE Book;
66GO
67--таблица с первичным ключом на основе последовательности
68CREATE TABLE Book
69(BookId int PRIMARY KEY,
70BookName VARCHAR(50) NOT NULL,
71Year int NULL,
72Country VARCHAR(50) NULL,
73Genre VARCHAR (50) NULL,
74Description VARCHAR(1000) NULL
75);
76GO
77
78CREATE SEQUENCE CountBy
79START WITH 1
80INCREMENT BY 1;
81GO
82
83INSERT Book (BookId, BookName, Year, Country, Genre, Description)
84VALUES (NEXT VALUE FOR CountBy, 'Inferno', 2013, 'USA', 'Mystery', 'Novel of Dan Brown "Inferno" will be loved by fans of adventures, dangers, and detectives. In this book main character is Robert Langdon, professor of Harvard Univerity, aleady known for readers for the book "Davinci code". Сюжет «Инферно» разворачивается в 21 веке во Флоренции, но даже здесь герою придется столкнуться с тайнами прошлого и разгадать секрет биологического цилиндра. Сюжет произведения тесно переплетен с «Божественной комедией» Данте Алигьери. «Ищите — и найдете» — эта фраза из Библии помогает герою приблизиться к разгадке. ' );
85INSERT Book (BookId, BookName, Year, Country, Genre, Description)
86VALUES (NEXT VALUE FOR CountBy, '1984', 1949, 'UK', 'Dystopian', 'Фантастическая антиутопия Оруэлла – это мир тотального контроля и страха, где люди живут ради войны, ради того, чтобы скрыть правду и воспитать настоящих патриотов. В жестоком тоталитарном государстве люди лишены гражданских прав и собственного мнения. Культ Большого Брата подразумевает жесткую социальную иерархию, где даже проявление любви считается мыслепреступлением. Уинстон Смит понимает, что он пешка в этой истории, и тем не менее пытается что-то изменить. «1984» – это аллюзия на страны, в которых господствовал тоталитаризм.');
87INSERT Book (BookId, BookName, Year, Country, Genre, Description)
88VALUES (NEXT VALUE FOR CountBy, 'Animal Farm', 1945, 'UK', 'Political satire', 'Притча, полная юмора и сарказма. Может ли скромная ферма стать символом тоталитарного общества? Конечно, да. Но… каким увидят это общество его «граждане» – животные, обреченные на бойню?');
89INSERT Book (BookId, BookName, Year, Country, Genre, Description)
90VALUES (NEXT VALUE FOR CountBy, 'Robinson Crusoe', 1719, 'UK', 'Adventure', 'История жизни Робинзона на необитаемом острове – повествование о мужественном и находчивом человеке, который сумел выжить и не одичать благодаря своему сильному духу и трудолюбию.');
91GO
92
93SELECT * FROM Book
94GO
95
96IF OBJECT_ID(N'ABooks_Qry') is not NULL
97DROP VIEW ABooks_Qry;
98GO
99--Создать представление на основе одной из таблиц задания 6
100CREATE VIEW ABooks_Qry AS
101SELECT
102a.ABookID, a.ABookName, a.BookYear, a.Year
103FROM AudioBook a
104WHERE a.ABookId = a.ABookID
105WITH CHECK OPTION
106GO
107
108SELECT * FROM ABooks_Qry;
109GO
110/*
111UPDATE ABooks_Qry
112SET a.Year = a.BookYear - a.Year
113WHERE a.ABookId = a.ABookID;
114GO
115*/
116--------------------------------------
117IF OBJECT_ID(N'Actor') IS NOT NULL
118DROP TABLE Actor;
119go
120
121CREATE TABLE Actor
122(--ActorId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
123ActorId int PRIMARY KEY NOT NULL,
124LastName VARCHAR (30) NOT NULL,
125FirstName VARCHAR (30) NOT NULL,
126BirthYear int NULL,
127Country VARCHAR(50) NULL,
128--CONSTRAINT PK_Actor PRIMARY KEY CLUSTERED (ActorId)
129)
130GO
131
132INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
133VALUES (1, 'Hanks', 'Thomas', 1956, 'U.S.');
134INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
135VALUES (2, 'Sherwood', 'Dominic', 1990, 'England');
136INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
137VALUES (3, 'McNamara', 'Katherine', 1995, 'U.S.');
138INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
139VALUES (4, 'Amell', 'Stephen', 1981, 'Canada');
140GO
141SELECT * FROM Actor
142
143IF OBJECT_ID(N'Film') IS NOT NULL
144DROP TABLE Film;
145go
146
147CREATE TABLE Film
148(FilmId int PRIMARY KEY NOT NULL,
149FilmName VARCHAR (50) NOT NULL,
150Year int NULL,
151Genre VARCHAR (50) NULL,
152Country VARCHAR(50) NULL,
153Actor_Id int NULL DEFAULT (3),
154--CONSTRAINT PK_Film PRIMARY KEY CLUSTERED (FilmId),
155CONSTRAINT FK_Actor_Film
156FOREIGN KEY (Actor_Id) REFERENCES Actor (ActorId)
157--ON DELETE CASCADE —Если из родительской таблицы удаляется строка, соответствующие ей строки удаляются и из ссылающейся таблицы.
158--ON UPDATE CASCADE
159--ON DELETE NO ACTION —Компонент Компонент SQL Server Database Engine формирует ошибку, и выполняется откат операции удаления строки из родительской таблицы.
160--ON UPDATE NO ACTION
161--ON DELETE SET NULL —Все значения, составляющие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в значение NULL.
162--ON UPDATE SET NULL
163ON DELETE SET DEFAULT --Все значения, содержащие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в значения по умолчанию. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию. Если столбец допускает значения NULL и значение по умолчанию явно не определено, значением столбца по умолчанию становится NULL.
164ON UPDATE SET DEFAULT
165)
166GO
167
168INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
169VALUES (1, 'Inferno', 2016, 'Mystery action thriller film', 'United States', 1);
170INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
171VALUES (2, 'Teenage Mutant Ninja Turtles: Out of the Shadows', 2016, 'Fantasy', 'United States', 4);
172INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
173VALUES (3, 'Vampire Academy', 2014, 'Fantasy comedy horror film', 'United States, United Kingdom', 2);
174INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
175VALUES (4, 'Maze Runner: The Death Cure', 2018, ' Dystopian science fiction film', 'United States', 3);
176GO
177
178SELECT * FROM Film
179GO
180
181
182IF OBJECT_ID(N'Actor_Film') is not NULL
183DROP VIEW Actor_Film;
184GO
185
186--Создать представление на основе полей обеих связанных таблиц задания 6
187CREATE VIEW Actor_Film AS
188SELECT
189a.ActorId, a.LastName, a.FirstName, a.BirthYear, a.Country, f.FilmName
190FROM Film f INNER JOIN Actor a
191ON f.Actor_Id = a.ActorId
192WITH CHECK OPTION
193GO
194
195SELECT * FROM Actor_Film;
196GO
197
198-----------------------------
199IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'ABooks_Idx')
200DROP INDEX ABooks_Idx ON Audiobook;
201GO
202
203--Создать индекс для одной из таблиц задания 6,включив в него дополнительные неключевые поля.
204CREATE NONCLUSTERED INDEX ABooks_Idx
205ON Audiobook (AbookId)
206INCLUDE (ABookName, ReaderFirstName)
207GO
208
209SELECT ABookName, ReaderFirstName, ABookId
210FROM Audiobook
211WHERE ABookId BETWEEN 1 AND 4;
212GO
213
214/*
215CREATE INDEX ABooks_Idx ON dbo.Audiobook (ABookName, Year, BookYear DESC)
216WITH (DROP_EXISTING = ON);
217GO*/
218SELECT * FROM sysindexes
219--SHOW INDEX FROM Lab7.AudioBook
220GO
221
222SELECT * FROM Audiobook
223
224--Создать индексированное представление.
225IF OBJECT_ID(N'Book_Qry', N'V') is not NULL
226DROP VIEW Book_Qry;
227GO
228
229CREATE VIEW Book_Qry
230WITH SCHEMABINDING
231AS
232SELECT a.BookID, a.BookName, a.Country, a.Year
233FROM dbo.Book a
234WHERE a.BookId = a.BookID
235WITH CHECK OPTION
236GO
237
238IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Book_Qry_Idx')
239DROP INDEX Book_Qry_Idx ON Book;
240GO
241
242CREATE UNIQUE CLUSTERED INDEX Book_Qry_Idx
243ON Book_Qry (BookId)
244GO
245
246SELECT * FROM sysindexes
247--SHOW INDEX FROM Lab7.AudioBook
248GO