USA
Canada
UK
Europe
Australia
Singapore
Japan
IndiaQ1. Explain the different types of SQL JOINs.
Short answer: INNER, LEFT/RIGHT OUTER, FULL OUTER and CROSS control which matched/unmatched rows are returned.
Strong answer: INNER JOIN returns only matching rows; LEFT JOIN returns all left rows plus matches (NULLs otherwise); RIGHT is the mirror; FULL OUTER returns all rows from both; CROSS produces the Cartesian product. Choosing correctly — and handling the NULLs from outer joins — is where candidates often slip.
Likely follow-up: How do NULLs from a LEFT JOIN affect a WHERE filter on the right table?
What the interviewer is checking: Core querying skill.
Common mistake: Filtering the right table in WHERE and accidentally turning a LEFT JOIN into an INNER JOIN.
Q2. What is the difference between WHERE and HAVING?
Short answer: WHERE filters rows before grouping; HAVING filters groups after aggregation.
Strong answer: WHERE applies to individual rows before GROUP BY, so it cannot use aggregate functions. HAVING filters the aggregated groups afterward and can use aggregates like COUNT/SUM. Use WHERE to reduce rows early (better performance) and HAVING only for conditions on aggregates.
Likely follow-up: Why is filtering in WHERE usually more efficient than HAVING?
What the interviewer is checking: Understanding of query execution order.
Common mistake: Putting non-aggregate filters in HAVING and hurting performance.
Q3. How do database indexes work and when should you add one?
Short answer: Indexes (usually B-trees) speed lookups/sorts at the cost of write overhead and storage.
Strong answer: An index is a sorted structure (typically a B-tree) that lets the engine find rows without scanning the whole table — great for WHERE, JOIN and ORDER BY columns. But every index adds write cost and storage. Index selective, frequently-filtered columns; consider composite and covering indexes; avoid over-indexing.
Likely follow-up: What is a covering index?
What the interviewer is checking: Performance-tuning knowledge.
Common mistake: Indexing everything, slowing writes and wasting space.
Q4. What are window functions and when are they useful?
Short answer: They compute across a set of rows related to the current row without collapsing them.
Strong answer: Window functions (ROW_NUMBER, RANK, LAG/LEAD, SUM OVER) compute values over a partition/order without grouping rows away — ideal for running totals, rankings, deduplication and period-over-period comparisons. They are more expressive and often faster than self-joins or subqueries for these tasks.
Likely follow-up: How would you get the latest row per group using a window function?
What the interviewer is checking: Modern SQL proficiency.
Common mistake: Reaching for correlated subqueries where a window function is cleaner.
Q5. Explain ACID properties and transaction isolation levels.
Short answer: ACID = Atomicity, Consistency, Isolation, Durability; isolation levels trade consistency for concurrency.
Strong answer: ACID guarantees reliable transactions. Isolation levels (Read Uncommitted → Read Committed → Repeatable Read → Serializable) control anomalies like dirty reads, non-repeatable reads and phantoms, trading concurrency for consistency. Pick the lowest level that prevents the anomalies your workload cannot tolerate.
Likely follow-up: Which anomaly does Repeatable Read still allow?
What the interviewer is checking: Transaction fundamentals.
Common mistake: Not knowing the anomalies each level permits.
Q6. How would you find and fix a slow query?
Short answer: Read the execution plan, look for scans/spills, then add indexes or rewrite the query.
Strong answer: Capture the query, read its execution plan for table scans, expensive sorts, key lookups or bad estimates, and check statistics. Fixes include adding/adjusting indexes, rewriting to be sargable (avoid functions on indexed columns), reducing returned columns, and updating statistics. Measure before and after.
Likely follow-up: What makes a predicate non-sargable?
What the interviewer is checking: Practical tuning method.
Common mistake: Guessing at indexes without reading the execution plan.
Q7. What is normalization, and when would you denormalize?
Short answer: Normalization removes redundancy; denormalization re-introduces it for read performance.
Strong answer: Normalization (1NF–3NF) organizes data to eliminate redundancy and update anomalies. Denormalization deliberately duplicates data to speed reads (fewer joins), common in reporting/analytics and read-heavy systems — at the cost of write complexity and consistency risk. Choose based on read/write patterns.
Likely follow-up: What are the risks of denormalized data?
What the interviewer is checking: Data-modeling judgment.
Common mistake: Treating normalization as always-right regardless of workload.
Q8. What is the difference between DELETE, TRUNCATE and DROP?
Short answer: DELETE removes rows (logged, filtered); TRUNCATE removes all rows fast; DROP removes the table.
Strong answer: DELETE removes selected rows with WHERE, is fully logged and can be rolled back and fire triggers. TRUNCATE removes all rows quickly with minimal logging and resets identity, but is less granular. DROP removes the table structure entirely. Choose by whether you need filtering, speed or full removal.
Likely follow-up: Can you roll back a TRUNCATE?
What the interviewer is checking: Command semantics.
Common mistake: Assuming TRUNCATE and DELETE behave identically.