EXAM Part 1: Assignment
Question 3. SQL & Relational Algebra (15 Marks) Melbourne Touch Rugby Summer Competition
“Touch” rugby is a five-person game that is played in the off season (usually late Spring to late early Autumn). It is a fast and lively game where instead of tackling other players to the ground – they only have to touch or ‘tag’ the player for there to be a restart in play.
There are 8 touch rugby clubs that compete in the Melbourne Touch Rugby Summer comptition. There are currently four competitions: mens, womens, seniors – for men over the age of 35 years, and a mixed division for teams made up of men and women. A mixed team must have a minimum of three female players in every game. A team must have a minimum of four players available to play the game. If one team is unable to field a side (minimum of four players) at the game start time they forfeit the game and suffer a walkover, and do not record any value for their score. The other team scores 28 points.
The competitions have been running for several seasons, with the details of every game and participating teams and players carefully recorded.
Figure 2. The Melbourne Touch Rugby schema
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020
6
EXAM Part 1: Assignment
Questions 3A– 3D require you to write one single SQL statement per question. Do not use views, temporary tables or inline views or any schema on read solution. Format code for ease of reading. Ensure user-friendly output by renaming columns where appropriate. Display the first ten rows and the total number of rows returned.
For Example:
Q. List the first name, last name and salary of Alice Munro
SELECT firstname, lastname, salary
FROM employee
WHERE firstname = ‘Alice’ AND lastname = ‘Munro’;
1 row returned
Q3A. List the game date, team name and all players who were selected for a team that eventually had to forfeit (walkover) in 2018?
( 3 marks)
Q3B. How many points did the Melbourne University Rugby Club team score in each year of the Dewar Shield competition? List the team name, and cumulative score for each year. Order the result from the highest cumulative score to lowest.
( 3 marks) Q3C. Elizabeth Blackburn has played for three different clubs. How many games did she play for each
club? List the club name and number of games.
Q3D. List the teams and scores for all games played on 4th October 2015 at 10.00 a.m. Your result should be in tabular form :
Q3E. Write the SQL and two (2) versions of Relational Algebra for the following query: List all team names of the Geelong Rugby Club
( 3 marks)
(3 marks)
Team A
32
Team B
27
Team C
15
Team D
23
Team E
23
Team F
6
Team G
0
Team H
10
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020
7
(1+ 1 + 1 = 3 marks)