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

single

Using Inner Subqueries in the FROM Section

Swipe to show menu

An inner subquery creates a table with which you then perform operations in the outer query.

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

Consider an example where you need to work with employees whose salary is above a certain value. But instead of a WHERE clause, you should use an inner query:


              
123456

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

A new table has been created using an inner subquery with which you can work and gather information.

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

Task

Swipe to start coding

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.

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

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.

Brief Instructions

  • Retrieve the average value of the budget column.
  • Assign the alias average_budget to that column.
  • In the FROM clause, you need to write a nested query.
  • In the nested query, select all columns from the department table.
  • In the nested query, use the WHERE clause with the condition type = 'manager'.
  • Assign the alias manager_departments to the nested query.

Solution

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
single

single

Ask AI

expand

Ask AI

ChatGPT

Ask anything or try one of the suggested questions to begin our chat

some-alt