SQL databases are relational, structured, and enforce a predefined schema. They store data in tables composed of rows and columns, leveraging ACID properties to guarantee transactional consistency.

NoSQL databases are non-relational, schema-flexible, and designed for horizontal scalability across distributed clusters. They utilize dynamic data models including Key-Value pairs, Document stores (JSON/BSON), Wide-Column stores, and Property Graphs.

SQL vs NoSQL Architectural Comparison

Q1: What is the difference between SQL (Relational) and NoSQL (Non-Relational) databases?

SQL statements are categorized into five sublanguages based on operational scope:

- **DDL (Data Definition Language)**: Defines and alters schema structures. Commands: `CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `RENAME`.
- **DML (Data Manipulation Language)**: Modifies stored data rows. Commands: `INSERT`, `UPDATE`, `DELETE`, `MERGE`.
- **DQL (Data Query Language)**: Retrieves records from schemas. Command: `SELECT`.
- **DCL (Data Control Language)**: Manages permissions and administrative privileges. Commands: `GRANT`, `REVOKE`.
- **TCL (Transaction Control Language)**: Manages atomic transactional changes. Commands: `COMMIT`, `ROLLBACK`, `SAVEPOINT`.

Q2: What are DDL, DML, DCL, TCL, and DQL in SQL?

Keys enforce entity integrity and relationship constraints within relational schemas:

- **Primary Key**: Uniquely identifies each record in a table. Enforces non-nullability and uniqueness. A table can have only one Primary Key (which can be composite).
- **Unique Key**: Ensures all values in a column or column group are distinct. Allows one or more `NULL` values (database dependent).
- **Foreign Key**: Maintains referential integrity by linking a column in a child table to the Primary Key or Unique Key of a parent table.

Q3: What are the differences between Primary Key, Foreign Key, and Unique Key constraints?

Although an SQL query is written starting with `SELECT`, the database engine processes clauses in a distinct logical order to construct result sets.

Logical SQL Execution Pipeline

Q4: What is the logical execution order of an SQL query?

Combining WHERE and HAVING in a Single Query

The fundamental difference lies in aggregation pipeline execution timing:

- **WHERE**: Filters individual records **before** group aggregations occur. It cannot evaluate aggregate functions like `SUM()`, `AVG()`, or `COUNT()`.
- **HAVING**: Filters aggregated groups **after** the `GROUP BY` step finishes computing group statistics.

Q5: What is the difference between the WHERE and HAVING clauses?

JOIN clauses combine columns from one or more tables using related attributes:

- **INNER JOIN**: Returns only matching records existing in both tables.
- **LEFT (OUTER) JOIN**: Returns all records from the left table and matching records from the right table. Unmatched right values return `NULL`.
- **RIGHT (OUTER) JOIN**: Returns all records from the right table and matching left records.
- **FULL (OUTER) JOIN**: Returns all records when a match exists in either left or right table.
- **CROSS JOIN**: Produces the Cartesian product of both tables (N × M rows).

Q6: Explain INNER, LEFT, RIGHT, FULL, and CROSS JOINs with examples.

These commands clear records or schema definitions with varying performance and transactional characteristics.

DELETE vs TRUNCATE vs DROP

Q7: What is the difference between DELETE, TRUNCATE, and DROP commands?

Handling NULL safely with COALESCE

`NULL` in SQL represents an unknown or missing value, not zero or an empty string (`''`).

Because `NULL` is unknown, direct equality comparisons (`field = NULL` or `NULL = NULL`) evaluate to `UNKNOWN`, not `TRUE` or `FALSE`. SQL utilizes **Three-Valued Logic (TRUE, FALSE, UNKNOWN)**.

To check for missing values, use `IS NULL` or `IS NOT NULL`. Functions like `COALESCE(col, fallback)` or `NVL()` replace `NULL` values with predefined defaults.

Q8: How does SQL evaluate NULL values and Three-Valued Logic?

`GROUP BY` collapses set rows sharing common values into summary groups. Aggregate functions (`SUM()`, `AVG()`, `COUNT()`, `MIN()`, `MAX()`) summarize each group.

### NULL Handling in Aggregates
- `COUNT(*)` counts all rows, including rows containing `NULL` values.
- `COUNT(column_name)` counts only non-null values in that column.
- `SUM()`, `AVG()`, `MIN()`, and `MAX()` automatically ignore `NULL` values during calculation.

Q9: How do GROUP BY and Aggregate Functions work, and how are NULLs handled?

High-Performance Set Merging

Both operators merge result sets from multiple `SELECT` queries into a single output set. They require matching column counts and compatible data types across positional indices.

- **UNION**: Combines result sets and performs an implicit duplicate elimination pass (sorting or hashing output records).
- **UNION ALL**: Concatenates result sets directly without deduplication, preserving all records.

Q10: What is the difference between UNION and UNION ALL?

Correlated Subquery Example

A subquery is an inner query nested within an outer `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement.

- **Scalar Subquery**: Returns a single value (1 row, 1 column). Can be used anywhere expressions are allowed.
- **Multi-Row Subquery**: Returns multiple rows in a single column. Used with operators like `IN`, `ANY`, or `ALL`.
- **Correlated Subquery**: References columns from the outer query. The subquery executes iteratively once for every row processed by the outer query, which can impair performance on large datasets.

Q11: What are the types of subqueries (Scalar, Multi-row, Correlated)?

- **IN**: Evaluates full inner subquery result lists before matching values against outer query expressions. If the subquery set contains `NULL` values, `NOT IN` queries can evaluate to `UNKNOWN` and return zero rows.
- **EXISTS**: Uses semi-join short-circuit logic. It checks for the existence of matching rows and returns `TRUE` as soon as a single match is found, ignoring subsequent rows.

Q12: What is the difference between EXISTS and IN operators?

- **Standard View**: A virtual table backed by a saved SQL query definition. It stores no physical data on disk. When queried, the underlying SQL expression executes in real time.
- **Materialized View**: A persisted table structure containing physical query execution results. It dramatically speeds up complex analytical aggregation workloads, but requires periodic manual or triggered refresh cycles (`REFRESH MATERIALIZED VIEW`) to stay in sync with base table changes.

Q13: What is the difference between a Standard View and a Materialized View?

Recursive CTE for Organizational Hierarchy Retrieval

A Common Table Expression (CTE) is a temporary, named result set defined within the execution scope of a single `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement using the `WITH` clause.

CTEs improve code readability compared to deeply nested subqueries and support **Recursive CTEs**, which enable hierarchical tree traversals (e.g., organizational charts or bill-of-materials structures).

Q14: What is a Common Table Expression (CTE) and when should you use it?

Comparing Employee Salary against Department Average

Window functions perform calculations across a set of table rows related to the current query row using the `OVER()` clause. 

Unlike `GROUP BY`, which collapses grouped rows into a single summary output row, window functions compute aggregate or positional values while **preserving individual row identities**.

Q15: What are Window Functions and how do they differ from GROUP BY?

All three are ranking window functions, but they handle tied values differently:

- `ROW_NUMBER()`: Assigns a unique, sequential integer to every row (1, 2, 3, 4, 5). Ties receive arbitrary unique numbers.
- `RANK()`: Assigns identical rank values to tied rows, but **skips subsequent ranks** to reflect tie counts (1, 2, 2, 4, 5).
- `DENSE_RANK()`: Assigns identical rank values to tied rows without skipping subsequent numbers (1, 2, 2, 3, 4).

Ranking Behavior Comparison (Tied Values at 100)

Q16: What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Robust Nth Highest Salary Query

There are two common ways to solve this classic interview question: using `DENSE_RANK()` or using an offset subquery.

Q17: How do you find the Nth highest salary in a table?

Calculating Month-over-Month Sales Growth

`LEAD()` and `LAG()` are positional window functions used to inspect adjacent row data relative to the current row without performing a self-join.

- `LAG(col, offset, default)`: Accesses column values from preceding rows.
- `LEAD(col, offset, default)`: Accesses column values from subsequent rows.

Q18: How do LEAD() and LAG() window functions work?

Safely Removing Duplicates with ROW_NUMBER()

To remove duplicates while preserving one original record, use a CTE containing `ROW_NUMBER()` partitioned by duplicate key attributes.

Q19: How do you identify and remove duplicate rows from a table?

Normalization structures database schemas to minimize data redundancy and prevent insertion, update, and deletion anomalies.

- **1NF (First Normal Form)**: Ensures atomic column values (no multi-valued lists or nested sets) and unique row keys.
- **2NF (Second Normal Form)**: Satisfies 1NF and removes partial dependencies (non-key attributes must depend on the *entire* primary key).
- **3NF (Third Normal Form)**: Satisfies 2NF and removes transitive dependencies (non-key attributes must depend *only* on primary key attributes).
- **BCNF (Boyce-Codd Normal Form)**: A stricter variant of 3NF where every determinant must be a candidate key.

Q20: What is Database Normalization and its forms (1NF, 2NF, 3NF, BCNF)?

Denormalization intentionally introduces redundant data or pre-aggregated summary columns into a normalized schema.

While normalization optimizes data integrity and write performance, denormalization optimizes **read latency** in high-scale analytical databases (OLAP) or read-heavy applications by minimizing expensive `JOIN` operations.

Q21: What is Denormalization and when should it be applied?

ACID guarantees that database transactions are executed reliably:

- **Atomicity**: 'All-or-Nothing' execution. If any statement in a transaction fails, all changes are fully rolled back.
- **Consistency**: Ensures transactions transition the database from one valid state to another, preserving all schema constraints.
- **Isolation**: Concurrent transactions execute without interfering with one another's uncommitted state.
- **Durability**: Once a transaction commits, its changes persist permanently on disk even during system crashes.

Q22: Explain the ACID properties of relational database transactions.

SQL defines four standard transaction isolation levels to balance concurrency against data consistency anomalies.

### Concurrency Read Anomalies
1. **Dirty Read**: Reading uncommitted changes made by another concurrent transaction.
2. **Non-Repeatable Read**: Re-reading the same record yields modified values because another committed transaction updated it.
3. **Phantom Read**: Re-running a range query returns newly inserted rows committed by another transaction.

Isolation Levels & Permitted Anomalies

Q23: What are Transaction Isolation Levels and Read Anomalies?

- **B-Tree Index**: A self-balancing search tree structure that keeps indexed data sorted. It efficiently processes exact equality matches (`=`), range scans (`>`, `<`, `BETWEEN`), and `ORDER BY` operations in logarithmic time ($O(\log N)$).
- **Hash Index**: Uses hash tables to map key values to row locations. It provides constant-time $O(1)$ lookups for equality checks (`=`), but **cannot support range queries** or ordering operations.

Q24: How do B-Tree and Hash Indexes work internally?

- **Clustered Index**: Determines the physical storage order of data rows on disk. Because rows can only be ordered one way, a table can have **only one Clustered Index** (typically assigned to the Primary Key).
- **Non-Clustered Index**: A secondary structure stored separately from data rows. It contains indexed column keys and pointers (row IDs or clustered key values) that reference the underlying table rows.

Q25: What is the difference between Clustered and Non-Clustered Indexes?

Creating a Covering Index with Included Columns

- **Index Seek**: The database engine uses index tree structures to jump directly to specific matching rows. Highly efficient.
- **Index Scan**: The engine traverses the entire index leaf structure row by row. Less efficient than a seek.
- **Covering Index**: A non-clustered index that includes all columns referenced by a query (filtering, joining, and selected output columns). It allows the engine to return results directly from the index without reading table data pages.

Q26: What is a Covering Index, and how does Index Seek differ from Index Scan?

A composite index spans multiple columns (e.g., `INDEX (col1, col2, col3)`).

### The Leftmost Prefix Rule
The database query planner can use a composite index only if query predicate conditions match column combinations starting from the **leftmost column** onward.

- `WHERE col1 = x` $\rightarrow$ **Uses Index**
- `WHERE col1 = x AND col2 = y` $\rightarrow$ **Uses Index**
- `WHERE col2 = y AND col3 = z` $\rightarrow$ **Cannot Use Index** (Missing leftmost prefix `col1`)

Q27: What is a Composite Index and the Leftmost Prefix Rule?

Converting Non-SARGable Queries into SARGable Queries

**SARGable** stands for **S**earch **A**rgument **A**ble. A query is SARGable if the database engine can leverage an index to speed up execution.

### Anti-Patterns That Invalidate Index Usage
1. **Wrapping indexed columns in functions**: `WHERE YEAR(created_at) = 2026` (Not SARGable).
2. **Wildcards at the start of LIKE patterns**: `WHERE name LIKE '%smith'` (Not SARGable).
3. **Implicit Type Conversion**: Comparing string columns against integer values directly.
4. **Arithmetic on indexed columns**: `WHERE salary * 12 > 100000` (Not SARGable).

Q28: What makes a query SARGable, and what invalidates index usage?

The `EXPLAIN` (or `EXPLAIN ANALYZE`) command reveals the execution steps chosen by the database query optimizer.

### Key Metrics to Analyze
- **Scan Types**: `Seq Scan` / `Table Scan` (slow) vs `Index Seek` / `Index Scan` (fast).
- **Join Algorithms**: `Nested Loop` (good for small sets), `Hash Join` (good for large unsorted sets), `Merge Join` (efficient for pre-sorted sets).
- **Cost Estimates**: Startup and total cost measures calculated by database statistics engines.
- **Actual Time / Rows**: Real execution timings provided by `EXPLAIN ANALYZE`.

Q29: How do you analyze an SQL query execution plan using EXPLAIN?

A **deadlock** occurs when two or more concurrent transactions hold locks on separate resources and each waits to acquire a lock held by the other, creating a circular dependency.

Circular Deadlock Condition

Q30: What is a Database Deadlock, and how can it be prevented?

- **Stored Procedure**: A compiled collection of SQL queries designed to execute administrative business routines. Can accept input/output parameters, perform full DML operations, control transactions (`COMMIT`/`ROLLBACK`), and return zero or multiple result sets. Cannot be called inside a `SELECT` statement.
- **User-Defined Function (UDF)**: A reusable calculation routine designed to compute and return a single value or table. Must be deterministic or safe, cannot alter transaction states (`COMMIT` is forbidden), and can be used directly inside `SELECT`, `WHERE`, and `JOIN` clauses.

Q31: What is the difference between a Stored Procedure and a User-Defined Function?

A **Trigger** is a procedural routine that executes automatically when specific schema events (`INSERT`, `UPDATE`, `DELETE`) occur on a target table.

### Common Use Cases
- Audit logging and tracking record modifications over time.
- Enforcing complex domain validation rules across relational boundaries.

### Operational Drawbacks
- Hidden side effects that complicate debugging and codebase maintenance.
- Row-level triggers execute iteratively, increasing latency during bulk batch operations.

Q32: What are Database Triggers, and what are their drawbacks?

- **Partitioning (Vertical/Horizontal)**: Splitting a large table into smaller sub-tables **within a single database instance**. The application interacts with the table normally while the database engine routes queries to specific partitions automatically.
- **Sharding**: Horizontally distributing data across **multiple independent physical server nodes**. Sharding requires network-level routing and cross-node coordination, enabling applications to scale out beyond single-node hardware limits.

Q33: What is the difference between Database Partitioning and Sharding?

- **Pessimistic Locking**: Assumes lock conflicts are likely. It locks targeted database rows immediately upon selection using statements like `SELECT ... FOR UPDATE`, blocking other concurrent operations until the lock releases.
- **Optimistic Locking**: Assumes conflicts are rare. It avoids holding record locks during read windows. Instead, it checks a version column or timestamp during writes (`WHERE id = 1 AND version = 5`) to confirm the data wasn't modified concurrently.

Q34: What is the difference between Optimistic and Pessimistic Locking?

- **Star Schema**: A central **Fact Table** surrounded by denormalized **Dimension Tables**. It simplifies query design and speeds up read analytical performance by minimizing required JOIN operations.
- **Snowflake Schema**: A variant where dimension tables are normalized into sub-dimension entities. It reduces data redundancy, but requires more complex multi-table JOIN queries.

Q35: What is the difference between a Star Schema and a Snowflake Schema in Data Warehousing?

Preventing Division-by-Zero using NULLIF and COALESCE

- `COALESCE(val1, val2, ...)`: Evaluates arguments in order and returns the **first non-null value** in the parameter list.
- `NULLIF(exp1, exp2)`: Compares two expressions. Returns `NULL` if `exp1 = exp2`; otherwise returns `exp1`.

Q36: How do COALESCE() and NULLIF() functions work?

Calculating Running Totals Partitioned by Year

Calculate a running total by using the `SUM()` aggregate window function with an explicit `ORDER BY` clause.

Q37: How do you calculate a Running Total (Cumulative Sum) in SQL?

Cross-Platform Pivoting via Conditional Aggregation

A **PIVOT** converts unique row values into individual query output columns (transposing data from vertical to horizontal). An **UNPIVOT** converts columns back into row records.

While databases like SQL Server support native `PIVOT` keywords, cross-database code uses conditional aggregation (`CASE WHEN`).

Q38: How do you perform PIVOT and UNPIVOT transformations in SQL?

Finding Employees and Their Managers via Self-JOIN

A **Self-JOIN** joins a table to itself. It is commonly used to query hierarchical data stored in a single table, such as employee-manager relationships or sequential state updates.

Q39: What is a Self-JOIN and when is it used?

`APPLY` operators (available in SQL Server and Oracle, or implemented via lateral joins in PostgreSQL) join a table to a table-valued function or correlated inline subquery evaluated for every outer row.

- **CROSS APPLY**: Functions like an `INNER JOIN`. Returns outer rows only if the invoked inline function produces output rows.
- **OUTER APPLY**: Functions like a `LEFT JOIN`. Preserves outer rows even if the inline function returns no rows, filling missing fields with `NULL`.

Q40: What is the difference between CROSS APPLY and OUTER APPLY?

Islands Grouping Solution

The **Gaps and Islands** problem involves identifying continuous sequences of data (islands) and missing values between sequences (gaps) in ordered datasets.

Calculate island groupings by subtracting a row number sequence (`ROW_NUMBER()`) from the ordering column.

Q41: How do you solve the classic 'Gaps and Islands' problem in SQL?

- **Hard Delete**: Removes rows permanently from the physical storage layer using `DELETE FROM table`. Cannot be undone except by restoring database backups.
- **Soft Delete**: Flags records as deleted without removing them physically, typically using a flag or timestamp column (`is_deleted BOOLEAN`, `deleted_at TIMESTAMP`).

### Soft Delete Considerations
Soft deletes preserve historical data and support simple record restoration, but require all application queries to include `WHERE deleted_at IS NULL` filters.

Q42: What is the difference between Soft Delete and Hard Delete?

Generating Sales Subtotals with ROLLUP

These extensions expand standard `GROUP BY` capabilities by generating multiple aggregate hierarchy sets in a single query pass.

- **GROUPING SETS**: Explicitly defines specific column combination sets to aggregate.
- **ROLLUP**: Generates hierarchical subtotal and grand total aggregations along a specified column hierarchy.
- **CUBE**: Generates subtotals for **all possible combinations** of specified dimension columns.

Q43: How do GROUPING SETS, ROLLUP, and CUBE operators work?

- **Temporary Tables (`#TempTable`)**: Persisted in temporary database storage (`tempdb`). They support statistics, secondary indexing, and multi-threaded parallel execution plans. Best for large intermediate datasets.
- **Table Variables (`@TableVar`)**: Scoped to the executing transaction block or routine. They generally lack column statistics, which can lead to single-threaded or sub-optimal execution plans on larger datasets. Best for small lookup sets.

Q44: What is the difference between Temporary Tables and Table Variables?

PostgreSQL UPSERT Syntax Example

An **UPSERT** inserts a new record or updates the existing row if a unique constraint conflict occurs.

- **PostgreSQL / SQLite**: Uses `INSERT INTO ... ON CONFLICT (key) DO UPDATE`.
- **ANSI Standard / SQL Server / Oracle**: Uses `MERGE INTO target USING source ON match_condition`.

Q45: How do UPSERT operations work in SQL (MERGE vs ON CONFLICT)?

A view can accept direct `INSERT`, `UPDATE`, or `DELETE` statements only if the underlying query meets strict criteria:

1. The view references **exactly one base table** in its `FROM` clause.
2. The query contains **no aggregation clauses** (`GROUP BY`, `HAVING`, `SUM`, `AVG`, `COUNT`).
3. The query contains **no set operations** (`UNION`, `INTERSECT`, `EXCEPT`) or `DISTINCT` clauses.
4. All target non-null columns missing from the view definition must have default values.

Q46: When is a SQL View updatable directly via DML statements?

Referential actions determine how child records react when a parent primary key row is updated or deleted:

- **CASCADE**: Automatically deletes or updates child records when the parent row is deleted or updated.
- **SET NULL**: Sets child foreign key values to `NULL` when the referenced parent record is modified.
- **RESTRICT / NO ACTION**: Prevents parent row modification or deletion if child rows still depend on it (throws a foreign key constraint violation error).

Q47: What are Referential Actions on Foreign Keys (CASCADE, SET NULL, RESTRICT)?

Top Earner per Department Query

Find the highest or Nth highest salary per group by pairing `DENSE_RANK()` or `ROW_NUMBER()` with `PARTITION BY`.

Q48: How do you find the highest salary in EACH department?

- **Non-Repeatable Read**: Transaction A reads a row. Transaction B **updates or deletes** that row and commits. Transaction A re-reads the row and finds the values modified or gone.
- **Phantom Read**: Transaction A executes a range query. Transaction B **inserts new rows** matching the predicate range and commits. Transaction A re-runs the range query and sees new 'phantom' rows included in the result set.

Q49: What is the difference between a Non-Repeatable Read and a Phantom Read?

Follow this step-by-step engineering checklist to diagnose and optimize slow database queries:

Query Optimization Step-by-Step Checklist

Q50: What step-by-step methodology do you follow to optimize a slow SQL query?

50 curated SQL technical interview questions ranging from fundamental syntax and database design to advanced analytical window functions, query execution plans, and concurrency control.

STAY UPDATED WITH BYTES!