Practicing SQL by Solving techTFQ's 20 SQL queries on an Olympic Games Kaggle dataset.
- Microsoft SQL Server
- SQL Code = Click Here For SQL File
-
olympic_history table:
-
noc_regions table:
Screen shots don't always show all the records
-- 1. How many olympic games have been held?
SELECT COUNT( DISTINCT Games) AS total_games_held
FROM olympic_history
-- 2. List down all the Olympic games held so far.
SELECT DISTINCT
Games,
City
FROM olympic_history
ORDER BY Games
-- 3. Mention the total number of nations who participated in each olympic games.
SELECT DISTINCT
Games,
City,
COUNT(DISTINCT NOC) AS number_of_nations_participating
FROM olympic_history
GROUP BY Games, City
ORDER BY Games
-- 4. Which year saw the highest and lowest no of countries participating in the olympics?
WITH
nations_participating AS (
SELECT DISTINCT
Games,
City,
COUNT(DISTINCT NOC) AS number_of_nations_participating
FROM olympic_history
GROUP BY Games, City
)
SELECT Games,
City,
number_of_nations_participating
FROM nations_participating
WHERE number_of_nations_participating IN (
(SELECT MIN(number_of_nations_participating) FROM nations_participating),
(SELECT MAX(number_of_nations_participating) FROM nations_participating)
)
-- 5. Which nation has participated in all of the olympic games?
WITH
olympic_games_held AS (
SELECT COUNT( DISTINCT Games) AS total_games_held
FROM olympic_history
),
games_participated AS (
SELECT DISTINCT
NOC,
COUNT(DISTINCT Games) AS total_games_participated
FROM olympic_history
GROUP BY NOC
)
SELECT n.region,
g.total_games_participated
FROM games_participated AS g
JOIN noc_regions AS n
ON g.NOC = n.NOC
JOIN olympic_games_held AS o
ON g.total_games_participated = o.total_games_held
-- 6. Identify the sport which was played in all summer olympics.
WITH
summer_olympics AS (
SELECT COUNT(DISTINCT Games) AS total_summer_olympics
FROM olympic_history
WHERE Season = 'Summer'
),
sports_played AS (
SELECT Sport,
COUNT(DISTINCT Games) AS total_olympics_played_at
FROM olympic_history
GROUP BY Sport
)
SELECT p.Sport,
p.total_olympics_played_at
FROM sports_played AS p
JOIN summer_olympics AS s
ON total_summer_olympics = total_olympics_played_at 
-- 7. Which Sports were just played once in the olympics?
WITH
sports_played AS (
SELECT Sport,
COUNT(DISTINCT Games) AS total_olympics_played_at
FROM olympic_history
GROUP BY Sport
HAVING COUNT(DISTINCT Games) = 1
)
SELECT DISTINCT
s.Sport,
s. total_olympics_played_at,
o. Games,
o. City
FROM sports_played AS s
JOIN olympic_history AS o
ON s.Sport = o.Sport
-- 8. Fetch the total number of sports played in each olympic games.
SELECT Games,
COUNT(DISTINCT Sport) AS total_sports_played
FROM olympic_history
GROUP BY Games
ORDER BY total_sports_played DESC
-- 9. Fetch oldest athletes to win a gold medal.
WITH
gold_medals AS (
SELECT MAX(Age) AS Age
FROM olympic_history
WHERE Medal = 'Gold'
)
SELECT o.Name,
o.Sex,
o.Age,
o.Team,
o.Games,
o.City,
o.Sport,
o.Event,
o.Medal
FROM olympic_history AS o
JOIN gold_medals AS g
ON o.Age = g.Age
WHERE o.Medal = 'Gold'
-- 10. Find the Ratio of female to male athletes that participated in all olympic games.
WITH
participants_by_sex AS (
SELECT CAST(SUM(CASE WHEN Sex = 'F' THEN 1 ELSE 0 END)AS FLOAT) AS female_participants,
CAST(SUM(CASE WHEN Sex = 'M' THEN 1 ELSE 0 END)AS FLOAT) AS male_participants
FROM olympic_history
)
SELECT CONCAT(female_participants/female_participants, ' : ', ROUND(male_participants/female_participants, 2)) AS ratio
FROM participants_by_sex
-- 11. Fetch the top 5 athletes who have won the most gold medals.
SELECT TOP 5
Name,
Team,
SUM(CASE WHEN Medal = 'Gold' THEN 1 ELSE 0 END) AS total_gold_medals
FROM olympic_history
GROUP BY Name, Team
ORDER BY total_gold_medals DESC
-- 12. Fetch the top 5 athletes who have won the most medals (gold/silver/bronze).
SELECT TOP 5
Name,
Team,
SUM(CASE WHEN Medal IN ('Gold', 'Silver', 'Bronze') THEN 1 ELSE 0 END) AS total_medals
FROM olympic_history
GROUP BY Name, Team
ORDER BY total_medals DESC
-- 13. Fetch the top 5 most successful countries in the olympics. Success is defined by no of medals won.
SELECT TOP 5
n.region AS Country,
SUM(CASE WHEN o.Medal IN ('Gold', 'Silver', 'Bronze') THEN 1 ELSE 0 END) AS total_medals
FROM olympic_history AS o
JOIN noc_regions AS n
ON o.NOC = n.NOC
GROUP BY n.region
ORDER BY total_medals DESC
-- 14. List down total gold, silver and bronze medals won by each country.
SELECT n.region,
SUM(CASE WHEN o.Medal = 'Gold' THEN 1 ELSE 0 END) AS gold,
SUM(CASE WHEN o.Medal = 'Silver' THEN 1 ELSE 0 END) AS silver,
SUM(CASE WHEN o.Medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze
FROM olympic_history AS o
JOIN noc_regions AS n
ON o.NOC = n.NOC
GROUP BY n.region
ORDER BY gold DESC
-- 15. List down total gold, silver and bronze medals won by each country corresponding to each olympic games.
SELECT o.Games,
n.region,
SUM(CASE WHEN o.Medal = 'Gold' THEN 1 ELSE 0 END) AS gold,
SUM(CASE WHEN o.Medal = 'Silver' THEN 1 ELSE 0 END) AS silver,
SUM(CASE WHEN o.Medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze
FROM olympic_history AS o
JOIN noc_regions AS n
ON o.NOC = n.NOC
GROUP BY o.Games, n.region
ORDER BY o.Games 
-- 16. Identify which country won the most gold, most silver and most bronze medals in each olympic games.
WITH
medals AS (
SELECT o.Games,
n.region,
SUM(CASE WHEN o.Medal = 'Gold' THEN 1 ELSE 0 END) AS gold,
SUM(CASE WHEN o.Medal = 'Silver' THEN 1 ELSE 0 END) AS silver,
SUM(CASE WHEN o.Medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze
FROM olympic_history AS o
JOIN noc_regions AS n
ON o.NOC = n.NOC
GROUP BY o.Games, n.region
)
SELECT DISTINCT
Games,
CONCAT (first_value(region) OVER(PARTITION BY games ORDER BY gold DESC), ' - ' , first_value(gold) OVER(PARTITION BY games ORDER BY gold DESC)) AS max_gold,
CONCAT (first_value(region) OVER(PARTITION BY games ORDER BY silver DESC), ' - ', first_value(silver) OVER(PARTITION BY games ORDER BY silver DESC)) AS max_silver,
CONCAT (first_value(region) OVER(PARTITION BY games ORDER BY bronze DESC), ' - ', first_value(bronze) OVER(PARTITION BY games ORDER BY bronze DESC)) AS max_bronze
FROM medals
GROUP BY Games, region, gold, silver, bronze
-- 17. Identify which country won the most gold, most silver, most bronze medals as well as the most medals in each olympic games.
WITH
medals AS (
SELECT o.Games,
n.region,
SUM(CASE WHEN o.Medal = 'Gold' THEN 1 ELSE 0 END) AS gold,
SUM(CASE WHEN o.Medal = 'Silver' THEN 1 ELSE 0 END) AS silver,
SUM(CASE WHEN o.Medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze,
SUM(CASE WHEN o.Medal IN ('Gold', 'Silver', 'Bronze') THEN 1 ELSE 0 END) AS total_medals
FROM olympic_history AS o
JOIN noc_regions AS n
ON o.NOC = n.NOC
GROUP BY o.Games, n.region
)
SELECT DISTINCT
Games,
CONCAT (first_value(region) OVER(PARTITION BY games ORDER BY gold DESC), ' - ' , first_value(gold) OVER(PARTITION BY games ORDER BY gold DESC)) AS max_gold,
CONCAT (first_value(region) OVER(PARTITION BY games ORDER BY silver DESC), ' - ', first_value(silver) OVER(PARTITION BY games ORDER BY silver DESC)) AS max_silver,
CONCAT (first_value(region) OVER(PARTITION BY games ORDER BY bronze DESC), ' - ', first_value(bronze) OVER(PARTITION BY games ORDER BY bronze DESC)) AS max_bronze,
CONCAT (first_value(region) OVER(PARTITION BY games ORDER BY total_medals DESC), ' - ' , first_value(total_medals) OVER(PARTITION BY games ORDER BY total_medals DESC)) AS max_medals
FROM medals
GROUP BY Games, region, gold, silver, bronze, total_medals 
-- 18. Which countries have never won a gold medal but have won silver/bronze medals?
WITH
medals AS (
SELECT n.region,
SUM(CASE WHEN o.Medal = 'Gold' THEN 1 ELSE 0 END) AS gold,
SUM(CASE WHEN o.Medal = 'Silver' THEN 1 ELSE 0 END) AS silver,
SUM(CASE WHEN o.Medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze
FROM olympic_history AS o
JOIN noc_regions AS n
ON o.NOC = n.NOC
GROUP BY n.region
)
SELECT DISTINCT
region,
gold,
silver,
bronze
FROM medals
WHERE gold = 0
AND (silver <> 0 OR bronze <> 0)
GROUP BY region, gold, silver, bronze
-- 19. In which Sport/event has India won the most medals?
SELECT TOP 1
Team AS Country,
Sport,
SUM(CASE WHEN Medal IN ('Gold', 'Silver', 'Bronze') THEN 1 ELSE 0 END) AS total_medals
FROM olympic_history
WHERE NOC = 'IND'
GROUP BY Team, Sport
ORDER BY total_medals DESC
-- 20. Break down all olympic games where India won a medal for Hockey, and show how many Hockey medals were won in each of those olympic games.
SELECT Games,
Team AS Country,
Sport,
SUM(CASE WHEN Medal IN ('Gold', 'Silver', 'Bronze') THEN 1 ELSE 0 END) AS total_medals
FROM olympic_history
WHERE NOC = 'IND'
AND Sport = 'Hockey'
AND Medal <> 'NA'
GROUP BY Games, Team, Sport
ORDER BY total_medals DESC