Impara Subqueries in FROM Clauses | Subqueries in Human Resources


              1234567891011121314151617
            
SELECT
    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:

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 2

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Suggested prompts:

Can you explain what a derived table is in more detail?

How does the subquery improve the readability of the SQL?

Can you give another example of using a subquery in the FROM clause?

Scorri per mostrare il menu


              1234567891011121314151617
            
SELECT
    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;

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:

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 2