Practice Writing SQL Queries using Real Dataset
“The very first thing, we must do when writing SQL queries, is to understand the underlying data. Once we understand the data and how this data is stored across different tables, it becomes much simpler to write SQL Queries to retrieve any information from that data”
In this blog, we shall practice writing SQL Queries on a real dataset. We shall download the 120 years of Olympics History dataset from Kaggle from the user rgriffin. This way instead of using cooked up data, we shall use real data to write our SQL Queries. By having real data, it will be much easier for us to understand the data and then relate to it and write different types of queries over it.
Downloading Data from Kaggle:
You can download the dataset from here. Or, you can download it by clicking on the below download button “Download Olympics Dataset”.
Click on the button “Download Table Structures“ to download the table structures which can be used to load this dataset.
Once you download, you would see two csv files “athlete_events.csv“ and “noc_regions.csv“. Data (sample data) in these files would look as below:
Data from athlete_events file. We load this data into a table named “OLYMPICS_HISTORY”.
Data from noc_regions file. We load this data into a table named “OLYMPICS_HISTORY_NOC_REGIONS”.
Loading dataset to PostgreSQL Database:
I have explained there steps to upload this dataset into PostgreSQL database in my below YouTube video. Please watch it to understand this process.
List of SQL Queries:
We shall write 20 SQL Queries using this data. For each of these queries, you would find the problem statement and then the screen shot of the expected output. Under each of these 20 problem statement, you would find a download button from where you can download the solved SQL query.
I would recommend, you first try to solve the query yourself before downloading the solved SQL query.
List of all these 20 queries mentioned below:
How many olympics games have been held?
List down all Olympics games held so far.
Mention the total no of nations who participated in each olympics game?
Which year saw the highest and lowest no of countries participating in olympics?
Which nation has participated in all of the olympic games?
Identify the sport which was played in all summer olympics.
Which Sports were just played only once in the olympics?
Fetch the total no of sports played in each olympic games.
Fetch details of the oldest athletes to win a gold medal.
Find the Ratio of male and female athletes participated in all olympic games.
Fetch the top 5 athletes who have won the most gold medals.
Fetch the top 5 athletes who have won the most medals (gold/silver/bronze).
Fetch the top 5 most successful countries in olympics. Success is defined by no of medals won.
List down total gold, silver and broze medals won by each country.
List down total gold, silver and broze medals won by each country corresponding to each olympic games.
Identify which country won the most gold, most silver and most bronze medals in each olympic games.
Identify which country won the most gold, most silver, most bronze medals and the most medals in each olympic games.
Which countries have never won gold medal but have won silver/bronze medals?
In which Sport/event, India has won highest medals.
Break down all olympic games where india won medal for Hockey and how many medals in each olympic games.
SQL Queries:
1. How many olympics games have been held?
Problem Statement: Write a SQL query to find the total no of Olympic Games held as per the dataset.
Expected Output: Query 1
2. List down all Olympics games held so far.
Problem Statement: Write a SQL query to list down all the Olympic Games held so far.
Expected Output: Query 2 (Showing few records only)
3. Mention the total no of nations who participated in each olympics game?
Problem Statement: SQL query to fetch total no of countries participated in each olympic games.
Expected Output: Query 3 (Showing few records only)
4. Which year saw the highest and lowest no of countries participating in olympics
Problem Statement: Write a SQL query to return the Olympic Games which had the highest participating countries and the lowest participating countries.
Expected Output: Query 4
5. Which nation has participated in all of the olympic games
Problem Statement: SQL query to return the list of countries who have been part of every Olympics games.
Expected Output: Query 5
6. Identify the sport which was played in all summer olympics.
Problem Statement: SQL query to fetch the list of all sports which have been part of every olympics.
Expected Output: Query 6
7. Which Sports were just played only once in the olympics.
Problem Statement: Using SQL query, Identify the sport which were just played once in all of olympics.
Expected Output: Query 7
8. Fetch the total no of sports played in each olympic games.
Problem Statement: Write SQL query to fetch the total no of sports played in each olympics.
Expected Output: Query 8 (Showing few records only)
9. Fetch oldest athletes to win a gold medal
Problem Statement: SQL Query to fetch the details of the oldest athletes to win a gold medal at the olympics.
Expected Output: Query 9
10. Find the Ratio of male and female athletes participated in all olympic games.
Problem Statement: Write a SQL query to get the ratio of male and female participants
Expected Output: Query 10
11. Fetch the top 5 athletes who have won the most gold medals.
Problem Statement: SQL query to fetch the top 5 athletes who have won the most gold medals.
Expected Output: Query 11 (Showing few records only)
12. Fetch the top 5 athletes who have won the most medals (gold/silver/bronze).
Problem Statement: SQL Query to fetch the top 5 athletes who have won the most medals (Medals include gold, silver and bronze).
Expected Output: Query 12 (Showing few records only)
13. Fetch the top 5 most successful countries in olympics. Success is defined by no of medals won.
Problem Statement: Write a SQL query to fetch the top 5 most successful countries in olympics. (Success is defined by no of medals won).
Expected Output: Query 13
14. List down total gold, silver and bronze medals won by each country.
Problem Statement: Write a SQL query to list down the total gold, silver and bronze medals won by each country.
Expected Output: Query 14 (Showing few records only)
15. List down total gold, silver and bronze medals won by each country corresponding to each olympic games.
Problem Statement: Write a SQL query to list down the total gold, silver and bronze medals won by each country corresponding to each olympic games.
Expected Output: Query 15 (Showing few records only)
16. Identify which country won the most gold, most silver and most bronze medals in each olympic games.
Problem Statement: Write SQL query to display for each Olympic Games, which country won the highest gold, silver and bronze medals.
Expected Output: Query 16 (Showing few records only)
17. Identify which country won the most gold, most silver, most bronze medals and the most medals in each olympic games.
Problem Statement: Similar to the previous query, identify during each Olympic Games, which country won the highest gold, silver and bronze medals. Along with this, identify also the country with the most medals in each olympic games.
Expected Output: Query 17 (Showing few records only)
18. Which countries have never won gold medal but have won silver/bronze medals?
Problem Statement: Write a SQL Query to fetch details of countries which have won silver or bronze medal but never won a gold medal.
Expected Output: Query 18 (Showing few records only)
19. In which Sport/event, India has won highest medals.
Problem Statement: Write SQL Query to return the sport which has won India the highest no of medals.
Expected Output: Query 19
20. Break down all olympic games where India won medal for Hockey and how many medals in each olympic games
Problem Statement: Write an SQL Query to fetch details of all Olympic Games where India won medal(s) in hockey.
Expected Output: Query 20 (Showing few records only)
