VCDhSx7W

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