Title: SQL Subquery Practice: 15 Exercises with Solutions

Table of Contents
Subqueries are often challenging for beginners. Practice makes perfect, so join us as we work through these 15 SQL subquery practice exercises!
In SQL, a subquery is a query nested within another query. It simplifies building intricate queries to retrieve data that meets specific conditions from various tables.
In this article, we present various ways of employing subqueries to create complex queries. We start by introducing SQL subqueries along with common use cases. Then we’ll walk you through 15 SQL subquery practice exercises – complete with solutions and explanations.
If you’ve never heard of SQL subqueries, check out our free Beginner’s Guide to the SQL Subquery. You can also get hands-on experience building subqueries in our SQL Basics course.
But if you know the basics and are ready to improve your knowledge, let’s get started practicing SQL subqueries.
Just to recap, a SQL subquery is a SELECT statement embedded in another SELECT statement. You can think of subqueries as building blocks that make up complex queries: they let us break down complicated tasks into smaller parts and make the code easier to read.
Imagine putting one question inside another – that's how subqueries work. With the help of subqueries, you can get specific information from different tables filtered by different conditions all in one go.
Here are some common use cases for SQL subqueries:
The following exercises use the orchestras dataset that contains three tables.
Now that we are familiar with the dataset, let’s proceed to the SQL practice exercises. The following exercises come from the SQL Practice Set course.
Exercise:
Select the names of all orchestras that have the same city of origin as any city in which any orchestra performed in 2013.
Solution:
Solution explanation:
We aim to select names of orchestras that fulfill a certain condition, so we start with SELECT name FROM orchestras. Then, the condition is going to be imposed on the city_origin column, as mentioned in the instructions.
We want to select only the orchestras whose city of origin belongs to the group of cities where concerts were played in the year 2013. To create this condition in the WHERE clause, we use SQL subquery.
Let’s create a (sub)query that selects all cities where concerts were played in 2013: SELECT city FROM concerts WHERE year = 2013. It returns a column containing city names.
To ensure that the city of origin belongs to the cities returned by the subquery, we use the IN operator.
Exercise:
Select the names and positions (i.e. instrument played) of all orchestra members that have above 10 years of experience and do not belong to orchestras with a rating below 8.0.
Solution:
Solution explanation:
We want to select names and positions of orchestra members that fulfill the conditions indicated in the instructions, so we start with SELECT name, position FROM members.
Then, we impose filtering conditions on members’ years of experience and orchestras where they belong. We want to select members whose years of experience exceed 10. Thus, we add the first WHERE clause condition: experience > 10.
We don’t want to select members who belong to orchestras with ratings below 8.0. Let’s create a (sub)query that selects all orchestras with a rating below 8.0: SELECT id FROM orchestras WHERE rating < 8.0.
To ensure that the orchestras do not belong to the orchestras listed by this subquery, we use the NOT IN operator.
Exercise:
Show the name and position of orchestra members who earn more than the average wage of all violinists.
Solution:
Solution explanation:
We select the name and position columns from the members table.
We use a subquery to find out the average wage of all violinists: SELECT AVG(wage) FROM members WHERE position = 'violin'.

Source: [[0]](https://learnsql.com/blog/sql-subquery-practice/)


Title: SQL Query Practice: 20 Exercises for Beginners - LearnSQL.com

Table of Contents
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 exercises. (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.
And you could go even further! We have the SQL Practice track with 10 SQL practice courses and each month we release a new Monthly SQL Practice course for yet more SQL query practice.
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:
Here’s the data from the table.
The table discipline holds information for all running disciplines. It has these columns:
This is a snapshot of the first five rows of the data:
The next table is event, which stores  information about each particular event:
Here are the first five rows of this table:
The data about each athlete is in the table athlete:
These are the first five rows:
The nationality table contains country information:
Here is a five-row snapshot of this table:
The last table is final_result. It contains information about the participants and their results in a particular event:
Here’s the snapshot:
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: Find the final dates of all events and the wind points.
Solution:
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:
Exercise: Show all the finals’ dates with a wind stronger than 0.5 points.
Solution:
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:
Exercise: Show the discipline data for all marathons.
Solution:
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.

Source: [[1]](https://learnsql.com/blog/sql-query-practice/)


Title: 15 SQL Server Practice Exercises with Solutions

Table of Contents
Enhance your SQL Server proficiency with our SQL Server practice exercises. Each of these 15 practical T-SQL tasks includes a detailed solution to help you improve your querying skills.
You know how everyone says, "Practice makes perfect"? Well, it couldn't be truer for SQL. The real learning happens when you start working with queries, tables, and data. If you're looking to sharpen your skills in MS SQL Server, you're in the right spot. These 15 beginner-friendly T-SQL exercises are perfect for getting the hang of the basics. (If you’re wondering, T-SQL is the SQL dialect used in SQL Server databases.)
The exercises are taken from our interactive course SQL Practice Set in MS SQL Server. This is a T-SQL practice course for beginners working with SQL Server. It offers over 80 hands-on practical exercises, grouped into sections on single table queries, aggregate functions, grouping and ordering results, JOINS, and subqueries, and others. If you like the exercises in this article, I recommend you try the course!
If you’re a beginner who wants to learn everything T-SQL has to offer, check out our complete learning track SQL from A to Z in MS SQL Server. The track contains 7 interactive T-SQL courses that will teach you complete modern T-SQL for data analysis.
In the first section, we’ll work on the table Cat. This table has the following columns:
Exercise: Select all data from the Cat table.
Solution:
Solution: We use the SELECT statement to select data from the database. The asterisk (*) following SELECT means that you want all columns from the table. The FROM Cat command indicates the table from which you pull the rows.  
Exercise: Select the Name, Breed, and Coloration for every cat that is younger than five years old.
Solution:
Explanation: As before, we use the SELECT statement. This time, we list the columns we want to select:  Name, Breed, and Coloration. In the FROM clause, we give the name of the table (Cats).
In the WHERE clause, we specify the condition to only select cats younger than 5 years old: Age < 5. We use the < operator to compare the value of the Age column with the number 5. The database will only select the cats who are younger than 5 (i.e. have Age lower than 5).
Exercise: Select the ID and name for every cat that is either younger than five years old or older than ten years old and that is of the Ragdoll breed.
Solution:
Explanation: This query is similar to the previous query. The only difference is in the WHERE clause.
We are looking for cats who are younger than 5 or older than 10. To this end, we use the condition (Age < 5 OR Age > 10). We use the comparison operators < and >, and we combine them with the logical operator OR. This selects cats who are either younger than 5 or older than 10.
We then use the condition Breed = 'Ragdoll' to only select Ragdoll cats. It’s important to note that the text value Ragdoll is put in single quotes: 'Ragdoll'.
Finally, we use the AND operator to combine the age and breed conditions.
Exercise: Select all data for cats whose:
Solution:
Explanation: We use a simple SELECT query to solve the exercise. In SELECT, we put the asterisk * to select all columns from the table.
We then use AND to combine three WHERE conditions. The first condition selects cats with breed names starting with R. We use the LIKE operator and the wildcard %: Breed LIKE 'R%'. This condition means that we are looking for breeds that start with R, followed by any text (empty or not).

Source: [[2]](https://learnsql.com/blog/sql-server-practice-exercises/)


Title: Subqueries (SQL Server) - SQL Server | Microsoft Learn

Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.
The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
A subquery can be used anywhere an expression is allowed. In this example, a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.
A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there's usually no performance difference between a statement that includes a subquery and a semantically equivalent version that doesn't. For architectural information on how SQL Server processes queries, see SQL statement processing. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results.
The following example shows both a subquery SELECT and a join SELECT that return the same result set and execution plan:
A subquery nested in the outer SELECT statement has the following components:
The SELECT query of a subquery is always enclosed in parentheses. It can't include a COMPUTE or FOR BROWSE clause, and can only include an ORDER BY clause when a TOP clause is also specified.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries do not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.
If a table appears only in a subquery and not in the outer query, then columns from that table can't be included in the output (the select list of the outer query).
Statements that include a subquery usually take one of these formats:
In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this isn't necessarily how SQL Server actually processes Transact-SQL statements with subqueries).
There are three basic types of subqueries. Those that:
A subquery is subject to the following restrictions:
In the following example, the BusinessEntityID column in the WHERE clause of the outer query is implicitly qualified by the table name in the outer query FROM clause (Sales.Store). The reference to CustomerID in the select list of the subquery is qualified by the subquery FROM clause, that is, by the Sales.Customer table.
The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level. If a column doesn't exist in the table referenced in the FROM clause of a subquery, it's implicitly qualified by the table referenced in the FROM clause of the outer query.
Here's what the query looks like with these implicit assumptions specified:
It's never wrong to state the table name explicitly, and it's always possible to override implicit assumptions about table names with explicit qualifications.
Important

Source: [[3]](https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16)


Title: Group Query Results by Month and Year in SQL - Baeldung

PostgreSQL offers several ways of grouping results by month and year. 2.1. Using EXTRACT. We can use the EXTRACT function to extract the year and month from a date or timestamp: SELECT EXTRACT (YEAR FROM reg_datetime) AS year, EXTRACT (MONTH FROM reg_datetime) AS month, COUNT (*) AS no_of_registrations.

Source: [[4]](https://www.baeldung.com/sql/group-by-date-parts)


Instruction: Using the provided web search results, to write a comprehensive reply to the user request.
Make sure to add the sources of cites using [[Number]](Url) notation after the reference. Example: [[0]](http://google.com)

User request:
SELECT DESCINCT a.id, a.model, a.year, p.cost_in_rubles FROM sql_test.auto a, sql_test.price p WHERE a.id = p.id AND a.id > 5 ORDER BY a.id DESC;