联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-21:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2019-05-04 10:45

INFO20003 A2 S1 2019 1

INFO20003 Semester 1, 2019

Assignment 2 – SQL

Due: 6:00pm Friday 3 May

Submission: Via LMS https://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.

INFO20003 A2 S1 2019 2

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 rugby.sql from the Assessments folder on LMS 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.

INFO20003 A2 S1 2019 3

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 will be

penalized for writing overly complicated SQL statements. DO NOT USE VIEWS to answer questions.

1. Count the number of players by the first letter of their surname. List the results in

alphabetical order. (1 mark)

Hint: The function LEFT(str, n) returns the first n characters of the string value str.

2. List the game ID, year, and participating team names for every cancelled

game. (1 mark)

3. List the first and last names of players who have played more than 20 games in total

across all competitions. (1 mark)

4. For all current members of the Melbourne City club who have played fewer than 12

games across all competitions, list their first and last names and the number of games

they have played. Place the players who have played the most games first. (2 marks)

5. List the names of men who play in the Bingham Trophy but not the Dewar Shield

competition. (2 marks)

6. List the full name (in one column) and current club of players who did not play a game

in any competition in the 2017 season. (2 marks)

7. Which club currently has the fewest female players? Print the club name and the

number of female players in it. (2 marks)

8. A team has a “winless season” when it does not win any of the games it plays in that

season. Find the teams that have had exactly one winless season. List the team name

and the year in which their winless season occurred. (3 marks)

9. Find the first and last names of players who have played against all their former clubs.

(3 marks)

10. How many points did the ‘Melbourne City Women’ team score in the Williams Plate in 2017? (3 marks)

INFO20003 A2 S1 2019 4

Submission Instructions

Submit a single PDF showing your answers to all questions to the Assessment page on LMS by 6pm

on the due date of Friday 3 May. Name your file 987654.pdf, 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).

Show a screenshot from Workbench containing the output of the query.

o 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 2 May. Medical certificates need to be at least two days in length.

To request an extension:

1. Email the head tutor, Alan Thomas (alan.thomas@unimelb.edu.au) 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!


版权所有:编程辅导网 2021 All Rights Reserved 联系方式:QQ:99515681 微信:codinghelp 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。 站长地图

python代写
微信客服:codinghelp