mgEvZRSm

1DROP DATABASE IF EXISTS PlayerStats;
2
3CREATE DATABASE PlayerStats CHARSET 'utf8';
4
5USE PlayerStats;
6
7CREATE TABLE StatTypes(
8
9	StatCode VARCHAR(3) PRIMARY KEY NOT NULL,
10    Name VARCHAR(50) NOT NULL
11    
12);
13
14INSERT INTO StatTypes VALUES('G','Гол');
15INSERT INTO StatTypes VALUES('A','Асистенция');
16INSERT INTO StatTypes VALUES('R','Червен картон');
17INSERT INTO StatTypes VALUES('Y','Жълт картон');
18INSERT INTO StatTypes VALUES('OG','Автогол');
19INSERT INTO StatTypes VALUES('IN','Смяна влиза');
20INSERT INTO StatTypes VALUES('OUT','Смяна излиза');
21
22CREATE TABLE Positions(
23
24	PositionCode CHAR(2) PRIMARY KEY NOT NULL,
25    Name VARCHAR(50) NOT NULL
26    
27);
28
29INSERT INTO Positions VALUES('GK','Вратар');
30INSERT INTO Positions VALUES('RB','Десен защитник');
31INSERT INTO Positions VALUES('LB','Ляв защитник');
32INSERT INTO Positions VALUES('CB','Централен защитник');
33INSERT INTO Positions VALUES('RM','Десен полузащитник');
34INSERT INTO Positions VALUES('LM','Ляв полузащитник');
35INSERT INTO Positions VALUES('CM','Полузащитник');
36INSERT INTO Positions VALUES('CF','Централен нападател');
37
38CREATE TABLE Players(
39
40	Id INTEGER AUTO_INCREMENT PRIMARY KEY,
41    Name VARCHAR(50) NOT NULL,
42    Num INTEGER NOT NULL,
43    PositionCode CHAR(2) NOT NULL,
44    FOREIGN KEY (PositionCode) REFERENCES Positions(PositionCode)
45    
46);
47
48INSERT INTO Players (Name, Num, PositionCode) VALUES('Ivaylo Trifonov',1,'GK');
49INSERT INTO Players (Name, Num, PositionCode) VALUES('Valko Trifonov',2,'RB');
50INSERT INTO Players (Name, Num, PositionCode) VALUES('Ognyan Yanev',3,'CB');
51INSERT INTO Players (Name, Num, PositionCode) VALUES('Zahari Dragomirov ',4,'CB');
52INSERT INTO Players (Name, Num, PositionCode) VALUES('Bozhidar Chilikov ',5,'LB');
53INSERT INTO Players (Name, Num, PositionCode) VALUES('Timotei Zahariev',6,'CM');
54INSERT INTO Players (Name, Num, PositionCode) VALUES('Marin Valentinov',7,'CM');
55INSERT INTO Players (Name, Num, PositionCode) VALUES('Mitre Cvetkov',99,'CF');
56INSERT INTO Players (Name, Num, PositionCode) VALUES('Zlatko Genov',9,'CF');
57INSERT INTO Players (Name, Num, PositionCode) VALUES('Matey Goranov',10,'RM');
58INSERT INTO Players (Name, Num, PositionCode) VALUES('Sergei Zhivkov',11,'LM');
59
60CREATE TABLE Tournaments(
61
62	Id INTEGER AUTO_INCREMENT PRIMARY KEY,
63    Name VARCHAR(50) NOT NULL
64    
65);
66
67INSERT INTO Tournaments (Name) VALUES('Шампионска лига');
68INSERT INTO Tournaments (Name) VALUES('Първа лига');
69INSERT INTO Tournaments (Name) VALUES('Купа на България');
70INSERT INTO Tournaments (Name) VALUES('Суперкупа на България');
71
72CREATE TABLE Matches(
73	
74	Id INTEGER AUTO_INCREMENT PRIMARY KEY,
75    MatchDate DATE NOT NULL,
76    TournamentId INTEGER NOT NULL,
77    FOREIGN KEY (TournamentId) REFERENCES Tournaments(Id)
78    
79);
80
81INSERT INTO Matches (MatchDate, TournamentId) VALUES('2018-04-08',2);
82INSERT INTO Matches (MatchDate, TournamentId) VALUES('2018-04-13',2);
83INSERT INTO Matches (MatchDate, TournamentId) VALUES('2018-04-21',2);
84INSERT INTO Matches (MatchDate, TournamentId) VALUES('2018-04-28',2);
85INSERT INTO Matches (MatchDate, TournamentId) VALUES('2018-05-06',2);
86INSERT INTO Matches (MatchDate, TournamentId) VALUES('2018-05-11',2);
87INSERT INTO Matches (MatchDate, TournamentId) VALUES('2017-09-21',3);
88INSERT INTO Matches (MatchDate, TournamentId) VALUES('2017-10-26',3);
89
90CREATE TABLE MatchStats(
91
92	Id INTEGER AUTO_INCREMENT PRIMARY KEY,
93    MatchId INTEGER NOT NULL,
94    PlayerId INTEGER NOT NULL,
95    EventMinute INTEGER NOT NULL,
96    StatCode VARCHAR(3) NOT NULL,
97    FOREIGN KEY (MatchId) REFERENCES Matches(Id),
98    FOREIGN KEY (PlayerId) REFERENCES Players(Id),
99    FOREIGN KEY (StatCode) REFERENCES StatTypes(StatCode)
100
101);
102
103INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(8,9,14,'G');
104INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(8,8,14,'A');
105INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(8,3,43,'Y');
106INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(7,2,28,'Y');
107INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(7,10,45,'Y');
108INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(7,10,65,'R');
109INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(1,10,23,'G');
110INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(1,9,23,'A');
111INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(1,9,43,'G');
112INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(2,4,33,'OG');
113INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(2,9,68,'G');
114INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(2,1,68,'A');
115INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(3,3,35,'G');
116INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(3,4,35,'A');
117INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(3,8,55,'G');
118INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(3,11,55,'A');
119INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(4,3,9,'G');
120INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(4,8,9,'G');
121INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(4,8,56,'OG');
122INSERT INTO MatchStats (MatchId, PlayerId, EventMinute, StatCode) VALUES(5,8,67,'G');
123
124SELECT p.Name, p.Num 
125FROM Players AS p 
126LEFT JOIN Positions AS position
127ON p.PositionCode = position.PositionCode
128WHERE position.Name LIKE '%защитник%';
129
130SELECT m.MatchDate, t.Name 
131FROM Matches AS m 
132LEFT JOIN Tournaments AS t
133ON m.TournamentId = t.Id
134WHERE m.MatchDate >= '2018-04-01' AND m.MatchDate <= '2018-04-30';
135
136SELECT matches.MatchDate, player.Name, player.Num, stats.EventMinute, stat_type.Name
137FROM Players AS player
138LEFT JOIN MatchStats AS stats ON player.Id = stats.PlayerId
139LEFT JOIN Matches AS matches ON stats.MatchId = matches.Id
140LEFT JOIN StatTypes AS stat_type ON stats.StatCode = stat_type.StatCode
141WHERE player.Num = 99;
142
143SELECT COUNT(*)
144FROM MatchStats AS stats
145WHERE stats.StatCode = 'OG';
146
147SELECT matches.MatchDate, COUNT(stats.StatCode)
148FROM Matches AS matches
149LEFT JOIN MatchStats AS stats ON matches.Id = stats.MatchId
150WHERE matches.MatchDate < '2018-05-01' && stats.StatCode LIKE '%G%'
151GROUP BY matches.MatchDate; 
152
153SELECT position.Name, COUNT(*)
154FROM Positions AS position
155INNER JOIN Players player ON position.PositionCode = player.PositionCode
156INNER JOIN MatchStats AS stats ON player.Id = stats.PlayerId
157WHERE stats.StatCode LIKE '%G%'
158GROUP BY position.Name;
159
160SELECT player.Name, player.Num, position.Name, COUNT(stats.StatCode)
161FROM Players AS player
162LEFT JOIN Positions AS position ON player.PositionCode = position.PositionCode
163LEFT JOIN MatchStats AS stats ON player.Id = stats.PlayerId AND (stats.StatCode LIKE '%Y%' OR stats.StatCode LIKE '%R%')
164GROUP BY player.Name ,player.Num ,position.Name
165ORDER BY COUNT(*) DESC;