| COMP3311 25T2 |
Assignment 1 Example Test Cases |
Database Systems |
[Assignment Spec] [SQL Schema] [Examples] [Testing]
This document contains examples of output.
$ select * from Q1; player | born ------------------------- Harry White | 1975-01-04 (1 row)
$ select * from Q2;
team | country | total_matches
----------------------------------
14 | Sweden | 67
13 | Spain | 64
10 | Japan | 61
3 | Brazil | 60
7 | Germany | 56
1 | Argentina | 54
4 | China | 53
12 | Netherlands | 53
6 | France | 52
2 | Australia | 50
9 | Iran | 49
15 | Uruguay | 49
5 | England | 48
11 | Korea | 46
8 | Italy | 38
(15 rows)
$ select * from Q3;
player_id | player | goals_scored | first_goal_date
-----------+------------------+--------------+-----------------
342 | Jung-Hwan Kim | 8 | 2000-04-21
214 | Michael Baumann | 8 | 2000-02-06
103 | Wei Wang | 7 | 2000-12-20
74 | Alexandre Carlos | 6 | 2001-01-14
145 | David Wilson | 6 | 2001-02-20
413 | Luis Garcia | 6 | 2000-01-10
9 | Manuel Lopez | 6 | 2001-05-19
484 | Paul Portas | 6 | 2001-10-13
434 | Pedro Navarro | 6 | 2000-01-02
(9 rows)
$ select * from Q4;
player_id | player | yellow_cards | red_cards | discipline_score
-----------+------------------+--------------+-----------+------------------
47 | Bruce Brown | 1 | 1 | 7
432 | Joaquin Enrique | 1 | 1 | 7
482 | Martin Rodriguez | 1 | 1 | 7
412 | Ramon Garcia | 1 | 1 | 7
192 | Julien Bernard | 2 | 0 | 4
372 | Jung-Hwan Kim | 2 | 0 | 4
419 | Luis Hierro | 2 | 0 | 4
208 | Michael Linke | 2 | 0 | 4
152 | Robert Wilson | 2 | 0 | 4
(9 rows)
$ select * from Q5;
match_id | home_team | away_team | goals_for_each_team
----------+-------------+-----------+---------------------
106 | Japan | Spain | 1-4
130 | Australia | Argentina | 4-1
142 | Japan | Iran | 4-2
151 | Italy | Germany | 2-4
170 | China | Spain | 2-3
236 | Sweden | Germany | 2-4
262 | Japan | Sweden | 5-0
273 | Korea | Germany | 2-4
276 | Uruguay | China | 2-4
279 | Sweden | Iran | 4-2
313 | Germany | China | 3-2
331 | Netherlands | Spain | 2-4
334 | France | Australia | 2-3
382 | Australia | Brazil | 3-2
413 | Japan | Korea | 2-3
432 | Japan | Korea | 4-1
440 | France | Sweden | 1-4
462 | France | England | 3-2
499 | England | Brazil | 1-4
(19 rows)
$ select * from Q6;
match_id | score | yellow | red
----------+-------+--------+-----
147 | 0-1 | 1 | 1
170 | 2-3 | 1 | 1
198 | 2-1 | 1 | 1
206 | 0-0 | 1 | 1
428 | 1-0 | 1 | 1
458 | 1-1 | 1 | 1
470 | 1-0 | 1 | 1
(7 rows)
$ select * from Q7;
match_id | winning_team | halftime_score | fulltime_score
----------+--------------+----------------+----------------
185 | England | 0-1 | 2-1
189 | Sweden | 1-0 | 1-2
208 | Sweden | 0-1 | 2-1
275 | Australia | 0-1 | 2-1
279 | Sweden | 1-2 | 4-2
294 | Italy | 0-1 | 2-1
298 | Australia | 0-1 | 2-1
351 | Sweden | 0-1 | 2-1
475 | Brazil | 0-1 | 2-1
(9 rows)
$ select q8('ab');
q8
--------------------------------------------
Babak Hashemian | Iran | defender | 2 | 1
Babak Jahanfad | Iran | forward | 2 | 0
Fabien Bernard | France | defender | 2 | 1
Fabien Desmet | France | midfield | 2 | 0
Fabien Wiseur | France | defender | 2 | 0
Fabien Zaine | France | midfield | 2 | 0
Babak Karimi | Iran | midfield | 1 | 0
Babak Mahjoob | Iran | midfield | 1 | 0
Fabien Simon | France | forward | 0 | 0
(9 rows)
$ select q8('SEN');
q8
----------------------------------------------------
Stefan Jansen | Netherlands | forward | 4 | 0
Hendrik Jansen | Netherlands | forward | 3 | 0
Sebastien Jansen | Netherlands | forward | 2 | 0
Sebastien Jansen | Netherlands | defender | 2 | 0
Johannes Jansen | Netherlands | forward | 1 | 1
Peter Jansen | Netherlands | forward | 1 | 0
Stefan Jansen | Netherlands | defender | 1 | 0
Willem Jansen | Netherlands | forward | 1 | 0
Johannes Jansen | Netherlands | goalkeeper | 0 | 0
Peter Jansen | Netherlands | goalkeeper | 0 | 0
(10 rows)
$ select q8('Li');
q8
------------------------------------------------
Hans Linke | Germany | forward | 5 | 1
Ronaldo Paulista | Brazil | forward | 5 | 0
Wei Li | China | forward | 5 | 0
Paulo Paulista | Brazil | forward | 4 | 0
Christian Linke | Germany | forward | 3 | 0
Jun Li | China | midfield | 3 | 0
Ali Karimi | Iran | midfield | 2 | 1
Ali Sadeghi | Iran | defender | 2 | 0
Dieter Linke | Germany | forward | 2 | 0
Gao Li | China | midfield | 2 | 0
Hao Li | China | midfield | 2 | 0
Julien Bernard | France | midfield | 2 | 2
Julien Desmet | France | midfield | 2 | 1
Julien Simon | France | forward | 2 | 0
Julien Zaine | France | forward | 2 | 0
Klaus Linke | Germany | forward | 2 | 0
Marcello Paulista | Brazil | midfield | 2 | 0
Tobias Linke | Germany | forward | 2 | 0
Alexandre Paulista | Brazil | midfield | 1 | 0
Ali Hashemian | Iran | defender | 1 | 1
Ali Jahanfad | Iran | forward | 1 | 0
Ali Mahjoob | Iran | forward | 1 | 0
Lei Li | China | midfield | 1 | 0
Michael Linke | Germany | forward | 1 | 2
Yi Li | China | forward | 1 | 0
Yu Li | China | midfield | 1 | 0
Fan Li | China | midfield | 0 | 0
Julien Michaud | France | goalkeeper | 0 | 0
Julien Wiseur | France | defender | 0 | 0
Pedro Paulista | Brazil | forward | 0 | 0
Rafael Paulista | Brazil | forward | 0 | 0
Wei Liu | China | goalkeeper | 0 | 0
Yi Liu | China | goalkeeper | 0 | 0
(33 rows)
$ select q8('qaq');
q8
----
(0 rows)
$ select Q9(275);
q9
------------------------------------------------------------------
[Melbourne, 2003-12-21] Australia (Team 2) vs Italy (Team 8) +
Half-time: 0-1 +
Full-time: 2-1 +
Minute 13: Goal (excellent) - Roberto Baggio (Italy, forward) +
Minute 73: Goal (excellent) - Bruce Muscat (Australia, defender)+
Minute 84: Goal (good) - Harry White (Australia, defender) +
Australia wins! +
A stunning comeback occurred! +
(1 row)
$ select Q9(116);
q9
-----------------------------------------------------------------
[Marseille, 2000-06-07] France (Team 6) vs Brazil (Team 3) +
Half-time: 0-2 +
Full-time: 0-3 +
Minute 3: Goal (good) - Alexandre Santos (Brazil, defender) +
Minute 7: Goal (good) - Marcello Carlos (Brazil, forward) +
Minute 55: Card (red) - Alexandre Ceni (Brazil, midfield) +
Minute 60: Goal (excellent) - Marcello Carlos (Brazil, forward)+
Brazil wins! +
Brazil won despite ending up with less than 11 players! +
$ select Q9(200);
q9
----------------------------------------------------------------
[Perugia, 2003-04-05] Italy (Team 8) vs Uruguay (Team 15) +
Half-time: 0-2 +
Full-time: 1-2 +
Minute 4: Goal (good) - Raul Portas (Uruguay, forward) +
Minute 26: Goal (amazing) - Jorge Morales (Uruguay, defender) +
Minute 55: Goal (excellent) - Marco Montella (Italy, midfield)+
Uruguay wins! +
(1 row)
$ select q9(212);
q9
--------------------------------------------------------------------
[Santa Cruz, 2000-05-24] Argentina (Team 1) vs France (Team 6) +
Half-time: 1-1 +
Full-time: 2-1 +
Minute 14: Goal (good) - Pierre Zaine (France, midfield) +
Minute 30: Goal (excellent) - Manuel Zanetti (Argentina, midfield)+
Minute 64: Goal (excellent) - Tomas Zanetti (Argentina, midfield) +
Minute 64: Card (yellow) - Jose Zanetti (Argentina, midfield) +
Argentina wins! +
(1 row)
$ select Q9(122);
q9
----------------------------------------------------------
[Berlin, 2004-11-11] Germany (Team 7) vs France (Team 6)+
Half-time: 0-0 +
Full-time: 0-0 +
No goals or cards occurred in this match. +
The match ended in a draw. +
(1 row)
$ select Q9(1);
q9
-----------------------
Match ID 1 not found.
(1 row)