# 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.

## 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.

idnamestart_dateend_dateyearlocation
7093747Rio de Janeiro Olympic Games2016-08-122016-08-212016Estádio Olímpico, Rio de Janeiro (BRA)
7093740London IAAF World Championships in Athletics2017-08-042017-08-132017Olympic Stadium, London (GBR)
7125365IAAF World Championships in Athletics2019-09-272019-10-062019Khalifa 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:

idnameis_mendistance
1Men's 100mTRUE100
2Men's 200mTRUE200
3Men's 400mTRUE400
4Men's 800mTRUE800
5Men's 1500mTRUE1,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:

idcompetition_iddiscipline_idfinal_datewind
1709374712016-08-140.2
2709374722016-08-18-0.5
3709374732016-08-140
4709374742016-08-150
5709374752016-08-200

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:

idfirst_namelast_namenationality_idbirth_date
14201847UsainBOLT11986-08-21
14238562JustinGATLIN21982-02-10
14535607AndréDE GRASSE31994-11-10
14201842YohanBLAKE11989-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:

idcountry_namecountry_abbr
1JamaicaJAM
2United StatesUSA
4South AfricaRSA
5Côte d’IvoireCIV

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_idathlete_idresultplaceis_dsqis_dnsis_dnf
1142018470:00:101FALSEFALSEFALSE
1142385620:00:102FALSEFALSEFALSE
1145356070:00:103FALSEFALSEFALSE
1142018420:00:104FALSEFALSEFALSE
1144177630:00:105FALSEFALSEFALSE

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_datewind
2016-08-140.2
2016-08-18-0.5
2016-08-140
2016-08-150
2016-08-200

### 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:

idnameis_mendistance
8Men's MarathonTRUE42,195
16Women's MarathonFALSE42,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_idathlete_idresultplaceis_dsqis_dnsis_dnf
614464221NULLNULLTRUEFALSEFALSE
714530623NULLNULLFALSEFALSETRUE
714573513NULLNULLFALSEFALSETRUE
814167397NULLNULLFALSEFALSETRUE
814177784NULLNULLFALSEFALSETRUE

### 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_idathlete_idresultplaceis_dsqis_dnsis_dnf
1414451797NULLNULLFALSETRUEFALSE
1614296979NULLNULLFALSETRUEFALSE
1914176330NULLNULLFALSETRUEFALSE
2214367867NULLNULLFALSETRUEFALSE
2414219653NULLNULLFALSETRUEFALSE
2414225279NULLNULLFALSETRUEFALSE
3214751813NULLNULLFALSETRUEFALSE
4114291986NULLNULLFALSETRUEFALSE

### 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_namecountry_abbr
AfghanistanAFG
AlgeriaALG
American SamoaASA
AndorraAND
AngolaANG

### 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_namelast_name
ZsófiaERDÉLYI
ZoeyCLARK
ZoeHOBBS
ZoeBUCKMAN

### 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_namefirst_nameplace
BOLTUsain3
BOLTUsain1
BOLTUsain1
GATLINJustin2
GATLINJustin1

### 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_nameyeardiscipline_name
Rio de Janeiro Olympic Games2016Men's Marathon
Rio de Janeiro Olympic Games2016Women's Marathon
London IAAF World Championships in Athletics2017Men's Marathon
London IAAF World Championships in Athletics2017Women's Marathon
IAAF World Championships in Athletics2019Men'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_namefinal_dateplaceresult
Men's 5000m2016-08-2010:13:03
Men's 10,000m2016-08-1310:27:05
Men's 5000m2017-08-1220:13:33
Men's 10,000m2017-08-0410:26:50
Men's 800m2017-08-08NULLNULL
Men's Marathon2019-10-05NULLNULL
Men's 100m2017-08-05NULLNULL

### 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_nameevents_held
IAAF World Championships in Athletics15
Rio de Janeiro Olympic Games16
London IAAF World Championships in Athletics16

### 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_namename_count
David9
Daniel7
Michael7
Jessica6
Alex6
Sarah5
Diana5
Jonathan5
Emmanuel5
Isaac5
Julian5
Anna5

### 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_nameathletes_no
Bahrain8
Ethiopia6
Turkey6
Kenya5
South Africa5

### 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_namelast_name
UsainBOLT
AndréDE GRASSE
AaronBROWN
LaShawnMERRITT
WaydeVAN 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:

idfirst_namelast_name
14590785Elijah MotoneiMANANGOI
14208194EliudKIPCHOGE
14603138DonavanBRAZIER
14289014Jemima JelagatSUMGONG
14536762NoahLYLES
14209691DavidRUDISHA
14431159HalimahNAKAAYI

### 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:

idfirst_namelast_name
14291986DafneSCHIPPERS

## 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.