· 7 years ago · Jan 08, 2019, 08:16 AM
1CREATE TABLE Teams (
2name TEXT PRIMARY KEY
3) ;
4
5CREATE TABLE Players (
6name TEXT,
7team TEXT REFERENCES Teams(name),
8number INT,
9place TEXT,
10CONSTRAINT name_team_key PRIMARY KEY (number,team)
11) ;
12
13CREATE TABLE Matches (
14team1 TEXT REFERENCES Teams(name),
15team2 TEXT REFERENCES Teams(name),
16playDate TEXT,
17goals1 INT,
18goals2 INT,
19CONSTRAINT teams_date_key PRIMARY KEY (team1,team2,playDate),
20CONSTRAINT no_self_match CHECK (team1 <> team2)
21) ;
22
23/*
24CREATE TABLE PlaysIn (
25team1 TEXT REFERENCES Matches(team1),
26team2 TEXT REFERENCES Matches(team2),
27playDate TEXT REFERENCES Matches(playDate),
28player INT REFERENCES Players(number),
29team TEXT REFERENCES Teams(name),
30CONSTRAINT player_in_key PRIMARY KEY (team1,team2,playDate,player,team),
31CONSTRAINT team_in_match CHECK (team = team1 OR team = team2)
32) ;
33--ERROR: there is no unique constraint matching given keys for referenced table "matches"
34*/
35
36-- 3
37CREATE TABLE Pls (
38name TEXT,
39number INT,
40team TEXT,
41age INT,
42CONSTRAINT pls_key PRIMARY KEY (team,number)
43) ;
44
45INSERT INTO Pls VALUES ('A',1,'M',11) ;
46INSERT INTO Pls VALUES ('B',1,'N',12) ;
47INSERT INTO Pls VALUES ('C',2,'M',13) ;
48INSERT INTO Pls VALUES ('D',2,'N',14) ;
49
50-- 3a
51SELECT name,age FROM Pls WHERE team = 'M' and AGE < 12 ;
52
53-- 3b
54SELECT B.name FROM Pls AS A, Pls AS B WHERE A.team='M' AND A.number=1 AND B.age > A.age ;
55SELECT name FROM Pls WHERE age > (SELECT age FROM Pls WHERE team = 'M' and number = 1) ;
56
57-- 3c
58SELECT name, age FROM Pls AS P WHERE age > (SELECT AVG(age) FROM Pls WHERE team = P.team) ;
59
60
61
62
63-- 4
64CREATE TABLE PlayerAges (
65 name TEXT,
66 age INT
67 ) ;
68
69CREATE TABLE PlayerTeams (
70 name TEXT,
71 team TEXT,
72 number INT
73 ) ;
74
75INSERT INTO PlayerAges VALUES ('Arnold',11) ;
76INSERT INTO PlayerAges VALUES ('Ben',12) ;
77
78INSERT INTO PlayerTeams VALUES ('Ben','IFK',15) ;
79INSERT INTO PlayerTeams VALUES ('Cicero','GAS',16) ;
80INSERT INTO PlayerTeams VALUES ('Donald','GAS',12) ;
81
82/*
83exam2016_8=# SELECT * FROM PlayerAges NATURAL JOIN PlayerTeams ;
84 name | age | team | number
85------+-----+------+--------
86 B | 12 | M | 15
87(1 row)
88
89exam2016_8=# SELECT * FROM PlayerAges FULL OUTER JOIN PlayerTeams USING (name) ;
90 name | age | team | number
91------+-----+------+--------
92 A | 11 | |
93 B | 12 | M | 15
94 C | | N | 16
95 D | | N | 12
96(4 rows)
97
98exam2016_8=# SELECT * FROM PlayerAges INNER JOIN PlayerTeams ON (age = number) ;
99 name | age | name | team | number
100------+-----+------+------+--------
101 B | 12 | D | N | 12
102(1 row)
103*/
104
105--5
106
107CREATE TABLE Matchs (
108team1 TEXT,
109team2 TEXT,
110goals1 INT,
111goals2 INT
112) ;
113
114INSERT INTO Matchs VALUES ('GAS','HACK',1,0) ;
115INSERT INTO Matchs VALUES ('IFC','GAS',2,2) ;
116INSERT INTO Matchs VALUES ('HACK','IFC',5,1) ;
117
118CREATE TABLE Results (
119team TEXT,
120matches INT,
121goals_scored INT,
122goals_conceded INT,
123points INT
124) ;
125
126CREATE OR REPLACE FUNCTION addMatch () RETURNS TRIGGER AS $$
127BEGIN
128 IF (NOT EXISTS (SELECT team FROM Results WHERE team = NEW.team1))
129 THEN INSERT INTO Results VALUES (NEW.team1,0,0,0,0) ;
130 END IF ;
131 IF (NOT EXISTS (SELECT team FROM Results WHERE team = NEW.team2))
132 THEN INSERT INTO Results VALUES (NEW.team2,0,0,0,0) ;
133 END IF ;
134 UPDATE Results
135 SET matches = matches + 1
136 WHERE team = NEW.team1 OR team = NEW.team2 ;
137 UPDATE Results
138 SET goals_scored = goals_scored + NEW.goals1, goals_conceded = goals_conceded + NEW.goals2
139 WHERE team = NEW.team1 ;
140 UPDATE Results
141 SET goals_scored = goals_scored + NEW.goals2, goals_conceded = goals_conceded + NEW.goals1
142 WHERE team = NEW.team2 ;
143 UPDATE Results
144 SET points = points + 3
145 WHERE (team = NEW.team1 AND NEW.goals1 > NEW.goals2) OR (team = NEW.team2 AND NEW.goals2 > NEW.goals1) ;
146 UPDATE Results
147 SET points = points + 1
148 WHERE (team = NEW.team1 OR team = NEW.team2) AND NEW.goals1 = NEW.goals2 ;
149 RETURN NEW ;
150END
151$$ LANGUAGE 'plpgsql' ;
152
153CREATE TRIGGER addMatch_trigger
154 AFTER INSERT ON Matchs
155 FOR EACH ROW
156 EXECUTE PROCEDURE addMatch() ;
157
158CREATE VIEW VResults AS (
159SELECT team, COUNT(*) AS matches, SUM(goals_scored) AS goals_scored, SUM(goals_conceded) AS goals_conceded, SUM(points) AS points
160FROM
161 ((SELECT team1 AS team, goals1 as goals_scored, goals2 as goals_conceded, 3 as points
162 FROM Matchs
163 WHERE goals1 > goals2
164 )
165
166 UNION
167
168 (SELECT team1 AS team, goals1 as goals_scored, goals2 as goals_conceded, 1 as points
169 FROM Matchs
170 WHERE goals1 = goals2
171 )
172
173 UNION
174
175 (SELECT team1 AS team, goals1 as goals_scored, goals2 as goals_conceded, 0 as points
176 FROM Matchs
177 WHERE goals1 < goals2
178 )
179
180 UNION
181
182 (SELECT team2 AS team, goals2 as goals_scored, goals1 as goals_conceded, 3 as points
183 FROM Matchs
184 WHERE goals2 > goals1
185 )
186
187 UNION
188
189 (SELECT team2 AS team, goals2 as goals_scored, goals1 as goals_conceded, 1 as points
190 FROM Matchs
191 WHERE goals2 = goals1
192 )
193
194 UNION
195
196 (SELECT team2 AS team, goals2 as goals_scored, goals1 as goals_conceded, 0 as points
197 FROM Matchs
198 WHERE goals2 < goals1
199 )
200 )
201 AS MM
202 GROUP BY team
203
204) ;
205
206
207CREATE VIEW VResultTable AS (
208 SELECT *
209 FROM VResults
210 ORDER BY (points, goals_scored - goals_conceded, points) DESC
211 ) ;
212
213CREATE VIEW ResultTable AS (
214 SELECT *
215 FROM Results
216 ORDER BY (points, goals_scored - goals_conceded, points) DESC
217 ) ;
218<?xml version="1.0" encoding="utf-8" standalone="no"?>
219<!DOCTYPE FT [
220<!ELEMENT FT (Label,FT*)>
221<!ELEMENT Label (#PCDATA)>
222]>
223
224<FT>
225 <Label>CarlGustaf</Label>
226 <FT>
227 <Label>Victoria</Label>
228 <FT>
229 <Label>Estelle</Label>
230 </FT>
231 <FT>
232 <Label>Oscar</Label>
233 </FT>
234 </FT>
235 <FT>
236 <Label>CarlPhilip</Label>
237 <FT>
238 <Label>Alexander</Label>
239 </FT>
240 </FT>
241 <FT>
242 <Label>Madeleine</Label>
243 <FT>
244 <Label>Leonore</Label>
245 </FT>
246 <FT>
247 <Label>Nicolas</Label>
248 </FT>
249 </FT>
250</FT>