Subqueries in FROM Clauses
1234567891011121314151617SELECT d.name AS department_name, avg_salaries.avg_salary FROM departments d JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS avg_salaries ON d.department_id = avg_salaries.department_id ORDER BY avg_salaries.avg_salary DESC;
When you use a subquery in the FROM clause, you are creating a derived table that exists only for the duration of the query. In the example above, the subquery calculates the average salary for each department from the employees table. This result set is given an alias, avg_salaries, and is treated just like a regular table in the main query. The main query then joins this derived table with the departments table, allowing you to display each department's name alongside its average salary. This approach is especially useful in HR analytics, where you often need to combine aggregated results with other tables to produce meaningful reports. Using subqueries in the FROM clause helps you break down complex problems into manageable steps, making your SQL more readable and modular.
1. Which of the following is a key benefit of using a subquery in the FROM clause?
2. Fill in the blanks to complete the query that finds the average salary per department and joins it with the departments table:
Takk for tilbakemeldingene dine!
Spør AI
Spør AI
Spør om hva du vil, eller prøv ett av de foreslåtte spørsmålene for å starte chatten vår
Fantastisk!
Completion rate forbedret til 5.56Subqueries in FROM Clauses
Sveip for å vise menyen
1234567891011121314151617SELECT d.name AS department_name, avg_salaries.avg_salary FROM departments d JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS avg_salaries ON d.department_id = avg_salaries.department_id ORDER BY avg_salaries.avg_salary DESC;
When you use a subquery in the FROM clause, you are creating a derived table that exists only for the duration of the query. In the example above, the subquery calculates the average salary for each department from the employees table. This result set is given an alias, avg_salaries, and is treated just like a regular table in the main query. The main query then joins this derived table with the departments table, allowing you to display each department's name alongside its average salary. This approach is especially useful in HR analytics, where you often need to combine aggregated results with other tables to produce meaningful reports. Using subqueries in the FROM clause helps you break down complex problems into manageable steps, making your SQL more readable and modular.
1. Which of the following is a key benefit of using a subquery in the FROM clause?
2. Fill in the blanks to complete the query that finds the average salary per department and joins it with the departments table:
Takk for tilbakemeldingene dine!
