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:
Danke für Ihr Feedback!
Fragen Sie AI
Fragen Sie AI
Fragen Sie alles oder probieren Sie eine der vorgeschlagenen Fragen, um unser Gespräch zu beginnen
Großartig!
Completion Rate verbessert auf 5.56Subqueries in FROM Clauses
Swipe um das Menü anzuzeigen
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:
Danke für Ihr Feedback!
