| COMP3311 26T1 |
Assignment 1 Example Test Cases |
Database Systems |
[Assignment Spec] [SQL Schema] [Examples]
This document contains examples of output.
$ select goals_in_match(110, true);
goals_in_match
----------------
2
(1 row)
$ select goals_in_match(3, true);
goals_in_match
----------------
0
(1 row)
$ select goals_in_match(382, true, 85);
goals_in_match
----------------
2
(1 row)
$ select goals_in_match(382, true);
goals_in_match
----------------
3
(1 row)
$ select * from Q2; team | country | total_goals ------+---------+------------ 10 | Japan | 74 14 | Sweden | 74 (2 rows)
$ select * from Q3;
match_id | player_id | player | first_half_goals | second_half_goals
----------+-----------+-----------------+------------------+-------------------
116 | 73 | Marcello Carlos | 1 | 1
141 | 115 | Jun Li | 1 | 1
151 | 206 | Klaus Linke | 1 | 1
163 | 9 | Manuel Lopez | 1 | 1
192 | 440 | Pedro Suarez | 1 | 1
465 | 107 | Jun Wang | 1 | 1
(6 rows)
$ select * from Q4;
team | country | yellow_cards | red_cards | discipline_score
------+----------+--------------+-----------+------------------
13 | Spain | 7 | 7 | 49
10 | Japan | 2 | 7 | 39
5 | England | 4 | 4 | 28
15 | Uruguay | 8 | 2 | 26
6 | France | 12 | 0 | 24
7 | Germany | 7 | 1 | 19
14 | Sweden | 7 | 1 | 19
2 | Australia| 6 | 1 | 17
9 | Iran | 6 | 1 | 17
11 | Korea | 5 | 1 | 15
(10 rows)
$ select * from Q5;
match_id | city | winner | loser | score
----------+------------+-----------+-----------+-------
343 | Busan | Korea | Argentina | 4-0
105 | Linkoping | Sweden | Argentina | 3-0
107 | Lyon | France | Uruguay | 3-0
116 | Marseille | Brazil | France | 0-3
163 | Santa Cruz | Argentina | France | 3-0
312 | Santa Cruz | Argentina | Brazil | 3-0
337 | Rio de Janiero | China | Brazil | 0-3
354 | Seoul | Uruguay | Korea | 0-3
466 | Shanghai | China | Iran | 3-0
(9 rows)
$ select * from Q6;
team | country | matches_played | goal_diff
------+---------+----------------+-----------
4 | China | 53 | 12
7 | Germany | 56 | 12
(2 rows)
$ select * from Q7;
match_id | city | winning_team | losing_team | score_85 | fulltime_score
----------+-------------+--------------+-------------+----------+----------------
131 | Melbourne | Australia | Spain | 1-1 | 2-1
136 | Rio de Janiero | Brazil | China | 0-0 | 1-0
220 | Brisbane | Australia | France | 0-0 | 1-0
281 | Marseille | Uruguay | France | 1-1 | 1-2
314 | Madrid | Uruguay | Spain | 1-1 | 1-2
325 | Stockholm | Japan | Sweden | 0-0 | 0-1
382 | Sydney | Australia | Brazil | 2-2 | 3-2
423 | Sao Paulo | Brazil | Germany | 0-0 | 1-0
(8 rows)
$ select * from Q8;
match_id | red_team | red_minute | score | goals_after_red
----------+----------+------------+-------+-----------------
106 | Japan | 41 | 1-4 | 3
170 | Spain | 52 | 2-3 | 3
132 | England | 60 | 2-0 | 2
146 | Spain | 78 | 2-0 | 2
181 | Germany | 29 | 1-2 | 2
397 | Japan | 71 | 1-2 | 2
439 | Italy | 17 | 2-0 | 2
(7 rows)
$ select Q9('an');
q9
--------------------------------
7 | Germany | 56 | 68 | 56 | 12
5 | England | 48 | 58 | 52 | 6
10 | Japan | 61 | 74 | 71 | 3
6 | France | 52 | 57 | 67 | -10
12 | Netherlands | 53 | 51 | 63 | -12
9 | Iran | 49 | 49 | 64 | -15
(6 rows)
$ select Q9('zzz');
q9
----
(0 rows)
$ select Q10(2);
Q10
------------------------------------------------------
Team 2: Australia
Matches: 50 | Wins: 17 | Draws: 16 | Losses: 17
Goals: 53-51 (GD=2)
Most frequent opponent: Sweden (Team 14) - 6 matches
Top scorers:
- Dave Smith (forward) : 5
- Harry Kewell (defender) : 4
- Bill Jones (forward) : 3
- Bill Kewell (defender) : 3
- Bruce Muscat (defender) : 3
(1 row)
$ select Q10(7);
Q10
------------------------------------------------------
Team 7: Germany
Matches: 56 | Wins: 23 | Draws: 16 | Losses: 17
Goals: 68-56 (GD=12)
Most frequent opponent: France (Team 6) - 9 matches
Top scorers:
- Michael Baumann (forward) : 8
- Christian Baumann (midfield) : 5
- Hans Linke (forward) : 5
- Christian Boehme (defender) : 4
- Hans Baumann (forward) : 4
(1 row)
$ select Q10(99);
Q10
-----------------------
Team ID 99 not found.
(1 row)
If a team has no players who have scored, the Top scorers section shows exactly:
Top scorers:
- None
Note: In the Goals line, GD is shown without a leading + for positive numbers (e.g. GD=2, not GD=+2).
The + signs for newlines have been omitted here.