WJyChNTE

1USE master;
2GO
3
4DROP DATABASE IF EXISTS lab7
5GO
6
7CREATE DATABASE lab7
8GO
9
10USE lab7;
11GO
12
13DROP TABLE IF EXISTS OrderTableFirst
14GO
15
16CREATE TABLE OrderTableFirst (
17    OrderId int IDENTITY(1,1) NOT NULL,
18    DateOrder DATETIME NOT NULL,
19    Tax Money NOT NULL,
20    Amount Money NOT NULL,
21	CONSTRAINT PK_OrderTableFirst PRIMARY KEY CLUSTERED (OrderId),
22	CONSTRAINT CHK_Amount CHECK (Amount > 0),
23)
24GO  
25
26INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
27VALUES ('2011-04-12T00:00:00.000', 13, 2300);
28INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
29VALUES ('2011-04-12T00:00:00.001', 15, 2860);
30INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
31VALUES ('2016-04-12T00:00:00.102', 1, 45);
32Select* from OrderTableFirst;
33GO
34
35DROP VIEW IF EXISTS OrderTableFirst_VIEW
36GO
37
38--1 Создать представление на основе одной из таблиц задания 6.
39CREATE VIEW OrderTableFirst_VIEW AS SELECT
40o.OrderID, o.DateOrder, o.Tax, o.Amount FROM OrderTableFirst AS o
41GO
42
43SELECT * FROM OrderTableFirst_VIEW WHERE OrderID = '2';
44
45USE lab7;
46GO
47
48DROP TABLE IF EXISTS Prov
49GO
50
51CREATE TABLE Prov( 
52    ProvID int PRIMARY KEY,
53    NumberPhoneProvider VARCHAR(15),
54    AddressProvider VARCHAR(255),
55    EmailProvider VARCHAR(255)
56)
57GO
58
59DROP TABLE IF EXISTS Material
60GO
61
62CREATE TABLE Material(
63    MaterialID int PRIMARY KEY IDENTITY(1,1),
64	NameMaterial varchar(30),
65	ProvID int
66	CONSTRAINT FK_Prov_Material FOREIGN KEY (ProvID) REFERENCES Prov (ProvID) ON UPDATE CASCADE
67	)
68GO
69
70INSERT Prov (ProvID, NumberPhoneProvider, EmailProvider)
71VALUES (2, '79777549073', 'vselennaya314@gmail.com');
72INSERT Prov (ProvID, NumberPhoneProvider, EmailProvider)
73VALUES (1, '76786745567', 'mail@gmail.com');
74INSERT Material (NameMaterial, ProvID)
75VALUES ('Cotton', 2);
76INSERT Material (NameMaterial, ProvID)
77VALUES ('Fur', 1);
78GO 
79
80DROP VIEW IF EXISTS MatProv
81GO
82
83--2 Создать представление на основе полей обеих связанных таблиц задания 6.
84CREATE VIEW MatProv WITH SCHEMABINDING AS 
85SELECT m.NameMaterial AS NameM, 
86    p.EmailProvider  AS NameP 
87FROM dbo.Material AS m JOIN dbo.Prov AS p ON m.ProvID = p.ProvID
88GO
89
90GO
91
92--4 Создать индексированное представление
93CREATE UNIQUE CLUSTERED INDEX MatProv
94	ON MatProv (NameM)
95GO
96SELECT * FROM MatProv;
97
98--3 Создать индекс для одной из таблиц задания 6, включив в него дополнительные неключевые поля.
99DROP INDEX IF EXISTS Material.Case3
100GO
101
102CREATE INDEX Case3 ON Material (MaterialID) INCLUDE (NameMaterial);
103SELECT NameMaterial FROM Material WHERE MaterialID > 0;
104GO