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

by
1. What is the difference between a clustered and non-clustered index?

Answer:

  • Clustered Index sorts and stores the data rows in the table based on the key values. Only one clustered index per table.

  • Non-Clustered Index creates a separate structure from the data rows.

Example:

-- Clustered Index

CREATE CLUSTERED INDEX idx_emp_id ON Employees(Id);
-- Non-Clustered Index

CREATE NONCLUSTERED INDEX idx_emp_name ON Employees(Name);
2. How many clustered indexes can a table have?

Answer: Only one clustered index per table because the data rows themselves can be sorted in only one way.

3. What are Common Table Expressions (CTE) in SQL?

Answer:

  • CTE provides a way to create a temporary result set that can be referred within a SELECT, INSERT, UPDATE, or DELETE.

Example: WITH CTE_Employee AS (
SELECT Name, Salary FROM Employees WHERE Salary > 50000
)
SELECT * FROM CTE_Employee;

4. How is a CTE different from a subquery?

Answer: CTE improves readability and structure compared to subqueries and can be self-referenced (recursive).

5. What is a recursive CTE? Give an example.

Answer:

  • A recursive CTE calls itself until it reaches a termination condition.

Example: WITH Numbers AS (
SELECT 1 AS Num
UNION ALL
SELECT Num + 1 FROM Numbers WHERE Num < 5
)
SELECT * FROM Numbers;

Output: 1, 2, 3, 4, 5

6. What is a Window Function in SQL?

Answer:

  • Window functions perform calculations across a set of table rows related to the current row without collapsing the rows.

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

7. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

Answer:

  • ROW_NUMBER(): No gaps in numbers.

  • RANK(): Gaps if there are ties.

  • DENSE_RANK(): No gaps even if there are ties.

8. What is the difference between UNION and UNION ALL?

Answer:

  • UNION removes duplicates.

  • UNION ALL allows duplicates.

Example: SELECT Name FROM EmployeesA
UNION
SELECT Name FROM EmployeesB;

9. What are Temporary Tables in SQL?

Answer:

  • Temporary tables are created and used during the session or procedure.

Example: CREATE TABLE #TempEmployee (Id INT, Name VARCHAR(50));

10. What is the difference between a Temp Table and a Table Variable?

Answer:

  • Temp Table is stored in tempdb database.

  • Table Variable is stored in memory and is faster for small data.

Example: DECLARE @TempTable TABLE (Id INT, Name VARCHAR(50));

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)