Mastering Complex SQL Queries: Insights from ETL Testing Interviews

In this blog post, we will delve into some complex SQL interview questions that are particularly relevant for ETL testing roles. These questions require an intermediate understanding of SQL, especially concerning correlated subqueries and aggregate functions. Let's explore these queries step by step.

Understanding Subqueries and Correlated Subqueries

Before we dive into the questions, it's essential to clarify the difference between subqueries and correlated subqueries:

• Subquery: An inner query that executes first and passes its result to the outer query.
• Correlated Subquery: An inner query that depends on the outer query for its values, meaning both queries are tightly coupled.

Question 1: Display Departments with Specific Salary Conditions

Question: Display department names where the sum of employee salaries is greater than 2500 and the department count is less than 10.

To solve this, we need to use two aggregate functions: SUM for the salaries and COUNT for the employees. The SQL query would look like this:

SELECT dpt_number, SUM(salary) AS total_salary, COUNT(*) AS employee_count
FROM emp
GROUP BY dpt_number
HAVING SUM(salary) > 2500 AND COUNT(*) < 10;

This query groups the data by department number, calculates the total salary and employee count, and filters the results using the HAVING clause.

Question 2: Employees with Salaries Above Maximum Defined for Job ID

Question: Write a query to display the list of employees whose salary is greater than the maximum defined for their respective job ID.

To achieve this, we will use a correlated subquery:

SELECT *
FROM emp e1
WHERE salary > (
    SELECT MAX(salary)
    FROM emp e2
    WHERE e1.job_band = e2.job_band
);

This query retrieves all employee details where their salary exceeds the maximum salary defined for their job band.

Question 3: Department-wise Maximum Salary

Question: Write a query to display the maximum salary for each department.

This is a straightforward query using the MAX function:

SELECT dpt_number, MAX(salary) AS max_salary
FROM emp
GROUP BY dpt_number;

Question 4: Department-wise Second Maximum Salary

Question: Write a query to display the second maximum salary for each department.

This requires a correlated subquery to find the maximum salary first and then filter for the second highest:

SELECT dpt_number, MAX(salary) AS second_max_salary
FROM emp e1
WHERE salary < (
    SELECT MAX(salary)
    FROM emp e2
    WHERE e1.dpt_number = e2.dpt_number
)
GROUP BY dpt_number;

Question 5: Custom Order for Employee IDs

Question: Write a query to display employee IDs in a specific order (1, 3, 2).

To achieve this custom order, we can use the DECODE function in the ORDER BY clause:

SELECT employee_number
FROM emp
WHERE employee_number IN (1, 2, 3)
ORDER BY DECODE(employee_number, 1, 1, 3, 2, 2);

Alternatively, a CASE statement can also be used:

SELECT employee_number
FROM emp
WHERE employee_number IN (1, 2, 3)
ORDER BY CASE
    WHEN employee_number = 1 THEN 1
    WHEN employee_number = 3 THEN 2
    ELSE 3
END;

Conclusion

These complex SQL queries are crucial for anyone preparing for ETL testing interviews. Mastering these concepts not only enhances your SQL skills but also prepares you for real-world data challenges. Practice these queries and explore similar scenarios to deepen your understanding. SQL is a foundational skill for data professionals, and proficiency in it can significantly impact your career.

If you found this post helpful, consider sharing it with your peers to help them enhance their SQL skills as well. Happy learning!