INFO20003 Semester 1, 2020

Assignment 2 – SQL
Due: 6:00pm Friday 15 May
Submission: Via LMS https://canvas.lms.unimelb.edu.au/
Weighting: 10% of your total assessment

Melbourne Touch Rugby
The Melbourne Touch Rugby competition is a 6-player-a-side competition for men’s, women’s and
mixed teams (a mixed team has a minimum of 3 women and maximum of 3 men on the field at any
time). Players must be registered with a club to play in the competition and players can only be
registered for one club at a time.
Currently there are 8 rugby clubs participating in the competition, which began in 2017. Each club has
three teams – a men’s team, a women’s team and a mixed team. The men’s competition is known as
the Dewar Shield; the women’s competition is known as the Williams Plate; and the mixed teams
competition is known as the Bingham Trophy.
A season is the set of games played in a competition in a calendar year. Each season consists of
rounds in which every team plays a game.
Even though each game is played by 12 players (6 from each team), a team is not a fixed group of 6
players. The team officials choose 6 players from their club, or possibly even from another club, to
play for that team in a game. As such, the composition of a team varies from round to round.
If for any reason a team is unable to organise enough players to play a game, that team will forfeit
the game and their opponents will score a “walkover”. A walkover awards 28 points to the team who
scores a walkover and 0 points for the team who forfeited. If a game is cancelled (e.g. due to extreme
heat, unsuitable playing pitch), no score is recorded against either team.
The Data Model

Figure 1: The ER Model for Melbourne Touch Rugby
Assignment 2 Setup
A dataset is provided against which you can test your solutions to the assignment. To set up the
dataset, download the file rugbysql_2020.sql from the Assignment on Canvas and run it in
Workbench. This script creates the database tables and populates them with data.
The script is designed to run against your account on the Engineering IT server
(info20003db.eng.unimelb.edu.au). If you want to install the schema on your own MySQL Server
installation, uncomment the BYOD section at the beginning of the script.

The SQL Tasks
In this section are listed 10 questions for you to answer. Write one (single) SQL statement per
question. Subqueries and nesting are allowed within a single SQL statement – however, you may be
penalized for writing overly complicated SQL statements. DO NOT USE VIEWS (or ‘WITH’
statements/CTEs) to answer questions.
1. List the first name and last name of all players who have ever played in the league and their
current club if applicable. List the players in ascending alphabetical order, primarily on last
name and secondarily on first name. A game is still considered to have been 'played' even if
it was cancelled or a walkover. (1 mark)
2. List the full name (presented as a single string) of female players who have played for more
than one team. (1 mark)
3. List the full name (presented as a single string) of players who have never played in a 'mixed’
competition. (1 mark)
4. List the season year(s) with the highest number of cancelled games, and the corresponding
number of cancelled games in the year(s). (2 marks)
5. List the name of the clubs where the number of current male members is not the same as the
number of current female members. For these clubs, present the number of current male and
female players, and the absolute difference between the number of current males and females
in the club. Order the results by the absolute difference between current males and females
in the club, with the club having the highest difference listed first. (2 marks)
6. List the first name, sex, and number of games played in 2018 and 2017 of players who played
more games in 2017 than in 2018. There might be players that didn’t play in 2018 but did in
2017. You should take these cases in account as well. A game is still considered to have been
'played' even if it was cancelled or a walkover. (2 marks)
7. List the name of all teams that scored more than 100 points in the Bingham Trophy in 2017,
along with the total points they scored. Order the result according to the points the team
scored, highest scoring team first. (2 marks)
8. For the player who has spent the fewest number of days as a member of ‘Melbourne City’ club
as of April 30th 2020, give the full name (presented as a single string) and the total number of
days spent as a member. Only consider players who have been a member of the club at some
point (i.e. do not include players who have never been signed up with Melbourne City).
Assume there are no joint shortest serving players. (3 marks)
9. A ‘foreign game’ is when a player has played a game in a team which was not part of the club
they were a member of at the time. Find the first and last names and the number of ‘foreign
games’ played for the 20 players who have played the most ‘foreign games’. List them from
highest number of foreign games to lowest. (3 marks)
10. Return the team name, the highest number of walkovers (forfeits) the team has ever given
away in a season, and the season year(s) during which this occurred for teams who have
given away 2 or more walkovers in the same season. Assume that any game with one team
scoring 28 and the other scoring 0 implies that the game was a walkover. (3 marks)
Submission Instructions
You must submit a .zip file containing two documents:
1. A Word .docx or .doc text-based format from which we can copy your SQL code to execute if
necessary (if using another editor, please export it as a doc or docx)
2. A PDF (to ensure compatibility / formatting)
This .zip file should be submitted on Canvas by 6pm on the due date of Friday 15 May. Name your
submission as 987654.zip, and the files inside as 987654.pdf and 987654.docx/987654.doc, where
987654 corresponds to YOUR student id. Other formatting requirements are listed below.
For each question, present an answer in the following format:
 Show the question number and question in black text.
 Show your answer (the SQL statement) in blue text (not a screenshot, we need to be able
to copy it).
 Show a screenshot from Workbench containing the output of the query.
 If the query returns more than 10 rows, take a screenshot of only the first 10 rows.
 Show how many rows were actually returned in red text.
 Show each query on a separate page.

Example:
QXX. List the names of clubs competing in the Melbourne Touch Rugby Competition.
SELECT clubname
FROM club;


8 rows returned
Requesting a Submission Deadline Extension
If you need an extension due to a valid (medical) reason, you will need to provide evidence to support
your request by 9pm, Thursday 14 May. Medical certificates need to be at least two days in length.
To request an extension:
1. Email the subject coordinator, Oscar Correa ([email protected]) from your
university email address, supplying your student ID, the extension request and supporting
evidence.
2. If your submission deadline extension is granted, you will receive an email reply granting the
new submission date. Replies may take up to 12 hours, so please be patient.
Reminder: INFO20003 Hurdle Requirements
To pass INFO20003 you must pass two hurdles:
 Hurdle 1: Obtain at least 50% (15/30) or higher for the three assignments (each worth 10%)
 Hurdle 2: Obtain a grade of 50% (35/70) or higher for the End of Semester Exam
Therefore, it is our recommendation to students that you attempt every assignment and every
question in the exam.
GOOD LUCK!