PJMGxVrR

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