Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]

31. What is a Common Table Expression (CTE)?

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

Example:

WITH DepartmentCTE AS (
SELECT DepartmentID, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID
)
SELECT * FROM DepartmentCTE;

---

32. What is a recursive CTE?

Answer: A recursive CTE calls itself repeatedly to return hierarchical data (like employees and managers).

Example:

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

---

33. What is the difference between WHERE and HAVING clause?

Answer:

• WHERE filters rows before grouping.

• HAVING filters groups after aggregation.

Example:

— WHERE
SELECT * FROM Employees WHERE Salary > 50000;

— HAVING
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;

---

34. What is a window function?

Answer:

• A window function performs a calculation across a set of rows related to the current row without collapsing the result set.

Example:

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

---

35. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Answer:

---

36. What is COALESCE() function?

Answer: COALESCE returns the first non-null value from a list.

Example:

SELECT COALESCE(NULL, NULL, 'Hello', 'World');

-- Output: 'Hello'

---

37. What is NULLIF() function?

Answer: NULLIF returns NULL if two expressions are equal; otherwise, returns the first expression.

Example:

---

38. What is an Index? Why is it used?

Answer: An Index speeds up the retrieval of rows from a table by creating a quick lookup structure.

Example: CREATE INDEX idx_employee_name
ON Employees (Name);

---

39. What are the types of indexes?

Answer:

• Clustered Index: Alters the physical order of data.

• Non-Clustered Index: Does not change data order, creates a separate lookup structure.

• Unique Index: Ensures all values are unique.

• Full-text Index: Used for searching text-based columns.

---

40. What is the difference between Clustered and Non-Clustered Index?

Answer:

 

Page : 1 2 3 4 5

Top 50 Basic Level SQL Interview Questions and Answers with Examples

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