Top 50 SQL Interview Questions and Answers

Structured Query Language (SQL) is the foundation of data manipulation and management in relational databases. Whether you are building a reporting dashboard, querying millions of rows for business insights, or powering the backend of a web application, SQL is an indispensable tool. As a result, it is one of the most frequently tested skills in technical interviews for roles such as data analyst, backend developer, business intelligence engineer, and database administrator.

Contents

Target Audience Top 50 SQL Interview Questions and Answers Basic-Level SQL Interview Questions (1–15)Intermediate-Level SQL Interview Questions (16–30)Scenario-Based SQL Interview Questions (41–50)Core SQL Concepts to Revise Before an Interview Conclusion

Interviewers use SQL questions not only to assess your command over syntax but also to evaluate your problem-solving approach, understanding of data relationships, and ability to optimize queries under real-world constraints.

This blog presents a carefully organized set of 50 SQL interview questions and answers, ranging from foundational concepts to complex, scenario-based problems. Whether you are a beginner or a professional revising before an interview, this guide will help you prepare with clarity and confidence.

Target Audience

This blog is designed for anyone looking to strengthen their SQL skills in preparation for technical interviews. Whether you are applying for a role in data, development, or analytics, a solid grasp of SQL can significantly improve your chances of success.

You will find this guide particularly useful if you are:

A student or fresher preparing for campus placements or entry-level positions
A data analyst or business intelligence professional working with tools like Power BI, Tableau, or Excel
A backend or full-stack developer who frequently interacts with databases in web or enterprise applications
A QA or automation engineer validating data flows and backend processes
A professional switching careers into tech, analytics, or data-related roles

Whether your goal is to master query writing, improve your logical reasoning with real-world scenarios, or simply review before an interview, this guide offers structured, progressive content to help you succeed.

Basic-Level SQL Interview Questions (1–15)

1. What is the difference between `WHERE` and `HAVING` in SQL?

Answer: WHERE Filters rows before aggregation, while HAVING filters groups after aggregation.

2. What is the difference between `DELETE`, `TRUNCATE`, and `DROP`?

Answer:

DELETE: Removes specific rows; can be rolled back; retains table structure.
TRUNCATE: Removes all rows; cannot be rolled back in most systems; faster than DELETE.
DROP: Deletes the table structure entirely from the database.

3. What is a Primary Key?

Answer: A primary key uniquely identifies each record in a table. It must contain unique, non-null values and can consist of one or more columns.

4. What is a Foreign Key?

Answer: A foreign key is a column or set of columns in one table that refers to the primary key in another table. It is used to enforce referential integrity between tables.

5. What is the difference between `WHERE` and `HAVING` clauses?

Answer:

WHERE filters rows before grouping.
HAVING filters groups after aggregation.
Example: Use WHERE to filter individual rows, HAVING to filter aggregated results like totals or counts.

6. What are the different types of SQL commands?

Answer: SQL commands are categorized as:

DDL (Data Definition Language): CREATE, ALTER, DROP
DML (Data Manipulation Language): INSERT, UPDATE, DELETE
DQL (Data Query Language): SELECT
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

7. What is the difference between `CHAR` and `VARCHAR`?

Answer:

CHAR(n): Fixed-length string of n characters; padded with spaces if input is shorter.
VARCHAR(n): Variable-length string up to n characters; more storage-efficient.

8. How do you retrieve all records from a table?

Answer: Use the SELECT * FROM table_name; statement. The asterisk (*) retrieves all columns from the specified table.

9. What is a NULL value in SQL?

Answer: NULL represents a missing, unknown, or undefined value in a table. It is not equivalent to zero or an empty string.

10. What is a UNIQUE constraint?

Answer: A UNIQUE constraint ensures that all values in a column (or a combination of columns) are distinct across rows. Unlike a primary key, a table can have multiple unique constraints.

11. What is the use of the `DISTINCT` keyword in SQL?

Answer: DISTINCT eliminates duplicate records from the result set.
Example: SELECT DISTINCT department FROM employees;

12. What are aliases in SQL?

Answer: An alias provides a temporary name for a column or table, improving readability.
Example:

sqlCopyEditSELECT first_name AS name FROM employees;

13. What is a constraint in SQL?

Answer: A constraint enforces rules on data in a table. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

14. What is the default sorting order in SQL?

Answer: By default, the ORDER BY clause sorts results in ascending order. To sort in descending order, use the DESC keyword.

15. How do you filter results using conditions in SQL?

Answer: Use the WHERE clause with comparison operators (e.g., =, !=, <, >, IN, BETWEEN, LIKE) to filter rows.
Example:

SELECT * FROM employees WHERE salary > 50000;

Intermediate-Level SQL Interview Questions (16–30)

16. What is a JOIN in SQL?

Answer: A JOIN is used to retrieve data from multiple tables based on a related column. It allows combining rows where specified conditions are met.

17. What are the types of JOINs in SQL?

Answer:

INNER JOIN: Returns matching rows from both tables
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left
FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables, with NULLs where no match exists
CROSS JOIN: Returns a Cartesian product of both tables

18. What is a subquery?

Answer: A subquery is a query nested inside another SQL query. It can be used in SELECT, FROM, or WHERE clauses.
Example:

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

19. What is the difference between `GROUP BY` and `ORDER BY`?

Answer:

GROUP BY is used to group rows that share the same values for aggregation (e.g., SUM, COUNT).
ORDER BY is used to sort the result set in ascending or descending order.

20. What is a correlated subquery?

Answer: A correlated subquery references columns from the outer query. It runs once for each row of the outer query.
Example:

SELECT e1.name FROM employees e1  
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department = e2.department);

21. What are aggregate functions in SQL?

Answer: Aggregate functions operate on a set of values and return a single value:

COUNT()
SUM()
AVG()
MIN()
MAX()

22. How can you find duplicate records in a table?

Answer:

SELECT name, COUNT(*)  
FROM employees  
GROUP BY name  
HAVING COUNT(*) > 1;

This identifies records where a specific field appears more than once.

23. What is a `BETWEEN` operator used for?

Answer: BETWEEN filters the result set within a specified range (inclusive).
Example:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

24. How can you retrieve only even-numbered records in SQL (assuming IDs)?

Answer:

SELECT * FROM table_name WHERE MOD(id, 2) = 0;

This returns records with even id values.

25. How do you update data in a table using SQL?

Answer:

UPDATE employees SET salary = salary + 5000 WHERE department = 'Sales';

This increases salaries for employees in the Sales department.

26. What is the difference between `IN` and `EXISTS`?

Answer:

IN checks if a value is within a list of values returned by a subquery.
EXISTS checks if a subquery returns any rows.
EXISTS is often more efficient when checking for the existence of rows.

27. How can you find NULL values in a column?

Answer:

SELECT * FROM employees WHERE manager_id IS NULL;

IS NULL is used because = NULL will not work correctly in SQL.

28. What is a CASE statement in SQL?

Answer: CASE provides conditional logic within SQL queries.
Example:

SELECT name,  
       CASE  
           WHEN salary > 100000 THEN 'High'  
           ELSE 'Standard'  
       END AS salary_band  
FROM employees;

29. What is a `UNION` in SQL?

Answer: UNION combines the result sets of two SELECT statements and removes duplicates.
Use UNION ALL to include duplicates.

30. What are views in SQL and why are they used?

Answer: A view is a virtual table based on the result of a SQL query. It is used to:

Simplify complex queries
Improve security by restricting access to certain columns
Provide a consistent structure to users

31. What are window functions in SQL?

Answer: Window functions perform calculations across a set of table rows related to the current row without collapsing them into a single output. Examples include ROW_NUMBER(), RANK(), LEAD(), LAG(), and SUM() OVER(...). They are used with the OVER() clause to define the partition and order.

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

Answer:

RANK(): Assigns the same rank to ties but skips subsequent ranks
DENSE_RANK(): Assigns the same rank to ties but does not skip ranks
ROW_NUMBER(): Assigns a unique number to each row, even for ties

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

Answer:
A CTE is a temporary result set defined using the WITH clause. It improves readability and simplifies complex joins and recursive queries.
Example:

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_avg WHERE avg_salary > 70000;

34. How do indexes work in SQL and when should you use them?

Answer: Indexes improve the speed of data retrieval by allowing the database engine to locate data without scanning entire tables. They should be used on columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses. However, over-indexing can slow down INSERT and UPDATE operations.

35. What is normalization? Explain its types.

Answer: Normalization is the process of organizing data to reduce redundancy and improve integrity. The common normal forms are:

1NF: Eliminate repeating groups; ensure atomicity
2NF: Remove partial dependencies
3NF: Remove transitive dependencies
Higher normal forms (BCNF, 4NF, 5NF) further refine data structure.

36. What is denormalization and when is it used?

Answer:
Denormalization is the process of introducing redundancy into a database for performance optimization. It is used in reporting systems or when fewer joins are desired, especially in read-heavy applications.

37. What is a clustered vs. non-clustered index?

Answer:

Clustered index: Determines the physical order of data in a table; only one per table.
Non-clustered index: Stores a logical pointer to the actual data; multiple allowed per table.

38. What is the difference between `DELETE` and `TRUNCATE` in terms of transaction logs?

Answer:

DELETE logs each deleted row individually, allowing rollback.
TRUNCATE logs only the deallocation of data pages and is not always recoverable (depends on RDBMS settings). TRUNCATE is faster but less granular.

39. What are stored procedures and why are they used?

Answer: Stored procedures are precompiled SQL code blocks that can be executed multiple times. They:

Improve performance by reducing query parsing time
Enhance security by abstracting direct table access
Promote reusability and modular code

40. What are triggers in SQL and when should you use them?

Answer: Triggers are special stored procedures that automatically execute in response to specific events (INSERT, UPDATE, DELETE) on a table. They are used for:

Enforcing business rules
Auditing changes
Preventing invalid data entry
However, excessive use can complicate debugging and affect performance.

Scenario-Based SQL Interview Questions (41–50)

41. Write a query to find the second highest salary in an employee table.

Answer:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

This query uses a subquery to exclude the highest salary and then finds the maximum among the remaining values.

42. How would you find customers who placed more than one order?

Answer:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

This groups orders by customer and filters only those with multiple entries.

43. Retrieve employees whose salary is above the average salary of their department.

Answer:

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

This correlated subquery compares each employee’s salary with their department’s average.

44. Write a query to pivot product sales data by region.

Answer:

SELECT product_id,
       SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS North,
       SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS South,
       SUM(CASE WHEN region = 'East' THEN sales ELSE 0 END) AS East,
       SUM(CASE WHEN region = 'West' THEN sales ELSE 0 END) AS West
FROM sales
GROUP BY product_id;

This uses conditional aggregation to pivot regions into columns.

45. How would you find the top 3 salaries per department?

Answer:

SELECT *
FROM (
  SELECT name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk <= 3;

This window function assigns ranks within each department.

46. Write a query to find products that have never been sold.

Answer:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

LEFT JOIN combined with IS NULL filters unsold products.

47. How would you handle division by zero in a query?

Answer:

SELECT order_id,
       CASE WHEN quantity = 0 THEN NULL ELSE total_amount / quantity END AS unit_price
FROM orders;

Using a CASE statement prevents runtime errors due to division by zero.

48. Retrieve employee names and the date of their most recent login.

Answer:

SELECT employee_id, MAX(login_date) AS last_login
FROM logins
GROUP BY employee_id;

MAX() aggregates the latest login per employee.

49. Write a query to count the number of employees in each department, including those with zero employees.

Answer:

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

A LEFT JOIN ensures departments with no employees are still listed.

50. Find consecutive login days for users.

Answer:

SELECT user_id, login_date,
       DATEDIFF(DAY, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date), login_date) AS group_id
FROM logins;

This technique groups consecutive days by leveraging the difference between ROW_NUMBER() and actual dates.

Core SQL Concepts to Revise Before an Interview

Success in a SQL interview depends on more than just writing correct queries—it requires a clear understanding of relational database theory, query optimization, and the ability to solve real-world data problems. Below is a summary of core SQL concepts you should revise to ensure a strong performance:

1. SQL Fundamentals

Basic command categories: DDL, DML, DQL, DCL, TCL
Syntax for SELECT, INSERT, UPDATE, DELETE
Data types (INT, VARCHAR, DATE, BOOLEAN, etc.)
Operators: =, IN, LIKE, BETWEEN, IS NULL, logical operators

2. Table Design and Constraints

Primary and foreign keys
Unique, not null, and check constraints
Auto-incrementing fields and default values
Referential integrity and cascading actions

3. Joins and Data Relationships

Types of JOINs (INNER, LEFT, RIGHT, FULL, CROSS)
Join conditions and aliases
Understanding when to use subqueries vs. joins
Self joins and many-to-many relationships

4. Aggregation and Grouping

Aggregate functions: SUM(), COUNT(), AVG(), MAX(), MIN()
GROUP BY and HAVING clauses
Combining grouping with filtering and sorting
Using aggregate functions with joins

5. Subqueries and CTEs

Scalar, correlated, and nested subqueries
Common Table Expressions (WITH clause)
Recursive queries
Use cases for breaking down complex logic

6. Window Functions and Ranking

ROW_NUMBER(), RANK(), DENSE_RANK()
Partitioning and ordering using OVER()
Cumulative totals and running averages
First/last value, LEAD() and LAG()

7. Indexing and Performance

Clustered vs. non-clustered indexes
How indexes affect query speed
Understanding query plans and EXPLAIN
Avoiding common performance bottlenecks (e.g., functions on indexed columns)

8. Data Integrity and Transactions

ACID properties: Atomicity, Consistency, Isolation, Durability
Transaction management using BEGIN, COMMIT, ROLLBACK
Savepoints and nested transactions
Locking and isolation levels

9. Views, Stored Procedures, and Triggers

Benefits and limitations of views
Basics of stored procedures and input/output parameters
Trigger use cases and best practices
Security considerations with stored logic

10. Real-World Query Logic

Handling NULLs and conditional logic (CASE, COALESCE)
Pivoting and unpivoting data
Identifying duplicates, gaps, and patterns in data
Writing readable and maintainable SQL

Mastering these concepts will allow you to approach SQL interviews with confidence. Aim not just to solve queries, but also to explain your approach, justify your choices, and write clean, efficient code that reflects real-world thinking.

Conclusion

SQL remains one of the most essential skills across data-centric and backend development roles. Whether you are analysing customer trends, designing relational databases, or building scalable data pipelines, your ability to write efficient and accurate SQL queries plays a critical role in day-to-day problem-solving.

In this blog, we explored 50 carefully selected SQL interview questions and answers, organized across basic, intermediate, advanced, and scenario-based categories. From understanding core commands and table relationships to solving real-world use cases with window functions and joins, this guide is built to help you develop confidence and technical fluency.

Target Audience