· 8 years ago · Apr 17, 2018, 10:34 AM
1DROP DATABASE IF EXISTS HandballDivision;
2CREATE DATABASE HandballDivision;
3USE HandballDivision;
4SET SQL_SAFE_UPDATES = 0;
5
6DROP TABLE IF EXISTS PlayerInjury;
7DROP TABLE IF EXISTS Injury;
8DROP TABLE IF EXISTS PlayerResult;
9DROP TABLE IF EXISTS Player;
10DROP TABLE IF EXISTS Coach;
11DROP TABLE IF EXISTS Game;
12DROP TABLE IF EXISTS Club;
13DROP TABLE IF EXISTS Transfer;
14DROP TABLE IF EXISTS Division;
15
16CREATE TABLE Game
17 (MatchID VARCHAR(5),
18 HomeClubGoals DECIMAL(5,0),
19 AwayClubGoals DECIMAL(5,0),
20 GameDate Date,
21 HomeClubID VARCHAR(5),
22 AwayClubID VARCHAR(5),
23 PRIMARY KEY (MatchID)
24 );
25
26CREATE TABLE Division
27 (DivisionID VARCHAR(3),
28 DivisionName VARCHAR(25),
29 PRIMARY KEY (DivisionID)
30 );
31
32 CREATE TABLE Injury
33 (InjuryID VARCHAR(3),
34 Diagnosis VARCHAR(20),
35 PRIMARY KEY (InjuryID)
36 );
37
38CREATE TABLE Club
39 (ClubID VARCHAR(4),
40 ClubName VARCHAR(25),
41 FoundingYear YEAR,
42 City VARCHAR(25),
43 DivisioniD VARCHAR(3),
44 PRIMARY KEY(ClubID),
45 FOREIGN KEY(DivisionID) REFERENCES Division(DivisionID) ON DELETE SET NULL
46 );
47
48CREATE TABLE Player
49 (PlayerId VARCHAR(5),
50 Firstname VarChar(20),
51 Lastname VarChar(20),
52 Birthday Date,
53 Country VARCHAR(20),
54 Salary VARCHAR(10),
55 ClubID VARCHAR(4),
56 PRIMARY KEY (PlayerID),
57 FOREIGN KEY (ClubID) REFERENCES Club(ClubID) ON DELETE SET NULL
58 );
59
60CREATE TABLE PlayerResult
61 (MatchID VARCHAR(5),
62 PlayerID VARCHAR(5),
63 NoOfGoals DECIMAL(5,0),
64 TwoMinSusp DECIMAL(5,0),
65 YellowCards DECIMAL(5,0),
66 RedCards DECIMAL(5,0),
67 Saves DECIMAL(5,0),
68 PRIMARY KEY (MatchID, PlayerID),
69 FOREIGN KEY (MatchID) REFERENCES Game(MatchID) ON DELETE CASCADE,
70 FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID) ON DELETE CASCADE
71 );
72
73
74
75CREATE TABLE Transfer
76 (TransferID VARCHAR(5),
77 PlayerID VARCHAR(5),
78 BuyingClubID VARCHAR(4),
79 SellingClubID VARCHAR(4),
80 TransferDate DATE,
81 Price VARCHAR(15),
82 PRIMARY KEY(TransferID),
83 FOREIGN KEY (BuyingClubID) REFERENCES Club(ClubID) ON DELETE CASCADE
84 );
85
86
87CREATE TABLE Coach
88 (CoachID VARCHAR(4),
89 FirstName VARCHAR(20),
90 LastName VARCHAR(20),
91 BirthDay DATE,
92 Country VARCHAR(20),
93 Salary VARCHAR(10),
94 ClubID VARCHAR(4),
95 PRIMARY KEY(CoachID),
96 FOREIGN KEY (ClubID) REFERENCES Club(ClubID) ON DELETE SET NULL
97 );
98
99CREATE TABLE PlayerInjury
100 (PlayerID VARCHAR(5),
101 InjuryID VARCHAR(3),
102 Active VARCHAR(3),
103 InjuryDate Date,
104 PRIMARY KEY (PlayerID,InjuryID),
105 FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID) ON DELETE CASCADE,
106 FOREIGN KEY (InjuryID) REFERENCES Injury(InjuryID) ON DELETE CASCADE
107 );
108
109 DROP VIEW IF EXISTS ClubStatus;
110
111#### VIEWS ####
112# Calculates statistics of clubs in all divisions.
113CREATE VIEW ClubStatus as
114 (SELECT ClubID, ClubName as Clubname,
115 SUM(
116 CASE WHEN (ClubID = HomeClubID AND HomeClubGoals > AwayClubGoals) OR (ClubID = AwayClubID AND AwayClubGoals > HomeClubGoals) THEN 2
117 WHEN (ClubID = HomeClubID OR ClubID = AwayClubID) AND (HomeClubGoals = AwayClubGoals) THEN 1
118 ELSE 0
119 END) AS TotalPoints,
120 COUNT(CASE WHEN (ClubID = HomeClubID AND HomeClubGoals > AwayClubGoals) OR (ClubID = AwayClubID AND AwayClubGoals > HomeClubGoals) THEN 1 END) as Wins,
121 COUNT(CASE WHEN (ClubID = HomeClubID OR ClubID = AwayClubID) AND (HomeClubGoals = AwayClubGoals) THEN 1 END) as draws,
122 COUNT(CASE WHEN (ClubID = HomeClubID AND HomeClubGoals < AwayClubGoals) OR (ClubID = AwayClubID AND AwayClubGoals < HomeClubGoals) THEN 1 END) as Losses,
123 DivisionID
124 FROM Game, Club
125 GROUP BY ClubID);
126
127SELECT * FROM ClubStatus;
128
129DROP VIEW IF EXISTS TotalPlayerGoals;
130
131#Shows all players and their total goals this season
132CREATE VIEW TotalPlayerGoals as
133SELECT PlayerID, Firstname, Lastname, Sum(NoOfGoals) AS Goals
134FROM Player NATURAL JOIN PlayerResult
135Group By PlayerID;
136
137
138
139SELECT * FROM TotalPlayerGoals;
140
141
142# 7. Typical SQL Statements
143
144# Show average Salary for each club.
145SELECT ClubID, ClubName, Avg(Salary) As AvgSalary
146FROM Club NATURAL JOIN Player
147Group By ClubID Order By AvgSalary DESC;
148
149
150# Show total 2 minute suspensions and red/yellow cards of players
151SELECT PlayerID, Sum(TwoMinSusp) as TotTwoMinSusp, Sum(YellowCards) AS TotYellowCards, Sum(RedCards) AS TotRedCards
152FROM PlayerResult
153GROUP BY PlayerID;
154
155
156
157# Increase salary by 5% of all players with more than 21 Goals (Update)
158UPDATE Player SET Player.Salary = Player.Salary*(1.05)
159WHERE
160(SELECT PlayerID From TotalPlayerGoals WHERE Goals > 21) = Player.PlayerID;
161
162# Show staff (players + coach(es)) of a club, GOG in this example.
163# DOES NOT WORK
164SELECT Player.FirstName, Player.LastName, Coach.FirstName, Player.LastName
165FROM Player, Coach WHERE Player.ClubID = '1001' AND Coach.ClubID = '1001';
166
167# Show players currently injured
168SELECT * FROM PlayerInjury WHERE Active = 'Yes';
169
170
171
172
173#### PROCEDURES ####
174####################
175
176DROP PROCEDURE IF EXISTS HomeOrAway;
177 # This procedure is called when the UpdateGame is triggered, it then updates the HomeClubGoals and
178 # AwayClubGoals in the Game table after Playeresults are being inserted.
179DELIMITER //
180CREATE PROCEDURE HomeOrAway
181 (IN vClubID VARCHAR(5), IN vHomeClubID VARCHAR(5), IN vAwayClubID VARCHAR(5), IN vMatchID VARCHAR(5), IN vNoOfGoals DECIMAL(4,0))
182BEGIN
183 IF (vClubID = vHomeClubID) THEN
184 UPDATE Game
185 SET Game.HomeClubGoals = Game.HomeClubGoals + vNoOfGoals
186 WHERE Game.MatchID = vMatchID;
187 ELSE
188 UPDATE Game
189 SET Game.AwayClubGoals = Game.AwayClubGoals+vNoOfGoals
190 WHERE Game.MatchID = vMatchID;
191 END IF;
192END; //
193DELIMITER ;
194
195# Sets an injury inactive for a chosen player's injury.
196CREATE PROCEDURE UpdatePlayerInjury (IN vPlayerID VARCHAR(5), IN vInjuryID VARCHAR(3))
197UPDATE PlayerInjury SET PlayerInjury.Active = 'No' WHERE PlayerInjury.PlayerID = vPlayerID AND PlayerInjury.InjuryID = vInjuryID;
198
199CALL UpdatePlayerInjury(17951,'704');
200SELECT * FROM PlayerInjury;
201
202#### TRIGGERS ####
203##################
204
205DROP TRIGGER IF EXISTS UpdateGame;
206# This trigger calls the procedure HomeOrAway after values are inserted into the PlayerResult table.
207DELIMITER //
208CREATE TRIGGER UpdateGame
209BEFORE INSERT ON PlayerResult FOR EACH ROW
210 IF NOT EXISTS (SELECT * FROM GAME WHERE Game.MatchID = NEW.MatchID)
211 THEN SIGNAL SQLSTATE 'HY000'
212 SET MYSQL_ERRNO = 1525,
213 MESSAGE_TEXT = 'This MatchID does not exist in Game.';
214 ELSE
215 CALL HomeOrAWAY ((SELECT ClubID FROM Player WHERE PlayerID = NEW.PlayerID), (SELECT HomeClubID FROM Game WHERE MatchID = NEW.MatchID),
216 (SELECT AwayClubID FROM Game WHERE MatchID = NEW.MatchID), NEW.MatchID, NEW.NoOfGoals);
217 END IF; //
218DELIMITER ;
219
220
221
222DROP TRIGGER IF EXISTS TransferUpdate;
223
224# Changes the Club a Player belongs to after he has gone through a Transfer.
225DELIMITER //
226CREATE TRIGGER TransferUpdate
227AFTER INSERT ON Transfer FOR EACH ROW
228 UPDATE Player
229 SET Player.ClubID = NEW.BuyingClubID WHERE Player.PlayerID=NEW.PlayerID
230; //
231DELIMITER ;
232
233SELECT * FROM PLAYER;
234DROP FUNCTION IF EXISTS PreviousClub;
235#### FUNCTIONS ####
236DELIMITER //
237CREATE FUNCTION PreviousClub(vPlayerID VARCHAR(5)) RETURNS VARCHAR(20)
238BEGIN
239 #DECLARE vClubName VARCHAR(20);
240 Return (SELECT SellingClubID FROM transfer WHERE PlayerID=vPlayerID AND TransferDate = (SELECT MAX(TransferDate) FROM Transfer WHERE PlayerID=vPlayerID));
241 #RETURN vClubName;
242END; //
243DELIMITER ;
244
245
246
247SELECT PlayerID, PreviousClub(PlayerID) FROM Transfer;
248SELECT PreviousClub(19821);
249SELECT PreviousClub(10894);
250SELECT * FROM Transfer;
251
252#### EVENTS ####
253################
254SET GLOBAL event_scheduler = 1;
255
256# Restarts the database when a new season begins.
257CREATE EVENT NewSeason
258ON SCHEDULE EVERY 1 YEAR
259STARTS '2018-06-01 23:59:59'
260DO DELETE FROM Game;
261
262
263#### INSERTS ####
264#################
265INSERT INTO Division (DivisionID, DivisionName) VALUES
266('001', '888-Ligaen'),
267('002', '1. Division');
268
269INSERT INTO Club (ClubID, ClubName, FoundingYear, City, DivisionID) VALUES
270('1001', 'GOG', 1973, 'Svendborg', '001'),
271('1002', 'KIF', 1970, 'Kolding', '001'),
272('2001', 'Ringsted', 1990, 'Ringsted', '002'),
273('2002', 'Ajax København', 1982, 'København', '002');
274
275INSERT INTO Game (MatchID, HomeClubGoals, AwayClubGoals, GameDate, HomeClubID, AwayClubID) VALUES
276('90000', 25, 27, '2018-04-12', '1001','1002'),
277('90001', 32, 24, '2018-03-12', '2001','2002'),
278('90002', 30, 20, '2018-03-12', '1002','2002'),
279('90003', 0, 0, '2018-03-12', '2001','1001');
280
281
282
283INSERT INTO Player (PlayerID, Firstname, Lastname, Birthday, Country, Salary, ClubID) VALUES
284(12548, 'Den', 'McGhie', '1990-10-18', 'Denmark', 174761, '1001'),
285(16679, 'Alasdair', 'Mitchenson', '1984-06-09', 'Denmark', 380609, '1001'),
286(11641, 'Georas', 'Bew', '1987-01-25', 'Denmark', 30362, '1002'),
287(17951, 'Briant', 'O''Doogan', '1986-09-19', 'Denmark', 353731, '1002'),
288(16272, 'Elton', 'Syfax', '1995-12-27', 'Denmark', 363602, '2001'),
289(19409, 'Archy', 'Murrock', '1987-11-18', 'Denmark', 268655, '2001'),
290(12138, 'Lyon', 'Bront', '1994-07-03', 'Denmark', 286842, '2002'),
291(10894, 'North', 'Bambridge', '1985-06-03', 'Denmark', 284661, '2002'),
292(19665, 'Pedro', 'Brugsma', '1997-08-08', 'Denmark', 393688, '1001'),
293(19821, 'Hillyer', 'Husher', '1996-01-13', 'Denmark', 45434, '1001'),
294(11920, 'Guillaume', 'Grunbaum', '1996-09-29', 'Denmark', 87839, '1002');
295
296INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
297('90000', 12548, 13, 1, 1, 1, 1),
298('90000', 11641, 12,0,1,1,0),
299('90002', 11641, 13,0,1,1,0);
300
301
302
303INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES #
304('90003',12548,8,0,0,1,0);
305INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
306('90003',16272,15,0,0,0,0);
307SELECT * FROM GAME;
308
309#INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
310#('900',16272,15,0,0,0,0);
311#SELECT * FROM GAME;
312
313
314INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
315('40000', 19821, '1002', '1001', '2018-04-16', 450000);
316
317INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
318('40001', 19821, '2001','1002', '2018-05-16', 450000);
319
320INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
321('40002', 11920, '2002', '1002', '2018-05-16', 450000);
322
323INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
324('40003', 10894, '2001', '2002', '2018-09-16', 450000);
325
326INSERT INTO Coach (CoachID, FirstName, LastName, BirthDay, Country, ClubID) VALUES
327('6001', 'Jakob', 'Larsen', '1974-12-16', 'Greenland', '1001'),
328('6002', 'Hans', 'Hansen', '1971-04-09', 'Denmark', '1002'),
329('6003', 'Jens', 'Jensen', '1968-01-14', 'Denmark', '2001'),
330('6004', 'Donald', 'Trump', '1950-01-28', 'USA', '2002');
331
332INSERT INTO INJURY (InjuryID, Diagnosis) VALUES
333('700', 'Broken Wrist'),
334('701', 'Sprained ankle'),
335('702', 'Knee injury'),
336('703', 'Shoulder Injury'),
337('704', 'Concussion');
338
339INSERT INTO PlayerInjury (PlayerID, InjuryID, Active, InjuryDate) VALUES
340(17951,'704', 'Yes', '2018-02-17'),
341(11641,'702', 'Yes', '2017-12-14'),
342(19665,'704', 'No', '2018-01-02'),
343(17951,'703', 'Yes', '2018-04-15');