· 8 years ago · Apr 16, 2018, 08:36 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
16
17#### TABLE CREATION ####
18CREATE TABLE Game
19 (MatchID VARCHAR(5),
20 HomeClubGoals DECIMAL(5,0),
21 AwayClubGoals DECIMAL(5,0),
22 GameDate Date,
23 HomeClubID VARCHAR(5),
24 AwayClubID VARCHAR(5),
25 PRIMARY KEY (MatchID)
26 );
27
28CREATE TABLE Division
29 (DivisionID VARCHAR(3),
30 DivisionName VARCHAR(25),
31 PRIMARY KEY (DivisionID)
32 );
33
34 CREATE TABLE Injury
35 (InjuryID VARCHAR(3),
36 Diagnosis VARCHAR(20),
37 PRIMARY KEY (InjuryID)
38 );
39
40CREATE TABLE Club
41 (ClubID VARCHAR(4),
42 ClubName VARCHAR(25),
43 FoundingYear YEAR,
44 City VARCHAR(25),
45 DivisioniD VARCHAR(3),
46 PRIMARY KEY(ClubID),
47 FOREIGN KEY(DivisionID) REFERENCES Division(DivisionID)
48 );
49
50CREATE TABLE Player
51 (PlayerId VARCHAR(5),
52 Firstname VarChar(20),
53 Lastname VarChar(20),
54 Birthday Date,
55 Country VARCHAR(20),
56 Salary VARCHAR(10),
57 ClubID VARCHAR(4),
58 PRIMARY KEY (PlayerID),
59 FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
60 );
61
62CREATE TABLE PlayerResult
63 (MatchID VARCHAR(5),
64 PlayerID VARCHAR(5),
65 NoOfGoals DECIMAL(5,0),
66 TwoMinSusp DECIMAL(5,0),
67 YellowCards DECIMAL(5,0),
68 RedCards DECIMAL(5,0),
69 Saves DECIMAL(5,0),
70 PRIMARY KEY (MatchID, PlayerID),
71 FOREIGN KEY (MatchID) REFERENCES Game(MatchID),
72 FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID)
73 );
74
75
76
77CREATE TABLE Transfer
78 (TransferID VARCHAR(5),
79 PlayerID VARCHAR(5),
80 BuyingClubID VARCHAR(4),
81 TransferDate DATE,
82 Price VARCHAR(15),
83 PRIMARY KEY(TransferID),
84 FOREIGN KEY (BuyingClubID) REFERENCES Club(ClubID)
85 );
86
87
88CREATE TABLE Coach
89 (CoachID VARCHAR(4),
90 FirstName VARCHAR(20),
91 LastName VARCHAR(20),
92 BirthDay DATE,
93 Country VARCHAR(20),
94 Salary VARCHAR(10),
95 ClubID VARCHAR(4),
96 PRIMARY KEY(CoachID),
97 FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
98 );
99
100 CREATE TABLE PlayerInjury
101 (PlayerID VARCHAR(5),
102 InjuryID VARCHAR(3),
103 Active VARCHAR(3),
104 InjueryDate Date,
105 PRIMARY KEY (PlayerID,InjuryID),
106 FOREIGN KEY (PlayerID) REFERENCES Player(PlayerID),
107 FOREIGN KEY (InjuryID) REFERENCES Injury(InjuryID)
108 );
109
110 DROP VIEW IF EXISTS ClubStatus;
111
112#### VIEWS ####
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
129
130DROP VIEW IF EXISTS TotalPlayerGoals;
131
132#Shows all players and their total goals this season
133CREATE VIEW TotalPlayerGoals as
134SELECT PlayerID, Firstname, Lastname, Sum(NoOfGoals) AS Goals
135FROM Player NATURAL JOIN PlayerResult
136Group By PlayerID;
137
138
139
140SELECT * FROM TotalPlayerGOals;
141## VIRKER IKKE?=!?!?!?=!??!
142#DROP VIEW IF EXISTS HighestPaidPlayers;
143
144#CREATE VIEW HighestPaidPlayers as
145#SELECT PlayerID,Firstname,Lastname, Salary FROM Player;
146
147#SELECT PlayerID,FirstName,Lastname,Salary
148#FROM HighestPaidPlayers
149#ORDER BY Salary;
150
151# 7. Typical SQL Statements
152
153# Show average Salary for each club.
154SELECT ClubID, ClubName, Avg(Salary) As AvgSalary
155FROM Club NATURAL JOIN Player
156Group By ClubID Order By AvgSalary DESC;
157
158
159# Show total 2 minute suspensions and red/yellow cards of players
160SELECT PlayerID, Sum(TwoMinSusp) as TotTwoMinSusp, Sum(YellowCards) AS TotYellowCards, Sum(RedCards) AS TotRedCards
161FROM PlayerResult
162GROUP BY PlayerID;
163
164# Increase salary by 5% of all players with more than 21 Goals
165UPDATE Player SET Player.Salary = Player.Salary*(1.05)
166WHERE
167(SELECT PlayerID From TotalPlayerGoals WHERE Goals > 21) = Player.PlayerID;
168
169
170
171
172#### TRIGGERS ####
173##################
174DROP TRIGGER IF EXISTS UpdateGame;
175
176# Updates the HomeClubGoals and AwayClubGoals in the Game table after Playeresults are being inserted.
177DELIMITER //
178CREATE TRIGGER UpdateGame
179AFTER INSERT ON PlayerResult FOR EACH ROW
180 IF NOT EXISTS (SELECT * FROM GAME WHERE Game.MatchID = NEW.MatchID)
181 THEN SIGNAL SQLSTATE 'HY000'
182 SET MYSQL_ERRNO = 1525,
183 MESSAGE_TEXT = 'This MatchID does not exist in Game.';
184 ELSE
185 IF (SELECT HomeClubID FROM Game WHERE Game.MatchID = New.MatchID) = (SELECT ClubID FROM Player WHERE PlayerID = New.PlayerID) THEN
186 UPDATE GAME
187 SET Game.HomeClubGoals = Game.HomeClubGoals + New.NoOfGoals WHERE Game.MatchID = New.MatchID;
188 ELSE
189 UPDATE GAME
190 SET Game.AwayClubGoals = Game.AwayClubGOals + NEW.NoOfGoals WHERE Game.MatchID = New.MatchID;
191 END IF;
192 END IF; //
193DELIMITER ;
194
195DROP TRIGGER IF EXISTS TransferUpdate;
196
197# Changes the Club a Player belongs to after he has gone through a Transfer.
198DELIMITER //
199CREATE TRIGGER TransferUpdate
200AFTER INSERT ON Transfer FOR EACH ROW
201 UPDATE Player
202 SET Player.ClubID = NEW.BuyingClubID
203; //
204DELIMITER ;
205
206DROP FUNCTION IF EXISTS PreviousClubs;
207#### FUNCTIONS ####
208DELIMITER //
209CREATE FUNCTION PreviousClubs(vPlayerID VARCHAR(5)) RETURNS VARCHAR(20)
210BEGIN
211 DECLARE vClubName VARCHAR(20);
212 SELECT BuyingClubID INTO vClubName FROM transfer where Transfer.PlayerID=vPlayerID;
213 RETURN vClubName;
214END; //
215DELIMITER ;
216
217SELECT *,PreviousClubs(11920) From Transfer;
218
219SELECT * FROM Transfer;
220#### INSERTS ####
221
222INSERT INTO Division (DivisionID, DivisionName) VALUES
223('001', '888-Ligaen'),
224('002', '1. Division');
225
226INSERT INTO Club (ClubID, ClubName, FoundingYear, City, DivisionID) VALUES
227('1001', 'GOG', 1973, 'Svendborg', '001'),
228('1002', 'KIF', 1970, 'Kolding', '001'),
229('2001', 'Ringsted', 1990, 'Ringsted', '002'),
230('2002', 'Ajax København', 1982, 'København', '002');
231
232INSERT INTO Game (MatchID, HomeClubGoals, AwayClubGoals, GameDate, HomeClubID, AwayClubID) VALUES
233('90000', 25, 27, '2018-04-12', '1001','1002'),
234('90001', 32, 24, '2018-03-12', '2001','2002'),
235('90002', 30, 20, '2018-03-12', '1002','2002'),
236('90003', 0, 0, '2018-03-12', '2001','1001');
237
238
239
240INSERT INTO Player (PlayerID, Firstname, Lastname, Birthday, Country, Salary, ClubID) VALUES
241(12548, 'Den', 'McGhie', '1990-10-18', 'Denmark', 174761, '1001'),
242(16679, 'Alasdair', 'Mitchenson', '1984-06-09', 'Denmark', 380609, '1001'),
243(11641, 'Georas', 'Bew', '1987-01-25', 'Denmark', 30362, '1002'),
244(17951, 'Briant', 'O''Doogan', '1986-09-19', 'Denmark', 353731, '1002'),
245(16272, 'Elton', 'Syfax', '1995-12-27', 'Denmark', 363602, '2001'),
246(19409, 'Archy', 'Murrock', '1987-11-18', 'Denmark', 268655, '2001'),
247(12138, 'Lyon', 'Bront', '1994-07-03', 'Denmark', 286842, '2002'),
248(10894, 'North', 'Bambridge', '1985-06-03', 'Denmark', 284661, '2002'),
249(19665, 'Pedro', 'Brugsma', '1997-08-08', 'Denmark', 393688, '1001'),
250(19821, 'Hillyer', 'Husher', '1996-01-13', 'Denmark', 45434, '1001'),
251(11920, 'Guillaume', 'Grunbaum', '1996-09-29', 'Denmark', 87839, '1002');
252
253INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
254('90000', 12548, 13, 1, 1, 1, 1),
255('90000', 11641, 12,0,1,1,0),
256('90002', 11641, 13,0,1,1,0);
257
258
259
260INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
261('90003',12548,8,0,0,2,0);
262INSERT INTO PlayerResult (MatchID, PlayerID, NoOfGoals, TwoMinSusp, YellowCards, RedCards, Saves) VALUES
263('90003',16272,15,0,0,0,0);
264
265
266INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, TransferDate, Price) VALUES
267('40000', 19821, '1002', '2018-04-16', 450000);
268
269INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, TransferDate, Price) VALUES
270('40001', 19821, '2001', '2018-05-16', 450000);
271
272INSERT INTO Transfer (TransferID, PlayerID, BuyingClubID, TransferDate, Price) VALUES
273('40002', 11920, '1002', '2018-05-16', 450000);
274
275INSERT INTO Coach (CoachID, FirstName, LastName, BirthDay, Country, ClubID) VALUES
276('6001', 'Jakob', 'Larsen', '1974-12-16', 'Greenland', '1001'),
277('6002', 'Hans', 'Hansen', '1971-04-09', 'Denmark', '1002'),
278('6003', 'Jens', 'Jensen', '1968-01-14', 'Denmark', '2001'),
279('6004', 'Donald', 'Trump', '1950-01-28', 'USA', '2002');