UbsAYYYe

1DROP DATABASE IF EXISTS PlayerStats;
2CREATE DATABASE PlayerStats CHARSET 'utf8';
3USE PlayerStats;
4
5CREATE TABLE StatTypes(
6	StatCode VARCHAR(3) NOT NULL PRIMARY KEY,
7	Name VARCHAR(150) NOT NULL
8);
9
10CREATE TABLE Positions(
11	PositionCode VARCHAR(3) NOT NULL PRIMARY KEY,
12	Name VARCHAR(150) NOT NULL
13);
14
15CREATE TABLE Players(
16	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
17	Name VARCHAR(150) NOT NULL,
18    Num INTEGER NOT NULL,
19    PositionCode VARCHAR(3) NOT NULL
20);
21
22CREATE TABLE Tournaments(
23	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
24	Name VARCHAR(150) NOT NULL
25);
26
27CREATE TABLE Matches(
28	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
29	MatchDate DATE NOT NULL,
30    TournamentId INTEGER NOT NULL
31);
32
33CREATE TABLE MatchStats(
34	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
35	MatchId INTEGER NOT NULL,
36    PlayerId INTEGER NOT NULL,
37    EventMinute INTEGER NOT NULL,
38	StatCode VARCHAR(3) NOT NULL
39);
40
41INSERT INTO StatTypes VALUES('G', 'Гол');
42INSERT INTO StatTypes VALUES('A', 'Асистенция');
43INSERT INTO StatTypes VALUES('R', 'Червен картон');
44INSERT INTO StatTypes VALUES('Y', 'Жълт картон');
45INSERT INTO StatTypes VALUES('OG', 'Автогол');
46INSERT INTO StatTypes VALUES('IN', 'Смяна влиза');
47INSERT INTO StatTypes VALUES('OUT', 'Смяна излиза');
48
49INSERT INTO Positions VALUES('GK', 'Вратар');
50INSERT INTO Positions VALUES('RB', 'Десен защитник');
51INSERT INTO Positions VALUES('LB', 'Ляв защитник');
52INSERT INTO Positions VALUES('CB', 'Централен защитник');
53INSERT INTO Positions VALUES('RM', 'Десен полузащитник');
54INSERT INTO Positions VALUES('LM', 'Ляв полузащитник');
55INSERT INTO Positions VALUES('CM', 'Полузащитник');
56INSERT INTO Positions VALUES('CF', 'Централен нападател');
57
58INSERT INTO Players VALUES(NULL, 'Ivaylo Trifonov', 1, 'GK');
59INSERT INTO Players VALUES(NULL, 'Valko Trifonov', 2, 'RB');
60INSERT INTO Players VALUES(NULL, 'Ognyan Yanev', 3, 'CB');
61INSERT INTO Players VALUES(NULL, 'Zahari Dragomirov', 4, 'CB');
62INSERT INTO Players VALUES(NULL, 'Bozhidar Chilikov', 5, 'LB');
63INSERT INTO Players VALUES(NULL, 'Timotei Zahariev', 6, 'CM');
64INSERT INTO Players VALUES(NULL, 'Marin Valentinov', 7, 'CM');
65INSERT INTO Players VALUES(NULL, 'Mitre Cvetkov', 99, 'CF');
66INSERT INTO Players VALUES(NULL, 'Zlatko Genov', 9, 'CF');
67INSERT INTO Players VALUES(NULL, 'Matey Goranov', 10, 'RM');
68INSERT INTO Players VALUES(NULL, 'Sergei Zhivkov', 11, 'LM');
69
70INSERT INTO Tournaments VALUES(NULL, 'Шампионска лига');
71INSERT INTO Tournaments VALUES(NULL, 'Първа лига');
72INSERT INTO Tournaments VALUES(NULL, 'Купа на България');
73INSERT INTO Tournaments VALUES(NULL, 'Суперкупа на България');
74
75INSERT INTO Matches VALUES(NULL, '2018-04-08', 2);
76INSERT INTO Matches VALUES(NULL, '2018-04-13', 2);
77INSERT INTO Matches VALUES(NULL, '2018-04-21', 2);
78INSERT INTO Matches VALUES(NULL, '2018-04-28', 2);
79INSERT INTO Matches VALUES(NULL, '2018-05-06', 2);
80INSERT INTO Matches VALUES(NULL, '2018-05-11', 2);
81INSERT INTO Matches VALUES(NULL, '2017-09-21', 3);
82INSERT INTO Matches VALUES(NULL, '2017-10-26', 3);
83
84INSERT INTO MatchStats VALUES(NULL, 8, 9, 14, 'G');
85INSERT INTO MatchStats VALUES(NULL, 8, 8, 14, 'A');
86INSERT INTO MatchStats VALUES(NULL, 8, 3, 43, 'Y');
87INSERT INTO MatchStats VALUES(NULL, 7, 2, 28, 'Y');
88INSERT INTO MatchStats VALUES(NULL, 7, 10, 45, 'Y');
89INSERT INTO MatchStats VALUES(NULL, 7, 10, 65, 'R');
90INSERT INTO MatchStats VALUES(NULL, 1, 10, 23, 'G');
91INSERT INTO MatchStats VALUES(NULL, 1, 9, 23, 'A');
92INSERT INTO MatchStats VALUES(NULL, 1, 9, 43, 'G');
93INSERT INTO MatchStats VALUES(NULL, 2, 4, 33, 'OG');
94INSERT INTO MatchStats VALUES(NULL, 2, 9, 68, 'G');
95INSERT INTO MatchStats VALUES(NULL, 2, 1, 68, 'A');
96INSERT INTO MatchStats VALUES(NULL, 3, 3, 35, 'G');
97INSERT INTO MatchStats VALUES(NULL, 3, 4, 35, 'A');
98INSERT INTO MatchStats VALUES(NULL, 3, 8, 55, 'G');
99INSERT INTO MatchStats VALUES(NULL, 3, 11, 55, 'A');
100INSERT INTO MatchStats VALUES(NULL, 4, 3, 9, 'G');
101INSERT INTO MatchStats VALUES(NULL, 4, 8, 9, 'G');
102INSERT INTO MatchStats VALUES(NULL, 4, 8, 56, 'OG');
103INSERT INTO MatchStats VALUES(NULL, 5, 8, 67, 'G');