The University of Queensland
Brisbane QLD 4072 Australia
ABN: 63 942 912 684
CRICOS PROVIDER NUMBER 00025B
INFS1200 / INFS7900 Assignment 2
Code Due: 6 October 2023 @ 4:00 PM AEST
(Oral Assessment to be held in Week 12, 17-20 October 2023)
Weighting: 25% Version 2.0
Full Name Student ID (8 digits)
Overview
The purpose of this assignment is to test your ability to use and apply SQL concepts to complete tasks in a real-world
scenario. Specifically, this assessment will examine your ability to use SQL Data Manipulation Language to return
specific subsets of information which exist in a database and Data Definition Language to create new relational schema.
The assignment is to be completed individually.
Submission
Assignment 2 is made up of two parts. Part 1 will be submitted through an electronic marking tool called Gradescope,
which will also be used for providing feedback. Part 2 is an oral assessment that will be completed during an in-person
meeting with a tutor during a tutorial or practical session in Week 12 (after your Gradescope submission). Details below:
Part 1: Answer the questions on this task sheet and submit them through an electronic marking tool called Gradescope.
For this assignment, you will need to submit two types of files to the portal:
• Query Files:
o For each question in Sections A, B and C, you are required to submit a separate .sql or .txt file which contains
your SQL query solution for that question (submit only one of these files; if you submit both, the .sql file will be
graded).
o Each file should only contain the SQL query(s) and no additional text.
o Each file should be named as per the Filename description in the question.
o The total number of queries allowed to be run per question is also specified in each question’s description.
o When submitting files to the autograder, select all of your .txt or .sql files as well as your .pdf file.
• Assignment PDF:
o Insert your answers for all Sections A-D into the template boxes on this assignment task sheet where
appropriate, then export this document to a PDF and also upload it to the Gradescope autograder portal.
o Only Section D will be hand-marked from your PDF submission, however this is also a backup for Sections A,
B and C in case of autograder failure.
o UPDATES:
 For Sections A, B and C, include a screenshot of the output of your query for each question in the space
provided. Use your zones to generate the output. For queries with a returning relation of more than 10
tuples, you can use the LIMIT 10 clause to only capture the first 10 tuples of the table.
 Please name your file ‘Assignment_2.pdf’. Please do not alter the format or layout of this document in
anyway and ensure the name and SID boxes are completed.
CRICOS Provider No: 00025B 2
Part 2 is an oral assessment, to verify your understanding of the code you submitted in Part 1 Sections A, B and C.
• This will be an oral critique of your submitted code. In a short meeting with a tutor during Week 12, you will
explain the work you have submitted in Part 1 and discuss your choices.
• All oral assessments must be given live and will be recorded by the teaching team for archiving purposes.
Marking
Assignment 2 is worth 25 course marks, and marking is made up of two parts.
First, the marks available per section of Part 1 are as follows (INFS1200 differs from INFS7900):
INFS1200 INFS7900
Section A – SQL DML (SELECT) 15 marks 14 marks
Section B – SQL DML (UPDATE, INSERT, DELETE) 4 marks 4 marks
Section C – SQL DDL 4 marks 4 marks
Section D – Critical thinking 2 marks 3 marks
Given these available marks, students must also achieve a pass (+/-) in Part 2, the oral critique, to be eligible to pass
Assignment 2. Failure in Part 2 will result in your mark being capped at 12.5%.
Grading and autograder feedback
Sections A, B and C of this assignment will be graded via an autograder deployed on Gradescope; however we reserve
the right to revert to hand marking using the pdf submission should the need arise. Specifically, your assignment may
be graded against several data instances, which may include a simple (and small) data instance, a large data instance
or an instance containing curated edge cases. The correctness of your queries will be judged by comparing your queries’
return values to those of our solutions, because there is usually more than one equivalent way to execute a given query.
Note that solutions to each question will be limited to contain a maximum of 3 queries.
When you submit your code, the autograder will provide you with two forms of immediate feedback:
• File existence and compilation tests: Your code will be checked to see if it compiles correctly. If it fails one
or more compilation test, the errors returned by the autograder will help you debug. Note that code that fails to
compile will receive 0 marks. No marks are given for passing the compilation tests.
• Simple instance data tests: The autograder will return your degree of success on the simple data instance,
so that you can judge your progress (i.e. 90% of simple instance tests passed). Individual test results will not
be revealed, and your submission’s performance on the more difficult instances will remain hidden until grades
are released. Final weightings on the different test instances will also remain hidden until grades are released.
More details will be provided regarding how you can interpret the results of these tests and what it means for your
assignment grade during practicals.
Note: Your queries must compile using MySQL version 8.0. This is the same DBMS software as is used on your zones.
You may use any MySQL function that have been used in class in addition to those specified in the questions. You may
also use other MySQL functions not covered in this course to assist with manipulating the data if needed, however
please ensure you read the MySQL documentation page first to ensure the functions works as intended.
The final details of the Gradescope autograder will be released closer to the assignment deadline. Note that you will be
able to resubmit to the autograder an unlimited number of times before the deadline.
Materials provided:
You will be provided with the database schema and the simple data instance. Because the autograder uses the same
DBMS as your zones, you are encouraged to use your zones to develop your assignment answers.
CRICOS Provider No: 00025B 3
Plagiarism
The University has strict policies regarding plagiarism. Penalties for engaging in unacceptable behaviour range from
loss of grades in a course through to expulsion from UQ. You are required to read and understand the policies on
academic integrity and plagiarism in the course profile (Section 6.1). If you have any questions regarding acceptable
level of collaboration with your peers, please see either the lecturer or your tutor for guidance. Remember that ignorance
is not a defence!
In particular, you are permitted to use generative AI tools to help you complete this assessment task. However, if you
do, please provide complete copies of your interactions with the AI tool in the space provided at the end of your
submission. Please note that if you use generative AI but fail to acknowledge this by attaching your interaction to the
end of the assignment, it will be considered misconduct as you are claiming credit for work that is not your own.
Task
For this assignment you will be presented with the simplified schema of an event management application for the
Olympics. It captures essential details about countries, sports, athletes, events, and the medals won in these events.
1. Countries table lists all the participating countries in the Olympic games. Each country has a unique identifier
(CountryID), a name (CountryName), and is associated with a specific geographical region (Region).
2. Sports table contains information about various sports played in the Olympics. Each sport has a unique
identifier (SportID) and a name (SportName).
3. Athletes table records all athletes participating in the games. Each athlete is uniquely identified by an
AthleteID, and they also have a name (AthleteName). They are associated with a specific country,
represented by the CountryID, which links to the Countries table. Their age is also stored in the database.
4. Events table represents the events held in different sports. Each event is uniquely identified by an EventID
and is associated with a specific sport, represented by the SportID, which refers to the Sports table and an
associated date and ticket price for the event.
5. Medals table archives the allocation of medals. Each entry, denoting a medal, is uniquely associated with a
particular athlete (AthleteID) and a specific event (EventID), and it documents the category of the medal
(MedalType - Gold, Silver, or Bronze) won by the athlete. The AthleteID references the Athletes table, and the
EventID refers to the Events table.
6. Contestants table contains all contestants in all events. Each entry is uniquely identified by an event
(EventID) and an athlete (AthleteID) participating in that event. The EventID refers to the Events table, and the
AthleteID references the Athletes table.
Relational Schema:
Countries [CountryID, CountryName, Region]
Sports [SportID, SportName]
Athletes [AthleteID, AthleteName, CountryID, Age]
Events [EventID, SportID, Date, TicketPrice]
Medals [AthleteID, EventID, MedalType]
Contestants [EventID, AthleteID]
Foreign Keys:
Athletes.CountryID references Countries.CountryID
Events.SportID references Sports.SportID
Medals.AthleteID references Athletes.AthleteID
Medals.EventID references Events.EventID
Contestants.EventID references Events.EventID
Contestants.AthleteID references Athletes.AthleteID
CRICOS Provider No: 00025B 4
For this assignment you will be required to write SQL queries to answer to complete the following tasks. Please use
the submission boxes provided to record your answers. An example is given below.
Output screenshot:
Example
Task Return the id and name of all athletes.
Explanation This query should return a table with two columns, one for the id and one for the
name of the athletes.
SQL Solution SELECT AthleteID, AthleteName
FROM Athletes
LIMIT 10;
CRICOS Provider No: 00025B 5
Section A – SQL DML (SELECT)
Output screenshot:
Question 1
Task Return the names of all sports played at the Olympics (duplicates should not be
included), ordered by SportName in alphabetical order.
Filename a1.sql or a1.txt
SQL Solution
CRICOS Provider No: 00025B 6
Output screenshot:
Question 2
Task Return the number of events that occurred for each sport during the month of July
2023.
Explanation This query should return two columns, one for the SportID and one for the
number of events that occurred for each sport.
Filename a2.sql or a2.txt
SQL Solution
CRICOS Provider No: 00025B 7
Output screenshot:
Question 3
Task Return the number of medals won for each country.
Explanation This query should return two columns, one for the CountryID, and one for the
number of medals won (if the country has won 0 medals, it should still be
included).
Filename a3.sql or a3.txt
SQL Solution
CRICOS Provider No: 00025B 8
Output screenshot:
Question 4
Task Return the medal tally for ‘Australia’ across all events.
Explanation This query should return a table with two columns, one with the type of medal
(Gold, Silver, or Bronze) and the other with the number of medals won for
Australia.
Filename a4.sql or a4.txt
SQL Solution
CRICOS Provider No: 00025B 9
Output screenshot:
Question 5
Task Return the country names of countries who have at least one participating athlete
over the age of 30.
Explanation This query should use at least one sub-query.
Filename a5.sql or a5.txt
SQL Solution
CRICOS Provider No: 00025B 10
Output screenshot:
Question 6
Task Return the name, age of the youngest Australian athlete(s) participating in the
Olympics.
Explanation
Filename a6.sql or a6.txt
SQL Solution
CRICOS Provider No: 00025B 11
Output screenshot:
Question 7
Task Return the country names of countries that won more than one gold medal in
the Olympics.
Explanation
Filename a7.sql or a7.txt
SQL Solution
CRICOS Provider No: 00025B 12
Output screenshot:
Question 8
Task Return the names of athletes that medalled in expensive sports (i.e., sports that
had at least 3 events with a ticket price over $100).
Explanation Hint. You may want to use one or more views in your answer.
Filename a8.sql or a8.txt
SQL Solution
CRICOS Provider No: 00025B 13
Section B – SQL DML (UPDATE, DELETE, INSERT)
Output screenshot:
Question 1
Task Sarah Smith has had a positive performance-enhancing drugs test, so her medals
(if any) need to be removed from the database.
Explanation
Filename b1.sql or b1.txt
SQL Solution
CRICOS Provider No: 00025B 14
Output screenshot:
Question 2
Task The ticket price for all sports and games except Basketball and Soccer are to be
reduced by 10% due to a lack of demand. Issue this update in the database.
Explanation This query should update the price of all other events in the future (i.e., the
Date is later than the current date), to be 10% less than the existing price in the
database.
Filename b2.sql or b2.txt
SQL Solution
CRICOS Provider No: 00025B 15
Section C – SQL DDL
Output screenshot:
Question 1
Task Write a SQL DDL query to implement the following relational schema and associated
foreign keys.
Explanation The relational schema for this the table is as follows:
Table: Venues
Column Data Type Allow
Nulls?
Primary
Key?
VenueID INT N Y
VenueName VARCHAR N N
VenueType (‘Indoor’, ‘Outdoor’,
‘Covered’)
N N
CountryID INT N N
Filename c1.sql or c1.txt
SQL
Solution
CRICOS Provider No: 00025B 16
Output screenshot:
Question 2
Task To ensure that all events are reasonably priced, add a constraint that ensures that
no ticket is priced under $10 and over $1000.
Explanation The following resources may be useful when answering this question:
Check constraints
Filename c2.sql or c2.txt
SQL Solution
CRICOS Provider No: 00025B 17
Section D – Critical Thinking
In this section, you will receive theoretical situations related to the UoD mentioned in the task description. Your task is
to offer strategies to tackle the situation and write SQL queries to execute the approaches.
• INFS1200 students answer Question 1 only.
• INFS7900 students answer both Question 1 and Question 2.
Question 1
Task Olympics games planners want to know what to expect during the any of the most
busy weeks in the Olympics (i.e., how many athletes will be participating, how
many different sports, how many different countries and so on). Propose a strategy
for the given task and write an SQL query to implement that strategy.
Hint: The SQL WEEK() function may be useful.
Strategies
SQL Solution
CRICOS Provider No: 00025B 18
Question 2 – INFS7900 ONLY
Task Olympics games planners want to know how many athletes to expect in the
Olympic Village on different days (so that they can provide enough
accommodation, catering and support services, etc). Propose a strategy for the
given task and write an SQL query to implement that strategy.
Strategies
SQL Solution
CRICOS Provider No: 00025B 19
Documenting the use of Generative AI
Please note that if you have used generative AI in any manner, you are required to provide a transcript of your
engagement with the system in this section. You can simply copy and paste your discussion with the generative AI
system below. It is fine if it goes across multiple pages.
A reminder that a failure to reference AI use may constitute student misconduct under the Student Code of Conduct.