30 SQL Interview Questions & Answers (Beginner → Advanced) — With Detailed Explanations & Code
SQL is the language of relational data. Interviews test not just syntax but how you think about data modeling, correctness, and performance. Crack your next SQL interview with confidence! Explore detailed SQL interview questions, real-world answers, and practical code snippets for all levels.Use these Q&As to sharpen fundamentals and ace practical scenarios.
🌱 Beginner / Fresher Level (1–10)
1) What is SQL? What problems does it solve?
Why it matters: Interviewers want to see if you grasp SQL’s role across systems and why relational databases still dominate.
Answer:
SQL (Structured Query Language) is the standard language for working with relational databases. It lets you define schemas (DDL), manipulate data (DML), control transactions (TCL), and manage permissions (DCL). SQL is declarative—you state what you want, and the optimizer figures out how to get it through indexes, joins, and plans. Because of its standardization and ACID guarantees, SQL remains the backbone for OLTP systems, analytics staging, and data warehousing.
Example SQL:
Pitfalls/Tips:
SQL is 3-valued logic (TRUE/FALSE/UNKNOWN via NULL).
Different engines add extensions (e.g., TOP, LIMIT, QUALIFY).
2) DDL vs DML vs DCL vs TCL — explain with examples
Why it matters: Shows you understand the full lifecycle: structure, data, security, consistency.
Answer:
DDL (Data Definition Language): schema changes — CREATE, ALTER, DROP.
DML (Data Manipulation Language): data changes — SELECT, INSERT, UPDATE, DELETE, MERGE.
DCL (Data Control Language): security — GRANT, REVOKE.
TCL (Transaction Control Language): atomic changes — COMMIT, ROLLBACK, SAVEPOINT.
Separation helps auditing, deployments, least-privilege access, and rollback planning.
Example SQL:
Pitfalls/Tips:
3) Primary Key vs Unique vs Foreign Key
Why it matters: Keys protect integrity and enable efficient joins.
Answer:
A Primary Key uniquely identifies a row and cannot be NULL (one per table). A Unique constraint enforces uniqueness but may allow one NULL (DB-specific). A Foreign Key references a parent table’s key and enforces referential integrity, optionally with ON DELETE/UPDATE CASCADE/RESTRICT/SET NULL.
Example SQL:
Pitfalls/Tips:
4) WHERE vs HAVING (and why HAVING isn’t a WHERE replacement)
Why it matters: Aggregation logic is a common source of wrong answers.
Answer:
WHERE filters rows before grouping/aggregation; HAVING filters groups after GROUP BY. Use WHERE for column-level conditions and HAVING for aggregate conditions (like SUM(), COUNT()).
Example SQL:
Pitfalls/Tips:
5) INNER vs LEFT vs RIGHT vs FULL OUTER JOIN (with NULL behavior)
Why it matters: Joins are the heart of relational querying.
Answer:
INNER JOIN: only matched rows.
LEFT JOIN: all left rows, NULLs for unmatched right.
RIGHT JOIN: mirror of left.
FULL OUTER JOIN: everything from both sides, NULLs where no match.
Understand NULL propagation and duplicate matches (one-to-many expands rows).
Example SQL:
Pitfalls/Tips:
Filters on right-table columns in LEFT JOIN should be placed in ON, not WHERE (to preserve null-extended rows).
Prefer LEFT JOIN over RIGHT; it’s easier to reason about.
6) DELETE vs TRUNCATE vs DROP
Why it matters: Data operations differ in speed, logging, and rollback.
Answer:
DELETE removes selected rows and logs each change; often rollback-able and triggers fire. TRUNCATE deallocates data pages (faster), resets identity (DB-specific), usually cannot filter, minimal logging. DROP removes the entire object (table + indexes + constraints). Plan carefully for permissions, locking, and recovery.
Example SQL:
Pitfalls/Tips:
7) NULL semantics (why NULL = NULL is UNKNOWN)
Why it matters: Misunderstood NULLs cause wrong results.
Answer:
NULL represents unknown. Comparisons with NULL yield UNKNOWN, not TRUE/FALSE, so use IS NULL / IS NOT NULL. Aggregates skip NULLs (except COUNT(*)). COALESCE/IFNULL can provide defaults, but use them carefully to avoid masking data quality issues.
Example SQL:
Pitfalls/Tips:
8) DISTINCT vs GROUP BY (and when to use which)
Why it matters: Both deduplicate but serve different purposes.
Answer:
DISTINCT removes duplicate rows in the projection. GROUP BY aggregates rows and can compute measures. If you only need unique pairs, DISTINCT is shorter; if you need aggregates per key, GROUP BY is correct.
Example SQL:
Pitfalls/Tips:
9) CHAR vs VARCHAR (and NVARCHAR)
Why it matters: Storage, performance, and correctness for text.
Answer:
CHAR(n) is fixed-length (pads spaces) and good for truly fixed codes; VARCHAR(n) is variable-length and preferred for most text. NVARCHAR stores Unicode (wider). Over-allocating length can bloat indexes; under-allocating truncates data.
Example SQL:
Pitfalls/Tips:
10) Aggregate functions & GROUP BY correctness
Why it matters: Ensures accurate metrics under groupings.
Answer:
Aggregates (SUM, AVG, COUNT, MIN, MAX) summarize rows. With GROUP BY, all non-aggregated selected columns must be grouped. COUNT(*) counts rows; COUNT(col) ignores NULLs. Always confirm granularity matches business logic.
Example SQL:
Pitfalls/Tips:
Mixing granularities (e.g., join to a detail table) can inflate aggregates.
Validate totals with a unit test query.
⚙️ Intermediate Level (11–20)
11) Subqueries: correlated vs non-correlated (and performance)
Why it matters: Common filter pattern; correlated subqueries can be slow.
Answer:
A non-correlated subquery is independent and runs once; a correlated subquery references the outer row and runs per row (optimizer may rewrite as join). Prefer EXISTS/JOIN for performance when appropriate, but subqueries can be the clearest expression.
Example SQL:
Pitfalls/Tips:
12) Window functions (analytics without collapsing rows)
Why it matters: Essential for ranking, running totals, time comparisons.
Answer:
Window (analytic) functions compute values across a window of rows relative to the current row, preserving row cardinality. Use PARTITION BY to define groups and ORDER BY to define order; optionally add frames (ROWS/RANGE BETWEEN ...) for moving windows.
Example SQL:
Pitfalls/Tips:
Default frame differs by DB (e.g., RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
For moving averages, specify frame explicitly.
13) UNION vs UNION ALL vs INTERSECT vs EXCEPT/MINUS
Why it matters: Set logic clarity and performance.
Answer:
UNION deduplicates; UNION ALL preserves duplicates (faster). INTERSECT returns common rows; EXCEPT/MINUS returns rows in left not in right. All require same number and compatible types. Prefer UNION ALL when you don’t need deduplication.
Example SQL:
Pitfalls/Tips:
14) EXISTS vs IN vs ANY/ALL
Why it matters: Correctness and performance in semi/anti joins.
Answer:
IN tests membership in a list or subquery result. EXISTS checks whether a subquery returns any row—often faster on large sets because it can short-circuit. ANY/ALL compare against set values. For anti-joins, use NOT EXISTS (safer with NULLs) rather than NOT IN with a nullable subquery.
Example SQL:
Pitfalls/Tips:
15) Indexes: clustered vs non-clustered, composite, selectivity, sargability
Why it matters: Index literacy = query speed.
Answer:
A clustered index defines the physical row order (one per table in many DBs); non-clustered are separate structures pointing to rows. Composite indexes follow leftmost prefix—order columns by selectivity and filtering usage. Write sargable predicates (Search ARGument ABLE) so the engine can use indexes (avoid wrapping indexed columns in functions).
Example SQL:
Pitfalls/Tips:
16) CTEs (WITH) vs subqueries vs temp tables
Why it matters: Readability, reuse, and optimizer behavior.
Answer:
CTEs improve readability and can be recursive. Many engines inline CTEs (like views) at optimization time; some materialize them depending on settings/size. Subqueries are concise but can become nested and hard to read. Temp tables can persist intermediate results across steps and allow indexing—handy for complex ETL or when the optimizer struggles.
Example SQL:
Pitfalls/Tips:
17) Stored procedures & parameterization (and SQL injection safety)
Why it matters: Reuse, security, and plan stability.
Answer:
Stored procedures encapsulate logic and promote parameterized execution (reducing SQL injection and improving plan reuse). Use strongly typed parameters, avoid dynamic SQL or sanitize/quote identifiers properly. Keep procedures idempotent where possible and log diagnostics.
Example SQL (T-SQL-ish):
Pitfalls/Tips:
18) CASE expressions (conditional logic)
Why it matters: Transform data without multiple passes.
Answer:
CASE evaluates conditions and returns derived values inline. Use it for bucketing, decoding codes, or guarded divisions. Prefer CASE WHEN ... THEN ... ELSE ... END over multiple queries.
Example SQL:
Pitfalls/Tips:
19) COALESCE vs ISNULL/IFNULL and NULL-safe logic
Why it matters: Null handling and portability.
Answer:
COALESCE(a,b,...) returns the first non-NULL and is ANSI standard; ISNULL/IFNULL are engine-specific 2-arg variants. Use COALESCE for portability and multiple fallbacks. Avoid defaulting NULLs in a way that hides data quality issues in analytics.
Example SQL:
Pitfalls/Tips:
20) RANK vs DENSE_RANK vs ROW_NUMBER (Top-N per group)
Why it matters: Essential for “top k” and dedup tasks.
Answer:
ROW_NUMBER() assigns a unique sequence; RANK() gives ties the same rank but leaves gaps; DENSE_RANK() removes gaps. For top-N per group, rank within PARTITION BY then filter.
Example SQL:
Pitfalls/Tips:
Use DENSE_RANK when you want all tied rows even if it exceeds N.
For speed, ensure a supporting index on partition/order columns.
🚀 Advanced / Senior Level (21–30)
21) OLTP vs OLAP (and schema design implications)
Why it matters: Context decides schema, indexing, and tuning.
Answer:
OLTP systems optimize for short, frequent transactions with strict ACID, highly normalized schemas, and selective indexes. OLAP (analytics) optimizes for large scans/aggregations, using star/snowflake schemas, partitioning, and fewer but broader indexes. ETL/ELT bridges them; columnar stores dominate OLAP for compression and vectorized scans.
Example SQL (star schema aggregate):
Pitfalls/Tips:
22) Query optimization: reading execution plans & sargability
Why it matters: Senior roles require diagnosing slow queries.
Answer:
Use EXPLAIN/EXPLAIN ANALYZE to inspect access paths (index seek/scan), join types (hash/merge/nested), and estimated vs actual rows. Rewrite non-sargable predicates (functions on columns, wildcards leading %) and ensure statistics are up to date. Consider covering indexes, predicate pushdown, and join order.
Example SQL:
Pitfalls/Tips:
23) Partitioning strategies (range, list, hash) & pruning
Why it matters: Big tables need physical strategies.
Answer:
Partitioning splits a table into segments on a key—range (e.g., by date), list (by country), hash (even distribution). Benefits: pruning (read only relevant partitions), faster maintenance (switch, rebuild), and parallelism. Choose a key aligned with common filters (e.g., order_date for time-series).
Example SQL (conceptual):
Pitfalls/Tips:
24) Materialized views vs views (refresh strategies)
Why it matters: Latency vs freshness trade-offs.
Answer:
A view is a saved query (virtual). A materialized view stores results physically, accelerating heavy aggregations at the cost of refresh complexity (on commit, on schedule, or manual). Use for expensive joins/aggregates with moderate freshness needs.
Example SQL:
Pitfalls/Tips:
25) Recursive CTEs (hierarchies, paths)
Why it matters: Organizational trees, dependencies, bill of materials.
Answer:
Recursive CTEs iterate until no new rows are found. Start with an anchor (root) query, then recursive member referencing the CTE. Useful for paths, depth, and rollups. Beware cycles; some DBs support CYCLE detection.
Example SQL (employees → managers):
Pitfalls/Tips:
26) Handling duplicates (find and delete while keeping one)
Why it matters: Data hygiene is a core responsibility.
Answer:
Find duplicates by grouping on business keys. To delete while keeping the “first”, rank rows per business key using ROW_NUMBER() and delete where rank > 1. Always validate with a SELECT first.
Example SQL:
Pitfalls/Tips:
27) Isolation levels & MVCC vs locking
Why it matters: Explains correctness under concurrency.
Answer:
Isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable (and Snapshot variants). Engines implement via locks or MVCC (multi-version concurrency control). Higher isolation reduces anomalies (dirty/non-repeatable/phantom reads) but costs throughput. Choose per workload.
Example (concept):
Pitfalls/Tips:
28) Deadlocks: detection, prevention, resolution
Why it matters: Senior engineers must debug these quickly.
Answer:
A deadlock occurs when two transactions hold locks the other needs. Engines detect and abort a victim. Prevent with consistent lock ordering, keeping transactions short, and indexing to narrow lock footprints. Consider NOWAIT/SKIP LOCKED patterns for queues.
Example SQL (anti-pattern):
Pitfalls/Tips:
29) SCD (Slowly Changing Dimensions) Types 1/2/3 in SQL
Why it matters: Common DW interview topic.
Answer:
Type 1: overwrite—no history.
Type 2: add new versioned row (valid_from/valid_to/is_current).
Type 3: store previous value in extra column(s).
Type 2 preserves full history and is most common for analytics.
Example SQL (Type 2 upsert concept):
Pitfalls/Tips:
Add a unique constraint on (business_key, is_current) or on valid date ranges (non-overlap).
Use surrogate keys for fact table joins.
30) “Second highest salary” — multiple correct patterns (pros/cons)
Why it matters: A classic that tests thinking, not memorization.
Answer:
You can solve it with MAX of < MAX, OFFSET, or DENSE_RANK. Prefer window functions for clarity; they handle ties explicitly. The set-based solutions scale better than correlated MAX for large data with proper indexes.
Example SQL (dense rank, returns ties):
Alternative (strict single value, ignoring ties):
Pitfalls/Tips:
✅ Bonus: Practical Patterns You’ll Be Asked To Demonstrate
Top-N per group → RANK()/ROW_NUMBER() with partition + filter.
Pagination → ORDER BY ... OFFSET ... FETCH NEXT ....
Anti-join → NOT EXISTS (NULL-safe).
Moving window metrics → explicit ROWS BETWEEN n PRECEDING AND CURRENT ROW.
Sargability → avoid functions on indexed columns in predicates.
🧭 Final Tips for Interviews
Always confirm granularity (row vs group) and NULL behavior.
Explain why a query is written a certain way (sargability, cardinality, join order).
If asked to “optimize,” talk about indexes, stats, plans, and I/O—then demonstrate with a rewritten query.
