CYYneSNP

1USE master ;  
2GO  
3
4IF DB_ID (N'lab6') IS NOT NULL
5DROP DATABASE lab6; 
6GO
7
8CREATE DATABASE lab6  
9GO
10--1
11USE lab6;
12GO
13
14DROP TABLE IF EXISTS OrderTableFirst
15GO
16
17CREATE TABLE OrderTableFirst (
18    OrderId int IDENTITY(1,1) NOT NULL,
19    DateOrder DATETIME NOT NULL,
20    Tax Money NOT NULL,
21    Amount Money NOT NULL,
22	
23	CONSTRAINT PK_OrderTableFirst PRIMARY KEY CLUSTERED (OrderId),
24	CONSTRAINT CHK_Amount CHECK (Amount > 0),
25)
26GO  
27
28--2
29ALTER TABLE OrderTableFirst ADD Bonus Money DEFAULT '0' 
30GO
31
32INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
33VALUES ('2011-04-12T00:00:00.000', 13, 2300);
34
35INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
36VALUES ('2011-04-12T00:00:00.001', 15, 2860);
37
38INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
39VALUES ('2016-04-12T00:00:00.102', 1, 45);
40
41SELECT SCOPE_IDENTITY()
42
43Select*from OrderTableFirst;
44GO
45
46--3
47USE lab6;
48GO
49
50IF OBJECT_ID (N'Client') IS NOT NULL
51DROP TABLE Client; 
52
53CREATE TABLE Client (
54	ClientID UNIQUEIDENTIFIER DEFAULT NEWID(),
55	NumberPhone VARCHAR(15),
56    AddressClient VARCHAR(255),
57    BirthdayClient DATETIME,
58    Email VARCHAR(255),
59    SurnameClient VARCHAR(100),
60    NameClient VARCHAR(100),
61    PatronymicClient VARCHAR(100),
62	CONSTRAINT PK_ClientID PRIMARY KEY CLUSTERED (ClientID)
63)
64GO 
65
66INSERT INTO Client(NameClient, SurnameClient, NumberPhone) VALUES ('Uma', 'Mirzoeva', '79777549073');
67INSERT INTO Client(NameClient, SurnameClient, NumberPhone) VALUES ('Max', 'Boichuk', '79645671413');
68
69Select*from Client;
70GO
71
72
73--4
74USE lab6;
75GO
76
77CREATE TABLE ProviderOfMaterials(
78    ProviderOfMaterialsID int PRIMARY KEY,
79	NumberPhoneProvider VARCHAR(15),
80    AddressProvider VARCHAR(255),
81    EmailProvider VARCHAR(255)
82)
83GO
84
85CREATE SEQUENCE ProviderOfMaterialsCount
86START WITH 0
87INCREMENT BY 1;
88GO
89
90INSERT ProviderOfMaterials (ProviderOfMaterialsID, AddressProvider, NumberPhoneProvider)
91VALUES (NEXT VALUE FOR ProviderOfMaterialsCount, 'Moscow, Red Square', '79777549073');
92INSERT ProviderOfMaterials (ProviderOfMaterialsID, AddressProvider, NumberPhoneProvider)
93VALUES (NEXT VALUE FOR ProviderOfMaterialsCount, 'Derbent, 6 magal', '79064821514');
94
95Select*from ProviderOfMaterials;
96GO
97
98
99-- 5
100USE lab6;
101GO
102
103CREATE TABLE Prov( 
104    ProvID int PRIMARY KEY,
105    NumberPhoneProvider VARCHAR(15),
106    AddressProvider VARCHAR(255),
107    EmailProvider VARCHAR(255)
108)
109GO
110
111CREATE TABLE Material(
112    MaterialID int PRIMARY KEY IDENTITY(1,1),
113	NameMaterial varchar(30),
114	ProvID int
115	CONSTRAINT FK_Prov_Material FOREIGN KEY (ProvID) REFERENCES Prov (ProvID)
116		 ON UPDATE CASCADE
117		-- ON UPDATE NO ACTION
118		--  ON UPDATE SET NULL
119		--  ON UPDATE SET DEFAULT 
120
121	)
122GO
123
124INSERT Prov (ProvID, NumberPhoneProvider, EmailProvider)
125VALUES (2, '79777549073', 'vselennaya314@gmail.com');
126
127INSERT Material (NameMaterial, ProvID)
128VALUES ('Cotton', 2);
129
130Select *from Material;
131GO
132UPDATE Prov
133SET ProvID = 1
134WHERE ProvID = 2
135Select *from Material;