Derived tables - tricky interview questions

 Derived tables are subqueries used within the FROM clause of an SQL query, acting as temporary tables that can be referenced later in the query. Here are some tricky interview questions related to derived tables:

1. Basic Understanding:

• Question: What is a derived table, and how does it differ from a regular subquery?
• Answer: A derived table is a subquery that appears in the FROM clause and is treated as a temporary table for the duration of the main query. Unlike regular subqueries in the SELECT or WHERE clauses, derived tables can be referenced by an alias and used like any other table in the query.

2. Usage and Syntax:

• Question: Write a query using a derived table to find the top 3 highest-paid employees in each department.
• Answer:

SELECT department_id, employee_id, salary
FROM (
    SELECT department_id, employee_id, salary,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
    FROM employees
) AS ranked_salaries
WHERE rank <= 3;

This query uses a derived table to rank employees by salary within each department and then filters for the top 3.

3. Multiple Derived Tables:

• Question: Can you use more than one derived table in a query? Provide an example.
• Answer: Yes, you can use multiple derived tables in a query. For example:

SELECT t1.department_id, t1.total_salary, t2.avg_salary
FROM (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
) AS t1
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS t2
ON t1.department_id = t2.department_id;

This query uses two derived tables: one for the total salary and one for the average salary per department.

4. Performance Considerations:

• Question: What are the performance implications of using derived tables, and how can you optimize queries that use them?
• Answer: Derived tables can impact performance if the subquery is complex or involves large datasets, as they are not indexed and must be re-evaluated every time the main query runs. Optimization strategies include using common table expressions (CTEs) if reusability is needed, breaking down complex queries, or ensuring that the derived table is as simple and efficient as possible.

5. Column Aliasing:

• Question: Why is it important to alias columns in a derived table, and what happens if you don't?
• Answer: Aliasing columns in a derived table is important to avoid ambiguity and make the query more readable. If you don't alias columns, the derived table will inherit the column names from the subquery, which can lead to conflicts or confusion, especially if multiple derived tables are used.

6. Nested Derived Tables:

• Question: Can you nest derived tables? Provide an example where this is necessary.
• Answer: Yes, you can nest derived tables. For example:

SELECT department_id, max_salary
FROM (
    SELECT department_id, MAX(salary) AS max_salary
    FROM (
        SELECT department_id, salary
        FROM employees
        WHERE salary > 50000
    ) AS filtered_salaries
    GROUP BY department_id
) AS max_salaries;

This query first filters out employees with salaries above 50,000, then calculates the maximum salary per department using nested derived tables.

7. Comparing Derived Tables and CTEs:

• Question: What are the differences between derived tables and common table expressions (CTEs)? When would you prefer one over the other?
• Answer: Derived tables are subqueries in the FROM clause, while CTEs are defined using the WITH clause before the main query. CTEs are generally preferred when the result needs to be reused multiple times in the same query, as they can be referenced multiple times, improving readability and maintainability. Derived tables are more straightforward but less reusable.

8. Aggregations in Derived Tables:

• Question: How would you use a derived table to calculate the percentage of each employee's salary relative to the total salary of all employees?
• Answer:

SELECT employee_id, salary, 
       (salary / total_salary) * 100 AS salary_percentage
FROM employees, 
(
    SELECT SUM(salary) AS total_salary
    FROM employees
) AS total

This query uses a derived table to calculate the total salary of all employees and then computes each employee's salary as a percentage of this total.

9. Derived Tables in UPDATE Statements:

• Question: Can you use derived tables in UPDATE statements? Provide an example.
• Answer: Yes, you can use derived tables in UPDATE statements. For example:

UPDATE employees
SET salary = salary * 1.10
WHERE department_id IN (
    SELECT department_id
    FROM (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
        HAVING AVG(salary) < 50000
    ) AS low_salary_departments
);

This query increases the salary of employees in departments where the average salary is below 50,000.

10. Dynamic Derived Tables:

• Question: How would you create a dynamic derived table that adapts based on user input or parameters?
• Answer: Dynamic derived tables can be created using parameterized queries or stored procedures. For example, in a stored procedure:

CREATE PROCEDURE GetTopSalaries(@TopN INT)
AS
BEGIN
    SELECT department_id, employee_id, salary
    FROM (
        SELECT department_id, employee_id, salary,
               ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as row_num
        FROM employees
    ) AS ranked_salaries
    WHERE row_num <= @TopN;
END;

This procedure returns the top N salaries per department based on a parameter.

These questions explore the various facets of derived tables, from their basic usage to more complex scenarios, emphasizing performance considerations, practical use cases, and comparisons with other SQL constructs.