hJnTXCPq

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