SQL Interview Questions 2026 Top 40 with Answers

The top 40 SQL interview questions for freshers and experienced in 2026 cover eight clusters: SELECT/WHERE/ORDER BY basics (22%), JOINs (25%), GROUP BY + HAVING (12%), subqueries (10%), indexes and query optimization (10%), window functions ROW_NUMBER/RANK/DENSE_RANK (8%), transactions and ACID (7%), and normalization 1NF/2NF/3NF (6%). Most fresher interviews (TCS NQT, Infosys InfyTQ, Cognizant GenC) dedicate one full coding round to SQL JOIN queries, plus 8–12 MCQs on output prediction. This guide gives you all 40 questions with concise answers and 12 working SQL examples you can run on MySQL or PostgreSQL.

Table of Contents

• SQL Topic Weightage in 2026 Fresher Interviews
• Company Round Formats (TCS / Infosys / Cognizant)
• SQL Basics (Q1–Q8)
• JOINs Explained (Q9–Q15)
• GROUP BY, HAVING, Aggregation (Q16–Q20)
• Subqueries & CTEs (Q21–Q25)
• Window Functions (Q26–Q30)
• Indexes & Performance (Q31–Q34)
• Transactions & ACID (Q35–Q37)
• Normalization (Q38–Q40)
• 3-Week SQL Interview Prep Plan

SQL Topic Weightage in 2026 Fresher Interviews

Trend in 2026: Window-function questions doubled in frequency at Infosys InfyTQ and TCS NQT compared to 2024. Expect at least one ROW_NUMBER() or RANK() question in any product-company fresher round.

Company Round Formats: SQL Coverage

1. Q1. What is the difference between SQL and MySQL/PostgreSQL?
   Answer: SQL (Structured Query Language) is the standard language for relational databases. MySQL, PostgreSQL, Oracle, and SQL Server are database management systems (DBMS) that implement SQL with proprietary extensions. ANSI SQL is the common subset; each DBMS adds vendor-specific functions (e.g., MySQL's LIMIT, PostgreSQL's ILIKE, Oracle's ROWNUM, SQL Server's TOP).
2. Q2. What is the difference between CHAR and VARCHAR?
   Answer: CHAR(n) stores exactly n characters, padding with spaces if the value is shorter (fixed-length, faster for fixed-size data like country codes). VARCHAR(n) stores up to n characters without padding (variable-length, saves space for variable data like names). Use CHAR for codes (ISO country codes, state codes); use VARCHAR for free text.
3. Q3. What is a primary key?
   Answer: A column (or combination of columns) that uniquely identifies each row in a table. Properties: NOT NULL, unique, immutable. Each table should have exactly one primary key. Composite primary keys use multiple columns (e.g., (order_id, line_item_id)). Surrogate keys (auto-increment integers or UUIDs) are often preferred over natural keys (real-world identifiers like email) because they're stable.
4. Q4. What is a foreign key?
   Answer: A column in one table that references the primary key of another table, enforcing referential integrity. A row cannot be inserted with a foreign-key value that doesn't exist in the referenced table; a referenced row cannot be deleted while child rows exist (unless ON DELETE CASCADE is set). Example: orders.customer_id references customers.id.
5. Q5. What is the difference between DELETE, TRUNCATE, and DROP?
   Answer:

   Command	Removes	Rollback	Resets identity
   DELETE FROM table WHERE ...	Specific rows (or all if no WHERE)	Yes (transactional)	No
   TRUNCATE TABLE table	All rows	No (DDL, auto-committed in most DBs)	Yes
   DROP TABLE table	Table itself + all data + structure	No	N/A (table is gone)

6. Q6. What is the difference between WHERE and HAVING?
   Answer: -- WHERE filters rows BEFORE grouping SELECT department, AVG(salary) AS avg_salary FROM employees WHERE hire_date >= '2025-01-01' -- only recent hires GROUP BY department; -- HAVING filters groups AFTER aggregation SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 80000; -- only well-paid departments

   WHERE cannot reference aggregate functions; HAVING can. WHERE is also faster because it reduces rows before grouping; HAVING runs after the aggregation is complete.

JOINs Explained (Q9–Q15)

1. Q9. Explain all JOIN types with examples.
   Answer:

   -- Sample tables
   -- customers:    id, name
   -- orders:       id, customer_id, amount
   
   -- INNER JOIN: only rows where customer.id = orders.customer_id
   SELECT c.name, o.amount
   FROM customers c
   INNER JOIN orders o ON c.id = o.customer_id;
   
   -- LEFT JOIN: all customers, with order amount where present (NULL if no order)
   SELECT c.name, o.amount
   FROM customers c
   LEFT JOIN orders o ON c.id = o.customer_id;
   
   -- RIGHT JOIN: all orders, with customer name where present
   SELECT c.name, o.amount
   FROM customers c
   RIGHT JOIN orders o ON c.id = o.customer_id;
   
   -- FULL OUTER JOIN: all customers + all orders, NULLs on missing side
   SELECT c.name, o.amount
   FROM customers c
   FULL OUTER JOIN orders o ON c.id = o.customer_id;
   
   -- CROSS JOIN: every customer × every order (Cartesian product)
   SELECT c.name, o.amount
   FROM customers c
   CROSS JOIN orders o;
   
   -- SELF JOIN: join a table to itself (e.g., employee → manager)
   SELECT e.name AS employee, m.name AS manager
   FROM employees e
   LEFT JOIN employees m ON e.manager_id = m.id;

2. Q10. What's the difference between INNER JOIN and LEFT JOIN?
   Answer: INNER JOIN returns only rows where the join key exists in BOTH tables. LEFT JOIN returns ALL rows from the left table, plus matching rows from the right table (NULLs where no match exists). Use INNER JOIN when you only want matched pairs (orders with their customers); use LEFT JOIN when you need to keep unmatched left rows (all customers whether or not they placed an order).
3. Q11. What is a SELF JOIN and when would you use it?
   Answer: A join where a table is joined to itself. Common uses: employee → manager hierarchy (manager is also an employee), finding duplicate rows, comparing rows within the same table (e.g., pairs of customers in the same city). Use table aliases (e for employee, m for manager) to distinguish the two roles.
4. Q12. What is the difference between JOIN and UNION?
   Answer: JOIN combines columns from two or more tables horizontally (adds more columns per row). UNION combines rows from two or more queries vertically (adds more rows). JOIN requires a related column; UNION requires the queries to have the same number of columns with compatible types. UNION removes duplicates; UNION ALL keeps them.
5. Q13. What is the difference between UNION and UNION ALL?
   Answer: UNION combines the result sets and removes duplicate rows (requires a sort/hash to deduplicate — slower). UNION ALL combines them and keeps all rows including duplicates (faster, no dedup step). Use UNION when you need a distinct set; use UNION ALL when duplicates are acceptable or expected (e.g., combining logs from two days).
6. Q14. How do you find rows in Table A that don't have a match in Table B?
   Answer:

   -- Three equivalent patterns:
   
   -- 1. LEFT JOIN ... WHERE NULL (most common in interviews)
   SELECT a.*
   FROM customers a
   LEFT JOIN orders b ON a.id = b.customer_id
   WHERE b.id IS NULL;
   
   -- 2. NOT EXISTS (often faster, clearer intent)
   SELECT *
   FROM customers a
   WHERE NOT EXISTS (
       SELECT 1 FROM orders b WHERE b.customer_id = a.id
   );
   
   -- 3. NOT IN (avoid when b.id can be NULL — NULLs in subquery break NOT IN)
   SELECT *
   FROM customers
   WHERE id NOT IN (SELECT customer_id FROM orders);

7. Q15. What is a CROSS JOIN and when is it useful?
   Answer: Returns the Cartesian product of two tables — every row in A paired with every row in B. SELECT * FROM colors CROSS JOIN sizes produces every color-size combination. Common uses: generating test data, building date × category grids for reports, combinatorial puzzles. Use sparingly on large tables — a 10K × 10K cross join produces 100M rows.

GROUP BY, HAVING, Aggregation (Q16–Q20)

1. Q16. What are the SQL aggregate functions?
   Answer: COUNT() (rows or non-null values), SUM() (total), AVG() (mean), MIN() (smallest), MAX() (largest). COUNT(*) counts rows including NULLs; COUNT(column) counts non-NULL values in that column. Aggregates ignore NULLs by default. Use DISTINCT inside aggregate for unique values: COUNT(DISTINCT customer_id).
2. Q17. How do you find the second-highest salary in a table?
   Answer:

   -- Three approaches:
   
   -- 1. Subquery with MAX (works on all DBs)
   SELECT MAX(salary) FROM employees
   WHERE salary < (SELECT MAX(salary) FROM employees);
   
   -- 2. LIMIT + OFFSET (MySQL/PostgreSQL)
   SELECT DISTINCT salary FROM employees
   ORDER BY salary DESC LIMIT 1 OFFSET 1;
   
   -- 3. DENSE_RANK (PostgreSQL/Oracle/SQL Server — modern way)
   SELECT salary FROM (
       SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
       FROM employees
   ) ranked
   WHERE rnk = 2;

3. Q18. How do you count employees per department?
   Answer:

   SELECT department, COUNT(*) AS employee_count
   FROM employees
   GROUP BY department
   ORDER BY employee_count DESC;

4. Q19. How do you find departments with more than 10 employees?
   Answer:

   SELECT department, COUNT(*) AS n
   FROM employees
   GROUP BY department
   HAVING COUNT(*) > 10;
   
   -- WHERE filters rows; HAVING filters groups.
   -- Don't write: GROUP BY ... WHERE COUNT(*) > 10  ← syntax error

5. Q20. What does GROUP BY do with NULL values?
   Answer: NULLs are grouped together into a single group (NULL is treated as its own value for grouping). SELECT col, COUNT(*) FROM t GROUP BY col will include one row with NULL if any rows have NULL in col. This is different from most other operations where NULL is treated as unknown.

Subqueries & CTEs (Q21–Q25)

1. Q21. What is a subquery?
   Answer: A query nested inside another query. Can appear in SELECT (scalar subquery), FROM (derived table), WHERE (filtered by subquery result), or HAVING. The subquery is evaluated first (in parentheses), and its result feeds the outer query. Correlated subqueries reference columns from the outer query and run once per outer row.
2. Q22. What is a CTE (Common Table Expression)?
   Answer:

   -- A CTE is a named temporary result set that exists only for the query.
   -- Cleaner than nested subqueries, especially for multi-step logic.
   
   WITH high_earners AS (
       SELECT department, employee_id, salary
       FROM employees
       WHERE salary > 100000
   ),
   dept_totals AS (
       SELECT department, SUM(salary) AS total_salary
       FROM high_earners
       GROUP BY department
   )
   SELECT d.department, d.total_salary, COUNT(h.employee_id) AS n_high_earners
   FROM dept_totals d
   JOIN high_earners h ON d.department = h.department
   GROUP BY d.department, d.total_salary;
   
   -- CTEs can reference themselves recursively (organizational hierarchies, graphs)

3. Q23. What is the difference between correlated and non-correlated subqueries?
   Answer: Non-correlated: subquery can run independently of the outer query (evaluated once). Faster and simpler. Correlated: subquery references columns from the outer query, so it must run once per outer row (slower on large data). Example correlated: SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department).
4. Q24. What is the difference between EXISTS and IN?
   Answer: -- IN: subquery returns a list; main query checks if value is in the list. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders); -- EXISTS: subquery returns a boolean (true if any row matches); -- typically faster on large data because it short-circuits on first match. SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

   Rule of thumb: use EXISTS when the subquery references the outer query (correlated); use IN for non-correlated lists.

Window Functions (Q26–Q30)

1. Q26. What is a window function?
   Answer: A function that computes a value across a set of rows related to the current row, WITHOUT collapsing the result like GROUP BY. The "window" is defined by OVER (PARTITION BY ... ORDER BY ...). Example:
2. Q27. What's the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
   Answer:

   SELECT
       name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn_row,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
   FROM employees;
   
   -- If salaries are 100, 90, 90, 80:
   --   ROW_NUMBER:    1, 2, 3, 4        (always unique, no ties)
   --   RANK:          1, 2, 2, 4        (ties share rank, next rank skips)
   --   DENSE_RANK:    1, 2, 2, 3        (ties share rank, next rank doesn't skip)
   
   -- Use ROW_NUMBER for "top N per group"; RANK/DENSE_RANK for "all employees earning top salary".

3. Q28. How do you find the top 3 highest salaries per department?
   Answer:

   -- Using ROW_NUMBER (top 3 strictly, even if there are ties at the boundary)
   SELECT *
   FROM (
       SELECT
           name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
       FROM employees
   ) ranked
   WHERE rn <= 3;
   
   -- Using DENSE_RANK (includes ties at boundary)
   SELECT *
   FROM (
       SELECT
           name, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
       FROM employees
   ) ranked
   WHERE rnk <= 3;

4. Q29. What are LAG() and LEAD()?
   Answer:

   -- Compare each row to the previous/next row in the same partition.
   SELECT
       order_date,
       amount,
       LAG(amount)  OVER (ORDER BY order_date) AS prev_amount,
       LEAD(amount) OVER (ORDER BY order_date) AS next_amount,
       amount - LAG(amount) OVER (ORDER BY order_date) AS diff_from_prev
   FROM orders;
   
   -- Common use: month-over-month growth, sequential comparisons, detecting gaps.

5. Q30. How do you calculate a running total?
   Answer:

   -- Cumulative sum ordered by date
   SELECT
       order_date,
       amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total,
       SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS explicit_running_total
   FROM orders;

Indexes & Performance (Q31–Q34)

1. Q31. What is an index?
   Answer: A separate data structure (typically a B-tree) that lets the database find rows matching a column value without scanning the entire table. Trade-off: indexes speed up SELECT/WHERE/JOIN/ORDER BY but slow down INSERT/UPDATE/DELETE (the index must be updated) and consume disk space. Common types: B-tree (default, good for equality + range), hash (equality only), bitmap (low-cardinality columns), full-text (text search).
2. Q32. When should you create an index?
   Answer:
   • Columns frequently used in WHERE, JOIN, ORDER BY, GROUP BY
   • High-cardinality columns (many distinct values — email, customer_id)
   • Foreign-key columns (often auto-indexed by DBMS)
   • Composite indexes for queries that filter on multiple columns together (column order matters: leftmost prefix)

   Don't index: low-cardinality columns (boolean, gender), small tables (full scan is faster), columns frequently updated.

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

-- Shows how the DB plans to execute the query:
--   - Index scan vs full table scan
--   - Join order and join algorithm (nested loop, hash join, merge join)
--   - Estimated row counts at each step
-- Use EXPLAIN ANALYZE (PostgreSQL) to also see actual timings and row counts.

Transactions & ACID (Q35–Q37)

1. Q35. What are ACID properties?
   Answer:

   Property	Meaning
   Atomicity	All operations in a transaction succeed or all fail (no partial state)
   Consistency	Transaction moves DB from one valid state to another (constraints not violated)
   Isolation	Concurrent transactions don't see each other's uncommitted changes
   Durability	Committed changes survive crashes (written to disk, not just memory)

2. Q36. What are the four transaction isolation levels?
   Answer:

   Level	Dirty reads	Non-repeatable reads	Phantom reads
   READ UNCOMMITTED	Yes	Yes	Yes
   READ COMMITTED (PostgreSQL default)	No	Yes	Yes
   REPEATABLE READ (MySQL InnoDB default)	No	No	Yes
   SERIALIZABLE	No	No	No

   Higher isolation = more correctness = less concurrency. Most production systems use READ COMMITTED for performance with explicit row locks for critical sections.

3. Q37. What is a deadlock and how does SQL handle it?
   Answer: Two transactions each hold a lock the other needs, so neither can proceed. Example: T1 locks row A then wants row B; T2 locks row B then wants row A. Modern DBMSs detect deadlocks and roll back one transaction (the cheaper one); the application should retry the rolled-back transaction. Prevention: acquire locks in a consistent order, keep transactions short, use appropriate isolation level.

Normalization (Q38–Q40)

1. Q38. What is 1NF (First Normal Form)?
   Answer: Every column holds atomic (indivisible) values; no repeating groups; each row is unique. Violation example: phone_numbers = "555-1234, 555-5678" in a single column. Fix: split into separate rows or a related table. 1NF also requires that there are no duplicate rows — enforced by a primary key.
2. Q39. What is 2NF and 3NF?
   Answer: 2NF: 1NF + every non-key column depends on the WHOLE primary key (no partial dependency). Violation: in order_items(order_id, product_id, product_name, qty), product_name depends only on product_id, not on the full composite key — split into products + order_items tables.
3. Q40. What is BCNF (Boyce-Codd Normal Form)?
   Answer:

   3NF + every determinant is a candidate key. A determinant is any column on which another column fully depends. Example violation: in (student, subject, professor) with rule "each subject has exactly one professor" — professor is determined by subject, but subject is not a candidate key (because (student, subject) is). Fix: split into (student, subject)

3-Week SQL Interview Preparation Plan

Resources and Next Steps

For the full 25-question curated Q&A collection at all difficulty levels (Level 1 foundational, Level 2 intermediate, Level 3 advanced), see the TutorsBot SQL Tutorial and the DBMS Interview Questions guide for RDBMS-specific patterns (normalization, ACID, indexing). For related interview prep, see our Python Interview Questions, Java Interview Questions, and OOPs Interview Questions.