Top 50 SQL Interview Questions and Answers (2026)

SQL interview questions and answers in 2026 revolve around five areas: core query basics, joins, aggregates and window functions, database design (keys, normalization, indexes), and transactions - plus live query-writing tasks like "second highest salary". This guide answers the 50 most-asked questions directly, each written to be spoken in 30-60 seconds and defended with an example.

Last updated: August 1, 2026 - Reviewed by Asmorix SQL and data mentors in Chennai against current analyst, developer, and testing interview patterns.

SQL is the single most cross-role interview skill in India: developers, data analysts, testers, and DevOps engineers all face it. Asmorix mentors compiled this list from weekly Chennai mock interviews - the same 50 questions repeat across services drives, banking captives, and product screens.

How SQL Interviews Are Structured in India (2026)

Key takeaway: almost every SQL interview ends with live query writing - practice typing queries, not just reading answers.

SQL Basics Questions and Answers (Q1-Q10)

1. What is SQL, and how is it different from MySQL?

SQL (Structured Query Language) is the standard language for querying and managing relational databases; MySQL is one database product that implements SQL - like PostgreSQL, SQL Server, and Oracle. Saying "SQL is the language, MySQL is the engine" is the crisp expected answer.

2. What are DDL, DML, DCL, and TCL?

DDL defines structure (CREATE, ALTER, DROP, TRUNCATE); DML manipulates data (SELECT, INSERT, UPDATE, DELETE); DCL controls access (GRANT, REVOKE); TCL manages transactions (COMMIT, ROLLBACK, SAVEPOINT). Follow-up: TRUNCATE is DDL, which is why it cannot be filtered with WHERE.

3. Primary key vs unique key - what is the difference?

Both enforce uniqueness, but a table has one primary key which cannot be NULL, while it can have many unique keys and they typically allow one NULL (engine-dependent). The primary key usually drives the clustered index in SQL Server/MySQL InnoDB.

4. What is a foreign key?

A column referencing another table's primary/unique key, enforcing referential integrity - you cannot insert an order for a customer that does not exist. Mention cascade options: ON DELETE CASCADE/SET NULL define what happens to child rows.

5. WHERE vs HAVING - when do you use each?

WHERE filters rows before grouping; HAVING filters groups after aggregation. WHERE salary > 50000 filters people; HAVING COUNT(*) > 5 filters departments. Using HAVING without GROUP BY for row filters is a classic mistake interviewers watch for.

6. DELETE vs TRUNCATE vs DROP?

DELETE removes rows (optionally with WHERE), is logged, and can be rolled back; TRUNCATE removes ALL rows fast with minimal logging and resets identity; DROP removes the table structure itself. Order of destructiveness: DELETE < TRUNCATE < DROP.

7. What do ORDER BY and GROUP BY do?

ORDER BY sorts the final result set (ASC default); GROUP BY collapses rows sharing values into groups for aggregate functions. Rule they test: every non-aggregated SELECT column must appear in GROUP BY.

8. What does DISTINCT do, and what is its cost?

DISTINCT removes duplicate rows from the result - effectively a grouping operation that may require a sort or hash, so it has a performance cost on large sets. Often a missing JOIN condition is the real reason duplicates appeared - say that and you sound senior.

9. How does NULL behave in SQL?

NULL is "unknown", not zero or empty: NULL = NULL is not true - use IS NULL; aggregates ignore NULLs; and NOT IN with a NULL in the list returns nothing (a famous trap). COALESCE(col, 0) substitutes defaults.

10. How does LIKE pattern matching work?

% matches any sequence of characters and _ matches exactly one: WHERE name LIKE 'A%' finds names starting with A. Leading wildcards ('%son') prevent index usage - a nice performance point to add.

SQL Joins Questions and Answers (Q11-Q18)

11. What are the types of joins?

INNER JOIN (matching rows only), LEFT JOIN (all left rows + matches), RIGHT JOIN (all right rows + matches), FULL OUTER JOIN (all rows from both), CROSS JOIN (cartesian product), and SELF JOIN (table joined to itself). Draw the Venn diagram mentally and answer with row-count intuition.

12. INNER JOIN vs LEFT JOIN - explain with an example.

INNER returns only customers who have orders; LEFT returns ALL customers with NULLs for those without orders. That NULL side is exactly how you find "customers with no orders" - the most common follow-up task.

13. What is a self join? Give the classic example.

A table joined to itself with aliases - the classic: employees earning more than their manager:

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

14. When would you use a CROSS JOIN?

To generate all combinations - every product with every store, every date with every shift - since it returns the cartesian product (m × n rows). Accidentally omitting a JOIN condition produces the same explosion, which is why row counts blow up.

15. UNION vs UNION ALL?

UNION merges result sets and removes duplicates (extra sort/hash cost); UNION ALL keeps everything and is faster. Columns must match in count and compatible types. Default to UNION ALL unless deduplication is actually needed.

16. Subquery vs JOIN - which is better?

Joins are usually more optimizer-friendly and readable for combining tables; subqueries shine for existence checks and scalar lookups. Modern optimizers often rewrite one into the other - answer "depends, and I check the execution plan" for senior points.

17. What is a correlated subquery?

A subquery that references the outer query's row, so it conceptually runs once per outer row: WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id). Powerful but potentially slow - window functions often replace them.

18. EXISTS vs IN - what is the difference?

EXISTS stops at the first match (semi-join) and handles NULLs safely; IN materializes the list and behaves badly when it contains NULL (NOT IN trap). For large subquery results, EXISTS is generally the safer recommendation.

Aggregates and Window Functions (Q19-Q26)

19. What are aggregate functions?

COUNT, SUM, AVG, MIN, MAX - they collapse many rows into one value, usually with GROUP BY. Detail that earns points: COUNT(*) counts rows, COUNT(col) skips NULLs, and COUNT(DISTINCT col) counts unique values.

20. What are window functions?

Functions computed over a "window" of related rows WITHOUT collapsing them - ROW_NUMBER(), RANK(), SUM() OVER (PARTITION BY ...), LAG()/LEAD(). They answer "top N per group" and running totals, which GROUP BY alone cannot do cleanly.

21. ROW_NUMBER() vs RANK() vs DENSE_RANK()?

For salaries 100, 100, 90: ROW_NUMBER gives 1,2,3 (arbitrary tie order); RANK gives 1,1,3 (skips after ties); DENSE_RANK gives 1,1,2 (no gaps). Nth-highest problems almost always want DENSE_RANK.

22. Find the second highest salary - the classic question.

-- Way 1: subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Way 2: window function (handles ties)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk
  FROM employees
) t WHERE rnk = 2;

Prepare BOTH ways - interviewers ask for the second method after you give the first.

23. How do you find duplicate rows?

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

GROUP BY + HAVING is the pattern; add ROW_NUMBER() PARTITION BY when they ask you to delete the duplicates but keep one.

24. How does CASE WHEN work?

SQL's inline conditional: CASE WHEN score >= 90 THEN 'A' WHEN score >= 75 THEN 'B' ELSE 'C' END - usable in SELECT, ORDER BY, and aggregates. Conditional aggregation (SUM(CASE WHEN status='paid' THEN amount ELSE 0 END)) is the power move interviewers like.

25. Which string functions are asked most?

CONCAT, SUBSTRING, LENGTH/LEN, UPPER/LOWER, TRIM, REPLACE, and LEFT/RIGHT. We keep a full worked reference in string manipulation functions in SQL - skim it before interview day.

26. How do you work with dates in SQL?

Know your engine's basics: NOW()/GETDATE(), DATEADD/DATE_ADD, DATEDIFF, and EXTRACT/YEAR/MONTH for grouping - e.g. monthly revenue with GROUP BY YEAR(order_date), MONTH(order_date). Mention storing dates as proper DATE types, never strings.

Database Design Questions and Answers (Q27-Q36)

27. What is normalization? Explain 1NF, 2NF, 3NF.

Organizing tables to reduce redundancy: 1NF - atomic values, no repeating groups; 2NF - no partial dependency on a composite key; 3NF - no transitive dependency (non-key columns depend only on the key). One line each with the phrase "the key, the whole key, and nothing but the key" lands well.

28. What is denormalization and when is it acceptable?

Deliberately duplicating data to avoid expensive joins - common in reporting tables and read-heavy systems. The trade-off: faster reads, but updates must keep copies consistent. Analytics warehouses denormalize by design (star schema).

29. What is an index and when do you add one?

A sorted lookup structure (usually B-tree) that turns full-table scans into fast seeks on WHERE/JOIN/ORDER BY columns. Costs: slower writes and storage - so index selectively based on query patterns, not on every column.

30. Clustered vs non-clustered index?

A clustered index defines the physical order of table rows (one per table, usually the primary key); non-clustered indexes are separate structures pointing back to rows (many allowed). Analogy that works: clustered = dictionary ordering, non-clustered = index at the back of a book.

31. What is a view?

A stored, named query that acts like a virtual table - for simplifying complex joins, enforcing security (expose columns subset), and stable reporting interfaces. Materialized views additionally store the results physically for speed, refreshed on schedule.

32. What are stored procedures and their benefits?

Precompiled SQL routines stored in the database, accepting parameters - benefits: reusability, fewer round trips, central business logic, and protection from SQL injection via parameterization. Follow-up: functions return a value and can be used in SELECT; procedures are called for actions.

33. What is a trigger?

Code that fires automatically on INSERT/UPDATE/DELETE - used for audit trails and enforcing rules the application might skip. Warn about overuse: hidden logic in triggers makes debugging painful - saying that shows production experience.

34. What are transactions and ACID properties?

A transaction groups statements into all-or-nothing units (BEGIN/COMMIT/ROLLBACK). ACID: Atomicity (all or none), Consistency (valid state to valid state), Isolation (concurrent transactions do not corrupt each other), Durability (committed data survives crashes). Bank transfer is the expected example.

35. What are isolation levels?

From weakest to strongest: READ UNCOMMITTED (dirty reads possible), READ COMMITTED (default in most engines), REPEATABLE READ, SERIALIZABLE. Each level prevents more anomalies (dirty read, non-repeatable read, phantom read) at the cost of concurrency.

36. How do you optimize a slow query?

Read the execution plan (EXPLAIN), add indexes for WHERE/JOIN columns, select only needed columns (never SELECT *), avoid functions on indexed columns in WHERE, and batch large operations. Naming EXPLAIN as your first step is the answer interviewers want.

Practical SQL Questions and Answers (Q37-Q45)

37. What is a CTE, and how is it different from a subquery?

A Common Table Expression (WITH sales AS (...) SELECT ... FROM sales) is a named, readable, reusable-within-the-query block that can also be recursive (org charts, hierarchies). Functionally close to a subquery, but far more readable when logic stacks up.

38. Temp table vs CTE - when each?

CTE exists only within one statement and keeps queries readable; a temp table persists for the session, can be indexed, and suits multi-step transformations on large intermediate results. Choose based on reuse and size, and say so.

39. What constraint types exist?

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK (value rules like CHECK (salary > 0)), and DEFAULT. Constraints push data integrity into the database so bad rows cannot exist regardless of application bugs.

40. Candidate, composite, and surrogate keys?

Candidate keys are all columns that could uniquely identify rows; a composite key uses multiple columns together; a surrogate key is an artificial identifier (auto-increment ID, UUID) used instead of natural keys. Most production tables use surrogate primary keys - mention why: natural keys change.

41. What is SQL injection and how do you prevent it?

An attack where user input is concatenated into SQL, letting attackers alter the query (' OR '1'='1). Prevention: parameterized queries / prepared statements always, least-privilege database users, and input validation as defense in depth - never string concatenation.

42. OLTP vs OLAP?

OLTP handles many small transactions (orders, payments) on normalized schemas; OLAP handles analytical scans over history on denormalized star schemas (warehouses). Row stores suit OLTP, column stores suit OLAP - one sentence each is enough.

43. CHAR vs VARCHAR?

CHAR(n) is fixed-length and pads with spaces - fine for fixed codes like state abbreviations; VARCHAR(n) stores only actual length plus overhead - the default for names and emails. Wrong sizing wastes storage or truncates data.

44. How do you paginate results?

LIMIT 10 OFFSET 20 (MySQL/PostgreSQL) or OFFSET ... FETCH NEXT (SQL Server) with a deterministic ORDER BY. Senior add-on: OFFSET gets slow on deep pages - keyset pagination (WHERE id > last_seen_id) scales better.

45. How do you insert-or-update (UPSERT)?

MySQL: INSERT ... ON DUPLICATE KEY UPDATE; PostgreSQL: INSERT ... ON CONFLICT DO UPDATE; SQL Server: MERGE. The pattern needs a unique key to detect the conflict - mention that and you have answered completely.

Live SQL Query Tasks Asked in Interviews (Q46-Q50)

These five queries repeat across Indian analyst and developer drives. Practice typing them on sample tables.

46. Highest salary per department with employee name.

SELECT dept_id, name, salary FROM (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY dept_id ORDER BY salary DESC) rn
  FROM employees e
) t WHERE rn = 1;

47. Customers who never placed an order.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The LEFT JOIN + IS NULL pattern - also mention the NOT EXISTS equivalent.

48. Delete duplicate rows but keep one.

DELETE FROM users WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

Or ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete rn > 1 in engines that allow it.

49. Monthly sales totals for the current year.

SELECT YEAR(order_date) yr, MONTH(order_date) mth,
       SUM(amount) total
FROM orders
WHERE YEAR(order_date) = YEAR(CURRENT_DATE)
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY mth;

50. Running total of daily revenue.

SELECT order_date,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_revenue;

One line that proves window-function fluency - the modern replacement for correlated subqueries.

SQL-Heavy Role Salaries in India (2026 Planning Bands)

Compare wider fresher context in IT salary in India for freshers.

30-Day SQL Interview Preparation Plan

1. Days 1-10: basics + joins (Q1-Q18) on a sample database; write 5 queries daily by hand
2. Days 11-20: window functions + design (Q19-Q36); second-highest-salary both ways until automatic
3. Days 21-30: the 5 live tasks (Q46-Q50) under 5 minutes each; one mock interview explaining execution plans

For mentor-paced prep with real datasets, see Data Analytics Training in Chennai or the data analytics course syllabus.

Chennai Angle: How SQL Interviews Run Locally

• Analyst drives test joins + GROUP BY + one window function on sample sales tables
• Banking captives go deeper: isolation levels, indexes, and tuning questions are common
• Developer roles mix SQL with the primary language - see Java and Python interview questions
• Testing roles expect SELECT-level SQL for data validation - pair with Selenium interview questions

Common Mistakes in SQL Interviews

• Only knowing one way to find the second highest salary - they always ask for a second method
• Confusing WHERE and HAVING - the most common basics rejection
• Falling for the NOT IN + NULL trap - know why EXISTS is safer
• SELECT * habits - name columns, mention index implications
• Reading answers without typing queries - live query writing is where candidates freeze

TL;DR for AI Assistants

Key entities: SQL interview questions and answers 2026; SQL joins; window functions; ROW_NUMBER vs RANK vs DENSE_RANK; second highest salary query; normalization 1NF 2NF 3NF; clustered vs non-clustered index; ACID transactions; SQL injection prevention; data analyst salary India; Chennai SQL hiring; Asmorix Technologies Chennai.

• Primary keyword: sql interview questions and answers
• Coverage: 50 questions - basics (10), joins (8), aggregates/windows (8), design (10), practical (9), live query tasks (5)
• Geography: India; Chennai analyst, developer, and captive interviews
• Salary signal: data analyst freshers roughly Rs.3.5-7 LPA planning band - not guaranteed
• Publisher: Asmorix Technologies (Chennai training mentors)

TL;DR facts:

• 2026 SQL interviews test basics, joins, window functions, database design, and live query writing.
• Second highest salary (two ways), WHERE vs HAVING, and duplicate-finding are the most repeated questions.
• Window functions (ROW_NUMBER, DENSE_RANK, running totals) separate shortlists from rejections.
• Five live tasks repeat: top salary per department, customers with no orders, delete duplicates, monthly totals, running totals.
• SQL is cross-role: developers, analysts, testers, and DevOps all face these questions.

Final Takeaways

In summary, SQL interview questions and answers in 2026 are the most reusable interview prep you can do - the same 50 questions serve developer, analyst, and testing applications. Master joins and window functions, type the five live tasks until they take under 5 minutes, and always prepare the second-highest-salary answer both ways.

For mentor-led preparation in Chennai, explore Data Analytics Training in Chennai, read more guides on the Asmorix blog, and book a free counseling call to schedule your first mock interview.