· 8 years ago · May 05, 2018, 01:18 PM
1CREATE DATABASE Tournament;
2
3USE Tournament;
4
5CREATE TABLE IF NOT EXISTS player (
6 ID VARCHAR(13) NOT NULL,
7 fNamE VARCHAR(15) NOT NULL,
8 lName VARCHAR(15) NOT NULL,
9 tel VARCHAR(13) NOT NULL,
10 rank SMALLINT NOT NULL,
11 sex CHAR(1),
12 valid CHAR(1),
13 finalResult SMALLINT,
14 PRIMARY KEY (ID)
15);
16
17CREATE TABLE IF NOT EXISTS results (
18 wID VARCHAR(13) NOT NULL REFERENCES player(ID),
19 lID VARCHAR(13) NOT NULL REFERENCES player(ID),
20 dPlayed DATE NOT NULL,
21 tStored VARCHAR(6) NOT NULL,
22 score VARCHAR(19) NOT NULL,
23 PRIMARY KEY (wID, lID, dPlayed)
24);
25
26DELIMITER //
27CREATE PROCEDURE finalizeMenRank()
28BEGIN
29 DECLARE done INT DEFAULT 0;
30 DECLARE id VARCHAR(13);
31 DECLARE count INT;
32 DECLARE orderCursor CURSOR
33 FOR SELECT player.ID
34 FROM player
35 WHERE player.sex = 'M'
36 ORDER BY player.rank;
37 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
38 SET count = 0;
39
40 OPEN orderCursor;
41
42 REPEAT
43 FETCH orderCursor INTO id;
44 IF NOT done THEN
45 BEGIN
46 SET count = count + 1;
47 UPDATE player
48 SET player.rank = count
49 WHERE player.ID = id;
50 END;
51 END IF;
52 UNTIL done END REPEAT;
53
54 CLOSE orderCursor;
55END//
56DELIMITER ;
57
58DELIMITER //
59CREATE PROCEDURE finalizeWomenRank()
60BEGIN
61 DECLARE done INT DEFAULT 0;
62 DECLARE id VARCHAR(13);
63 DECLARE count INT;
64 DECLARE orderCursor CURSOR
65 FOR SELECT player.ID
66 FROM player
67 WHERE player.sex = 'F'
68 ORDER BY player.rank;
69 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
70 SET count = 0;
71
72 OPEN orderCursor;
73
74 REPEAT
75 FETCH orderCursor INTO id;
76 IF NOT done THEN
77 BEGIN
78 SET count = count + 1;
79 UPDATE player
80 SET player.rank = count
81 WHERE player.ID = id;
82 END;
83 END IF;
84 UNTIL done END REPEAT;
85
86 CLOSE orderCursor;
87END //
88DELIMITER ;
89
90
91DELIMITER //
92CREATE TRIGGER addFinal BEFORE INSERT ON results
93 FOR EACH ROW BEGIN
94 UPDATE player, results SET player.finalResult = num + 1
95 WHERE player.id = results.wID;
96 END;
97//
98DELIMITER ;
99
100
101USE Tournament;
102
103INSERT INTO player VALUES ('2', 'Gideon', 'Visser', '(021)930-1903', '43', 'M', NULL, NULL);
104INSERT INTO player VALUES ('4', 'John', 'Loopy', '(021)939-2344', '221', 'M', NULL, NULL);
105INSERT INTO player VALUES ('42', 'Paul', 'Murr', '073-4516412', '11', 'M', NULL, NULL);
106INSERT INTO player VALUES ('12l', 'Tony', 'Tank', '(021)939-2224', '32', 'M', NULL, NULL);
107INSERT INTO player VALUES ('22l', 'Jacob', 'Soap', '(021)591-2114', '554', 'M', NULL, NULL);
108INSERT INTO player VALUES ('id4', 'Freddie', 'Flop', '(011)529-2249', '2', 'M', NULL, NULL);
109
110INSERT INTO player VALUES ('46', 'Lucy', 'Stone', '(021)930-1233', '3', 'F', NULL, NULL);
111INSERT INTO player VALUES ('7', 'Polly', 'Con', '(021)939-2354', '11', 'F', NULL, NULL);
112INSERT INTO player VALUES ('41', 'Tracy', 'Dick', '073-451622', '9', 'F', NULL, NULL);
113INSERT INTO player VALUES ('2al', 'Tammy', 'Sohn', '082-3053445', '13', 'F', NULL, NULL);
114INSERT INTO player VALUES ('66l', 'Pip', 'Teena', '(021)591-1114', '5', 'F', NULL, NULL);
115INSERT INTO player VALUES ('id3', 'Lonsa', 'Ego', '(011)549-2149', '20', 'F', NULL, NULL);
116
117INSERT INTO results VALUES ('2', '22l', '2008-06-14', '12:30', '3-2');
118INSERT INTO results VALUES ('22l', '42', '2008-06-16', '11:30', '4-2');
119INSERT INTO results VALUES ('2', '12l', '2008-06-18', '13:30', '2-1');
120INSERT INTO results VALUES ('46', '66', '2008-06-20', '15:00', '3-1');
121INSERT INTO results VALUES ('46', '7', '2008-06-22', '12:00', '1-0');
122
123USE Tournament;
124
125UPDATE player
126 SET valid = 'Y'
127 WHERE ID = '2';
128
129UPDATE player
130 SET valid = 'Y'
131 WHERE ID = '42';
132
133UPDATE player
134 SET valid = 'Y'
135 WHERE ID = '22l';
136
137UPDATE player
138 SET valid = 'Y'
139 WHERE ID = '12l';
140
141UPDATE player
142 SET valid = 'Y'
143 WHERE ID = '46';
144
145UPDATE player
146 SET valid = 'Y'
147 WHERE ID = '7';
148
149UPDATE player
150 SET valid = 'Y'
151 WHERE ID = '66';
152
153UPDATE player
154 SET valid = 'Y'
155 WHERE ID = '2al';