Implementation
- a) Find the days and details of all sponsored events.
SELECT
event.event_name
, event.even_date
, event.description
, sponsor.sponsore_name
FROM
cwg18.sponsored_by
INNER JOIN cwg18.event
ON (sponsored_by.event_id = event.event_id)
INNER JOIN cwg18.sponsor
ON (sponsored_by.sponsor_name = sponsor.sponsore_name);
- b) Count the number of athletes who are participating in Athletics.
SELECT sport.type AS ‘Sport Type’, COUNT(athlete_id) AS ‘No of Participants’
FROM
cwg18.performance
INNER JOIN cwg18.event
ON (performance.event_id = event.event_id)
INNER JOIN cwg18.sport
ON (event.sport_id = sport.sport_id)
WHERE sport.type =’Athletics’
GROUP BY sport.type;
- c) List the all the countries being represented in the Games, and the number of participating athletes per country.
SELECT
`country`.`country_code` , `country`.`country`, COUNT(`performance`.`athlete_id`) AS ‘No of Participants’
FROM
`cwg18`.`athlete`
INNER JOIN `cwg18`.`country`
ON (`athlete`.`country_code` = `country`.`country_code`)
INNER JOIN `cwg18`.`performance`
ON (`performance`.`athlete_id` = `athlete`.`athlete_id`)
GROUP BY `country`.`country_code` , `country`.`country`;
- d) Find all individual athletes who have won a medal at an event. Display the athlete name and medal.
SELECT
`athlete`.`first_name`
, `athlete`.`middle_name`
, `athlete`.`last_name`
, `performance`.`award`
FROM
`cwg18`.`performance`
INNER JOIN `cwg18`.`athlete`
ON (`performance`.`athlete_id` = `athlete`.`athlete_id`)
INNER JOIN `cwg18`.`event`
ON (`performance`.`event_id` = `event`.`event_id`)
WHERE award IS NOT NULL AND event.`phase` =’Finals’;
- Which sport(s) has the most number of events.
SELECT
sport.sport, COUNT(`event`.`event_name`) AS ‘No of Events’
FROM
`cwg18`.`event`
INNER JOIN `cwg18`.`sport`
ON (`event`.`sport_id` = `sport`.`sport_id`)
GROUP BY sport ORDER BY COUNT(`event`.`event_name`) DESC LIMIT 1;
- f) List all venues that can have more than 100 attendees.
SELECT `venue_name` , `max_capacity` FROM `venue` WHERE `max_capacity` > 100
ORDER BY `max_capacity` ASC;
- g) Find the sport that had the shortest timed performance.
SELECT
`event_venue`.`start_time`
, `event_venue`.`end_time`
, `event`.`event_name`
FROM
`cwg18`.`event`
INNER JOIN `cwg18`.`sport`
ON (`event`.`sport_id` = `sport`.`sport_id`)
INNER JOIN `cwg18`.`event_venue`
ON (`event_venue`.`event_id` = `event`.`event_id`);
- Which athletes broke an event world record at the games.
SELECT
`athlete`.`first_name`
, `athlete`.`middle_name`
, `athlete`.`last_name`
,event.`description`
, `performance`.`score` AS ‘New World Record’
, `world_records`.`record` AS ‘Old World Record’
FROM
`cwg18`.`world_records`
INNER JOIN `cwg18`.`sport`
ON (`world_records`.`sport_id` = `sport`.`sport_id`)
INNER JOIN `cwg18`.`event`
ON (`event`.`sport_id` = `sport`.`sport_id`)
INNER JOIN `cwg18`.`performance`
ON (`performance`.`event_id` = `event`.`event_id`)
INNER JOIN `cwg18`.`athlete`
ON (`performance`.`athlete_id` = `athlete`.`athlete_id`)
WHERE `performance`.`score` < `world_records`.`record`;
- j) List the medal counts (gold, silver, bronze) per country.
SELECT
`country`.`country`, `performance`.`award`, COUNT(`performance`.`award`) AS ‘No. Awards’
FROM
`cwg18`.`athlete`
INNER JOIN `cwg18`.`country`
ON (`athlete`.`country_code` = `country`.`country_code`)
INNER JOIN `cwg18`.`performance`
ON (`performance`.`athlete_id` = `athlete`.`athlete_id`)
WHERE `performance`.`award` IS NOT NULL
GROUP BY `country`.`country`, `performance`.`award`
ORDER BY COUNT(`performance`.`award`) DESC, country, award
;
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
