SQL interview questions with answers

Whether you’re preparing for a SQL Server interview or just want to sharpen your T-SQL skills, practical questions and hands-on SQL queries are the best way to learn. This article covers common and advanced SQL questions that are often asked in technical interviews, complete with clear answers and explanations.

Section 1: SQL Server and T-SQL – Quick Overview

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It uses T-SQL (Transact-SQL), Microsoft’s proprietary extension of SQL (Structured Query Language). T-SQL adds procedural programming, local variables, error handling, and transaction control.

Why It Matters for Interviews

SQL is fundamental in data-related roles — data analysts, BI developers, backend developers, and database administrators. Interviewers want to see how well you can work with data, write queries, and solve real business problems using SQL.

Section 2: Basic SQL Queries Interview Questions

1. Retrieve all records from a table

Question:

Get all the records from a table named Employees.

SQL Query:

SELECT * FROM Employees;

Why it’s asked:

To check your understanding of basic SELECT syntax.

2. Filter records with a WHERE clause

Question:

Find all employees in the Sales department.

SQL Query:

SELECT * FROM Employees
WHERE Department = 'Sales';

Tip:

Interviewers will often follow up with case sensitivity or partial match questions (e.g., using LIKE).

3. Use of ORDER BY

Question:

List employees ordered by their salary, highest first.

SQL Query:

SELECT * FROM Employees
ORDER BY Salary DESC;

4. Get unique department names

Question:

How do you list all unique department names?

SQL Query:

SELECT DISTINCT Department FROM Employees;

5. Count rows in a table

Question:

How many employees are there?

SQL Query:

SELECT COUNT(*) FROM Employees;

Section 3: Intermediate SQL Query Questions

6. Aggregation with GROUP BY

Question:

Get the number of employees in each department.

SQL Query:

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

Why it’s asked:

To test your understanding of GROUP BY and aggregations.

7. HAVING vs WHERE

Question:

Find departments with more than 5 employees.

SQL Query:

SELECT Department, COUNT(*) AS Total
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

Note:

You can’t use WHERE with aggregate functions — HAVING is the correct choice here.

8. IN, BETWEEN, and LIKE

Question:

Find employees with a salary between 50000 and 80000.

SQL Query:

SELECT * FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;

Follow-up variations:

Use IN for specific matches.

Use LIKE for pattern matches (e.g., names starting with “A”).

9. Joins – INNER JOIN

Question:

Get employee names and their department names from two tables: Employees and Departments.

SQL Query:

SELECT E.EmployeeName, D.DepartmentName
FROM Employees E
INNER JOIN Departments D
ON E.DepartmentID = D.DepartmentID;

Why it’s important:

Joins are one of the most common and essential SQL skills.

10. LEFT JOIN vs INNER JOIN

Question:

What’s the difference?

Answer:

INNER JOIN returns only matching rows.

LEFT JOIN returns all rows from the left table, and matched rows from the right. If no match, returns NULLs for right side.

Example:

-- Show all employees and their department names (even if no department)
SELECT E.EmployeeName, D.DepartmentName
FROM Employees E
LEFT JOIN Departments D
ON E.DepartmentID = D.DepartmentID;

Section 4: Advanced SQL Query Interview Questions

11. Find the second highest salary

SQL Query:

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Alternative (using CTE and ROW_NUMBER):

WITH RankedSalaries AS (
    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
    FROM (SELECT DISTINCT Salary FROM Employees) AS DistinctSalaries
)
SELECT Salary
FROM RankedSalaries
WHERE Rank = 2;

12. Use of CASE statement

Question:

Show employee names and a column indicating if they earn above or below 60000.

SQL Query:

SELECT EmployeeName,
       CASE 
           WHEN Salary >= 60000 THEN 'Above 60K'
           ELSE 'Below 60K'
       END AS SalaryCategory
FROM Employees;

13. Find duplicate records

Question:

Find employees who appear more than once in the table.

SQL Query:

SELECT EmployeeName, COUNT(*)
FROM Employees
GROUP BY EmployeeName
HAVING COUNT(*) > 1;

14. Delete duplicates (keep one)

SQL Query:

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeName ORDER BY EmployeeID) AS rn
    FROM Employees
)
DELETE FROM CTE WHERE rn > 1;

Why this is asked:

To check knowledge of window functions and how to manage data quality.

15. Use of RANK() / DENSE_RANK()

Question:

Assign ranks to employees based on salary.

SQL Query:

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

Follow-up:

Explain the difference between RANK() and DENSE_RANK().

Section 5: Scenario-Based Questions

16. Top N per group

Question:

Get the highest-paid employee in each department.

SQL Query:

WITH RankedEmployees AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rn
    FROM Employees
)
SELECT * FROM RankedEmployees WHERE rn = 1;

17. Find employees who don’t have managers

Assuming a ManagerID column.

SQL Query:

SELECT * FROM Employees
WHERE ManagerID IS NULL;

18. Employees who manage others

SQL Query:

SELECT DISTINCT E.*
FROM Employees E
WHERE E.EmployeeID IN (
    SELECT DISTINCT ManagerID
    FROM Employees
    WHERE ManagerID IS NOT NULL
);

19. Running totals

SQL Query:

SELECT EmployeeID, Salary,
       SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

20. Monthly sales aggregation

Assuming a Sales table with SaleAmount and SaleDate.

SQL Query:

SELECT YEAR(SaleDate) AS SaleYear, MONTH(SaleDate) AS SaleMonth,
       SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY YEAR(SaleDate), MONTH(SaleDate)
ORDER BY SaleYear, SaleMonth;

Section 6: Transactions, Indexes, and Optimization Questions

21. What is a transaction?

Answer:

A transaction is a sequence of operations performed as a single unit. It must follow the ACID properties: Atomicity, Consistency, Isolation, Durability.

Example:

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
COMMIT;

22. What is an index?

Answer:

An index speeds up data retrieval but may slow down INSERT, UPDATE, or DELETE operations.

Create index example:

CREATE INDEX IX_Employee_Department
ON Employees(DepartmentID);

23. Difference between clustered and non-clustered index

Clustered: Physically sorts the data rows. One per table.

Non-clustered: Stores pointers to data. Multiple allowed.

24. What is a deadlock?

Answer:

A deadlock occurs when two or more transactions block each other by holding locks on resources the others need. SQL Server detects and resolves deadlocks automatically by killing one of the processes.

25. How to find slow queries

Answer:

Use execution plans or check dynamic management views:

SELECT TOP 10
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    SUBSTRING(qt.text, 1, 100) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

Final Thoughts

SQL interviews are about more than syntax. They test your ability to understand business problems, write readable queries, optimize performance, and work with real-world data patterns.

Tips for Acing SQL Interviews:

Think out loud when writing queries.

Use table aliases and formatting to make your queries readable.

Always test your queries on edge cases.

Be ready to explain performance implications.