· 6 years ago · Dec 15, 2019, 05:56 PM
1USE master;
2GO
3--удаление БД
4IF DB_ID (N'Lab7') IS NOT NULL
5DROP DATABASE Lab7;
6GO
7
8--создание БД
9CREATE DATABASE Lab7
10 ON ( NAME = Lab7_dat,
11 FILENAME = '/tmp/data/lab7dat.mdf',
12 SIZE = 10, MAXSIZE = 150, FILEGROWTH = 5% )
13 LOG ON ( NAME = Lab7_log,
14 FILENAME = '/tmp/data/lab7dat.ldf',
15 SIZE = 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 (
29 ABookID int IDENTITY(1,1) NOT NULL,
30 ABookName VARCHAR(50) NOT NULL,
31 ReaderLastName VARCHAR(30) NULL,
32 ReaderFirstName VARCHAR(30) NULL,
33 BookYear INT NULL,
34 CONSTRAINT checkValue CHECK (BookYear > 1700),
35 Year INT NULL
36 CHECK (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,
70 BookName VARCHAR(50) NOT NULL,
71 Year int NULL,
72 Country VARCHAR(50) NULL,
73 Genre VARCHAR (50) NULL,
74 Description NVARCHAR(1000) NULL
75);
76GO
77
78CREATE SEQUENCE CountBy
79 START WITH 1
80 INCREMENT 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
101 SELECT
102 a.ABookID, a.ABookName, a.BookYear, a.Year
103 FROM AudioBook a
104 -- WHERE a.ABookId = a.ABookID
105 WHERE a.ABookId = 2
106 WITH CHECK OPTION
107GO
108
109INSERT INTO ABooks_Qry
110VALUES ('Israel', 2001, 1999);
111
112
113SELECT * FROM ABooks_Qry;
114GO
115
116/*IF OBJECT_ID(N'european_cities') is not NULL
117DROP VIEW european_cities;
118GO
119
120CREATE VIEW european_cities
121AS
122SELECT BookID, BookName, Year
123FROM Book
124WHERE Year in
125 (SELECT ABookID
126 FROM Audiobook
127
128 WHERE ABookId = 2)
129WITH CHECK OPTION;
130GO
131INSERT INTO european_cities
132VALUES (5, 'Israel', 2001);
133*/
134/*
135UPDATE ABooks_Qry
136SET a.Year = a.BookYear - a.Year
137WHERE a.ABookId = a.ABookID;
138GO
139*/
140--------------------------------------
141IF OBJECT_ID(N'Actor') IS NOT NULL
142DROP TABLE Actor;
143go
144
145CREATE TABLE Actor
146(--ActorId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
147ActorId int PRIMARY KEY NOT NULL,
148 LastName VARCHAR (30) NOT NULL,
149 FirstName VARCHAR (30) NOT NULL,
150 BirthYear int NULL,
151 Country VARCHAR(50) NULL,
152 --CONSTRAINT PK_Actor PRIMARY KEY CLUSTERED (ActorId)
153)
154GO
155
156INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
157VALUES (1, 'Hanks', 'Thomas', 1956, 'U.S.');
158INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
159VALUES (2, 'Sherwood', 'Dominic', 1990, 'England');
160INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
161VALUES (3, 'McNamara', 'Katherine', 1995, 'U.S.');
162INSERT Actor (ActorId, LastName, FirstName, BirthYear, Country)
163VALUES (4, 'Amell', 'Stephen', 1981, 'Canada');
164GO
165SELECT * FROM Actor
166
167IF OBJECT_ID(N'Film') IS NOT NULL
168DROP TABLE Film;
169go
170
171CREATE TABLE Film
172(FilmId int PRIMARY KEY NOT NULL,
173 FilmName VARCHAR (50) NOT NULL,
174 Year int NULL,
175 Genre VARCHAR (50) NULL,
176 Country VARCHAR(50) NULL,
177 Actor_Id int NULL DEFAULT (3),
178 --CONSTRAINT PK_Film PRIMARY KEY CLUSTERED (FilmId),
179 CONSTRAINT FK_Actor_Film
180 FOREIGN KEY (Actor_Id) REFERENCES Actor (ActorId)
181 --ON DELETE CASCADE —Если из родительской таблицы удаляется строка, соответствующие ей строки удаляются и из ссылающейся таблицы.
182 --ON UPDATE CASCADE
183 --ON DELETE NO ACTION —Компонент Компонент SQL Server Database Engine формирует ошибку, и выполняется откат операции удаления строки из родительской таблицы.
184 --ON UPDATE NO ACTION
185 --ON DELETE SET NULL —Все значения, составляющие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в значение NULL.
186 --ON UPDATE SET NULL
187 ON DELETE SET DEFAULT --Все значения, содержащие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в значения по умолчанию. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию. Если столбец допускает значения NULL и значение по умолчанию явно не определено, значением столбца по умолчанию становится NULL.
188 ON UPDATE SET DEFAULT
189)
190GO
191
192INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
193VALUES (1, 'Inferno', 2016, 'Mystery action thriller film', 'United States', 1);
194INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
195VALUES (2, 'Teenage Mutant Ninja Turtles: Out of the Shadows', 2016, 'Fantasy', 'United States', 4);
196INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
197VALUES (3, 'Vampire Academy', 2014, 'Fantasy comedy horror film', 'United States, United Kingdom', 2);
198INSERT Film (FilmId, FilmName, Year, Genre, Country, Actor_Id)
199VALUES (4, 'Maze Runner: The Death Cure', 2018, ' Dystopian science fiction film', 'United States', 3);
200GO
201
202SELECT * FROM Film
203GO
204
205
206IF OBJECT_ID(N'Actor_Film') is not NULL
207DROP VIEW Actor_Film;
208GO
209
210--Создать представление на основе полей обеих связанных таблиц задания 6
211CREATE VIEW Actor_Film AS
212 SELECT
213 a.ActorId, a.LastName, a.FirstName, a.BirthYear, a.Country, f.FilmName
214 FROM Film f INNER JOIN Actor a
215 ON f.Actor_Id = a.ActorId
216 WITH CHECK OPTION
217GO
218
219SELECT * FROM Actor_Film;
220GO
221
222-----------------------------
223IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'ABooks_Idx')
224DROP INDEX ABooks_Idx ON Audiobook;
225GO
226
227--Создать индекс для одной из таблиц задания 6,включив в него дополнительные неключевые поля.
228CREATE NONCLUSTERED INDEX ABooks_Idx
229 ON Audiobook (AbookId)
230 INCLUDE (ABookName, ReaderFirstName)
231GO
232
233SELECT ABookName, ReaderFirstName, ABookId
234FROM Audiobook
235WHERE ABookId BETWEEN 1 AND 4;
236GO
237
238/*
239CREATE INDEX ABooks_Idx ON dbo.Audiobook (ABookName, Year, BookYear DESC)
240WITH (DROP_EXISTING = ON);
241GO*/
242SELECT * FROM sysindexes
243--SHOW INDEX FROM Lab7.AudioBook
244GO
245
246SELECT * FROM Audiobook
247
248--Создать индексированное представление.
249IF OBJECT_ID(N'Book_Qry', N'V') is not NULL
250DROP VIEW Book_Qry;
251GO
252
253CREATE VIEW Book_Qry
254 WITH SCHEMABINDING
255AS
256 SELECT a.BookID, a.BookName, a.Country, a.Year
257 FROM dbo.Book a
258 WHERE a.BookId = a.BookID
259 WITH CHECK OPTION
260GO
261
262IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Book_Qry_Idx')
263DROP INDEX Book_Qry_Idx ON Book;
264GO
265
266CREATE UNIQUE CLUSTERED INDEX Book_Qry_Idx
267 ON Book_Qry (BookId)
268GO
269
270SELECT * FROM sysindexes
271--SHOW INDEX FROM Lab7.AudioBook
272GO