· 8 years ago · Apr 17, 2018, 08:16 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
99 CREATE 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 ####
112CREATE VIEW ClubStatus as
113 (SELECT ClubID, ClubName as Clubname,
114 SUM(
115 CASE WHEN (ClubID = HomeClubID AND HomeClubGoals > AwayClubGoals) OR (ClubID = AwayClubID AND AwayClubGoals > HomeClubGoals) THEN 2
116 WHEN (ClubID = HomeClubID OR ClubID = AwayClubID) AND (HomeClubGoals = AwayClubGoals) THEN 1
117 ELSE 0
118 END) AS TotalPoints,
119 COUNT(CASE WHEN (ClubID = HomeClubID AND HomeClubGoals > AwayClubGoals) OR (ClubID = AwayClubID AND AwayClubGoals > HomeClubGoals) THEN 1 END) as Wins,
120 COUNT(CASE WHEN (ClubID = HomeClubID OR ClubID = AwayClubID) AND (HomeClubGoals = AwayClubGoals) THEN 1 END) as draws,
121 COUNT(CASE WHEN (ClubID = HomeClubID AND HomeClubGoals < AwayClubGoals) OR (ClubID = AwayClubID AND AwayClubGoals < HomeClubGoals) THEN 1 END) as Losses,
122 DivisionID
123 FROM Game, Club
124 GROUP BY ClubID);
125
126
127DROP VIEW IF EXISTS TotalPlayerGoals;
128
129#Shows all players and their total goals this season
130CREATE VIEW TotalPlayerGoals as
131SELECT PlayerID, Firstname, Lastname, Sum(NoOfGoals) AS Goals
132FROM Player NATURAL JOIN PlayerResult
133Group By PlayerID;
134
135
136
137SELECT * FROM TotalPlayerGOals;
138
139
140# 7. Typical SQL Statements
141
142# Show average Salary for each club.
143SELECT ClubID, ClubName, Avg(Salary) As AvgSalary
144FROM Club NATURAL JOIN Player
145Group By ClubID Order By AvgSalary DESC;
146
147
148# Show total 2 minute suspensions and red/yellow cards of players
149SELECT PlayerID, Sum(TwoMinSusp) as TotTwoMinSusp, Sum(YellowCards) AS TotYellowCards, Sum(RedCards) AS TotRedCards
150FROM PlayerResult
151GROUP BY PlayerID;
152
153
154
155# Increase salary by 5% of all players with more than 21 Goals (Update)
156UPDATE Player SET Player.Salary = Player.Salary*(1.05)
157WHERE
158(SELECT PlayerID From TotalPlayerGoals WHERE Goals > 21) = Player.PlayerID;
159
160
161#### PROCEDURES ####
162####################
163
164DROP PROCEDURE IF EXISTS HomeOrAway;
165 # This procedure is called when the UpdateGame is triggered, it then updates the HomeClubGoals and
166 # AwayClubGoals in the Game table after Playeresults are being inserted.
167DELIMITER //
168CREATE PROCEDURE HomeOrAway
169 (IN vClubID VARCHAR(5), IN vHomeClubID VARCHAR(5), IN vAwayClubID VARCHAR(5), IN vMatchID VARCHAR(5), IN vNoOfGoals DECIMAL(4,0))
170BEGIN
171 IF (vClubID = vHomeClubID) THEN
172 UPDATE Game
173 SET Game.HomeClubGoals = Game.HomeClubGoals + vNoOfGoals
174 WHERE Game.MatchID = vMatchID;
175 ELSE
176 UPDATE Game
177 SET Game.AwayClubGoals = Game.AwayClubGoals+vNoOfGoals
178 WHERE Game.MatchID = vMatchID;
179 END IF;
180END; //
181DELIMITER ;
182
183
184#### TRIGGERS ####
185##################
186
187DROP TRIGGER IF EXISTS UpdateGame;
188# This trigger calls the procedure HomeOrAway after values are inserted into the PlayerResult table.
189DELIMITER //
190CREATE TRIGGER UpdateGame
191BEFORE INSERT ON PlayerResult FOR EACH ROW
192 IF NOT EXISTS (SELECT * FROM GAME WHERE Game.MatchID = NEW.MatchID)
193 THEN SIGNAL SQLSTATE 'HY000'
194 SET MYSQL_ERRNO = 1525,
195 MESSAGE_TEXT = 'This MatchID does not exist in Game.';
196 ELSE
197 CALL HomeOrAWAY ((SELECT ClubID FROM Player WHERE PlayerID = NEW.PlayerID), (SELECT HomeClubID FROM Game WHERE MatchID = NEW.MatchID),
198 (SELECT AwayClubID FROM Game WHERE MatchID = NEW.MatchID), NEW.MatchID, NEW.NoOfGoals);
199 END IF; //
200DELIMITER ;
201
202SET GLOBAL event_scheduler = 1;
203
204
205DROP TRIGGER IF EXISTS TransferUpdate;
206
207# Changes the Club a Player belongs to after he has gone through a Transfer.
208DELIMITER //
209CREATE TRIGGER TransferUpdate
210AFTER INSERT ON Transfer FOR EACH ROW
211 UPDATE Player
212 SET Player.ClubID = NEW.BuyingClubID WHERE Player.PlayerID=NEW.PlayerID
213; //
214DELIMITER ;
215
216SELECT * FROM PLAYER;
217DROP FUNCTION IF EXISTS PreviousClub;
218#### FUNCTIONS ####
219DELIMITER //
220CREATE FUNCTION PreviousClub(vPlayerID VARCHAR(5)) RETURNS VARCHAR(20)
221BEGIN
222 #DECLARE vClubName VARCHAR(20);
223 Return (SELECT SellingClubID FROM transfer WHERE PlayerID=vPlayerID AND TransferDate = (SELECT MAX(TransferDate) FROM Transfer WHERE PlayerID=vPlayerID));
224 #RETURN vClubName;
225END; //
226DELIMITER ;
227
228SELECT PlayerID, PreviousClub(PlayerID) FROM Transfer;
229SELECT PreviousClub(19821);
230SELECT PreviousClub(10894);
231SELECT * FROM Transfer;
232
233#### EVENTS ####
234################
235
236# Restarts the database when a new season begins.
237CREATE EVENT NewSeason
238ON SCHEDULE EVERY 1 YEAR
239STARTS '2018-06-01 23:59:59'
240DO DELETE FROM Game;
241
242
243#### INSERTS ####
244#################
245INSERT INTO Division (DivisionID, DivisionName) VALUES
246('001', '888-Ligaen'),
247('002', '1. Division');
248
249INSERT INTO Club (ClubID, ClubName, FoundingYear, City, DivisionID) VALUES
250('1001', 'GOG', 1973, 'Svendborg', '001'),
251('1002', 'KIF', 1970, 'Kolding', '001'),
252('2001', 'Ringsted', 1990, 'Ringsted', '002'),
253('2002', 'Ajax København', 1982, 'København', '002');
254
255INSERT INTO Game (MatchID, HomeClubGoals, AwayClubGoals, GameDate, HomeClubID, AwayClubID) VALUES
256('90000', 25, 27, '2018-04-12', '1001','1002'),
257('90001', 32, 24, '2018-03-12', '2001','2002'),
258('90002', 30, 20, '2018-03-12', '1002','2002'),
259('90003', 0, 0, '2018-03-12', '2001','1001');
260
261
262
263INSERT INTO Player (PlayerID, Firstname, Lastname, Birthday, Country, Salary, ClubID) VALUES
264(12548, 'Den', 'McGhie', '1990-10-18', 'Denmark', 174761, '1001'),
265(16679, 'Alasdair', 'Mitchenson', '1984-06-09', 'Denmark', 380609, '1001'),
266(11641, 'Georas', 'Bew', '1987-01-25', 'Denmark', 30362, '1002'),
267(17951, 'Briant', 'O''Doogan', '1986-09-19', 'Denmark', 353731, '1002'),
268(16272, 'Elton', 'Syfax', '1995-12-27', 'Denmark', 363602, '2001'),
269(19409, 'Archy', 'Murrock', '1987-11-18', 'Denmark', 268655, '2001'),
270(12138, 'Lyon', 'Bront', '1994-07-03', 'Denmark', 286842, '2002'),
271(10894, 'North', 'Bambridge', '1985-06-03', 'Denmark', 284661, '2002'),
272(19665, 'Pedro', 'Brugsma', '1997-08-08', 'Denmark', 393688, '1001'),
273(19821, 'Hillyer', 'Husher', '1996-01-13', 'Denmark', 45434, '1001'),
274(11920, 'Guillaume', 'Grunbaum', '1996-09-29', 'Denmark', 87839, '1002');
275
276INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
277('90000', 12548, 13, 1, 1, 1, 1),
278('90000', 11641, 12,0,1,1,0),
279('90002', 11641, 13,0,1,1,0);
280
281
282
283INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES #
284('90003',12548,8,0,0,2,0);
285INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
286('90003',16272,15,0,0,0,0);
287SELECT * FROM GAME;
288
289#INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
290#('900',16272,15,0,0,0,0);
291#SELECT * FROM GAME;
292
293
294INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
295('40000', 19821, '1002', '1001', '2018-04-16', 450000);
296
297INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
298('40001', 19821, '2001','1002', '2018-05-16', 450000);
299
300INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
301('40002', 11920, '2002', '1002', '2018-05-16', 450000);
302
303INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
304('40003', 10894, '2001', '2002', '2018-09-16', 450000);
305
306INSERT INTO Coach (CoachID, FirstName, LastName, BirthDay, Country, ClubID) VALUES
307('6001', 'Jakob', 'Larsen', '1974-12-16', 'Greenland', '1001'),
308('6002', 'Hans', 'Hansen', '1971-04-09', 'Denmark', '1002'),
309('6003', 'Jens', 'Jensen', '1968-01-14', 'Denmark', '2001'),
310('6004', 'Donald', 'Trump', '1950-01-28', 'USA', '2002');
311
312INSERT INTO INJURY (InjuryID, Diagnosis) VALUES
313('700', 'Broken Wrist'),
314('701', 'Sprained ankle'),
315('702', 'Knee injury'),
316('703', 'Shoulder Injury'),
317('704', 'Concussion');