· 6 years ago · Oct 28, 2019, 08:22 PM
1use master;
2go
3if DB_ID (N'lab6') is not null
4drop database lab6;
5go
6create database lab6
7on (
8NAME = lab6dat,
9FILENAME = 'C:\DB\lab6\lab6dat.mdf',
10SIZE = 10,
11MAXSIZE = UNLIMITED,
12FILEGROWTH = 5
13)
14log on (
15NAME = lab6log,
16FILENAME = 'C:\DB\lab6\lab6log.ldf',
17SIZE = 5,
18MAXSIZE = 20,
19FILEGROWTH = 5
20);
21go
22
23--======================================================================================--
24use lab6;
25go
26if OBJECT_ID(N'Squad',N'U') is NOT NULL
27 DROP TABLE Squad;
28go
29
30CREATE TABLE Squad (
31 Squad_ID int IDENTITY(1,1) PRIMARY KEY,
32 Surname char(30) NOT NULL,
33 Name char(30) NOT NULL,
34 Year_of_birth numeric(4) NULL CHECK (Year_of_birth > 1985 AND Year_of_birth < 2000),
35 Position char(3),
36 Number numeric(2),
37 Biography char(1000) DEFAULT ('Unknown'),
38 CONSTRAINT checkSquad CHECK (Number < 100)
39);
40go
41
42INSERT INTO Squad(Surname,Name,Year_of_birth,Position, Number)
43VALUES (N'Аррисабалага',N'Кепа', 1994, N'ВРТ', 1),
44 (N'Алонсо',N'Маркос', 1990, N'ЛЗ', 3),
45 (N'Рюдигер',N'Антонио', 1993, N'ЦЗ', 2),
46 (N'Томори',N'Фикайо', 1997, N'ЦЗ', 29),
47 (N'Аспиликуэта',N'Сесар', 1989, N'ПЗ', 28),
48 (N'Канте',N'Н’Голо', 1991, N'ЦПЗ', 7),
49 (N'Ковачич',N'Матео', 1994, N'ЦПЗ', 17),
50 (N'Маунт',N'Мейсон', 1999, N'ЦАП', 19),
51 (N'Абрахам',N'Тэмми', 1997, N'ФРВ', 9),
52 (N'Виллиан',N'Боржес да Силва', 1988, N'ПФА', 10),
53 (N'Пулишич',N'Кристиан', 1998, N'ЛФА', 22)
54go
55
56SELECT * FROM Squad
57go
58
59SELECT IDENT_CURRENT('dbo.Squad') as Squad_ID
60go
61--======================================================================================--
62
63if OBJECT_ID(N'Stadiums',N'U') is NOT NULL
64 DROP TABLE Stadiums;
65go
66
67CREATE TABLE Stadiums (
68 Stadiums_ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NEWID()),
69 Title char(30) NOT NULL,
70 Country char(20) NOT NULL,
71 Year_of_construction int NOT NULL CHECK (Year_of_construction > 1850),
72 Cost money NULL DEFAULT (NULL)
73);
74go
75
76INSERT INTO Stadiums(Title, Country, Year_of_construction)
77VALUES (N'Стэмфорд Бридж',N'Англия', 1877),
78 (N'Ванда Метрополитано',N'Испания', 2017),
79 (N'Сантьяго Бернабеу',N'Испания', 1947),
80 (N'Лужники',N'Россия', 1956),
81 (N'Парк де Пренс',N'Франция', 1897)
82go
83
84SELECT * FROM Stadiums
85go
86
87--======================================================================================--
88
89IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'Clubs' AND TYPE='SO')
90DROP SEQUENCE TestSequence
91go
92
93CREATE SEQUENCE Clubs
94 START WITH 1
95 INCREMENT BY 1;
96go
97
98if OBJECT_ID(N'Club',N'U') is NOT NULL
99 DROP TABLE Club;
100go
101
102CREATE TABLE Club (
103 element_id int PRIMARY KEY NOT NULL,
104 Title char(50),
105 );
106go
107
108INSERT INTO Club(element_id,Title)
109VALUES (NEXT VALUE FOR DBO.Clubs,N'Челси'),
110 (NEXT VALUE FOR DBO.Clubs,N'Ливерпуль'),
111 (NEXT VALUE FOR DBO.Clubs,N'Манчестер Сити'),
112 (NEXT VALUE FOR DBO.Clubs,N'Манчестер Юнайтед'),
113 (NEXT VALUE FOR DBO.Clubs,N'Арсенал'),
114 (NEXT VALUE FOR DBO.Clubs,N'Тоттенхэм')
115go
116
117SELECT * From Club
118go
119
120--======================================================================================--
121IF OBJECT_ID(N'FK_Teams', N'F') IS NOT NULL
122ALTER TABLE Players DROP CONSTRAINT FK_Teams
123go
124
125IF OBJECT_ID(N'Teams') IS NOT NULL
126DROP TABLE Teams;
127go
128
129CREATE TABLE Teams(
130 Teams_ID int PRIMARY KEY NOT NULL,
131 Title char(30) NOT NULL,
132 Country char(20) NOT NULL,
133 Division char(20) DEFAULT ('Unknown')
134);
135
136INSERT INTO Teams(Teams_ID,Title, Country)
137VALUES (1,'Челси', 'Англия'),
138 (2,'Бавария', 'Германия'),
139 (3,'ПСЖ', 'Франция'),
140 (4,'Зенит', 'Россия')
141go
142
143SELECT * FROM Teams;
144go
145
146IF OBJECT_ID(N'Players') IS NOT NULL
147DROP TABLE Players;
148go
149
150CREATE TABLE Players(
151 Players_ID int IDENTITY(1,1) PRIMARY KEY,
152 Surname char(30) NOT NULL,
153 Name char(30) NOT NULL,
154 Number int NOT NULL,
155 External_ID int NULL,
156 Biography char(1000) DEFAULT ('Unknown'),
157 CONSTRAINT FK_Teams FOREIGN KEY (External_ID) REFERENCES Teams (Teams_ID)
158 --ON UPDATE CASCADE
159 --ON UPDATE SET NULL
160 --ON UPDATE SET DEFAULT
161 ON DELETE CASCADE
162 --ON DELETE SET NULL
163 --ON UPDATE NO ACTION
164 --ON DELETE NO ACTION
165 --ON DELETE SET DEFAULT
166);
167go
168
169INSERT INTO Players(Surname,Name,Number, External_ID)
170VALUES ('Левандовски', 'Роберт', 9, 1),
171 ('Эдинсон', 'Кавани', 22,2),
172 ('Криштиану', 'Роналду', 7,3),
173 ('Головин', 'Александр', 17,4)
174go
175
176SELECT * FROM Players
177go
178
179DELETE FROM Teams
180WHERE Teams_ID = 2
181go
182
183/*UPDATE Teams
184SET Title = 'ssssss'
185DELETE FROM Teams
186go*/
187
188SELECT * FROM Teams
189SELECT * FROM Players