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:
Дякуємо за ваш відгук!
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
Чудово!
Completion показник покращився до 5.56Subqueries 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:
Дякуємо за ваш відгук!
