SQL Interview Questions and Answers for 2026 (Freshers and Experienced)

If you are preparing for a data analyst, backend developer, or database role, mastering the most common SQL interview questions is one of the highest-return things you can do before your next round. SQL shows up in almost every technical screen in India, from product companies to service firms and startups, because nearly every application eventually reads and writes to a relational database. This guide collects 45+ of the questions that interviewers actually ask, grouped by topic, with concise correct answers, comparison tables, and real query examples you can practise on.

Whether you are a fresher walking into your first technical round or an experienced engineer brushing up before a senior interview, work through these questions in order. The early sections build fundamentals, and the later sections move into joins, subqueries, indexing, transactions, and query-writing problems that decide most interviews. Let us begin.

SQL Basics

These are the warm-up questions. Interviewers use them to check that you understand what SQL is and how the core statements are categorised. Answer them crisply and you set a confident tone for the rest of the round.

1. What is SQL?

SQL (Structured Query Language) is the standard language for managing and manipulating data in a relational database management system (RDBMS). It lets you create database structures, insert and update rows, query data, and control access. Popular RDBMS platforms that use SQL include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

2. What is the difference between DBMS and RDBMS?

A DBMS (Database Management System) stores data as files with little enforced structure between them. An RDBMS (Relational DBMS) stores data in tables made of rows and columns and enforces relationships between those tables using keys and constraints. An RDBMS also supports properties like ACID compliance and normalization. MySQL, PostgreSQL, and Oracle are RDBMS platforms.

3. What are the main categories of SQL commands?

SQL commands are grouped into five categories:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE. Defines and changes structure.
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE. Works with the data itself. (Some texts classify SELECT under DQL.)
• DCL (Data Control Language): GRANT, REVOKE. Manages permissions.
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT. Manages transactions.
• DQL (Data Query Language): SELECT. Retrieves data.

4. What is the difference between CHAR and VARCHAR?

CHAR(n) is a fixed-length string type that always reserves n characters, padding shorter values with spaces. VARCHAR(n) is variable-length and only stores the characters you actually insert, up to n. Use CHAR for values of consistent length (like a country code) and VARCHAR for values that vary (like names or emails), which saves storage.

5. What is a NULL value? Is it the same as zero or a blank string?

NULL represents a missing or unknown value. It is not the same as zero (a number) or an empty string (a value of length zero). Because NULL means "unknown," any arithmetic or comparison with NULL returns NULL, which is why you must use IS NULL or IS NOT NULL rather than = NULL.

SELECT name FROM employees WHERE manager_id IS NULL;

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

This is one of the most frequently asked questions in fresher rounds. Know the table cold.

Feature	DELETE	TRUNCATE	DROP
Category	DML	DDL	DDL
Removes	Selected or all rows	All rows	Entire table (structure + data)
WHERE clause	Yes	No	No
Rollback	Yes (within a transaction)	Usually no (auto-commit)	No
Speed	Slower (row by row)	Faster (deallocates pages)	Fast
Resets identity	No	Yes	N/A (table gone)
Triggers fire	Yes	No	No

DELETE FROM orders WHERE status = 'cancelled';  -- removes specific rows
TRUNCATE TABLE staging_orders;                    -- empties table fast
DROP TABLE staging_orders;                         -- removes table entirely

7. What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has aggregated them, so it can use aggregate functions like SUM() or COUNT(), which WHERE cannot.

Aspect	WHERE	HAVING
Filters	Rows	Groups
Runs	Before GROUP BY	After GROUP BY
Aggregate functions	Not allowed	Allowed

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'          -- row-level filter first
GROUP BY department
HAVING COUNT(*) > 10;            -- group-level filter after

Keys and Constraints

Keys and constraints define how your data stays consistent. Interviewers probe these to see whether you understand data integrity, not just query syntax.

8. What is a primary key?

A primary key uniquely identifies each row in a table. It cannot contain NULL values and must be unique across the table. A table can have only one primary key, though that key can span multiple columns (a composite key).

9. What is a foreign key?

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It enforces referential integrity, meaning you cannot insert a value in the child table that does not exist in the parent table.

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

10. What is the difference between a primary key and a unique key?

Both enforce uniqueness. The differences:

Feature	Primary Key	Unique Key
NULLs allowed	No	Yes (usually one NULL)
Number per table	One	Many
Purpose	Main row identifier	Enforce uniqueness on other columns
Default index	Clustered (in many RDBMS)	Non-clustered

11. What are constraints in SQL? Name the common ones.

Constraints are rules enforced on table columns to maintain data integrity. The common ones are:

• NOT NULL: column cannot store NULL.
• UNIQUE: all values in the column must be distinct.
• PRIMARY KEY: NOT NULL + UNIQUE combined.
• FOREIGN KEY: enforces a link to another table.
• CHECK: values must satisfy a condition, e.g. CHECK (age >= 18).
• DEFAULT: supplies a value when none is provided.

12. What is a composite key?

A composite key is a primary key made up of two or more columns, used when no single column uniquely identifies a row. For example, in a course_enrollments table, the combination of student_id and course_id may together be unique.

13. What is referential integrity?

Referential integrity is the guarantee that a foreign key value always points to an existing, valid row in the referenced parent table. It prevents orphaned records, for example an order that references a customer who does not exist.

Joins

Joins are the single most tested SQL topic in interviews. If you can explain each join type clearly and write one from memory, you will clear most rounds. Consider two tables for the examples below: employees(emp_id, name, dept_id) and departments(dept_id, dept_name).

14. What is a JOIN? What are the types of joins?

A JOIN combines rows from two or more tables based on a related column. The main types:

Join Type	Returns
INNER JOIN	Only rows with matches in both tables
LEFT (OUTER) JOIN	All rows from left table + matches from right (NULLs if none)
RIGHT (OUTER) JOIN	All rows from right table + matches from left (NULLs if none)
FULL (OUTER) JOIN	All rows from both tables, matched where possible
CROSS JOIN	Cartesian product (every row paired with every row)
SELF JOIN	A table joined to itself

15. Write an INNER JOIN query.

An inner join returns only employees who have a matching department.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

16. What is a LEFT JOIN, and when would you use it?

A LEFT JOIN returns every row from the left table and the matching rows from the right table, filling NULL where no match exists. Use it when you want to keep all records from the primary table, for example to find employees who are not assigned to any department.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;   -- employees with no department

17. What is a self join?

A self join joins a table to itself, using table aliases to distinguish the two references. It is commonly used for hierarchical data, such as finding each employee's manager when both are stored in the same table.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

18. What is a cross join?

A cross join produces the Cartesian product of two tables, pairing every row of the first with every row of the second. If one table has 5 rows and the other has 4, the result has 20 rows. It is used deliberately for generating combinations, but accidental cross joins (a missing join condition) are a common bug.

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

UNION combines the result sets of two queries and removes duplicate rows. UNION ALL combines them and keeps all rows, including duplicates. Because UNION ALL skips the de-duplication step, it is faster. Both require the same number of columns with compatible data types.

SELECT city FROM customers
UNION            -- distinct cities only
SELECT city FROM suppliers;

Aggregations and GROUP BY

Aggregation questions test whether you can summarise data, which is the core of most analytics and reporting work. This is where India data analyst rounds spend a lot of time.

20. What are aggregate functions? Name a few.

Aggregate functions perform a calculation over a set of rows and return a single value. The common ones are COUNT(), SUM(), AVG(), MIN(), and MAX().

21. What does GROUP BY do?

GROUP BY groups rows that share the same value in one or more columns, so aggregate functions can be applied to each group. For example, total salary per department.

SELECT dept_id, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

22. What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

• COUNT(*) counts all rows, including those with NULLs.
• COUNT(column) counts rows where that column is not NULL.
• COUNT(DISTINCT column) counts the number of unique non-NULL values.

SELECT COUNT(*)                AS total_rows,
       COUNT(manager_id)       AS with_manager,
       COUNT(DISTINCT dept_id) AS distinct_depts
FROM employees;

23. What is the logical order of execution of a SELECT query?

Even though you write SELECT first, SQL evaluates clauses in this logical order: FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT (including window functions and aliases), then ORDER BY, and finally LIMIT. This is why you cannot reference a column alias defined in SELECT inside a WHERE clause.

24. How do you find the second highest salary?

A classic interview problem. One clean approach uses a subquery.

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

A more general approach uses DENSE_RANK():

SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

25. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

All three are window functions that assign a number based on an ordering.

• ROW_NUMBER() gives every row a unique sequential number, even on ties.
• RANK() gives tied rows the same rank but leaves gaps afterward (1, 1, 3).
• DENSE_RANK() gives tied rows the same rank with no gaps (1, 1, 2).

Subqueries

Subqueries let you nest one query inside another. Interviewers use them to test whether you can break a problem into steps and reason about correlation.

26. What is a subquery?

A subquery is a query nested inside another SQL statement. It can appear in the SELECT, FROM, WHERE, or HAVING clause and is executed to provide a value or a set of values to the outer query.

27. What is the difference between a correlated and a non-correlated subquery?

A non-correlated subquery runs independently and once, then passes its result to the outer query. A correlated subquery references a column from the outer query, so it runs once per row of the outer query, which makes it slower but more expressive.

-- Correlated: employees earning above their own department's average
SELECT e.name
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees
  WHERE dept_id = e.dept_id   -- references outer row
);

28. What is the difference between IN, EXISTS, and ANY?

• IN checks whether a value matches any value in a list or subquery result.
• EXISTS checks whether a subquery returns any rows at all, returning TRUE or FALSE. It often performs better with correlated subqueries because it stops at the first match.
• ANY (and ALL) compares a value against a set using an operator, e.g. salary > ANY (...).

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

A CTE is a temporary, named result set defined with the WITH keyword that exists only for the duration of a single query. It improves readability and can be referenced multiple times, and a recursive CTE can traverse hierarchical data.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

Normalization

Normalization questions test database design thinking. Experienced candidates especially should be able to explain the tradeoff between normalization and query performance.

30. What is normalization?

Normalization is the process of organising data to reduce redundancy and improve data integrity, by dividing large tables into smaller related tables and defining relationships between them. Each normal form addresses a specific type of anomaly.

31. Explain 1NF, 2NF, and 3NF.

• First Normal Form (1NF): every column holds atomic (indivisible) values and each row is unique. No repeating groups or arrays in a single column.
• Second Normal Form (2NF): the table is in 1NF and every non-key column depends on the whole primary key, not just part of it (removes partial dependency).
• Third Normal Form (3NF): the table is in 2NF and no non-key column depends on another non-key column (removes transitive dependency).

32. What is BCNF?

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF. A table is in BCNF if, for every functional dependency A to B, A is a super key. It handles certain edge cases that 3NF does not, particularly when a table has multiple overlapping candidate keys.

33. What is denormalization and why would you use it?

Denormalization is the deliberate introduction of redundancy by combining tables or adding duplicate columns, to reduce the number of joins and speed up read-heavy queries. It is a common tradeoff in reporting and analytics systems, at the cost of more complex writes and potential inconsistency.

Indexes

Indexing is where interviews for experienced roles get serious, because it directly affects performance. Freshers should at least know what an index is and its cost.

34. What is an index?

An index is a database structure that speeds up data retrieval on a table, similar to the index of a book. It lets the database find rows without scanning the entire table. The tradeoff is that indexes take extra storage and slow down writes (INSERT, UPDATE, DELETE), because the index must also be updated.

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

Feature	Clustered Index	Non-Clustered Index
Data storage	Sorts and stores the actual table rows	Separate structure with pointers to rows
Number per table	One	Many
Speed	Faster for range scans	Slightly slower (extra lookup)
Analogy	Dictionary (data itself is ordered)	Book index (points to page)

In many RDBMS platforms, the primary key automatically creates a clustered index.

36. When should you avoid adding an index?

Avoid indexes on small tables (a full scan is cheap), on columns with very low cardinality (like a boolean flag), and on columns that are updated very frequently, because each write must maintain the index. Too many indexes hurt write performance.

37. What is a composite index and does column order matter?

A composite index spans multiple columns. Column order matters because the index is usable left-to-right (the "leftmost prefix" rule). An index on (last_name, first_name) helps queries filtering on last_name alone or on both, but not on first_name alone.

Views, Stored Procedures, and Triggers

These questions check whether you know the database features that support application logic, not just raw queries.

38. What is a view?

A view is a virtual table defined by a stored SQL query. It does not store data itself (unless it is a materialized view). Views simplify complex queries, restrict access to specific columns, and present a consistent interface even if underlying tables change.

CREATE VIEW active_employees AS
SELECT emp_id, name, dept_id
FROM employees
WHERE status = 'active';

39. What is the difference between a view and a materialized view?

A standard view runs its underlying query every time it is accessed, so it always shows current data but adds compute cost. A materialized view stores the query result physically and must be refreshed, so reads are fast but data can be stale between refreshes. Materialized views suit heavy analytical queries that do not need real-time data.

40. What is a stored procedure?

A stored procedure is a precompiled set of SQL statements stored in the database that can be executed by name. It reduces network traffic, encourages code reuse, improves security through controlled access, and can accept input and output parameters.

41. What is a trigger?

A trigger is a block of SQL that automatically executes in response to an event on a table, such as INSERT, UPDATE, or DELETE. Triggers are used to enforce business rules, maintain audit logs, or keep derived data in sync. They can fire BEFORE or AFTER the triggering event.

42. What is the difference between a stored procedure and a function?

A function must return a value and can be used inside a SQL expression (for example in a SELECT list), and it typically cannot modify database state. A stored procedure may return zero or more values, can perform INSERT, UPDATE, and DELETE, and is called with an EXECUTE statement rather than embedded in a query.

Transactions and ACID

Transaction questions are common for backend engineers and anyone working with payment or order systems, where correctness under concurrency matters.

43. What is a transaction?

A transaction is a single logical unit of work made up of one or more SQL statements that must either all succeed or all fail together. Classic example: transferring money debits one account and credits another, and both must happen or neither.

44. What are the ACID properties?

ACID is the set of guarantees that keep transactions reliable:

• Atomicity: all operations in a transaction complete, or none do.
• Consistency: a transaction moves the database from one valid state to another, respecting all constraints.
• Isolation: concurrent transactions do not interfere with each other, as if they ran one at a time.
• Durability: once committed, changes survive crashes and power loss.

45. What are COMMIT, ROLLBACK, and SAVEPOINT?

COMMIT permanently saves all changes made in the current transaction. ROLLBACK undoes changes since the last commit or savepoint. SAVEPOINT sets a marker within a transaction so you can roll back to that point without discarding the entire transaction.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- ROLLBACK TO after_debit;   -- would undo only the credit
COMMIT;

46. What are transaction isolation levels?

Isolation levels control how much one transaction sees of another's uncommitted work, trading consistency against concurrency:

• Read Uncommitted: can read uncommitted changes (dirty reads).
• Read Committed: only reads committed data (avoids dirty reads).
• Repeatable Read: same rows return the same values within a transaction (avoids non-repeatable reads).
• Serializable: the strictest, transactions behave as if run sequentially (avoids phantom reads).

47. What is a deadlock?

A deadlock occurs when two or more transactions each hold a lock the other needs, so neither can proceed. The database detects this and aborts one transaction (the victim) so the others can continue. You reduce deadlocks by accessing tables in a consistent order and keeping transactions short.

Common Query-Writing Questions

Interviewers almost always finish with a live query challenge. Practise writing these from scratch. Assume an employees table with columns emp_id, name, salary, dept_id, manager_id, and hire_date, plus a departments table with dept_id and dept_name.

48. Find and delete duplicate rows.

To find duplicates based on email:

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

To delete duplicates while keeping the row with the smallest id:

DELETE u1 FROM users u1
JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

49. Find the Nth highest salary.

Using a window function generalises to any N:

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 3;   -- 3rd highest

50. Find employees who earn more than their manager.

A self join solves this cleanly:

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

51. Find the department with the highest average salary.

SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC
LIMIT 1;

52. Fetch the top 3 highest-paid employees per department.

SELECT name, dept_id, salary
FROM (
  SELECT name, dept_id, salary,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

53. Count employees hired each year.

SELECT EXTRACT(YEAR FROM hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;

54. Use COALESCE to handle NULLs.

COALESCE returns the first non-NULL argument, useful for supplying defaults.

SELECT name, COALESCE(commission, 0) AS commission
FROM employees;

How to Prepare for an SQL Interview

Reading answers is not the same as being ready. Here is a practical plan that works well for candidates in India preparing for data analyst and backend fresher rounds.

Practise by writing, not reading. Set up a free local database (MySQL or PostgreSQL) or use an online SQL playground, load a sample schema like the employees and departments tables used above, and write every query yourself. Muscle memory matters when you are at a whiteboard or sharing your screen.

Master joins and aggregations first. These two topics account for the majority of interview questions. If you can write any join from memory and explain GROUP BY with HAVING confidently, you are ahead of most candidates.

Understand the "why," not just the syntax. Interviewers often follow up with "why is TRUNCATE faster than DELETE?" or "when would an index hurt?" Being able to explain the reasoning separates a memoriser from an engineer.

Solve query problems out loud. In a real interview you must narrate your thinking. Practise talking through your approach before writing the query. A great way to build this skill is to run realistic mock rounds where you get feedback on both your answers and your communication. Goodspace's AI Mock Interview lets you rehearse SQL and technical questions in a realistic setting and get instant feedback, so you walk into the real round calm and prepared.

Time-box your revision. In the last week, review the comparison tables (DELETE vs TRUNCATE, WHERE vs HAVING, clustered vs non-clustered), then spend the rest of your time solving fresh query problems. Simulate the pressure of a live round with a timed AI-powered mock interview so nothing feels new on the day.

Frequently Asked Questions

Are SQL interview questions the same for freshers and experienced candidates?

The topics overlap, but the depth differs. Freshers are usually tested on basics, joins, keys, and simple query writing. Experienced candidates get deeper questions on indexing strategy, query optimization, isolation levels, and designing schemas, along with more complex query problems.

Which SQL topics are most important for a data analyst role?

Joins, GROUP BY with aggregate functions, subqueries, window functions, and filtering with WHERE and HAVING are the core. Data analyst rounds in India lean heavily on writing queries to answer business questions, so practise translating a question in words into a correct query.

Do I need to know a specific database like MySQL or PostgreSQL?

Standard SQL concepts transfer across databases, and most interviews accept any correct standard SQL. It helps to know one platform well, since some functions differ (for example date functions and LIMIT versus TOP). Confirm the platform if the interviewer mentions one.

How many SQL questions should I practise before an interview?

There is no fixed number, but aim to be comfortable writing 30 to 50 query problems covering joins, aggregations, subqueries, and window functions. Quality of practice matters more than quantity, so make sure you can write each one without looking at the answer.

What is the most common SQL interview mistake?

Confusing WHERE and HAVING, forgetting that NULL comparisons need IS NULL, and writing accidental cross joins by omitting the join condition. Another frequent slip is using COUNT(column) when you meant COUNT(*) and missing NULL rows.

How can I practise answering out loud before the real round?

Mock interviews are the most effective way. Rehearsing with a tool that simulates the interview, asks follow-up questions, and gives structured feedback builds both your technical accuracy and your confidence, which is often what decides the outcome.

Final Thoughts

SQL rewards structured practice more than almost any other interview subject, because the questions are predictable and the skills are concrete. Work through the questions in this guide, write every query yourself, and focus on explaining your reasoning. Master joins, aggregations, and the classic query problems, understand the tradeoffs behind indexes and transactions, and you will be ready for freshers and experienced rounds alike. Combine this preparation with a few realistic mock interviews, and you will walk into your next SQL round prepared to perform. Good luck.