· 6 years ago · Dec 17, 2019, 08:04 PM
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*/
76SELECT * FROM Car
77GO
78
79IF OBJECT_ID(N'Car_VIEW') is not NULL
80DROP VIEW Car_VIEW;
81GO
82
83CREATE VIEW Car_VIEW AS
84 SELECT
85 c.Manufacturer_Id, c.CarId, c.CarName, c.Price, c.ProductionYear
86 FROM Car c
87 WHERE c.ProductionYear > 2010
88-- WITH CHECK OPTION
89GO
90
91SELECT * FROM Car_VIEW
92GO
93
94--UPDATE Car_VIEW SET ProductionYear = ProductionYear - 1000 WHERE ProductionYear = 2015
95--UPDATE Car SET ProductionYear = ProductionYear - 1000 WHERE ProductionYear = 2015
96--INSERT INTO
97GO
98
99SELECT * FROM Car
100GO
101
102SELECT * FROM Car_VIEW
103GO
104
105
106-----------------------------------------------------Пункт 2
107/*
108Создать представление на основе полей обеих
109связанных таблиц задания 6
110*/
111IF OBJECT_ID(N'Manufacturer_Car') is not NULL
112DROP VIEW Manufacturer_Car;
113GO
114
115CREATE VIEW Manufacturer_Car AS
116 SELECT
117 c.CarId, c.CarName, c.Price, c.ProductionYear, c.Manufacturer_Id, m.ManufacturerName, m.ManufacturerId
118 FROM Manufacturer m INNER JOIN Car c
119 ON m.ManufacturerId = c.Manufacturer_Id
120GO
121
122
123SELECT * FROM Manufacturer_Car;
124
125
126-----------------------------------------------------Пункт 3
127/*
128Создать индекс для одной из таблиц задания 6,
129включив в него дополнительные неключевые поля.
130*/
131IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Car_Idx')
132DROP INDEX Car_Idx ON Car;
133GO
134
135
136CREATE INDEX Car_Idx
137 ON Car (ProductionYear)
138 INCLUDE (CarName)
139GO
140
141SELECT ProductionYear, CarName FROM Car
142 WHERE ProductionYear BETWEEN 2000 AND 2020;
143GO
144
145
146-----------------------------------------------------Пункт 4
147/*
148Создать индексированное представление.
149*/
150
151IF OBJECT_ID(N'Manufacturer_VIEW', N'V') is not NULL
152DROP VIEW Manufacturer_VIEW;
153GO
154
155--для чего нужно WITH SCHEMABINDING и что это дает
156CREATE VIEW Manufacturer_VIEW
157 WITH SCHEMABINDING
158AS
159 SELECT
160 m.ManufacturerName, m.Country
161 FROM dbo.Manufacturer m
162 WHERE m.Country = m.Country
163GO
164
165
166IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Manufacturer_VIEW_Idx')
167DROP INDEX Manufacturer_VIEW_Idx ON Manufacturer;
168GO
169
170CREATE UNIQUE CLUSTERED INDEX Manufacturer_VIEW_Idx
171 ON Manufacturer_VIEW (ManufacturerName, Country)
172GO