10 SQL Interview Questions (Pune Data, 2026)

The list

1. 1

   Explain INNER, LEFT, RIGHT, and FULL OUTER JOIN with examples.

   INNER: only matched rows in both tables. LEFT: all left + matched right (right NULL if no match). RIGHT: mirror of LEFT. FULL OUTER: all rows from both, NULL where no match (not in MySQL — emulate via UNION). Walk through a Users + Orders example: INNER gives users with orders; LEFT gives all users including no-order ones.

   Why it matters: Asked at ~95% of Pune SQL fresher rounds. Most-screened SQL concept by frequency.

   Best for: Universal fresher screening; expected to know cold.

2. 2

   What's the difference between WHERE and HAVING?

   WHERE filters rows before grouping/aggregation; HAVING filters groups after aggregation. WHERE can't use aggregate functions (SUM, COUNT, AVG); HAVING can. Example: `WHERE order_date >= '2026-01-01'` (filter rows) vs `HAVING SUM(amount) > 1000` (filter aggregated groups). Both can appear in the same query.

   Why it matters: Asked at ~75% of Pune rounds. Common follow-up: 'why can't I use COUNT() in WHERE?' (rows aren't aggregated yet at WHERE phase).

   Best for: Query-execution-order understanding.

3. 3

   Explain window functions with an example.

   Window functions perform calculations across a set of rows related to the current row WITHOUT collapsing them (unlike GROUP BY). Syntax: `func() OVER (PARTITION BY col1 ORDER BY col2)`. Common: ROW_NUMBER() (rank), RANK() (rank with ties skip), DENSE_RANK() (no skip), LAG/LEAD (previous/next row), SUM() OVER (running totals).

   Why it matters: Asked at ~60% of Pune data + backend product company rounds. Modern SQL discriminator — older candidates often miss this; senior-fresher signal.

   Best for: Differentiating beyond basic SQL fluency.

4. 4

   When should you add an index? When shouldn't you?

   Add on: columns frequently in WHERE filters, JOIN conditions, ORDER BY, foreign keys. Index columns with high selectivity (many distinct values) + read-heavy workloads. Don't index: small tables (<1000 rows), low-selectivity columns (boolean flags, gender), write-heavy tables where index maintenance cost outweighs read benefit, every column 'just in case'. Use EXPLAIN to verify usage.

   Why it matters: Asked at ~65% of Pune rounds. Trade-off framing (each index adds write overhead + storage) signals senior-fresher depth.

   Best for: Performance + production-engineering signal.

5. 5

   What is a CTE (Common Table Expression) and when do you use it?

   A named temporary result set referenced within a single query, defined via WITH. Use when: breaking complex queries into readable steps, recursive queries (organisation hierarchies, dependency graphs), avoiding subquery repetition. Cleaner than nested subqueries. Recursive variant: `WITH RECURSIVE cte AS (base case UNION ALL recursive case) SELECT * FROM cte`.

   Why it matters: Asked at ~45% of Pune rounds, especially product company + analytics-leaning interviews. Modern SQL feature; most pre-2015 SQL learners miss it.

   Best for: Query-readability + advanced SQL signal.

6. 6

   Explain ACID properties of transactions.

   Atomicity: transaction fully completes or has no effect — rollback on failure. Consistency: transaction moves DB from one valid state to another, respecting all constraints. Isolation: concurrent transactions appear sequential (controlled by isolation level: READ COMMITTED, REPEATABLE READ, SERIALIZABLE). Durability: committed data survives crashes (typically via write-ahead logging).

   Why it matters: Asked at ~70% of Pune backend rounds. Walk through a banking transfer example — debit + credit must be atomic.

   Best for: Database fundamentals; differentiator from data-analyst-only tracks.

7. 7

   How do you find the second-highest salary from an Employees table?

   Multiple approaches: (1) `SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)` — works but two table scans. (2) Window function: `SELECT salary FROM (SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees) WHERE rank = 2` — handles ties cleanly. (3) `SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1` — simplest but doesn't handle ties.

   Why it matters: Asked at ~50% of Pune rounds — the canonical SQL puzzle. Knowing 3 approaches + their trade-offs (ties handling, performance) is the senior-fresher signal.

   Best for: Practical-SQL competence + edge-case thinking.

8. 8

   What's the N+1 query problem? How do you fix it in SQL or ORMs?

   Fetching N parent entities then accessing a related child triggers 1 query for the list + N queries for children = N+1 total. Fix in raw SQL: single query with JOIN. Fix in ORMs: eager loading via JOIN FETCH (JPA), `.includes()` (Rails), `.select_related()` / `.prefetch_related()` (Django). Most ORMs default to lazy loading — recognising and fixing N+1 is a senior-fresher signal.

   Why it matters: Asked at ~50% of Pune backend rounds (~60% at product cos). The most-asked-about query performance pattern.

   Best for: Backend + ORM-fluency discriminator.

9. 9

   Explain the difference between UNION and UNION ALL.

   UNION combines result sets from multiple SELECTs AND removes duplicate rows (extra sort/hash step → slower). UNION ALL combines without deduplication (faster). Use UNION when you actually need unique rows; UNION ALL when you know they're disjoint or duplicates are fine. Common production mistake: defaulting to UNION when UNION ALL would work + perform 30-50% faster.

   Why it matters: Asked at ~35% of Pune rounds. Performance-awareness question — knowing the deduplication overhead distinguishes senior-fresher candidates.

   Best for: Query performance depth.

10. 10

    How do you write a query to find duplicate rows in a table?

    Standard approach: `SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1`. To return the full duplicate rows (not just the keys): subquery + join, or window function `SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn FROM table) t WHERE rn > 1`. The window-function version also lets you keep one and delete the rest.

    Why it matters: Asked at ~40% of Pune rounds, especially data analyst + ETL-focused interviews. Common data-quality interview puzzle.

    Best for: Data-quality + GROUP BY fluency.