Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)

11. What is CTE (Common Table Expression) and why use it?

Answer:

• Temporary result set defined within the execution of a single SQL statement.

• Useful for improving readability and managing complex joins/queries.

Example:

WITH EmployeeCTE AS (
SELECT EmployeeID, Name, ManagerID
FROM Employees
)
SELECT * FROM EmployeeCTE;

---

12. What is a Recursive CTE?

Answer: A CTE that calls itself to deal with hierarchical data like org charts or folder structures.

Example:

WITH EmployeeHierarchy AS (
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

---

13. What are Indexed Views?

Answer: Materialized views where the result is stored on disk like a table for performance boost.

Example:

CREATE VIEW vw_TotalSales
WITH SCHEMABINDING
AS
SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
FROM dbo.Sales
GROUP BY SalesPersonID;

CREATE UNIQUE CLUSTERED INDEX idx_TotalSales ON vw_TotalSales(SalesPersonID);

---

14. Explain the difference between UNION and UNION ALL.

Answer:

• UNION removes duplicates.

• UNION ALL keeps all records including duplicates (faster).

Example: SELECT Name FROM Employees
UNION
SELECT Name FROM Managers;

---

15. What is a Deadlock in SQL?

Answer:

• Two or more transactions are waiting for each other to release locks, causing a block.

Example (Conceptual):

• Transaction 1 locks Table A, wants Table B.

• Transaction 2 locks Table B, wants Table A.
  Result: Deadlock.

---

16. How to detect and resolve deadlocks?

Answer:

• Use SQL Server Deadlock Graphs or Trace Flags.

• Resolve by:

  • Access resources in the same order.

  • Reduce transaction scope.

  • Use TRY…CATCH to retry logic.

---

17. What is the difference between SCOPE_IDENTITY(), @@IDENTITY, and IDENT_CURRENT()?

Answer:

• SCOPE_IDENTITY(): Last identity value in current scope.

• @@IDENTITY: Last identity value globally.

• IDENT_CURRENT(‘table’): Last identity value for a specific table.

Example:

INSERT INTO Employees (Name) VALUES (‘John’);
SELECT SCOPE_IDENTITY();

---

18. Explain LAG and LEAD functions.

Answer: Access data from previous (LAG) or next (LEAD) rows without self-joins.

Example:

SELECT Name, Salary,
LAG(Salary, 1) OVER (ORDER BY Salary) AS PrevSalary,
LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;

---

19. What is a CROSS APPLY vs OUTER APPLY?

Answer:

• CROSS APPLY works like INNER JOIN.

• OUTER APPLY works like LEFT JOIN.

Example:

SELECT e.Name, p.ProjectName
FROM Employees e
OUTER APPLY (
SELECT TOP 1 ProjectName
FROM Projects p
WHERE p.EmployeeID = e.EmployeeID
ORDER BY p.StartDate DESC
) p;

---

20. What is Query Store in SQL Server?

Answer:

• Feature that captures query execution history, execution plans, and performance data.

• Useful for troubleshooting performance regressions.

Page : 1 2 3 4 5

Top 50 Basic Level SQL Interview Questions and Answers with Examples
Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]