ISYS2421 Assignment 1a - Basic SQL Queries

ISYS2421 - Business Data Management and Analytics

SPECIFICATIONS
Read the following questions carefully. You will be asked to specify SQL queries to answer them.

QUESTIONS
You will be working with a set of tables for an Olympic database. You can access these tables by using the Olympic database on the mySql server (mo.its.rmit.edu.au). You are to prepare 10 SQL query statements that will provide answers to the following 10 requests for information.
Please NOTE: all these queries require only ONE TABLE in the FROM clause.
The answer to the following questions will support the commentators who are looking to get a historical perspective on several sports, the countries competing and returning athlete records.

1. List all the countries that belong to the continent of “Central America”. Show all the details stored in the database for each country.
2. Provide a list of athletes, who have participated in an Olympic summer games, who are taller than 210 centimeters. Show the athlete’s full name, gender, weight and height. Show the data from tallest to shortest.
3. Show a list of events that has the word “ball” in the event name. show event name only in alphabetical order.
4. Show a list of all the scheduled sports for the Sydney Olympics (i.e., game_id is 9) and show only those sports who are at a venue_id between 150 and 160 (inclusive); Since we are only access ONE table, only show the sport_id and venue_id.
5. Show all the Italian athletes that have a first name of "Vincenzo" or "Vincenza" that weighs less than 50 kilograms. Show all the data about the athletes.
6. How often has each sport_id been scheduled in a summer Olympic games. Show sport_id and count of how often it has been scheduled. For example, how many times has baseball been scheduled as a sport at the Olympics, but for all sports!!
7. Show a list of each place an Olympic game have been held with the number of times it has been held at that place. Show the place name and the total number held, of only those that have hosted the Olympics two or more times. Show the most games held first, and the least games held last.
8. Show a list of Australian athlete’s gender and count of the number of athletes per gender. Not all the athletes have the gender set to M or F, also show the athletes with an unset gender.
9. Show for each month of a year, the number of Olympics that started in that month. Show only the month name and the count of Olympic games starting in that month; show the data in chronological MONTH order!!! Note: not all months will appear.
10. Provide a list of all the dignitaries title (i.e., President, Duke, King, etc…) with a count of how many of each of these titles opened the Olympic games. Show a list of the title only and a count of the number of dignitaries with that title. Show the data ordered from highest to lowest count.
School of Accounting, Information Systems and Supply Chain Semester 2 - 2021 Business Data Management and Analytics v1.0 Page 3 of 3 RMIT Classification: Trusted

REQUIREMENTS
10 SQL queries that answer the questions asked, based on the data model, and
implemented database (on mo.its.rmit.edu.au) provided.

ASSESSMENT
Assessment of the data model will be based on the following areas (by the tutor):
• How well the query answers the questions, in relation to the case study provided.
• Understanding of data structure
• Efficiency and simplicity of resulting query

DEMONSTRATION
Selected students will be required to attend a demonstration session where they will be
asked to demonstrate and explain the queries they have written and to write several new
queries for the same database. Failure to explain the queries written or the inability to
write new queries will result in a FAIL mark being recorded for assignment 1 part a.

SUBMISSION
• SQL queries (output not required) only.
• Assignment will be submitted online using the canvas.
• By submitting online, you are observing adhering to all the requirements specified on a
standard assignment coversheet.