Interview Q&A

Pick a topic — languages & databases — 100+ questions and answers in the center column.

Calculators Salary Emoji Website tools ← Listings

SQL

Interview questions & answers

Relational query language — joins, indexes, transactions, modeling.

102 questions

Question 1
What is SQL?
Structured Query Language for defining, querying, and manipulating relational data. ANSI/ISO standards exist; vendors add dialect extensions (MySQL, PostgreSQL, SQL Server).
Example code
```
SELECT 'hello' AS greeting
```
Question 2
DDL vs DML vs DCL?
DDL shapes schema (CREATE, ALTER, DROP). DML changes or reads rows (SELECT, INSERT, UPDATE, DELETE). DCL controls permissions (GRANT, REVOKE).
Example code
```
CREATE TABLE t (id INT PRIMARY KEY);
```
Question 3
What is a primary key?
Uniquely identifies a row; enforces NOT NULL + uniqueness. Surrogate keys (auto-increment/UUID) vs natural keys (email) is a modeling choice.
Example code
```
ALTER TABLE t ADD COLUMN x INT
```
Question 4
What is a foreign key?
Column(s) referencing another table’s primary/unique key—enforces referential integrity; ON DELETE/UPDATE actions define cascade behavior.
Example code
```
DROP TABLE IF EXISTS t
```
Question 5
UNIQUE constraint?
Allows one NULL in many SQL DBs per column; ensures no duplicate non-null values—multiple NULLs possible depending on dialect.
Example code
```
REFERENCES parent(id) ON DELETE CASCADE
```
Question 6
How does NULL behave in SQL?
Unknown—not equal to anything including NULL; use IS NULL / IS NOT NULL. Aggregates often ignore NULLs; three-valued logic in WHERE.
Example code
```
UNIQUE (email)
```
Question 7
INNER JOIN?
Returns rows where join predicate matches in both tables—most common join; omitting join condition can accidentally cross join.
Example code
```
WHERE col IS NULL
```
Question 8
LEFT OUTER JOIN?
Keeps all left rows; right side columns NULL when no match—use to find missing relationships.
Example code
```
SELECT * FROM a INNER JOIN b ON a.id = b.aid
```
Question 9
RIGHT and FULL OUTER JOIN?
RIGHT mirrors LEFT with table sides swapped. FULL keeps both sides with NULL fill-outs—supported in PostgreSQL; MySQL lacks native FULL (workarounds exist).
Example code
```
SELECT * FROM a LEFT JOIN b ON a.id = b.aid
```
Question 10
CROSS JOIN?
Cartesian product—every left row paired with every right row; useful with filters or small dimension tables.
Example code
```
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.aid
```
Question 11
WHERE vs HAVING?
WHERE filters rows before grouping. HAVING filters groups after GROUP BY—cannot reference non-aggregated columns not in GROUP BY (standard SQL).
Example code
```
SELECT * FROM a CROSS JOIN b
```
Question 12
GROUP BY rules?
Selected non-aggregated columns must appear in GROUP BY (or be functionally dependent in some engines). MySQL historically was permissive.
Example code
```
SELECT d FROM t GROUP BY d HAVING COUNT(*) > 1
```
Question 13
Common aggregate functions?
COUNT, SUM, AVG, MIN, MAX—COUNT(*) counts rows; COUNT(col) ignores NULLs in col.
Example code
```
SELECT d, COUNT(*) FROM t GROUP BY d
```
Question 14
What is a subquery?
Query nested in another—correlated if it references outer columns (often slower, row-by-row). Uncorrelated can be optimized as joins.
Example code
```
SELECT AVG(price) FROM orders
```
Question 15
EXISTS vs IN?
EXISTS stops at first match—often efficient for correlated checks. IN with subquery fine for small sets; beware NULLs inside IN lists.
Example code
```
SELECT * FROM t WHERE id IN (SELECT aid FROM x)
```
Question 16
UNION vs UNION ALL?
UNION deduplicates (sort/hash cost). UNION ALL concatenates—prefer when duplicates impossible or acceptable.
Example code
```
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM x WHERE x.tid = t.id)
```
Question 17
INSERT patterns?
Single row, multi-row VALUES, INSERT…SELECT—mind triggers and identity columns returning generated keys.
Example code
```
SELECT a FROM u UNION SELECT a FROM v
```
Question 18
UPDATE pitfalls?
Always scope with WHERE; forgetting it updates entire table. Joined updates vary by dialect.
Example code
```
SELECT a FROM u UNION ALL SELECT a FROM v
```
Question 19
DELETE vs TRUNCATE?
DELETE is row-by-row, can fire triggers, logged heavily. TRUNCATE fast reset (table-level locks, reset identities)—permissions and FK rules differ by engine.
Example code
```
INSERT INTO t (a,b) VALUES (1,'x')
```
Question 20
What is ACID?
Atomicity, Consistency, Isolation, Durability—transaction guarantees; engines implement with logging, locking, MVCC.
Example code
```
UPDATE t SET a = 2 WHERE id = 1
```
Question 21
Transaction isolation levels?
Read uncommitted / committed / repeatable read / serializable—balance dirty reads vs phantom reads vs performance.
Example code
```
DELETE FROM t WHERE id = 1
```
Question 22
What is a deadlock?
Two transactions wait on each other’s locks—DBMS picks a victim to roll back; apps should retry idempotently.
Example code
```
TRUNCATE TABLE t
```
Question 23
Why use indexes?
Speed lookups and joins; cost is slower writes and storage—wrong or redundant indexes hurt.
Example code
```
BEGIN;
UPDATE t SET x=1;
COMMIT
```
Question 24
B-tree (B+tree) index?
Default balanced tree for range and equality—leaf pages store row pointers or clustered data.
Example code
```
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
```
Question 25
Covering index?
Index contains all columns needed for query—avoids table lookups (index-only scan).
Example code
```
-- deadlock: pick victim, app retries
```
Question 26
Composite index column order?
Leftmost prefix rule—put selective, equality-filtered columns first; order matters for range scans.
Example code
```
CREATE INDEX idx_t_a ON t(a)
```
Question 27
First through third normal form?
1NF: atomic values. 2NF: no partial dependency on composite keys. 3NF: no transitive dependency on non-key attributes.
Example code
```
CREATE INDEX idx_t_ab ON t(a,b)
```
Question 28
When to denormalize?
Read-heavy reporting, caching aggregates, controlled duplication—trade space and update complexity for speed.
Example code
```
SELECT a,b FROM t WHERE a = 1 -- covering if index matches
```
Question 29
What is a view?
Stored named query—simplifies access and can enforce column-level abstraction; not always materialized.
Example code
```
CREATE INDEX idx_t_abc ON t(a,b,c)
```
Question 30
Materialized view?
Persisted query results—refresh on schedule or trigger; great for analytics; naming differs (PostgreSQL matview, SQL Server indexed view).
Example code
```
CREATE VIEW v AS SELECT id,a FROM t
```
Question 31
Common Table Expression (CTE)?
WITH clause names a subquery—improves readability; recursive CTEs traverse hierarchies (standard SQL).
Example code
```
WITH cte AS (SELECT * FROM t) SELECT * FROM cte
```
Question 32
Window functions?
OVER (PARTITION BY … ORDER BY …)—compute running totals, ranks without collapsing rows like GROUP BY.
Example code
```
SELECT id, SUM(x) OVER (PARTITION BY g ORDER BY id) AS r FROM t
```
Question 33
ROW_NUMBER vs RANK vs DENSE_RANK?
ROW_NUMBER unique sequential. RANK ties skip numbers. DENSE_RANK ties without gaps.
Example code
```
SELECT ROW_NUMBER() OVER (ORDER BY x) FROM t
```
Question 34
COALESCE and NULLIF?
COALESCE returns first non-NULL. NULLIF(a,b) returns NULL when equal—handy for divide-by-zero guards.
Example code
```
SELECT COALESCE(a,0), NULLIF(b,0) FROM t
```
Question 35
CASE expression?
Conditional scalar logic in SQL—searched CASE vs simple CASE; use in SELECT, ORDER BY, aggregates.
Example code
```
SELECT CASE WHEN x>0 THEN 1 ELSE 0 END FROM t
```
Question 36
Prepared statements?
Parameterized queries separate SQL structure from data—primary defense against injection alongside validation.
Example code
```
PREPARE s AS SELECT * FROM t WHERE id = $1
```
Question 37
How to avoid SQL injection?
Bind parameters, least-privilege DB users, ORM/query builders with binding, never concatenate untrusted input into SQL text.
Example code
```
-- use bound params, never concat user input
```
Question 38
What is a database schema?
Logical container for tables, views, indexes, and security objects—meaning of 'schema' vs 'database' differs (PostgreSQL schema inside a database; MySQL often treats them similarly).
Example code
```
CREATE SCHEMA IF NOT EXISTS app
```
Question 39
What is CHECK constraint (SQL)?
Row-level boolean predicate enforced on insert/update—portable integrity beyond types.
Example code
```
-- CHECK constraint
SELECT 1
```
Question 40
What is DEFAULT values (SQL)?
Column defaults reduce application branching; can use functions (e.g., now()) per dialect.
Example code
```
-- DEFAULT values
SELECT 1
```
Question 41
What is Sequences (SQL)?
Monotonic number generators—SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL; gaps after rollback are normal.
Example code
```
-- Sequences
SELECT 1
```
Question 42
What is UUID keys (SQL)?
Random/globally unique keys—avoid hot index pages with UUIDv7 or hash prefixes in some designs.
Example code
```
-- UUID keys
SELECT 1
```
Question 43
What is Partial index (SQL)?
Index with WHERE predicate—smaller, targeted (PostgreSQL); other engines use filtered equivalents or workarounds.
Example code
```
-- Partial index
SELECT 1
```
Question 44
What is Expression index (SQL)?
Index on function or expression—speeds queries matching that expression exactly.
Example code
```
-- Expression index
SELECT 1
```
Question 45
What is Hash index (SQL)?
O(1) equality in some engines—no range scans; use case-specific.
Example code
```
-- Hash index
SELECT 1
```
Question 46
What is Clustered index (SQL)?
Table row order follows index—one per table typically (SQL Server clustered, InnoDB PK).
Example code
```
-- Clustered index
SELECT 1
```
Question 47
What is Nonclustered index (SQL)?
Separate structure pointing to rows—most secondary indexes.
Example code
```
-- Nonclustered index
SELECT 1
```
Question 48
What is Index selectivity (SQL)?
Low cardinality columns alone may not help—combine with selective predicates.
Example code
```
-- Index selectivity
SELECT 1
```
Question 49
What is Covering include columns (SQL)?
SQL Server INCLUDE columns add payload without sort key overhead in nonclustered indexes.
Example code
```
-- Covering include columns
SELECT 1
```
Question 50
What is Query planner (SQL)?
Cost-based optimizer chooses joins and indexes—statistics quality matters; ANALYZE/UPDATE STATISTICS.
Example code
```
-- Query planner
SELECT 1
```
Question 51
What is Histograms and stats (SQL)?
Column distribution stats help estimate cardinality—stale stats cause bad plans.
Example code
```
-- Histograms and stats
SELECT 1
```
Question 52
What is Nested loop join (SQL)?
Row-by-row probe—good for small inputs with index.
Example code
```
-- Nested loop join
SELECT 1
```
Question 53
What is Hash join (SQL)?
Build hash table on one side—good for equi-joins of larger sets without helpful indexes.
Example code
```
-- Hash join
SELECT 1
```
Question 54
What is Merge join (SQL)?
Both sides sorted—efficient for large pre-sorted or index-ordered inputs.
Example code
```
-- Merge join
SELECT 1
```
Question 55
What is Anti-join pattern (SQL)?
NOT EXISTS often clearer and optimizable vs NOT IN with NULL pitfalls.
Example code
```
-- Anti-join pattern
SELECT 1
```
Question 56
What is Semi-join (SQL)?
EXISTS / IN subqueries that only test presence—planner may rewrite to semi-join operators.
Example code
```
-- Semi-join
SELECT 1
```
Question 57
What is PIVOT / UNPIVOT (SQL)?
Rotate rows to columns and back—syntax varies; CASE aggregates are portable fallback.
Example code
```
-- PIVOT / UNPIVOT
SELECT 1
```
Question 58
What is MERGE statement (SQL)?
Upsert pattern in one statement—powerful but dialect-specific behavior and concurrency caveats.
Example code
```
-- MERGE statement
SELECT 1
```
Question 59
What is RETURNING clause (SQL)?
Return inserted/updated/deleted rows in same statement—PostgreSQL, modern MySQL.
Example code
```
-- RETURNING clause
SELECT 1
```
Question 60
What is UPSERT dialects (SQL)?
ON CONFLICT DO UPDATE (PostgreSQL), ON DUPLICATE KEY UPDATE (MySQL)—learn conflict targets.
Example code
```
-- UPSERT dialects
SELECT 1
```
Question 61
What is Temporal tables (SQL)?
System-versioned history—SQL Server temporal; other DBs use triggers or audit tables.
Example code
```
-- Temporal tables
SELECT 1
```
Question 62
What is Soft delete (SQL)?
deleted_at flag instead of physical DELETE—complicates unique constraints; partial unique indexes help.
Example code
```
-- Soft delete
SELECT 1
```
Question 63
What is Optimistic locking (SQL)?
Version column checked on UPDATE—detect concurrent writes without long DB locks.
Example code
```
-- Optimistic locking
SELECT 1
```
Question 64
What is Pessimistic locking (SQL)?
SELECT … FOR UPDATE—holds row locks until transaction end; risk deadlocks.
Example code
```
-- Pessimistic locking
SELECT 1
```
Question 65
What is Read replicas (SQL)?
Async followers for scale-out reads—replication lag means stale reads; routing logic required.
Example code
```
-- Read replicas
SELECT 1
```
Question 66
What is Sharding (SQL)?
Horizontal partition by key—application routing, cross-shard queries expensive.
Example code
```
-- Sharding
SELECT 1
```
Question 67
What is Partitioning (SQL)?
Table split by range/list/hash—pruning improves scans; manage partition maintenance.
Example code
```
-- Partitioning
SELECT 1
```
Question 68
What is Columnar storage (SQL)?
Analytics engines store by column—great compression and aggregates; OLTP row stores differ.
Example code
```
-- Columnar storage
SELECT 1
```
Question 69
What is Star schema (SQL)?
Fact surrounded by dimensions—classic warehouse modeling for BI.
Example code
```
-- Star schema
SELECT 1
```
Question 70
What is Snowflake schema (SQL)?
Normalized dimensions—saves space, more joins than star.
Example code
```
-- Snowflake schema
SELECT 1
```
Question 71
What is Slowly changing dimensions (SQL)?
Type 1 overwrite, Type 2 history rows, Type 3 limited attributes—warehouse modeling pattern.
Example code
```
-- Slowly changing dimensions
SELECT 1
```
Question 72
What is ETL vs ELT (SQL)?
Transform before load vs load raw then transform in warehouse—modern warehouses favor ELT with SQL transforms.
Example code
```
-- ETL vs ELT
SELECT 1
```
Question 73
What is OLTP vs OLAP (SQL)?
Transactional many small writes vs analytical large scans—different tuning and engines.
Example code
```
-- OLTP vs OLAP
SELECT 1
```
Question 74
What is EXPLAIN plans (SQL)?
Read cost estimates and actuals—find sequential scans, bad nested loops, missing indexes.
Example code
```
-- EXPLAIN plans
SELECT 1
```
Question 75
What is Hinting (SQL)?
OPTIMIZER hints force plans—last resort when stats can’t be fixed; hurts portability.
Example code
```
-- Hinting
SELECT 1
```
Question 76
What is Connection pooling (SQL)?
Reuse DB connections—set pool size vs DB max_connections and app instances.
Example code
```
-- Connection pooling
SELECT 1
```
Question 77
What is Least privilege (SQL)?
App role without DDL, minimal table grants—limits blast radius of bugs.
Example code
```
-- Least privilege
SELECT 1
```
Question 78
What is Row-level security (SQL)?
Policy filters rows per user—PostgreSQL RLS; SQL Server RLS; app must still parameterize.
Example code
```
-- Row-level security
SELECT 1
```
Question 79
What is Encryption at rest (SQL)?
TDE / volume encryption—protects disks; TLS protects data in flight.
Example code
```
-- Encryption at rest
SELECT 1
```
Question 80
What is Backup types (SQL)?
Full, incremental, differential, PITR with WAL/binlog—test restores, not just backups.
Example code
```
-- Backup types
SELECT 1
```
Question 81
What is Point-in-time recovery (SQL)?
Replay logs to a timestamp—requires continuous archiving and base backup.
Example code
```
-- Point-in-time recovery
SELECT 1
```
Question 82
What is Two-phase commit (SQL)?
Distributed transaction prepare/commit across databases—complex, avoid when possible.
Example code
```
-- Two-phase commit
SELECT 1
```
Question 83
What is CAP in practice (SQL)?
Distributed systems pick consistency vs availability under partition—SQL clusters still face network splits.
Example code
```
-- CAP in practice
SELECT 1
```
Question 84
What is JSON in SQL (SQL)?
JSON/JSONB columns with query operators—convenient but easy to skip indexing; validate shape in app.
Example code
```
-- JSON in SQL
SELECT 1
```
Question 85
What is Full-text search (SQL)?
CONTAINS, to_tsvector, MATCH—better than LIKE '%x%' for large text; specialized engines exist.
Example code
```
-- Full-text search
SELECT 1
```
Question 86
What is Spatial types (SQL)?
Geometry/geography with indexes—PostGIS, MySQL spatial; know SRID and precision.
Example code
```
-- Spatial types
SELECT 1
```
Question 87
What is Window framing (SQL)?
ROWS BETWEEN, RANGE BETWEEN—control sliding aggregates; off-by-one bugs common.
Example code
```
-- Window framing
SELECT 1
```
Question 88
What is LATERAL join (SQL)?
Correlate subquery per left row (PostgreSQL, SQL Server)—powerful for top-N per group.
Example code
```
-- LATERAL join
SELECT 1
```
Question 89
What is Recursive CTE depth (SQL)?
Hierarchies and graphs in SQL—mind cycle detection and depth limits.
Example code
```
-- Recursive CTE depth
SELECT 1
```
Question 90
What is SQL:1999 vs :2003 (SQL)?
Window functions standardized later—verify dialect support for modern features.
Example code
```
-- SQL:1999 vs :2003
SELECT 1
```
Question 91
What is Information schema (SQL)?
Portable metadata views—tables, columns, constraints discovery across engines.
Example code
```
-- Information schema
SELECT 1
```
Question 92
What is Dialect portability (SQL)?
Test on target DBs; avoid silent type coercion differences and LIMIT/OFFSET quirks on old versions.
Example code
```
-- Dialect portability
SELECT 1
```
Question 93
What is GENERATED columns (SQL)?
Computed persisted or virtual columns—keep logic in DB vs app; indexing rules vary.
Example code
```
-- GENERATED columns
SELECT 1
```
Question 94
What is Triggers (SQL)?
BEFORE/AFTER row or statement—hidden side effects complicate debugging; prefer explicit app logic when possible.
Example code
```
-- Triggers
SELECT 1
```
Question 95
What is Stored procedures (SQL)?
Server-side routines—reduce round trips but tie logic to one engine’s language and deployment.
Example code
```
-- Stored procedures
SELECT 1
```
Question 96
What is Cursor pitfalls (SQL)?
Server-side iterators hold locks and memory—avoid large cursor loops in OLTP; batch or set-based SQL instead.
Example code
```
-- Cursor pitfalls
SELECT 1
```
Question 97
What is Bulk load (SQL)?
COPY / LOAD DATA / BULK INSERT—disable indexes or use minimal logging modes per engine for speed with care.
Example code
```
-- Bulk load
SELECT 1
```
Question 98
What is EXPLAIN ANALYZE (SQL)?
Execute and measure actual timings—reveals misestimates vs plain EXPLAIN plans.
Example code
```
-- EXPLAIN ANALYZE
SELECT 1
```
Question 99
What is Table statistics job (SQL)?
Scheduled ANALYZE/UPDATE STATISTICS—keeps planner accurate after bulk changes.
Example code
```
-- Table statistics job
SELECT 1
```
Question 100
What is Surrogate vs natural keys (SQL)?
Surrogate (opaque id) stable under renames; natural keys readable but brittle when business rules change.
Example code
```
-- Surrogate vs natural keys
SELECT 1
```
Question 101
What is Multi-column FK (SQL)?
Composite foreign keys match composite parent keys—order and null rules must align.
Example code
```
-- Multi-column FK
SELECT 1
```
Question 102
What is CHECK with subqueries (SQL)?
Some engines disallow subqueries in CHECK—use triggers or constraints in newer versions.
Example code
```
-- CHECK with subqueries
SELECT 1
```

What is SQL?

DDL vs DML vs DCL?

What is a primary key?

What is a foreign key?

UNIQUE constraint?

How does NULL behave in SQL?

INNER JOIN?

LEFT OUTER JOIN?

RIGHT and FULL OUTER JOIN?

CROSS JOIN?

WHERE vs HAVING?

GROUP BY rules?

Common aggregate functions?

What is a subquery?

EXISTS vs IN?

UNION vs UNION ALL?

INSERT patterns?

UPDATE pitfalls?

DELETE vs TRUNCATE?

What is ACID?

Transaction isolation levels?

What is a deadlock?

Why use indexes?

B-tree (B+tree) index?

Covering index?

Composite index column order?

First through third normal form?

When to denormalize?

What is a view?

Materialized view?

Common Table Expression (CTE)?

Window functions?

ROW_NUMBER vs RANK vs DENSE_RANK?

COALESCE and NULLIF?

CASE expression?

Prepared statements?

How to avoid SQL injection?

What is a database schema?

What is CHECK constraint (SQL)?

What is DEFAULT values (SQL)?

What is Sequences (SQL)?

What is UUID keys (SQL)?

What is Partial index (SQL)?

What is Expression index (SQL)?

What is Hash index (SQL)?

What is Clustered index (SQL)?

What is Nonclustered index (SQL)?

What is Index selectivity (SQL)?

What is Covering include columns (SQL)?

What is Query planner (SQL)?

What is Histograms and stats (SQL)?

What is Nested loop join (SQL)?

What is Hash join (SQL)?

What is Merge join (SQL)?

What is Anti-join pattern (SQL)?

What is Semi-join (SQL)?

What is PIVOT / UNPIVOT (SQL)?

What is MERGE statement (SQL)?

What is RETURNING clause (SQL)?

What is UPSERT dialects (SQL)?

What is Temporal tables (SQL)?

What is Soft delete (SQL)?

What is Optimistic locking (SQL)?

What is Pessimistic locking (SQL)?

What is Read replicas (SQL)?

What is Sharding (SQL)?

What is Partitioning (SQL)?

What is Columnar storage (SQL)?

What is Star schema (SQL)?

What is Snowflake schema (SQL)?

What is Slowly changing dimensions (SQL)?

What is ETL vs ELT (SQL)?

What is OLTP vs OLAP (SQL)?

What is EXPLAIN plans (SQL)?

What is Hinting (SQL)?

What is Connection pooling (SQL)?

What is Least privilege (SQL)?

What is Row-level security (SQL)?

What is Encryption at rest (SQL)?

What is Backup types (SQL)?