Basic SQL Query Practice Online: 20 Exercises for Beginners

These 20 exercises are just what beginners need for SQL query practice. Try to solve each of them, and then look at the solutions. If something needs to be clarified, there are explanations for each solution.

In this article, there’ll be less talking than usual. Instead, you’re going to write the answers to SQL practice queries. (Don’t worry; we’ve included the solutions if you get stuck.) The whole point is to give you, as a beginner, plenty of opportunities for SQL query practice.

I’ve selected twenty examples from our Basic SQL Practice: Run Track Through Queries!. If you feel you need to practice more by the end of the article – I recommend that wholeheartedly! – you’ll find almost 100 more interactive SQL exercises in that course. They cover topics like querying one table, using JOINs, sorting data with ORDER BY, aggregating data and using GROUP BY, dealing with NULLs, doing mathematical operations, and writing subqueries.

These are all topics that any SQL beginner should know well before going on to the more complex topics. The best way to learn anything in SQL is to consistently write your own code. That way, you’ll master SQL syntax and its basic features; plus, you’ll understand problem-solving. After all, the whole point of knowing SQL is knowing how to use data to solve problems.

And you could go even further! We have the SQL Practice track and the Monthly SQL Practice course for yet more SQL query practice.

With that being said, let’s dive straight into the SQL practice, starting with the dataset.

• Dataset
• Exercise #1: Show the Final Dates of All Events and the Wind Points
• Exercise #2: Show All Finals Where the Wind Was Above .5 Points
• Exercise #3: Show All Data for All Marathons
• Exercise #4: Show All Final Results for Non-Placing Runners
• Exercise #5: Show All the Result Data for Non-Starting Runners
• Exercise #6: Show Names for Men’s Discipline Runs Under 500 Meters
• Exercise #7: Sort Country Names and Abbreviations
• Exercise #8: Sort Athletes’ First and Last Names
• Exercise #9: Sort Final Results Over Three Hours
• Exercise #10: Show Top 3 Athletes’ Names and Places
• Exercise #11: Show All Marathons with Their Competition Name, Competition Year, and Discipline Name
• Exercise #12: Show Mo Farah’s Scores for All Disciplines
• Exercise #13: Show the Competitions’ Names and the Number of Events
• Exercise #14: Show the Most Popular Athlete Names
• Exercise #15: Show Each Country and the Number of Athletes Who Finished Without a Place
• Exercise #16: Calculate the Average Pace for Each Run
• Example #17: Find All Faster-Than-Average Times for 1,500 Meter Runs
• Exercise #18: Find All Athletes Who Ran in at Least Two Events In a Competition
• Exercise #19: Show Runners Who Only Finished First
• Exercise #20: Find All the Athletes Who Didn’t Start and Who Won At Least Once

SQL Query Practice

Dataset

The dataset contains data about the finals of track running competitions across athletics championships: Rio de Janeiro Olympic Games in 2016, London IAAF World Championships in Athletics in 2017, and Doha IAAF World Championships in Athletics in 2019.

Data is stored in six tables: competition, event, discipline, final_result, athlete, and nationality. The schema is shown below:

The competition information is stored in the table competition. It has the following columns:

• id – The ID of the competition and the primary key of the table.
• name – The competition's name.
• start_date – The competition's first day.
• end_date – The competition's last day.
• year – The year during which this competition occurred.
• location – The location of this competition.

Here’s the data from the table.

id	name	start_date	end_date	year	location
7093747	Rio de Janeiro Olympic Games	2016-08-12	2016-08-21	2016	Estádio Olímpico, Rio de Janeiro (BRA)
7093740	London IAAF World Championships in Athletics	2017-08-04	2017-08-13	2017	Olympic Stadium, London (GBR)
7125365	IAAF World Championships in Athletics	2019-09-27	2019-10-06	2019	Khalifa International Stadium, Doha (QAT)

The table discipline holds information for all running disciplines. It has these columns:

• id – The ID of the discipline and the primary key of the table.
• name – The discipline's name.
• is_men – TRUE if it's a men's discipline, FALSE if it's a women's.
• distance – The discipline's distance, in meters.

This is a snapshot of the first five rows of the data:

id	name	is_men	distance
1	Men's 100m	TRUE	100
2	Men's 200m	TRUE	200
3	Men's 400m	TRUE	400
4	Men's 800m	TRUE	800
5	Men's 1500m	TRUE	1,500

The next table is event, which stores  information about each particular event:

• id – The ID of the event and the primary key of the table.
• competition_id – Links the event to a competition.
• discipline_id – Links the event to a discipline.
• final_date – When this event's final was held.
• wind – The wind points during the final.

Here are the first five rows of this table:

id	competition_id	discipline_id	final_date	wind
1	7093747	1	2016-08-14	0.2
2	7093747	2	2016-08-18	-0.5
3	7093747	3	2016-08-14	0
4	7093747	4	2016-08-15	0
5	7093747	5	2016-08-20	0

The data about each athlete is in the table athlete:

• id – The ID of the athlete and the primary key of the table.
• first_name – The athlete's first name.
• last_name – The athlete's last name.
• nationality_id – The athlete's nationality.
• birth_date – The athlete's birth date.

These are the first five rows:

id	first_name	last_name	nationality_id	birth_date
14201847	Usain	BOLT	1	1986-08-21
14238562	Justin	GATLIN	2	1982-02-10
14535607	André	DE GRASSE	3	1994-11-10
14201842	Yohan	BLAKE	1	1989-12-26

The nationality table contains country information:

• id – The ID of the country and the primary key of the table.
• country_name – The country's name.
• country_abbr – The country's three-letter abbreviation.

Here is a five-row snapshot of this table:

id	country_name	country_abbr
1	Jamaica	JAM
2	United States	USA
3	Canada	CAN
4	South Africa	RSA
5	Côte d’Ivoire	CIV

The last table is final_result. It contains information about the participants and their results in a particular event:

• event_id – The event ID.
• athlete_id – The athlete’s
• result – The time/score for the athlete (can be NULL).
• place – The place achieved by the athlete (can be NULL).
• is_dsq – TRUE if disqualification occurred.
• is_dnf – TRUE if the athlete did not finish the run.
• is_dns – TRUE if the athlete did not start the run.

Here’s the snapshot:

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
1	14201847	0:00:10	1	FALSE	FALSE	FALSE
1	14238562	0:00:10	2	FALSE	FALSE	FALSE
1	14535607	0:00:10	3	FALSE	FALSE	FALSE
1	14201842	0:00:10	4	FALSE	FALSE	FALSE
1	14417763	0:00:10	5	FALSE	FALSE	FALSE

Now that you’ve had a good look at the dataset, let’s start our basic SQL query practice! All the exercises will require you to know some SQL, so make sure you know all the basic elements of an SQL query.

Exercise #1: Show the Final Dates of All Events and the Wind Points

Exercise: Find the final dates of all events and the wind points.

Solution:

SELECT
  final_date,
  wind
FROM event;

Explanation: The data you need is in the table event. You have to select two columns from it: final_date and wind. You do that by writing the first column in the SELECT statement. Next, you write the second column name and separate the column names with a comma.

Finally, you reference the table in the FROM clause.

Output:

final_date	wind
2016-08-14	0.2
2016-08-18	-0.5
2016-08-14	0
2016-08-15	0
2016-08-20	0

Exercise #2: Show All Finals Where the Wind Was Above .5 Points

Exercise: Show all the finals’ dates with a wind stronger than 0.5 points.

Solution:

SELECT final_date
FROM event
WHERE wind > 0.5;

Explanation: First, select the column final_date from the table event. With that, you’d get a list of all the finals. However, you don’t need the whole list – only those finals where the wind was stronger than 0.5.

So, you need to filter data using the WHERE clause. In it, you write the column name you want to filter; in this case, it’s the column wind. To get the wind above 0.5, use the ‘greater than’ (>) logical operator.

Output:

final_date
2017-08-11
2019-09-28
2019-10-02

Exercise #3: Show All Data for All Marathons

Exercise: Show the discipline data for all marathons.

Solution:

SELECT *
FROM discipline	
WHERE name LIKE '%Marathon%';

Explanation: To select all the columns, you don’t have to write their names explicitly. There’s a shorthand for ‘all columns’ called asterisk (*). Instead of the columns’ names, just put an asterisk in SELECT.

Then, as you want data from the table discipline, you reference it in FROM.

Finally, you have to filter the data. Use  WHERE and the LIKE operator. This operator looks through textual data in the column and returns all rows containing the text in the WHERE condition. In other words, the condition will look for the word ‘Marathon’. You must put the word in single quotes.

However, you don’t know the exact name of the discipline; you just know it has to contain that word. It can be anywhere in the discipline name: at the beginning, middle, or end. To look anywhere in the string, put the modulo (%) operator before and after the word you’re searching.

Output:

id	name	is_men	distance
8	Men's Marathon	TRUE	42,195
16	Women's Marathon	FALSE	42,195

Exercise #4: Show All Final Results for Non-Placing Runners

Exercise: Show all the data for final results for runners who did not place.

Solution:

SELECT *
FROM final_result
WHERE place IS NULL;

Explanation: You need all the columns, so use an asterisk in SELECT and reference the table final_result in FROM.

You need to show only those results where runners ended without a place. You will use WHERE this time, too, and filter on the column place. If a runner ends without a place, then the column place will be empty (i.e. NULL). You need the IS NULL operator after the column name to return all these rows.

Knowing what a NULL is in SQL  would be a good idea before using the IS NULL operator.

Output:

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
6	14464221	NULL	NULL	TRUE	FALSE	FALSE
7	14530623	NULL	NULL	FALSE	FALSE	TRUE
7	14573513	NULL	NULL	FALSE	FALSE	TRUE
8	14167397	NULL	NULL	FALSE	FALSE	TRUE
8	14177784	NULL	NULL	FALSE	FALSE	TRUE

Exercise #5: Show All the Result Data for Non-Starting Runners

Exercise: Show all the results data for runners that didn’t start the run at all.

Solution:

SELECT *
FROM final_result
WHERE is_dns IS TRUE;

Explanation: Select all the columns from the table final_result using an asterisk and referencing the table in FROM.

Then, you want to use WHERE and filter the column by is_dns. If the runner didn’t start the race, this column will have the TRUE value. So, you need to use the IS TRUE operator after the column name.

Output: Here’s the whole output:

event_id	athlete_id	result	place	is_dsq	is_dns	is_dnf
14	14451797	NULL	NULL	FALSE	TRUE	FALSE
16	14296979	NULL	NULL	FALSE	TRUE	FALSE
19	14176330	NULL	NULL	FALSE	TRUE	FALSE
22	14367867	NULL	NULL	FALSE	TRUE	FALSE
24	14219653	NULL	NULL	FALSE	TRUE	FALSE
24	14225279	NULL	NULL	FALSE	TRUE	FALSE
32	14751813	NULL	NULL	FALSE	TRUE	FALSE
41	14291986	NULL	NULL	FALSE	TRUE	FALSE

Exercise #6: Show Names for Men’s Discipline Runs Under 500 Meters

Exercise: Show only the men’s discipline names where the distance to be run is less than 500 meters.

Solution:

SELECT name
FROM discipline
WHERE is_men IS TRUE
  AND distance < 500;

Explanation: First, select the column name from the table discipline.

You again need to filter data – this time, by putting two conditions in WHERE.

The first condition is that it’s a male discipline. So, you have to filter the column is_men using the IS TRUE operator. Then you add the second condition: the values in the column distance have to be below 500. This condition uses the less than operator (<). Since both conditions have to be satisfied, separate the conditions using the AND operator.

Output:

name
Men's 100m
Men's 200m
Men's 400m

Exercise #7: Sort Country Names and Abbreviations

Exercise: Show all the countries’ names and abbreviations. Sort the output alphabetically by country name.

Solution:

SELECT
  country_name,
  country_abbr
FROM nationality
ORDER BY country_name ASC;

Explanation: Select the country name and its abbreviation from the table nationality.

To sort the output, use the ORDER BY clause. You want to sort by country name, so write country_name in ORDER BY. The output should be sorted alphabetically, so use the keyword ASC (ascending) after the column name.

Output: Here are the first five rows of the output:

country_name	country_abbr
Afghanistan	AFG
Algeria	ALG
American Samoa	ASA
Andorra	AND
Angola	ANG

Exercise #8: Sort Athletes’ First and Last Names

Exercise: Show every athlete’s first and last name. Sort the output descendingly by the athlete’s first name. If multiple athletes have the same name, show their surnames sorted descendingly.

Solution:

SELECT
  first_name,
  last_name
FROM athlete
ORDER BY first_name DESC, last_name DESC;

Explanation: Select the first and last name from the table athlete.

Then, add the ORDER BY clause. First sort by the first name descendingly, adding DESC after the column name. The second sorting condition sorts by the last name, also descendingly. Again, write the column name and add DESC. The conditions have to be separated by a comma.

Output: Here are the first five rows of the output:

first_name	last_name
Zsófia	ERDÉLYI
Zouhair	AWAD
Zoey	CLARK
Zoe	HOBBS
Zoe	BUCKMAN

Exercise #9: Sort Final Results Over Three Hours

Exercise: For all final results, show the times that are at least three hours. Sort the rows by the result in descending order.

Solution:

SELECT result
FROM final_result
WHERE result >= INTERVAL '3 hours'
ORDER BY result DESC;

Explanation: Select the column result from the table final_result.

Then, use WHERE to find the results that are below three hours. You can use the ‘greater than or equal’ (>=) and INTERVAL operators.

The data in the result column is formatted as time. So, you need to use INTERVAL to get the specific part (interval) from that data. In this case, it’s three hours. Simply write ‘3 hours’ after INTERVAL.

Finally, sort the output descendingly by the result.

Output: Here are the first five rows of the output:

result
3:20:20
3:16:11
3:15:18
3:11:31
3:11:05

Exercise #10: Show Top 3 Athletes’ Names and Places

Exercise: For every athlete ever on the podium (i.e. finished in the top 3), show their last and first name and their place.

Solution:

SELECT
  a.last_name,
  a.first_name,
  fin.place
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place <= 3;

Explanation: In this exercise, you need to use data from two tables: athlete and final_result. So, let’s start the explanation from the FROM clause.

You reference the athlete table and give it an alias ‘a’, so you won’t need to write the table’s full name elsewhere in the query. To get data from another table, too, you need to join the tables. In this case, use JOIN, which will return only the matching rows from both tables. You do that by simply referencing the table final_result in JOIN and adding the ‘fin’ alias.

Next, you have to specify the joining condition using the keyword ON. The tables are joined on shared columns: id from athlete and athlete_id from final_result. You’re looking for rows where the values in these two columns are equal, so put an equal sign (=) between them. In front of each column name, put the table alias followed by a dot so the database knows which table that column is in.

Now that you have joined the tables, you can select the columns. In front of each column name, put the table alias for the same reason as explained earlier. Now, you have the athletes’ last and first names and their places.

As a last step, simply filter data using WHERE and the column place. You’re looking for podium finishes, so the values must be equal to or less than three. Use the ‘less than or equal’ (<=) operator.

This SQL query practice requires you to know SQL JOINs. If you’re still unsure how they work, look at these SQL JOINs practice questions before you go to other exercises.

Output: Here are the first five rows of the output:

last_name	first_name	place
BOLT	Usain	3
BOLT	Usain	1
BOLT	Usain	1
GATLIN	Justin	2
GATLIN	Justin	1

Exercise #11: Show All Marathons with Their Competition Name, Competition Year, and Discipline Name

Exercise: Show all marathons, the name (rename this column competition_name) and year of the competition, and the name of the discipline (rename this column discipline_name).

Solution:

SELECT
  c.name AS competition_name,
  c.year,
  d.name AS discipline_name
FROM competition c
JOIN event e
  ON e.competition_id = c.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE d.name LIKE '%Marathon%';

Explanation: This exercise shows how to join multiple tables. The principle is the same as with two tables. You just add more JOINs and the joining conditions.

In this case, you join the competition and event tables where e.competition_id equals the c.id column.

Then, you need to add the discipline table to the joining chain. Write JOIN again and reference the table discipline. Add the joining condition: the column discipline_id from the event has to be equal to the id column from the discipline table.

Now, select the required columns, remembering to put the table alias in front of each column. Alias competition_name and discipline_name using the keyword AS to give them the column names described in the instructions.

Finally, filter the results to show only marathon disciplines.

Output:

competition_name	year	discipline_name
Rio de Janeiro Olympic Games	2016	Men's Marathon
Rio de Janeiro Olympic Games	2016	Women's Marathon
London IAAF World Championships in Athletics	2017	Men's Marathon
London IAAF World Championships in Athletics	2017	Women's Marathon
IAAF World Championships in Athletics	2019	Men's Marathon

Exercise #12: Show Mo Farah’s Scores for All Disciplines

Exercise: Show Mo Farah's (athlete ID of 14189197) scores for all disciplines. Show NULL if he has never participated in a given discipline. Show all the male disciplines' names, dates, places, and results.

Solution:

SELECT
  d.name AS discipline_name,
  e.final_date,
  fin.place,
  fin.result
FROM discipline d
LEFT JOIN event e
  ON e.discipline_id = d.id
LEFT JOIN final_result fin
  ON fin.event_id = e.id
 AND athlete_id = 14189197
WHERE is_men IS TRUE;

Explanation: Join the tables discipline and event on the columns discipline_id and id. You need to use LEFT JOIN. This type of join will return all the rows from the first (left) table and only the matching rows from the second (right) table. If there are no matching rows, the values will be NULL. This is ideal for this exercise, as you need to show all disciplines and use  NULLs if Mo Farah has never participated in the discipline.

The next join is also a LEFT JOIN. It joins the table event with the table final_result. The first joining condition here joins the tables on the columns event_id and id. You also need to include the second condition by adding the keyword AND. This second condition will only look for Mo Farah’s data, i.e., the athlete with the ID of 14189197.

As a last step, use WHERE to find only men’s disciplines.

Output:

discipline_name	final_date	place	result
Men's 5000m	2016-08-20	1	0:13:03
Men's 10,000m	2016-08-13	1	0:27:05
Men's 5000m	2017-08-12	2	0:13:33
Men's 10,000m	2017-08-04	1	0:26:50
Men's 800m	2017-08-08	NULL	NULL
Men's Marathon	2019-10-05	NULL	NULL
Men's 100m	2017-08-05	NULL	NULL

Exercise #13: Show the Competitions’ Names and the Number of Events

Exercise: Show all the competitions’ names and the number of events for each competition.

Solution:

SELECT
  c.name AS competition_name,
  COUNT(*) AS events_held
FROM competition c
JOIN event e
  ON e.competition_id = c.id
GROUP BY c.name;

Explanation: First, show the column name from the table competition and rename the column to competition_name.

Then, use the aggregate function COUNT(*) to count the number of events that were held. The COUNT() function with an asterisk will count all the rows from the output, including NULLs. For better readability, we alias the resulting column as events_held.

The tables we join are competition and event. Finally, to get the number of events per competition, you need to GROUP BY the competition name.

Output:

competition_name	events_held
IAAF World Championships in Athletics	15
Rio de Janeiro Olympic Games	16
London IAAF World Championships in Athletics	16

Exercise #14: Show the Most Popular Athlete Names

Exercise: Show the most popular athlete names. Names are popular if at least five athletes share them. Alongside the name, also show the number of athletes with that name. Sort the results so that the most popular names come first.

Solution:

SELECT
  first_name,
  COUNT(*) AS name_count
FROM athlete
GROUP BY first_name
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC;

Explanation: First, select the first names and count them using COUNT(*). Then, group by the first name of the athlete. Now you have all the names and their count.

But you need to show only those names with a count above five. You’ll achieve that by using the HAVING clause. It has the same use as WHERE, but HAVING is used for filtering aggregated data.

Finally, sort the output by the name count from the highest to the lowest. You can’t simply write the name_count column name in ORDER BY because sorting is done before aggregation; SQL won’t recognize the column name. Instead, copy COUNT(*) and sort descendingly.

This exercise shows a typical SQL problem that requires filtering data with an aggregate function.

Output:

first_name	name_count
David	9
Daniel	7
Michael	7
Jessica	6
Alex	6
Sarah	5
Diana	5
Jonathan	5
Emmanuel	5
Isaac	5
Julian	5
Anna	5

Exercise #15: Show Each Country and the Number of Athletes Who Finished Without a Place

Exercise: Show all countries with the number of their athletes that finished without a place. Show 0 if none. Sort the output in descending order by the number of athletes and by the country name ascendingly.

Solution:

SELECT
  n.country_name,
  COUNT(fin.athlete_id) AS athletes_no
FROM nationality n
LEFT JOIN athlete a
  ON n.id = a.nationality_id
LEFT JOIN final_result fin
  ON a.id = fin.athlete_id
 AND fin.place IS NULL
GROUP BY n.country_name
ORDER BY
  COUNT(fin.athlete_id) DESC,
  n.country_name ASC;

Explanation: You have to keep all rows from the nationality table, so you need to LEFT JOIN it with the athlete table. You do that where id equals nationality_id. Then, LEFT JOIN another table where id from the athlete table equals athlete_id from the final_result table.

Because you need all the nationality rows, you can’t use the IS NULL condition in WHERE. There’s a solution: move it to the ON clause, and you’ll get all the values where the place is NULL.

Now, you can select the column country_name. Also, use the COUNT() function on the athlete_id column to get the number of athletes who finished without a place. You can’t use COUNT(*) here because it would’ve counted f, and you need the count of concrete athletes.

To get the count value by country, group the output by country name.

Finally, sort the output by the number of athletes descendingly and by the country name ascendingly.

Output: Here are the first five rows of the output:

country_name	athletes_no
Bahrain	8
Ethiopia	6
Turkey	6
Kenya	5
South Africa	5

Exercise #16: Calculate the Average Pace for Each Run

Exercise: Calculate the average pace for each run and show it in the column named average_pace.

Solution:

SELECT fin.result / (d.distance * 1.0 / 1000) AS average_pace
FROM event e
JOIN discipline d
  ON e.discipline_id = d.id
JOIN final_result fin
  ON fin.event_id = e.id;

Explanation: To get the average pace by run, you need to divide the result by the distance. This is what the above query does, but with two tweaks.

First, you need to multiply the distance by 1.0. You do that to convert the distance to a decimal number. Without that, the division might return a different result, as the result will be divided by the whole number. The second tweak is that you divide the distance by 1,000. By doing this, you’ll convert the distance from meters to kilometers.

Now that you have the calculation, give this column the alias average_pace.

The rest of the query is what you already saw in previous examples: you’re joining the table event with the table discipline and then with the table final_result.

Output: Here are the first five rows from the output:

average_pace
0:01:38
0:01:39
0:01:39
0:01:39
0:01:39

Example #17: Find All Faster-Than-Average Times for 1,500 Meter Runs

Exercise: Output the times for all 1,500-meter runs. Show only times that are faster than the average time for that run.

Solution:

SELECT fin.result
FROM final_result fin
JOIN event e
  ON fin.event_id = e.id
JOIN discipline d
  ON e.discipline_id = d.id
WHERE distance = 1500
  AND fin.result < (
    SELECT AVG(fin.result)
    FROM final_result fin
    JOIN event e
      ON fin.event_id = e.id
    JOIN discipline d
      ON e.discipline_id = d.id
    WHERE distance = 1500
  );

Explanation: You need to know SQL subqueries to solve this exercise. Their basic definition is that they are queries within a main query. Let’s see how this works!

Select the result column from the table final_result. Then, JOIN the table with event and then with the discipline table.

After that, you have to set two conditions in WHERE. The first one selects only distances that are equal to 1,500.

The second one looks for data where the result is below the total average for 1,500-meter runs. To calculate the average, use a subquery in the following way.

In the parentheses after the comparison operator, write another SELECT statement ( i.e., a subquery). In it, use the AVG() aggregate function to calculate the average result. The rest of the query is the same as the main query; you’re joining the same tables and using the same filtering condition in WHERE.

Output: Here are the first few rows from the output:

result
0:03:51
0:03:51
0:03:51
0:03:51
0:03:51
0:03:50
0:03:50
0:03:51

Exercise #18: Find All Athletes Who Ran in at Least Two Events In a Competition

Exercise: Output a list of athletes who ran in two or more events within any competition. Show only their first and last names.

Solution:

SELECT
  first_name,
  last_name
FROM athlete
WHERE id IN (
  SELECT fin.athlete_id
  FROM event e
  JOIN final_result fin
    ON fin.event_id = e.id
  GROUP BY e.competition_id, fin.athlete_id
  HAVING COUNT(*) >= 2
);

Explanation: Start by selecting the first and the last name from the table athlete.

Then, use WHERE to set up a condition. We again use a subquery to return data we wanted to compare, this time with the column id. However, in the previous example, we used the ‘less than’ (<) operator because the subquery returned only one value. This time, we use the operator IN, which will go through all the values returned by the subquery and return those that satisfy the condition.

The condition is that the athletes compete in at least two events within a competition. To find those athletes, select the column athlete_id and join the tables event and final_result. Then, group the results by the competition and athlete IDs. This example shows you can group the output by the column that is not in SELECT. However, all the columns that appear in SELECT have to also appear in GROUP BY.

Finally, use HAVING to filter the data. Count the number of rows using COUNT(*). That way, you’re counting how many times each athlete appears. Set the condition to return only those athletes with a count equal to or above two.

Output: Here’s the output snapshot.

first_name	last_name
Usain	BOLT
André	DE GRASSE
Aaron	BROWN
LaShawn	MERRITT
Wayde	VAN NIEKERK

Exercise #19: Show Runners Who Only Finished First

Exercise: Show all runners who have never finished at any place other than first; place was never missing for them. Show three columns: id, first_name, and last_name.

Solution:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1

EXCEPT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place != 1 OR fin.place IS NULL;

Explanation: For this solution, you need to use the EXCEPT set operator. The set operators are used to return the values from two or more queries. EXCEPT returns all the unique records from the first query except those returned by the second query.

The first query in the solution looks for those athletes who finished first. To get these values, select the required columns from the table athlete. Then, join the table with the table final_result. After that, set the condition in WHERE to find only the first places.

Now, write the EXCEPT keyword and follow it with the second query.

The second query is almost the same as the first one. The only difference is two conditions in WHERE.

The first condition returns all the places that are not the first by using the ‘not equal’ (!=) operator. The second condition looks for the non-NULL places, i.e., the place was never missing for that athlete. The conditions are connected using OR because one of those conditions has to be true; the athlete can’t finish below first place and also not finish at all.

Note that for set operators to work, there has to be the same number of columns of the same data type in both queries.

Output:

id	first_name	last_name
14590785	Elijah Motonei	MANANGOI
14208194	Eliud	KIPCHOGE
14603138	Donavan	BRAZIER
14289014	Jemima Jelagat	SUMGONG
14536762	Noah	LYLES
14377814	Lelisa	DESISA
14209691	David	RUDISHA
14431159	Halimah	NAKAAYI

Exercise #20: Find All the Athletes Who Didn’t Start and Who Won At Least Once

Exercise: Output the athletes who didn’t start at least one race and won at least one race. Show three columns: id, first_name, and last_name.

Solution:

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.is_dns IS TRUE

INTERSECT

SELECT
  a.id,
  a.first_name,
  a.last_name
FROM athlete a
JOIN final_result fin
  ON a.id = fin.athlete_id
WHERE fin.place = 1;

Explanation: This exercise uses another set operators. This time, it’s INTERSECT, which returns all the values that are the same in both queries.

The first query in the solution lists the athlete IDs and first and last names. The tables athlete and final_result are joined on the columns id and athlete_id from the tables.

The condition in WHERE looks for rows with TRUE as a value in the column is_dns, i.e., the column that shows whether the athlete started the race.

As in the previous example, write the set operator and then the second query.

The second query is the same as the first one, except for WHERE. The filtering condition will find the athletes who finished first.

Together, these two queries output the athletes that didn’t start the race at least once but also finished first at least once.

Output:

id	first_name	last_name
14291986	Dafne	SCHIPPERS

From Basic SQL Query Practice to Becoming an SQL Master

You have to start from somewhere. These 20 basic SQL query practices are ideal for building foundations before learning more advanced concepts.

You learned plenty as you practiced writing queries that used WHERE, ORDER BY, JOINs, GROUP BY, and HAVING. I also showed you several examples of dealing with NULLs, doing computations, writing subqueries, and using set operators. The queries in this article have been taken from our Basic SQL Practice: Run Track Through Queries! You’ll find more basic SQL exercises there. And if you want more practice, check out our SQL Practice track, which contains 9 SQL practice courses for beginners.

Add 20 basic SQL query examples and 10 beginner SQL practice exercises to the mix, and you’ll be equipped with an intermediate level of SQL proficiency.