Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Using Inner Subqueries in the FROM Section | Nested Subqueries
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BYOrder of StatementsTotal Ride Time for Each Subway Line ChallengeAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVINGSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesNon-tech Department Employees ChallengeUsing Inner Subqueries in the FROM SectionEmployees With More Than Average Salary ChallengeUNION ClauseCombining Table Results
3. Joining Tables
JOIN 2 TablesGet Category By Product PriceGet Minimum Price By CategoryGet Product Amount By CategoryLEFT, RIGHT and INNER JOINsTable Join PracticeFULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

bookUsing Inner Subqueries in the FROM Section

As you may have understood from previous chapters, an inner subquery creates a table with which we then perform operations in the outer query.

From this, we can conclude that we can also use an inner query within the FROM section to select something from this table.

Let's consider an example where we need to work with employees whose salary is above a certain value. But instead of a WHERE clause, we will use an inner query:


              
123456

            
SELECT *
FROM (
  SELECT * 
  From employees
  WHERE salary >= 70000
) AS high_salary_employees
copy

We've created a new table using an inner subquery with which we can work and gather information.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Now, let's move on to a small task!

In this task, we'll be working with the department table.

Let me remind you what it looks like:

Task

Your task is to retrieve the average budget (budget column) for all managerial departments from the department table. Use an inner subquery in the FROM section to filter only the managerial type of department. Also, use the alias manager_departments for this table so the task is checked correctly.

Note:

The response should have only one column, average_budget. This is an alias for the aggregate function AVG().

Note

This task could also be done using a WHERE clause, but we use a nested query here to practice this syntax, which will be useful for solving more complex problems in the future.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 2. Chapter 3
toggle bottom row

bookUsing Inner Subqueries in the FROM Section

As you may have understood from previous chapters, an inner subquery creates a table with which we then perform operations in the outer query.

From this, we can conclude that we can also use an inner query within the FROM section to select something from this table.

Let's consider an example where we need to work with employees whose salary is above a certain value. But instead of a WHERE clause, we will use an inner query:


              
123456

            
SELECT *
FROM (
  SELECT * 
  From employees
  WHERE salary >= 70000
) AS high_salary_employees
copy

We've created a new table using an inner subquery with which we can work and gather information.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Now, let's move on to a small task!

In this task, we'll be working with the department table.

Let me remind you what it looks like:

Task

Your task is to retrieve the average budget (budget column) for all managerial departments from the department table. Use an inner subquery in the FROM section to filter only the managerial type of department. Also, use the alias manager_departments for this table so the task is checked correctly.

Note:

The response should have only one column, average_budget. This is an alias for the aggregate function AVG().

Note

This task could also be done using a WHERE clause, but we use a nested query here to practice this syntax, which will be useful for solving more complex problems in the future.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 2. Chapter 3
toggle bottom row

bookUsing Inner Subqueries in the FROM Section

As you may have understood from previous chapters, an inner subquery creates a table with which we then perform operations in the outer query.

From this, we can conclude that we can also use an inner query within the FROM section to select something from this table.

Let's consider an example where we need to work with employees whose salary is above a certain value. But instead of a WHERE clause, we will use an inner query:


              
123456

            
SELECT *
FROM (
  SELECT * 
  From employees
  WHERE salary >= 70000
) AS high_salary_employees
copy

We've created a new table using an inner subquery with which we can work and gather information.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Now, let's move on to a small task!

In this task, we'll be working with the department table.

Let me remind you what it looks like:

Task

Your task is to retrieve the average budget (budget column) for all managerial departments from the department table. Use an inner subquery in the FROM section to filter only the managerial type of department. Also, use the alias manager_departments for this table so the task is checked correctly.

Note:

The response should have only one column, average_budget. This is an alias for the aggregate function AVG().

Note

This task could also be done using a WHERE clause, but we use a nested query here to practice this syntax, which will be useful for solving more complex problems in the future.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

As you may have understood from previous chapters, an inner subquery creates a table with which we then perform operations in the outer query.

From this, we can conclude that we can also use an inner query within the FROM section to select something from this table.

Let's consider an example where we need to work with employees whose salary is above a certain value. But instead of a WHERE clause, we will use an inner query:


              
123456

            
SELECT *
FROM (
  SELECT * 
  From employees
  WHERE salary >= 70000
) AS high_salary_employees
copy

We've created a new table using an inner subquery with which we can work and gather information.

Note

Notice that for such a table, it's essential to provide an alias.

When might this be useful?

Such a query is highly readable; sometimes, it can replace a WHERE clause.

Additionally, if you need to work not with the entire table but only with a filtered part, an inner subquery within the FROM section can be extremely handy.

Now, let's move on to a small task!

In this task, we'll be working with the department table.

Let me remind you what it looks like:

Task

Your task is to retrieve the average budget (budget column) for all managerial departments from the department table. Use an inner subquery in the FROM section to filter only the managerial type of department. Also, use the alias manager_departments for this table so the task is checked correctly.

Note:

The response should have only one column, average_budget. This is an alias for the aggregate function AVG().

Note

This task could also be done using a WHERE clause, but we use a nested query here to practice this syntax, which will be useful for solving more complex problems in the future.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Section 2. Chapter 3
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
some-alt