Q: What is the difference between WHERE and HAVING?

## WHERE vs HAVING Both clauses filter result rows, but they run at different stages of logical query execution: - **`WHERE`** filters individual raw rows **before** they are grouped by `GROUP BY`. It cannot refer to aggregate functions (like `SUM` or `COUNT`). - **`HAVING`** filters summary group rows **after** they are grouped by `GROUP BY`. It is designed to filter on aggregate calculations. <svg class='iq-diagram' viewBox='0 0 480 120' role='img' aria-label='Logical query execution order'

Q: What are the different types of SQL joins?

## SQL Joins SQL joins combine rows from two or more tables based on a related column between them. Relational databases support several join types that handle non-matching rows differently: | Join Type | Behavior | Output Description | |---|---|---| | `INNER JOIN` | Default join | Returns rows only when there is a match in **both** tables. | | `LEFT JOIN` | Outer join | Returns **all** rows from the left table, plus matching rows from the right (or `NULL`s). | | `RIGHT JOIN` | Outer join | Re

Q: What does DISTINCT do and what is its cost?

## DISTINCT — Removing Duplicate Rows `SELECT DISTINCT` eliminates duplicate rows from the result set. Two rows are considered duplicates if **all selected columns** have identical values. <rect cl

Q: When would you use a transaction explicitly?

## Explicit Transactions By default, most databases run in **auto-commit** mode — each statement is its own transaction. You use **explicit transactions** (`BEGIN` / `COMMIT` / `ROLLBACK`) when you need **atomicity across multiple statements** — either they all succeed or none of them take effect. | Mode | Behavior | Use Case | |---|---|---| | **Auto-commit** | Each statement is its own transaction | Simple reads, single-statement writes | | **Explicit** | Multiple statements grouped atomicall

Q: What do COALESCE and NULLIF do?

## COALESCE and NULLIF Both functions manage `NULL` values cleanly to prevent unexpected errors during computations: - **`COALESCE(val1, val2, ...)`:** Returns the **first non-NULL value** in its arguments list. It provides fallback values sequentially. - **`NULLIF(val1, val2)`:** Returns `NULL` if `val1` equals `val2`. Otherwise, it returns `val1` unchanged. Useful to prevent division-by-zero errors. <svg class='iq-diagram' viewBox='0 0 480 150' role='img' aria-label='COALESCE fallback evalu

Q: What is a CHECK constraint?

## CHECK Constraints A `CHECK` constraint enforces domain integrity by validating values before they are written to a column. If a query tries to write a value that evaluates to `FALSE`, the database rejects the statement and raises a constraint violation error: ### Common Formats - **Range checks:** `CHECK (age >= 18)` - **Format pattern checks:** `CHECK (email LIKE '%@%')` - **In-list options checks:** `CHECK (status IN ('active', 'inactive'))` - **Multi-column check constraints:** `CHECK (s

Question 1

What is the difference between a primary key and a foreign key?

Accepted Answer

## Primary Key vs. Foreign Key

Primary keys and foreign keys define database constraints that guarantee relational integrity across tables:

- **Primary Key (PK):** Uniquely identifies each record inside a table. It cannot contain `NULL` values, and a table can have **only one** primary key.
- **Foreign Key (FK):** Reference column that maps to a primary key in another table (or the same table). It enforces **referential integrity**, ensuring that the value in the child column matches an existi

Question 2

What is the difference between a primary key and a unique constraint?

Accepted Answer

## Primary Key vs. Unique Constraint

Both constraints guarantee uniqueness for values in a column (or set of columns), but differ in structural behaviors:

| Feature | Primary Key | Unique Constraint |
|---|---|---|
| **Nullability** | Cannot contain `NULL` | Can contain `NULL` (usually one or more depending on DB) |
| **Quantity** | Max **one** per table | Can define **multiple** unique columns per table |
| **Index Type** | Defaults to **Clustered Index** | Defaults to **Non-Clustered Index**

Question 3

What is the logical order of execution of a SQL query?

Accepted Answer

## Logical Execution Order vs. Syntactic Order

SQL is a **declarative language**, so the order you write clauses (`SELECT`, `FROM`, `WHERE`, etc.) is **not** the order the engine processes them. Understanding the logical execution order is essential for debugging column aliasing issues, understanding when filters apply, and writing correct queries.

| Step | Clause | Purpose |
|---:|---|---|
| 1 | `FROM` / `JOIN` | Identify source tables and build the initial Cartesian product or join result. |

Question 4

What is the CASE expression?

Accepted Answer

## CASE Expression

The `CASE` expression is SQL's conditional helper, matching `if-then-else` logical branching. It returns a single formatted value based on condition paths:

### 1. Simple CASE
Compares a single expression against static values:
```sql
CASE product
  WHEN 'Apple' THEN 'Fruit'
  ELSE 'Other'
END
```

### 2. Searched CASE (Recommended)
Evaluates complex boolean check conditions sequentially:
```sql
CASE 
  WHEN amount >= 150 THEN 'High'
  ELSE 'Low'
END
```

Question 5

What is the difference between WHERE and HAVING?

Accepted Answer

## WHERE vs HAVING

Both clauses filter result rows, but they run at different stages of logical query execution:

- **`WHERE`** filters individual raw rows **before** they are grouped by `GROUP BY`. It cannot refer to aggregate functions (like `SUM` or `COUNT`).
- **`HAVING`** filters summary group rows **after** they are grouped by `GROUP BY`. It is designed to filter on aggregate calculations.

Question 6

What are the different types of SQL joins?

Accepted Answer

## SQL Joins

SQL joins combine rows from two or more tables based on a related column between them. Relational databases support several join types that handle non-matching rows differently:

| Join Type | Behavior | Output Description |
|---|---|---|
| `INNER JOIN` | Default join | Returns rows only when there is a match in **both** tables. |
| `LEFT JOIN` | Outer join | Returns **all** rows from the left table, plus matching rows from the right (or `NULL`s). |
| `RIGHT JOIN` | Outer join | Re

Question 7

What does DISTINCT do and what is its cost?

Accepted Answer

## DISTINCT — Removing Duplicate Rows `SELECT DISTINCT` eliminates duplicate rows from the result set. Two rows are considered duplicates if **all selected columns** have identical values.

Question 8

When would you use a transaction explicitly?

Accepted Answer

## Explicit Transactions

By default, most databases run in **auto-commit** mode — each statement is its own transaction. You use **explicit transactions** (`BEGIN` / `COMMIT` / `ROLLBACK`) when you need **atomicity across multiple statements** — either they all succeed or none of them take effect.

| Mode | Behavior | Use Case |
|---|---|---|
| **Auto-commit** | Each statement is its own transaction | Simple reads, single-statement writes |
| **Explicit** | Multiple statements grouped atomicall

Question 9

What do COALESCE and NULLIF do?

Accepted Answer

## COALESCE and NULLIF

Both functions manage `NULL` values cleanly to prevent unexpected errors during computations:

- **`COALESCE(val1, val2, ...)`:** Returns the **first non-NULL value** in its arguments list. It provides fallback values sequentially.
- **`NULLIF(val1, val2)`:** Returns `NULL` if `val1` equals `val2`. Otherwise, it returns `val1` unchanged. Useful to prevent division-by-zero errors.

Question 10

What is a CHECK constraint?

Accepted Answer

## CHECK Constraints

A `CHECK` constraint enforces domain integrity by validating values before they are written to a column. If a query tries to write a value that evaluates to `FALSE`, the database rejects the statement and raises a constraint violation error:

### Common Formats
- **Range checks:** `CHECK (age >= 18)`
- **Format pattern checks:** `CHECK (email LIKE '%@%')`
- **In-list options checks:** `CHECK (status IN ('active', 'inactive'))`
- **Multi-column check constraints:** `CHECK (s

SQL Interview Library

Question Catalog

What is the difference between a primary key and a foreign key?

What is the difference between a primary key and a unique constraint?

What is the logical order of execution of a SQL query?

What is the CASE expression?

What is the difference between WHERE and HAVING?

What are the different types of SQL joins?

What does DISTINCT do and what is its cost?

When would you use a transaction explicitly?

What do COALESCE and NULLIF do?

What is a CHECK constraint?

What is the difference between CHAR, VARCHAR, and TEXT?

What is a subquery and what is a correlated subquery?

How do you read an EXPLAIN query plan?

What is a covering index?

How does GROUP BY work with aggregate functions?

What are COMMIT, ROLLBACK, and SAVEPOINT?

How do you aggregate values into a single string?

What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?

Why might EXISTS outperform a JOIN for existence checks?

How does SQL handle NULL values?

What is a view and a materialized view?

What are transaction isolation levels?

What are foreign key cascade options?

What is the difference between EXISTS and IN?

What is a CTE (Common Table Expression)?

How do you update a table based on another table?

What is an UPSERT?

What are ACID properties?

What is the difference between UNION and UNION ALL?

How do you paginate efficiently in SQL?

What are the common aggregate functions?

What is the difference between DELETE, TRUNCATE, and DROP?

What is a stored procedure?

What is a self join?

What is an index and how does it speed up queries?

What do GROUP BY ROLLUP and CUBE do?

What is a database trigger?

What is database normalization?

How do recursive CTEs work?

What do the LAG and LEAD window functions do?

What is a LATERAL join?

How do you optimize a slow SQL query?

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

How do you pivot rows into columns?

Why does column order matter in a composite index?

How do you find the Nth highest salary?

What are window functions?

What is a deadlock and how do you prevent it?

What is the difference between a clustered and non-clustered index?

What is table partitioning?

Difficulty Distribution

Tested Topics

Study Guide