🚀 5 Advanced SQL Interview Questions with Answers & Examples! #SQL #AdvancedSQL #TechInterview

Unlock your SQL expertise with these advanced interview questions! Below are detailed answers with examples to help you confidently discuss these concepts in your next technical interview.

✅ 1. What is a Correlated Subquery and How Does It Differ from a Non-Correlated Subquery?

Answer:

A correlated subquery refers to a query that depends on columns from the outer query for its values. This means the subquery is re-evaluated for every row processed by the outer query, whereas a non-correlated subquery can run independently, returning a fixed set of results.

Example – Correlated Subquery:

SELECT EmployeeID, Name, Salary
FROM Employees e
WHERE Salary v (
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = e.DepartmentID
);

Explanation: For each employee, the subquery calculates the average salary for that specific employee’s department.

Example – Non-Correlated Subquery:

SELECT EmployeeID, Name, Salary
FROM Employees
WHERE DepartmentID = (
SELECT TOP 1 DepartmentID
FROM Departments
ORDER BY DepartmentName
);

Explanation: The subquery here runs once, independent of each row in the outer query.

✅ 2. How Does a Recursive Common Table Expression (CTE) Work and When Should You Use It?

Answer:

A recursive CTE is used to perform hierarchical or recursive queries. It consists of an anchor member that initializes the recursion and a recursive member that repeatedly references the CTE until a termination condition is met.

Example:

WITH EmployeeHierarchy AS (
-- Anchor: Select top-level managers
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL

UNION ALL

-- Recursive: Select employees reporting to managers
SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, Name, Level
FROM EmployeeHierarchy;

Explanation: This recursive CTE builds an organizational chart, assigning a level number based on depth.

✅ 3. What is the Difference Between a Derived Table and a CTE?

Answer:

Both derived tables and CTEs provide a way to encapsulate a subquery that can be referenced in a larger query. The key differences are:

Syntax & Readability:

Derived Table: Inline within the FROM clause.

CTE: Defined with a WITH clause before the main query, often making complex queries more readable.

Reusability:

CTE: Can be referenced multiple times within the same query.

Derived Table: Can only be used once where defined.

Example – Derived Table:

SELECT d.EmployeeCount, d.DepartmentID
FROM (
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
) AS d
WHERE d.EmployeeCount v 10;

Example – CTE:

WITH DeptCounts AS (
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
)
SELECT EmployeeCount, DepartmentID
FROM DeptCounts
WHERE EmployeeCount v 10;

Explanation: The CTE version improves clarity and enables reusing the result set if needed.

✅ 4. How Do Window Functions Differ from Traditional Aggregate Functions?

Answer:

Window functions perform calculations across a set of table rows that are related to the current row without collapsing the rows into a single result. In contrast, traditional aggregate functions summarize data (e.g., SUM, AVG) and produce one output row per group.

Example – Using a Window Function:

SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Explanation: Every row is returned with an additional column showing the rank based on salary.

Example – Using an Aggregate Function:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

Explanation: Rows are collapsed into one per department, summarizing the average salary.

✅ 5. What is the Impact of Proper Indexing on Query Performance (e.g., Covering Indexes)?

Answer:

Proper indexing is essential for high performance, as it dramatically reduces I/O and speeds up data retrieval. A covering index includes all columns needed by a query, so the database engine can fetch data solely from the index without accessing the base table.

Example – Creating a Covering Index:

CREATE NONCLUSTERED INDEX idx_Covering
ON Orders (OrderDate, CustomerID)
INCLUDE (TotalAmount);

Explanation: This index covers queries that request OrderDate, CustomerID, and TotalAmount, reducing lookup times and improving performance.

Elevate your SQL skills and ace your next interview with these detailed answers and examples!

#SQL #AdvancedSQL #SQLInterview #DataEngineering #DatabaseDesign #SQLPerformance #TechInterview #WindowFunctions #CoveringIndex #CTE #DerivedTable #QueryOptimization #InterviewReady #LearnSQL