· 6 years ago · Dec 16, 2019, 09:36 AM
1USE master;
2GO
3--удаление БД
4IF DB_ID (N'Lab7Grom') IS NOT NULL
5DROP DATABASE Lab7Grom;
6GO
7
8USE master;
9GO
10--создание БД
11CREATE 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 )
18GO
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 NO ACTION -- ормируется ошибка, и выполняется откат операции удаления строки из родительской таблицы.
55 --ON UPDATE NO ACTION
56 ON DELETE CASCADE --каскадное изменение ссылающихся таблиц;
57 ON UPDATE CASCADE
58 --ON DELETE SET NULL --установка NULL для ссылающихся внешних ключей
59 --ON UPDATE SET NULL
60 --ON DELETE SET DEFAULT --установка значений по умолчанию для ссылающихся внешних ключей
61 --ON UPDATE SET DEFAULT
62 );
63
64INSERT INTO Car (CarId, CarName, Price, ProductionYear, Manufacturer_Id)
65VALUES (1, 'Audi Q8', 4990000, 2017, 1),
66 (2, 'Audi Q7', 3995000, 2015, 1),
67 (3, 'Hyundai Creta', 957000, 2014, 2),
68 (4, 'BMW x6', 5420000, 2006, 3);
69
70GO
71
72SELECT * FROM Manufacturer
73GO
74SELECT * FROM Car
75GO
76
77-----------------------------------------------------Пункт 1
78/*
79Создать представление на основе одной из таблиц
80задания 6
81*/
82IF OBJECT_ID(N'Car_VIEW') is not NULL
83DROP VIEW Car_VIEW;
84GO
85
86CREATE VIEW Car_VIEW AS
87 SELECT
88 c.Manufacturer_Id, c.CarId, c.CarName, c.Price, c.ProductionYear
89 FROM Car c
90 WHERE c.Manufacturer_Id = 1
91 WITH CHECK OPTION
92GO
93
94SELECT * FROM Car_VIEW
95GO
96
97
98-----------------------------------------------------Пункт 2
99/*
100Создать представление на основе полей обеих
101связанных таблиц задания 6
102*/
103IF OBJECT_ID(N'Manufacturer_Car') is not NULL
104DROP VIEW Manufacturer_Car;
105GO
106
107CREATE VIEW Manufacturer_Car AS
108 SELECT
109 c.CarId, c.CarName, c.Price, c.ProductionYear, c.Manufacturer_Id, m.ManufacturerName, m.ManufacturerId
110 FROM Manufacturer m INNER JOIN Car c
111 ON m.ManufacturerId = c.Manufacturer_Id
112 WITH CHECK OPTION
113GO
114
115SELECT * FROM Manufacturer_Car;
116
117
118-----------------------------------------------------Пункт 3
119/*
120Создать индекс для одной из таблиц задания 6,
121включив в него дополнительные неключевые поля.
122*/
123IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Car_Idx')
124DROP INDEX Car_Idx ON Car;
125GO
126
127
128CREATE NONCLUSTERED INDEX Car_Idx
129 ON Car (CarId)
130 INCLUDE (CarName, ProductionYear)
131GO
132
133
134-----------------------------------------------------Пункт 4
135/*
136Создать индексированное представление.
137*/
138
139IF OBJECT_ID(N'Manufacturer_VIEW', N'V') is not NULL
140DROP VIEW Manufacturer_VIEW;
141GO
142
143CREATE VIEW Manufacturer_VIEW
144 WITH SCHEMABINDING
145AS
146 SELECT
147 m.ManufacturerId, m.ManufacturerName, m.Country
148 FROM dbo.Manufacturer m
149 WHERE m.Country = m.Country
150 WITH CHECK OPTION
151GO
152
153
154IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Manufacturer_VIEW_Idx')
155DROP INDEX Manufacturer_VIEW_Idx ON Manufacturer;
156GO
157
158CREATE UNIQUE CLUSTERED INDEX Manufacturer_VIEW_Idx
159 ON Manufacturer_VIEW (ManufacturerId)
160GO