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

by
41. What is a Common Table Expression (CTE)?

Answer: A temporary result set you can reference within a SELECT, INSERT, UPDATE, or DELETE.

Example:

WITH EmployeeCTE AS (
SELECT Name, DepartmentID
FROM Employees
WHERE Salary > 50000
)
SELECT * FROM EmployeeCTE;

42. What is Recursive CTE?

Answer: A CTE that refers to itself to solve hierarchical problems (e.g., manager-employee structure).

Example:

WITH ManagerCTE (ID, ManagerID, Name, Level) AS (
SELECT ID, ManagerID, Name, 0
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ID, e.ManagerID, e.Name, Level + 1
FROM Employees e
INNER JOIN ManagerCTE m ON e.ManagerID = m.ID
)
SELECT * FROM ManagerCTE;
43. What is the difference between Stored Procedure and Function?

Answer:

Aspect Stored Procedure Function
Returns value Optional Must return value
Can call Functions, Procedures Only functions
Can modify DB Yes No (except inline Table Functions)
44. What are CROSS APPLY and OUTER APPLY?

Answer: Used to join a table to a table-valued function.

Example:

SELECT e.Name, d.DepartmentName
FROM Employees e
CROSS APPLY GetDepartments(e.ID) d;

45. What is a Filtered Index?

Answer: An index with a WHERE clause to improve performance for specific queries.

Example:

CREATE NONCLUSTERED INDEX idx_Filtered
ON Employees (Name)
WHERE Active = 1;
46. What is Partitioned Table?

Answer: Large tables split into smaller partitions based on key values for better performance.

47. Explain Sequence Object.

Answer: Used to generate a sequence of numeric values, similar to identity but more flexible.

Example:

CREATE SEQUENCE Seq_EmpID
START WITH 1
INCREMENT BY 1;
SELECT NEXT VALUE FOR Seq_EmpID;
48. What is an Optimistic Concurrency Control?

Answer: Assumes multiple transactions can complete without affecting each other and checks conflicts at commit time.

49. What is Rowversion (Timestamp) in SQL Server?

Answer: A mechanism to automatically generate a unique binary number when a row is modified.

Example: ALTER TABLE Employees ADD RowVer ROWVERSION;

50. How to find the second highest salary in a table?

Answer:
Using TOP and ORDER BY:

SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS Temp
ORDER BY Salary ASC;

Or using DENSE_RANK():

WITH SalaryCTE AS (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS RankNo
FROM Employees
)
SELECT Salary
FROM SalaryCTE
WHERE RankNo = 2;

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]

1 thought on “Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)”

  1. Pingback: Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples) - Telugu Data Hub

Leave a Comment