Complex SQL Query Examples with Subqueries
Q: Can you provide an example of a complex SQL query involving multiple subqueries, and explain the reasoning behind your design choices?
- SQL Developer
- Senior level question
Explore all the latest SQL Developer interview questions and answers
ExploreMost Recent & up-to date
100% Actual interview focused
Create SQL Developer interview for FREE!
Certainly! Here’s an example of a complex SQL query that uses multiple subqueries, along with an explanation of my design choices:
```sql
SELECT e.employee_id, e.name, e.department_id,
(SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id) AS avg_department_salary,
(SELECT COUNT(*)
FROM employees
WHERE department_id = e.department_id AND salary > e.salary) AS higher_salary_count
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees)
ORDER BY e.department_id, e.salary DESC;
```
### Reasoning Behind Design Choices:
1. Use of Subqueries: The query uses subqueries to calculate two values: the average salary of the employee's department and the count of other employees in the same department who earn more than the current employee. This encapsulation of logic promotes modularity and allows for dynamic calculation tailored to each employee.
2. Performance Considerations: Every subquery runs for each employee, so I ensured they were as efficient as possible. For instance, querying the average salary doesn't retrieve more data than necessary—it only considers employees in the same department.
3. Hierarchical Data Presentation: The main query selects employees earning above the overall average salary, which focuses our results on higher-performing employees. This association helps in performance evaluation and departmental comparisons.
4. Ordering: The results are ordered by department and then by salary. This offers a clear hierarchical view of data, which is helpful for analysis and reporting.
Each design choice contributes towards building a clear, efficient query that delivers meaningful insights into employee performance relative to their peers within each department.
```sql
SELECT e.employee_id, e.name, e.department_id,
(SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id) AS avg_department_salary,
(SELECT COUNT(*)
FROM employees
WHERE department_id = e.department_id AND salary > e.salary) AS higher_salary_count
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees)
ORDER BY e.department_id, e.salary DESC;
```
### Reasoning Behind Design Choices:
1. Use of Subqueries: The query uses subqueries to calculate two values: the average salary of the employee's department and the count of other employees in the same department who earn more than the current employee. This encapsulation of logic promotes modularity and allows for dynamic calculation tailored to each employee.
2. Performance Considerations: Every subquery runs for each employee, so I ensured they were as efficient as possible. For instance, querying the average salary doesn't retrieve more data than necessary—it only considers employees in the same department.
3. Hierarchical Data Presentation: The main query selects employees earning above the overall average salary, which focuses our results on higher-performing employees. This association helps in performance evaluation and departmental comparisons.
4. Ordering: The results are ordered by department and then by salary. This offers a clear hierarchical view of data, which is helpful for analysis and reporting.
Each design choice contributes towards building a clear, efficient query that delivers meaningful insights into employee performance relative to their peers within each department.