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