ISYS2421 - Business Data Management and Analytics Assignment 1

Assumption: COUNTRY is the country team playing, unsure whether to call it COUNTRY or TEAM; An ATHLETE belongs to a country (country.code) and when competing the compete.team1_code links to country.code; Athlete’s “height” is in centimetres and “weight” is in kilograms. PARTICIPANT (supertype) and ATHLETE & COUNTRY (subtypes) were rolled down with COMPETE (many2many associative table) related to ATHELETE & COUNTRY to cover individual ATHLETE sports (eg. Swimming – athlete1_id) + ATHLETE vs ATHLETE sport (eg. Boxing – athlete1_id + athlete2_id) + COUNTRY team sport (eg. Team relay – team1_code) + COUNTRY team vs COUNTRY team sports (eg. Basketball – team1_code + team2_code). Score1 and score2 can be a rank, goals scored, points, time taken, or one of the RESULT_TYPEs; these fields are stored as CHAR. Olympic schedule data from: https://www.olympiandatabase.com/; Athlete data from:
https://www.kaggle.com/heesoo37/120-years-of-olympic-history-athletes-and-results

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.

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.