CruWYwEp

1/*drop database if exists PlayerStats;
2create database PlayerStats;
3*/
4use PlayerStats;
5/*
6create table StatTypes (StatCode varchar(3) primary key, Name varchar(20));
7INSERT INTO StatTypes (StatCode, Name) VALUES ('G', 'Гол');
8INSERT INTO StatTypes (StatCode, Name) VALUES ('A', 'Асистенция');
9INSERT INTO StatTypes (StatCode, Name) VALUES ('R', 'Червен картон');
10INSERT INTO StatTypes (StatCode, Name) VALUES ('Y', 'Жълт картон');
11INSERT INTO StatTypes (StatCode, Name) VALUES ('OG', 'Автогол');
12INSERT INTO StatTypes (StatCode, Name) VALUES ('IN', 'Смяна влиза');
13INSERT INTO StatTypes (StatCode, Name) VALUES ('OUT', 'Смяна излиза');
14
15create table Positions (
16	PositionCode varchar(2) primary key,
17    Name varchar(20)
18);
19
20INSERT INTO Positions (PositionCode, Name) VALUES ('GK', 'Вратар');
21INSERT INTO Positions (PositionCode, Name) VALUES ('RB', 'Десен защитник');
22INSERT INTO Positions (PositionCode, Name) VALUES ('LB', 'Ляв защитник');
23INSERT INTO Positions (PositionCode, Name) VALUES ('CB', 'Централен защитник');
24INSERT INTO Positions (PositionCode, Name) VALUES ('RM', 'Десен полузащитник');
25INSERT INTO Positions (PositionCode, Name) VALUES ('LM', 'Ляв полузащитник');
26INSERT INTO Positions (PositionCode, Name) VALUES ('CM', 'Полузащитник');
27INSERT INTO Positions (PositionCode, Name) VALUES ('CF', 'Централен нападател');
28
29
30create table Players (
31	Id Integer NOT NULL primary key auto_increment,
32    Name varchar(20), 
33    Num Integer not null,
34	PositionCode varchar(3),
35    foreign key(PositionCode) references Positions(PositionCode)
36);
37
38INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('1', 'Ivailo Trifonov', '1', 'GK');
39INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('2', 'Valko Trifonov', '2', 'RB');
40INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('3', 'Ognyan Yanev', '3', 'CB');
41INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('4', 'Zahari Dragomirov', '4', 'CB');
42INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('5', 'Bozhidar Chilikov', '5', 'LB');
43INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('6', 'Bozhidar Chilikov', '6', 'CM');
44INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('7', 'Marin Valentinov', '7', 'CM');
45INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('8', 'Mitre Cvetkov', '99', 'CF');
46INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('9', 'Zlatko Genov', '9', 'CF');
47INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('10', 'Matey Goranov', '10', 'RM');
48INSERT INTO Players (Id, Name, Num, PositionCode) VALUES ('11', 'Sergei Zhivkov', '11', 'LM');
49
50
51create table Tournaments (
52	Id Integer NOT NULL primary key auto_increment,
53    Name varchar(30)
54);
55
56INSERT INTO Tournaments (Id, Name) VALUES ('1', 'Шампионска лига');
57INSERT INTO Tournaments (Id, Name) VALUES ('2', 'Първа лига');
58INSERT INTO Tournaments (Id, Name) VALUES ('3', 'Купа на България');
59INSERT INTO Tournaments (Id, Name) VALUES ('4', 'Суперкупа на България');
60
61create table Matches (
62	Id Integer NOT NULL primary key auto_increment,
63    MatchDate date,
64    TournamentId Integer not null,
65	foreign key(TournamentId) references Tournaments(Id)
66);
67
68INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('1', '2018-04-08', '2');
69INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('2', '2018-04-13', '2');
70INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('3', '2018-04-21', '2');
71INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('4', '2018-04-28', '2');
72INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('5', '2018-05-06', '2');
73INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('6', '2018-05-11', '2');
74INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('7', '2017-09-21', '3');
75INSERT INTO Matches (Id, MatchDate, TournamentId) VALUES ('8', '2017-10-26', '3');
76
77create table MatchStats (
78	Id Integer NOT NULL primary key auto_increment,
79    MatchId Integer,
80    foreign key(MatchId) references Matches(Id),
81    PlayerId Integer,
82    foreign key(PlayerId) references Players(Id),
83    EventMinute Integer,
84    StatCode varchar(3),
85    foreign key(StatCode) references StatTypes(StatCode)
86);
87
88INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('1', '8', '9', '14', 'G');
89INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('2', '8', '8', '14', 'A');
90INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('3', '8', '3', '43', 'Y');
91INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('4', '7', '2', '28', 'Y');
92INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('5', '7', '10', '45', 'Y');
93INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('6', '7', '10', '65', 'R');
94INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('7', '1', '10', '23', 'G');
95INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('8', '1', '9', '23', 'A');
96INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('9', '1', '9', '43', 'G');
97INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('10', '2', '4', '33', 'OG');
98INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('11', '2', '9', '68', 'G');
99INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('12', '2', '1', '68', 'A');
100INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('13', '3', '3', '35', 'G');
101INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('14', '3', '4', '35', 'A');
102INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('15', '3', '8', '55', 'G');
103INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('16', '3', '11', '55', 'A');
104INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('17', '4', '3', '9', 'G');
105INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('18', '4', '8', '9', 'G');
106INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('19', '4', '8', '56', 'OG');
107INSERT INTO MatchStats (Id, MatchId, PlayerId, EventMinute, StatCode) VALUES ('20', '5', '8', '67', 'G');
108
109SELECT p.Name, p.Num FROM Players p
110LEFT JOIN Positions po	ON p.PositionCode = po.PositionCode
111WHERE po.Name LIKE '% защитник%';
112
113SELECT m.MatchDate, t.Name From Matches m 
114LEFT JOIN Tournaments t ON m.TournamentId = t.Id
115WHERE m.MatchDate LIKE '%04%';
116
117Select mat.MatchDate, player.Name, player.Num, event.EventMinute, stat.Name 
118FROM MatchStats event 
119LEFT JOIN Matches mat on event.MatchId = mat.Id
120LEFT JOIN Players player on event.PlayerId = player.Id
121LEFT JOIN StatTypes stat on event.StatCode = stat.StatCode
122WHERE player.Num = 99;
123
124Select Count(ms.StatCode) FROM MatchStats ms
125LEFT JOIN StatTypes stat ON ms.StatCode = stat.StatCode
126WHERE stat.Name = 'Автогол';
127
128SELECT m.MatchDate, Count(stat.Name) FROM Matches m
129LEFT JOIN MatchStats ms ON m.Id = ms.MatchId
130LEFT JOIN StatTypes stat ON ms.StatCode = stat.StatCode
131WHERE m.MatchDate < '2018-05-01' AND stat.Name = 'Гол'
132GROUP BY m.MatchDate 
133HAVING Count(stat.Name = 'Гол') >= 1;
134
135SELECT pos.Name, Count(st.Name) from MatchStats ms
136LEFT JOIN StatTypes st ON ms.StatCode = st.StatCode 
137RIGHT JOIN Players pl ON ms.PlayerId = pl.Id AND st.Name = 'Гол'
138LEFT JOIN Positions pos ON pl.PositionCode = pos.PositionCode 
139GROUP BY pos.Name;
140*/
141
142SELECT pl.Name, pl.Num, pos.Name, Count(st.Name) FROM MatchStats ms
143LEFT JOIN StatTypes st ON ms.StatCode = st.StatCode 
144LEFT JOIN Players pl ON ms.PlayerId = pl.Id
145LEFT JOIN Positions pos ON pl.PositionCode = pos.PositionCode 
146WHERE st.Name LIKE '%картон%' 
147GROUP BY pl.Name, pl.Num, pos.Name
148ORDER BY (Count(st.Name like '%картон%')) DESC;
149*/