· 6 years ago · Dec 18, 2019, 01:26 AM
1USE master;
2GO
3----удаление БД
4--IF DB_ID (N'Lab7Grom') IS NOT NULL
5--DROP DATABASE Lab7Grom;
6--GO
7
8--USE master;
9--GO
10----создание БД
11--CREATE DATABASE Lab7Grom
12-- ON ( NAME = Lab7_dat,
13-- FILENAME = 'C:\data\lab7\lab7gromdat.mdf',
14-- SIZE = 10, MAXSIZE = 150, FILEGROWTH = 5% )
15-- LOG ON ( NAME = Lab7Grom_log,
16-- FILENAME = 'C:\data\lab7\lab7gromlog.ldf',
17-- SIZE = 5MB, MAXSIZE = 150MB, FILEGROWTH = 5MB )
18--GO
19
20USE Lab7Grom
21GO
22
23IF OBJECT_ID(N'Car') IS NOT NULL
24DROP TABLE Car;
25GO
26IF OBJECT_ID(N'Manufacturer_VIEW', N'V') is not NULL
27DROP VIEW Manufacturer_VIEW;
28GO
29IF OBJECT_ID(N'Manufacturer') IS NOT NULL
30DROP TABLE Manufacturer;
31GO
32
33CREATE TABLE Manufacturer
34 (ManufacturerId INT PRIMARY KEY NOT NULL,
35 ManufacturerName VARCHAR (30) NOT NULL,
36 Country VARCHAR (50) NOT NULL);
37
38INSERT INTO Manufacturer (ManufacturerId, ManufacturerName, Country)
39VALUES (1, 'Audi', 'Germany'),
40 (2, 'Hyundai', 'South Korea'),
41 (3, 'BMW', 'Germany');
42GO
43
44
45
46CREATE TABLE Car
47 (CarId INT PRIMARY KEY NOT NULL,
48 CarName VARCHAR (30) NOT NULL,
49 Price MONEY NOT NULL,
50 ProductionYear INT NOT NULL,
51 Manufacturer_Id INT NULL,
52 CONSTRAINT FK_Manufacturer_Car
53 FOREIGN KEY (Manufacturer_Id) REFERENCES Manufacturer (ManufacturerId)
54 ON DELETE CASCADE
55 ON UPDATE CASCADE
56 );
57
58INSERT INTO Car (CarId, CarName, Price, ProductionYear, Manufacturer_Id)
59VALUES (1, 'Audi Q8', 4990000, 2017, 1),
60 (2, 'Audi Q7', 3995000, 2015, 1),
61 (3, 'Hyundai Creta', 957000, 2014, 2),
62 (4, 'BMW x6', 5420000, 2006, 3);
63
64GO
65
66SELECT * FROM Manufacturer
67GO
68SELECT * FROM Car
69GO
70
71-----------------------------------------------------Пункт 1
72/*
73Создать представление на основе одной из таблиц
74задания 6
75*/
76
77IF OBJECT_ID(N'Car_VIEW') is not NULL
78DROP VIEW Car_VIEW;
79GO
80
81CREATE VIEW Car_VIEW AS
82 SELECT
83 c.Manufacturer_Id, c.CarId, c.CarName, c.Price, c.ProductionYear
84 FROM Car c
85 WHERE c.ProductionYear > 2010
86 WITH CHECK OPTION
87GO
88
89SELECT * FROM Car_VIEW
90GO
91
92--UPDATE Car SET ProductionYear = ProductionYear - 1000 WHERE ProductionYear = 2015
93--UPDATE Car_VIEW SET ProductionYear = ProductionYear - 1000 WHERE ProductionYear = 2015
94
95--INSERT INTO Car (CarId, CarName, Price, ProductionYear, Manufacturer_Id)
96--VALUES (5, 'Test', 4990000, 2019, 2)
97--INSERT INTO Car_VIEW (Manufacturer_Id, CarId, CarName, Price, ProductionYear)
98--VALUES (2, 5, 'Test', 4990000, 2009)
99--INSERT INTO
100
101--DELETE FROM Car WHERE ProductionYear = 2006
102--DELETE FROM Car_VIEW WHERE ProductionYear = 2014
103
104
105--GO
106
107--SELECT * FROM Car
108--GO
109
110--SELECT * FROM Car_VIEW
111--GO
112
113
114-----------------------------------------------------Пункт 2
115/*
116Создать представление на основе полей обеих
117связанных таблиц задания 6
118*/
119IF OBJECT_ID(N'Manufacturer_Car') is not NULL
120DROP VIEW Manufacturer_Car;
121GO
122
123CREATE VIEW Manufacturer_Car AS
124 SELECT
125 c.CarId, c.CarName, c.Price, c.ProductionYear, c.Manufacturer_Id, m.ManufacturerName, m.ManufacturerId
126 FROM Manufacturer m INNER JOIN Car c
127 ON m.ManufacturerId = c.Manufacturer_Id
128GO
129
130
131SELECT * FROM Manufacturer_Car;
132
133
134-----------------------------------------------------Пункт 3
135/*
136Создать индекс для одной из таблиц задания 6,
137включив в него дополнительные неключевые поля.
138*/
139IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Car_Idx')
140DROP INDEX Car_Idx ON Car;
141GO
142
143
144CREATE INDEX Car_Idx
145 ON Car (ProductionYear)
146 INCLUDE (CarName)
147GO
148
149SELECT ProductionYear, CarName FROM Car
150 WHERE ProductionYear BETWEEN 2000 AND 2020;
151GO
152
153
154-----------------------------------------------------Пункт 4
155/*
156Создать индексированное представление.
157*/
158
159IF OBJECT_ID(N'Manufacturer_VIEW', N'V') is not NULL
160DROP VIEW Manufacturer_VIEW;
161GO
162
163--для чего нужно WITH SCHEMABINDING и что это дает
164CREATE VIEW Manufacturer_VIEW
165 WITH SCHEMABINDING
166AS
167 SELECT
168 m.ManufacturerName, m.Country
169 FROM dbo.Manufacturer m
170 WHERE m.Country = m.Country
171GO
172
173
174IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Manufacturer_VIEW_Idx')
175DROP INDEX Manufacturer_VIEW_Idx ON Manufacturer;
176GO
177
178CREATE UNIQUE CLUSTERED INDEX Manufacturer_VIEW_Idx
179 ON Manufacturer_VIEW (ManufacturerName, Country)
180GO