SWUX7YVN

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