· 6 years ago · Oct 21, 2019, 10:24 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
42
43INSERT INTO Squad(Surname,Name,Year_of_birth,Position, Number)
44VALUES (N'Аррисабалага',N'Кепа', 1994, N'ВРТ', 1),
45 (N'Алонсо',N'Маркос', 1990, N'ЛЗ', 3),
46 (N'Рюдигер',N'Антонио', 1993, N'ЦЗ', 2),
47 (N'Томори',N'Фикайо', 1997, N'ЦЗ', 29),
48 (N'Аспиликуэта',N'Сесар', 1989, N'ПЗ', 28),
49 (N'Канте',N'Н’Голо', 1991, N'ЦПЗ', 7),
50 (N'Ковачич',N'Матео', 1994, N'ЦПЗ', 17),
51 (N'Маунт',N'Мейсон', 1999, N'ЦАП', 19),
52 (N'Абрахам',N'Тэмми', 1997, N'ФРВ', 9),
53 (N'Виллиан',N'Боржес да Силва', 1988, N'ПФА', 10),
54 (N'Пулишич',N'Кристиан', 1998, N'ЛФА', 22)
55go
56
57SELECT * FROM Squad
58go
59
60--======================================================================================--
61
62if OBJECT_ID(N'Stadiums',N'U') is NOT NULL
63 DROP TABLE Stadiums;
64go
65
66CREATE TABLE Stadiums (
67 Stadiums_ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NEWID()),
68 Title char(30) NOT NULL,
69 Country char(20) NOT NULL,
70 Year_of_construction int NOT NULL CHECK (Year_of_construction > 1850),
71 Cost money NULL DEFAULT (NULL)
72);
73go
74
75INSERT INTO Stadiums(Title, Country, Year_of_construction)
76VALUES (N'Стэмфорд Бридж',N'Англия', 1877),
77 (N'Ванда Метрополитано',N'Испания', 2017),
78 (N'Сантьяго Бернабеу',N'Испания', 1947),
79 (N'Лужники',N'Россия', 1956),
80 (N'Парк де Пренс',N'Франция', 1897)
81go
82
83INSERT INTO Stadiums(Stadiums_ID,Title,Country,Year_of_construction)
84VALUES ('294B4012-1617-482A-8058-F73039852768',N'Сан-Сиро',N'Италия', 1926)
85
86SELECT * FROM Stadiums
87go
88
89--======================================================================================--
90
91IF EXISTS (SELECT * FROM sys.sequences WHERE NAME = N'Clubs' AND TYPE='SO')
92DROP SEQUENCE TestSequence
93go
94
95CREATE SEQUENCE Clubs
96 START WITH 1
97 INCREMENT BY 1;
98go
99
100if OBJECT_ID(N'Club',N'U') is NOT NULL
101 DROP TABLE Club;
102go
103
104CREATE TABLE Club (
105 element_id int PRIMARY KEY NOT NULL,
106 Title char(50),
107 );
108go
109
110INSERT INTO Club(element_id,Title)
111VALUES (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'Манчестер Юнайтед'),
115 (NEXT VALUE FOR DBO.Clubs,N'Арсенал'),
116 (NEXT VALUE FOR DBO.Clubs,N'Тоттенхэм')
117go
118
119SELECT * From Club
120go
121
122--======================================================================================--