yt58452b

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