SQL Assignment Solution
- List all the countries that belong to the continent of “Central America”. Show all the details stored in the database for each country.
Query:
SELECT * FROM country WHERE continent='Central America';
Explanation:
SELECT command is used to select all fields from the country table then only those countries are displayed in which continent field has “Central America” in the record by using WHERE command.
- Provide a list of athletes, who have participated in an Olympic summer games, who are
taller than 210 centimetres. Show the athlete’s full name, gender, weight and height. Show the data from tallest to shortest.
Query:
SELECT first_name, last_name, gender, weight, height FROM athlete WHERE height>210 ORDER BY height DESC;
Explanation:
SELECT command is used to select fields first name, last name, gender, weight and height from the athlete table then WHERE condition is used to sort out athletes who have heights greater than 210cm, then ORDER BY command is used to display all athletes in descending order.
- Show a list of events that has the word “ball” in the event name. show event name only in alphabetical order.
Query:
SELECT name FROM event WHERE name LIKE '%ball%' ORDER BY name ASC;
Explanation:
SELECT command is used to select name field from the event table then WHERE condition is used that has word like ball in the name, then all the event names are sorted in alphabetical order by using there command ORDER BY ASC.
- 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.
Query:
SELECT sport_id, venue_id FROM schedule WHERE game_id='9' AND venue_id >= 150 AND venue_id <= 160;
Explanation:
SELECT command is used to select fields sports id and venue id from schedule table, then WHERE condition is used to sort only ones that have game id 9 and another WHERE condition is used to sort out venue id in range of 150 and 160.
- 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.
Query:
SELECT * FROM athlete WHERE (first_name='Vincenzo' OR first_name='Vincenza') AND weight < 50 AND country='ITA';
Explanation:
SELECT command is used to select all fields from the athlete table then WHERE condition is used to sort out athletes that have first names "Vincenzo" or "Vincenza" and another WHERE condition is used to sort athletes only from country Italy and that have weight less than 50kg.
Read More
- 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!!
Query:
SELECT sport_id, COUNT(*) FROM schedule GROUP BY sport_id;
Explanation:
SELECT command is used to select sports id from the schedule table, and then the COUNT function is applied to it to count how many times that sport id has appeared in the schedule table.
- 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.
Query:
SELECT place, COUNT(game_id) FROM game GROUP BY place HAVING COUNT(game_id)>=2 ORDER BY COUNT(game_id) DESC;
Explanation:
SELECT command is used to select place from the game table, then GROUP BY command is applied to sort them into similar game id with place field displayed, then COUNT function is used to count how many time the summer games have taken place at same place, then ORDER BY command is used to sort them in descending order.
- 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.
Query:
SELECT gender, COUNT(*) FROM athlete WHERE country='AUS' GROUP BY gender;
Explanation:
SELECT command is used to select gender from athlete table and COUNT function is used to count all of them separately, WHERE condition is used to sort athletes only from Australia, then GROUP BY condition is used to count them according to gender.
- 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.
Query:
SELECT MONTHNAME(START),COUNT(*) FROM `game` GROUP BY MONTHNAME(START)
Explanation:
SELECT command is used to select the month name from the game table, then the COUNT function is used and GROUP BY command is used to sort and count them according to the same month's start date.
- 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
Query:
SELECT COUNT(*) AS COUNT,
(CASE WHEN opened_by LIKE '%president%' THEN 'president'
WHEN opened_by LIKE '%duke%' THEN 'duke'
WHEN opened_by LIKE '%queen%' THEN 'queen'
WHEN opened_by LIKE '%emperor%' THEN 'emperor'
WHEN opened_by LIKE '%chancellor%' THEN 'chancellor'
WHEN opened_by LIKE '%king%' THEN 'king'
END) AS dignitaries_title
FROM game
GROUP BY (CASE WHEN opened_by LIKE '%president%' THEN 'president'
WHEN opened_by LIKE '%duke%' THEN 'duke'
WHEN opened_by LIKE '%queen%' THEN 'queen'
WHEN opened_by LIKE '%emperor%' THEN 'emperor'
WHEN opened_by LIKE '%chancellor%' THEN 'chancellor'
WHEN opened_by LIKE '%king%' THEN 'king' END)
ORDER BY COUNT DESC;
Explanation:
SELECT command is used to count all olympics opened by dignitaries from game table separately, then WHEN conditions is applied, there are 6 WHEN LIKE conditions those are counted and displayed as a new field called dignitaries title, then all of them are sorted in descending order by using ORDER BY DESC command.
