· 8 years ago · Apr 17, 2018, 10:22 PM
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
145# Show average Salary for each club.
146SELECT ClubID, ClubName, Avg(Salary) As AvgSalary
147FROM Club NATURAL JOIN Player
148Group By ClubID Order By AvgSalary DESC;
149
150
151# Show total 2 minute suspensions and red/yellow cards of players
152SELECT PlayerID, Sum(TwoMinSusp) as TotTwoMinSusp, Sum(YellowCards) AS TotYellowCards, Sum(RedCards) AS TotRedCards
153FROM PlayerResult
154GROUP BY PlayerID;
155
156
157
158# Increase salary by 5% of all players with more than 21 Goals (Update)
159UPDATE Player SET Player.Salary = Player.Salary*(1.05)
160WHERE Player.PlayerID IN (SELECT PlayerID From TotalPlayerGoals WHERE Goals > 20);
161
162
163# Show staff (players + coach(es)) of a club, GOG in this example.
164# DOES NOT WORK
165SELECT Player.FirstName, Player.LastName, Coach.FirstName, Player.LastName
166FROM Player, Coach WHERE Player.ClubID = '1001' AND Coach.ClubID = '1001';
167
168# Show players currently injured
169SELECT * FROM PlayerInjury WHERE Active = 'Yes';
170
171
172
173
174#### PROCEDURES ####
175####################
176
177DROP PROCEDURE IF EXISTS HomeOrAway;
178 # This procedure is called when the UpdateGame is triggered, it then updates the HomeClubGoals and
179 # AwayClubGoals in the Game table after Playeresults are being inserted.
180DELIMITER //
181CREATE PROCEDURE HomeOrAway
182 (IN vClubID VARCHAR(5), IN vHomeClubID VARCHAR(5), IN vAwayClubID VARCHAR(5), IN vMatchID VARCHAR(5), IN vNoOfGoals DECIMAL(4,0))
183BEGIN
184 IF (vClubID = vHomeClubID) THEN
185 UPDATE Game
186 SET Game.HomeClubGoals = Game.HomeClubGoals + vNoOfGoals
187 WHERE Game.MatchID = vMatchID;
188 ELSE
189 UPDATE Game
190 SET Game.AwayClubGoals = Game.AwayClubGoals+vNoOfGoals
191 WHERE Game.MatchID = vMatchID;
192 END IF;
193END; //
194DELIMITER ;
195
196# Sets an injury inactive for a chosen player's injury.
197CREATE PROCEDURE UpdatePlayerInjury (IN vPlayerID VARCHAR(5), IN vInjuryID VARCHAR(3))
198UPDATE PlayerInjury SET PlayerInjury.Active = 'No'
199WHERE PlayerInjury.PlayerID = vPlayerID AND PlayerInjury.InjuryID = vInjuryID;
200
201CALL UpdatePlayerInjury(17951,'704');
202SELECT * FROM PlayerInjury;
203
204#### TRIGGERS ####
205##################
206
207DROP TRIGGER IF EXISTS UpdateGame;
208# This trigger calls the procedure HomeOrAway after values are inserted into the PlayerResult table.
209DELIMITER //
210CREATE TRIGGER UpdateGame
211BEFORE INSERT ON PlayerResult FOR EACH ROW
212 IF NOT EXISTS (SELECT * FROM GAME WHERE Game.MatchID = NEW.MatchID)
213 THEN SIGNAL SQLSTATE 'HY000'
214 SET MYSQL_ERRNO = 1525,
215 MESSAGE_TEXT = 'This MatchID does not exist in Game.';
216 ELSE
217 CALL HomeOrAWAY ((SELECT ClubID FROM Player WHERE PlayerID = NEW.PlayerID), (SELECT HomeClubID FROM Game WHERE MatchID = NEW.MatchID),
218 (SELECT AwayClubID FROM Game WHERE MatchID = NEW.MatchID), NEW.MatchID, NEW.NoOfGoals);
219 END IF; //
220DELIMITER ;
221
222
223
224DROP TRIGGER IF EXISTS TransferUpdate;
225
226# Changes the Club a Player belongs to after he has gone through a Transfer.
227DELIMITER //
228CREATE TRIGGER TransferUpdate
229AFTER INSERT ON Transfer FOR EACH ROW
230 UPDATE Player
231 SET Player.ClubID = NEW.BuyingClubID WHERE Player.PlayerID=NEW.PlayerID
232; //
233DELIMITER ;
234
235SELECT * FROM PLAYER;
236DROP FUNCTION IF EXISTS PreviousClub;
237#### FUNCTIONS ####
238
239# A function that will show the previous club of a player.
240DELIMITER //
241CREATE FUNCTION PreviousClub(vPlayerID VARCHAR(5)) RETURNS VARCHAR(20)
242BEGIN
243 Return (SELECT SellingClubID
244 FROM transfer
245 WHERE PlayerID=vPlayerID AND TransferDate = (SELECT MAX(TransferDate) FROM Transfer WHERE PlayerID=vPlayerID));
246
247END; //
248DELIMITER ;
249
250
251
252SELECT PlayerID, PreviousClub(PlayerID) FROM Transfer;
253SELECT PreviousClub(19821);
254SELECT PreviousClub(10894);
255SELECT * FROM Transfer;
256
257#### EVENTS ####
258################
259SET GLOBAL event_scheduler = 1;
260
261# Restarts the database when a new season begins.
262CREATE EVENT NewSeason
263ON SCHEDULE EVERY 1 YEAR
264STARTS '2018-06-01 23:59:59'
265DO DELETE FROM Game;
266
267
268#### INSERTS ####
269#################
270INSERT INTO Division (DivisionID, DivisionName) VALUES
271('001', '888-Ligaen'),
272('002', '1. Division');
273
274INSERT INTO Club (ClubID, ClubName, FoundingYear, City, DivisionID) VALUES
275('1001', 'GOG', 1973, 'Svendborg', '001'),
276('1002', 'KIF', 1970, 'Kolding', '001'),
277('2001', 'Ringsted', 1990, 'Ringsted', '002'),
278('2002', 'Ajax København', 1982, 'København', '002');
279
280INSERT INTO Game (MatchID, HomeClubGoals, AwayClubGoals, GameDate, HomeClubID, AwayClubID) VALUES
281('90000', 25, 27, '2018-04-12', '1001','1002'),
282('90001', 32, 24, '2018-03-12', '2001','2002'),
283('90002', 30, 20, '2018-03-12', '1002','2002'),
284('90003', 0, 0, '2018-03-12', '2001','1001');
285
286
287
288INSERT INTO Player (PlayerID, Firstname, Lastname, Birthday, Country, Salary, ClubID) VALUES
289(12548, 'Den', 'McGhie', '1990-10-18', 'Denmark', 174761, '1001'),
290(16679, 'Alasdair', 'Mitchenson', '1984-06-09', 'Denmark', 380609, '1001'),
291(11641, 'Georas', 'Bew', '1987-01-25', 'Denmark', 30362, '1002'),
292(17951, 'Briant', 'O''Doogan', '1986-09-19', 'Denmark', 353731, '1002'),
293(16272, 'Elton', 'Syfax', '1995-12-27', 'Denmark', 363602, '2001'),
294(19409, 'Archy', 'Murrock', '1987-11-18', 'Denmark', 268655, '2001'),
295(12138, 'Lyon', 'Bront', '1994-07-03', 'Denmark', 286842, '2002'),
296(10894, 'North', 'Bambridge', '1985-06-03', 'Denmark', 284661, '2002'),
297(19665, 'Pedro', 'Brugsma', '1997-08-08', 'Denmark', 393688, '1001'),
298(19821, 'Hillyer', 'Husher', '1996-01-13', 'Denmark', 45434, '1001'),
299(11920, 'Guillaume', 'Grunbaum', '1996-09-29', 'Denmark', 87839, '1002');
300
301INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
302('90000', 12548, 13, 1, 1, 1, 1),
303('90000', 11641, 12,0,1,1,0),
304('90002', 11641, 13,0,1,1,0);
305
306
307
308INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES #
309('90003',12548,8,0,0,1,0);
310INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
311('90003',16272,15,0,0,0,0);
312SELECT * FROM GAME;
313
314#INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
315#('900',16272,15,0,0,0,0);
316#SELECT * FROM GAME;
317
318
319INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
320('40000', 19821, '1002', '1001', '2018-04-16', 450000);
321
322INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
323('40001', 19821, '2001','1002', '2018-05-16', 450000);
324
325INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
326('40002', 11920, '2002', '1002', '2018-05-16', 450000);
327
328#INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, SellingClubID, TransferDate, Price) VALUES
329#('40003', 10894, '2001', '2002', '2018-09-16', 450000);
330
331SELECT * FROM Transfer;
332
333
334INSERT INTO Coach (CoachID, FirstName, LastName, BirthDay, Country, ClubID) VALUES
335('6001', 'Jakob', 'Larsen', '1974-12-16', 'Greenland', '1001'),
336('6002', 'Hans', 'Hansen', '1971-04-09', 'Denmark', '1002'),
337('6003', 'Jens', 'Jensen', '1968-01-14', 'Denmark', '2001'),
338('6004', 'Donald', 'Trump', '1950-01-28', 'USA', '2002');
339
340INSERT INTO INJURY (InjuryID, Diagnosis) VALUES
341('700', 'Broken Wrist'),
342('701', 'Sprained ankle'),
343('702', 'Knee injury'),
344('703', 'Shoulder Injury'),
345('704', 'Concussion');
346
347INSERT INTO PlayerInjury (PlayerID, InjuryID, Active, InjuryDate) VALUES
348(17951,'704', 'Yes', '2018-02-17'),
349(11641,'702', 'Yes', '2017-12-14'),
350(19665,'704', 'No', '2018-01-02');
351#(17951,'703', 'Yes', '2018-04-15');
352
353Select * FROM Player;
354SELECT * FROM PlayerInjury;
355SELECT * FROM PlayerResult;
356
357
358#DELETE FROM Player WHERE PlayerID = 12548;
359
360
361################ kirke test
362
363DELIMITER //
364CREATE PROCEDURE MoveTeamsProc ()
365 BEGIN
366 #DECLARE OldRowsCount001, OldRowsCount002, NewRowsCount001, NewRowsCount002 INT DEFAULT 0;
367 #START TRANSACTION;
368 #SET OldRowsCount001 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "001");
369 CREATE TEMPORARY TABLE IF NOT EXISTS Temp AS (SELECT * FROM ClubStatus);
370 UPDATE Club
371 SET DivisionID = "001"
372 WHERE TotalPoints IN (SELECT * FROM Temp WHERE DivisionID = "002" ORDER BY TotalPoints DESC LIMIT 2);
373 #SET NewRowsCount001 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "001");
374 # SET OldRowsCount002 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "002");
375 UPDATE Club
376 SET DivisionID = "002"
377 WHERE Club.ClubID IN (SELECT * FROM Temp WHERE DivisionID = "001" ORDER BY TotalPoints LIMIT 2);
378 #SET NewRowsCount002 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "002");
379 # IF (OldRowsCount001 = NewRowsCount001+2) AND (OldRowsCount002 = NewRowsCount002-2)
380 # THEN COMMIT;
381 # ELSE ROLLBACK;
382 #END IF;
383 END; //
384DELIMITER ;