SQL Advanced Last-Minute Interview Notes

Fast revision for SQL interviews, coding assessments, production queries, analytics, and database fundamentals.

Learn the pattern → understand the execution → write the query → check edge cases → optimize it.

0. SQL Interview Mental Model

When you see a SQL problem, do not immediately start typing.

Think:

What is the required output?
        ↓
What tables contain the required data?
        ↓
What is the relationship between tables?
        ↓
Do I need row filtering or group filtering?
        ↓
Do I need aggregation?
        ↓
Do I need ranking/window functions?
        ↓
Could duplicates appear?
        ↓
How should NULL behave?
        ↓
Can the query be optimized?

Logical query processing order

A useful mental model is:

FROM / JOIN
      ↓
WHERE
      ↓
GROUP BY
      ↓
HAVING
      ↓
WINDOW FUNCTIONS
      ↓
SELECT
      ↓
DISTINCT
      ↓
ORDER BY
      ↓
LIMIT / FETCH

The exact optimizer execution plan can differ. This is the logical order used to reason about SQL.

1. SQL vs MySQL

SQL

SQL is a language used to define, query, manipulate, and control relational data.

MySQL

MySQL is a relational database management system that implements SQL plus database-specific behavior and extensions.

Similar distinction:

SQL       → language
MySQL     → database system
PostgreSQL → database system
Oracle    → database system
SQL Server → database system

Do not say:

"SQL and MySQL are the same."

2. SQL Command Categories

Category	Purpose	Examples
DDL	Define database objects	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`
DML	Modify data	`INSERT`, `UPDATE`, `DELETE`
DQL	Query data	`SELECT`
DCL	Permissions	`GRANT`, `REVOKE`
TCL	Transactions	`COMMIT`, `ROLLBACK`, `SAVEPOINT`

3. SELECT Fundamentals

SELECT name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC
LIMIT 10;

Important

Avoid:

SELECT *

in production queries when you only need specific columns.

Reasons:

Unnecessary data transfer
More I/O
More network traffic
Less explicit dependencies
Can interact poorly with covering/index strategies

4. DISTINCT

SELECT DISTINCT department
FROM employees;

DISTINCT removes duplicate result rows.

Trap

DISTINCT applies to the complete selected row, not just one visually important column.

SELECT DISTINCT department, salary
FROM employees;

The combination (department, salary) must be unique in the result.

5. WHERE

Filters rows before aggregation.

SELECT *
FROM employees
WHERE salary > 50000;

Multiple conditions:

WHERE department = 'IT'
  AND salary > 50000

6. NULL

NULL means missing/unknown/not-applicable depending on context.

It is not:

0
''
FALSE

Wrong

WHERE manager_id = NULL

Correct

WHERE manager_id IS NULL

Not equal to NULL

WHERE manager_id IS NOT NULL

7. Three-Valued Logic

SQL conditions can evaluate to:

TRUE
FALSE
UNKNOWN

Example:

NULL = 10

is not TRUE or FALSE in the ordinary sense; it evaluates to UNKNOWN.

This matters especially with:

WHERE
JOIN
NOT IN
CASE

8. COUNT() and NULL

Important distinction:

COUNT(*)

counts rows.

COUNT(column)

counts non-NULL values in that column.

Example:

SELECT
    COUNT(*) AS total_rows,
    COUNT(manager_id) AS rows_with_manager
FROM employees;

9. GROUP BY

Used to create groups for aggregation.

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Common aggregate functions:

COUNT()
SUM()
AVG()
MIN()
MAX()

10. WHERE vs HAVING

WHERE

Filters rows before grouping.

WHERE salary > 50000

HAVING

Filters groups after aggregation.

HAVING COUNT(*) > 5

Example:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) >= 5;

Mental model:

WHERE
 ↓
rows

GROUP BY
 ↓
groups

HAVING
 ↓
groups

11. JOINs

INNER JOIN

Returns matching rows.

SELECT e.name, d.name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

LEFT JOIN

Returns every row from the left table plus matching rows from the right.

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id;

If there is no match:

right-side columns → NULL

RIGHT JOIN

Everything from the right table plus matching rows from the left.

In many codebases, rewriting it as a LEFT JOIN by swapping table order can make queries easier to read.

FULL OUTER JOIN

Returns matched and unmatched rows from both sides.

Not supported with identical syntax by every database system; always check your database dialect.

CROSS JOIN

Cartesian product.

SELECT *
FROM colors
CROSS JOIN sizes;

If:

colors = 3 rows
sizes  = 4 rows

result:

3 × 4 = 12 rows

SELF JOIN

A table joined to itself.

Example employee-manager relationship:

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

12. The LEFT JOIN + WHERE Trap

Consider:

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.name = 'IT';

The WHERE condition removes rows where d.name is NULL, so the result behaves like an inner join for that condition.

If you want to preserve unmatched left rows while restricting matches:

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
   AND d.name = 'IT';

This is a common interview question.

13. Duplicate Rows After JOIN

If a join unexpectedly multiplies rows, check the relationship.

Example:

customers
    1
    ↓
orders
    many

Joining one customer to many orders naturally produces multiple rows per customer.

Before joining, ask:

1:1?
1:N?
N:1?
N:N?

Many SQL bugs are actually cardinality mistakes.

14. Find Duplicates

SELECT
    email,
    COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Find duplicate combinations:

SELECT
    first_name,
    last_name,
    birth_date,
    COUNT(*)
FROM customers
GROUP BY
    first_name,
    last_name,
    birth_date
HAVING COUNT(*) > 1;

15. Remove Duplicates from Results

First ask:

Why are duplicates being generated?

Do not blindly add:

DISTINCT

DISTINCT can hide an incorrect join.

Better:

Understand cardinality
        ↓
Fix JOIN
        ↓
Use aggregation if required
        ↓
Use DISTINCT only when duplicate result rows are legitimately unwanted

16. Subqueries

Scalar subquery

Returns one value.

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Multi-row subquery

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Bangalore'
);

Correlated subquery

References a value from the outer query.

SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

A correlated subquery can be expensive depending on the database and optimizer. Do not assume it literally executes once per outer row; inspect the execution plan.

17. EXISTS vs IN

EXISTS

Checks whether at least one matching row exists.

SELECT c.customer_id
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

IN

Compares against a set of values.

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
);

Do not claim that EXISTS is always faster than IN or vice versa.

Performance depends on:

Data distribution
Indexes
Database engine
Query shape
Optimizer

18. NOT IN + NULL Trap

This is a classic interview question.

Suppose:

WHERE id NOT IN (
    SELECT employee_id
    FROM terminated_employees
)

If the subquery contains NULL, three-valued logic can cause unexpected results.

Safer pattern when appropriate:

WHERE NOT EXISTS (
    SELECT 1
    FROM terminated_employees t
    WHERE t.employee_id = e.employee_id
);

Always reason explicitly about NULL.

19. CTE

Common Table Expression:

WITH department_stats AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM department_stats
WHERE avg_salary > 60000;

Advantages:

Readability
Breaking complex queries into steps
Easier debugging
Recursive queries

A CTE is not automatically faster than a subquery. Optimization behavior is database-specific.

20. Recursive CTE

Useful for hierarchical data.

Example:

WITH RECURSIVE employee_tree AS (
    SELECT
        employee_id,
        name,
        manager_id,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        t.level + 1
    FROM employees e
    JOIN employee_tree t
        ON e.manager_id = t.employee_id
)
SELECT *
FROM employee_tree;

Common use cases:

Organization hierarchy
Category trees
Graph-like traversal
Parent/child relationships

21. Window Functions

Window functions calculate across related rows without collapsing the result into one row per group.

MySQL documents window functions as calculations over rows related to the current row. citeturn0search5

General form:

function(...) OVER (
    PARTITION BY ...
    ORDER BY ...
)

22. ROW_NUMBER vs RANK vs DENSE_RANK

Suppose salaries:

ROW_NUMBER

RANK

DENSE_RANK

23. Top N Per Group

One of the most important SQL patterns.

Example: top 3 salaries per department.

WITH ranked AS (
    SELECT
        employee_id,
        name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
)
SELECT *
FROM ranked
WHERE rnk <= 3;

Use ROW_NUMBER() if you need exactly N rows per group.

Use DENSE_RANK() if ties should share a rank.

24. LAG and LEAD

Compare the current row with another row.

SELECT
    employee_id,
    salary,
    LAG(salary) OVER (
        ORDER BY employee_id
    ) AS previous_salary
FROM employees;

Useful for:

Month-over-month changes
Previous transaction
Previous status
Trend analysis

25. Running Total

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
    ) AS running_total
FROM orders;

Partitioned running total:

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
)

26. Moving Average

AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

This represents a 7-row moving average, not necessarily a 7-day average.

That distinction matters when dates have gaps.

27. GROUP BY vs Window Function

GROUP BY

Collapses rows.

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

Result:

one row per department

Window

Keeps rows.

SELECT
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id
    ) AS department_avg
FROM employees;

Result:

one row per employee
+
department average

28. CASE

Conditional logic:

SELECT
    name,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_band
FROM employees;

Conditional aggregation:

SELECT
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count
FROM users;

Portable alternative:

SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)

29. COALESCE

Returns the first non-NULL expression.

SELECT
    name,
    COALESCE(phone, 'Not Available')
FROM customers;

Useful for:

Default values
NULL-safe calculations
Display logic

30. NULLIF

Returns NULL when two expressions are equal.

Useful for preventing division-by-zero:

SELECT
    revenue / NULLIF(orders, 0)
FROM sales;

31. Date and Time

Common operations:

CURRENT_DATE
CURRENT_TIMESTAMP

But date functions vary between database systems.

Typical tasks:

Extract year/month
Date difference
Date arithmetic
Monthly aggregation
Rolling periods
Latest record

Example pattern:

SELECT
    DATE(order_date) AS order_day,
    COUNT(*) AS orders
FROM orders
GROUP BY DATE(order_date);

For production queries, prefer sargable date predicates when possible.

Instead of wrapping an indexed timestamp column unnecessarily:

WHERE DATE(created_at) = '2026-09-25'

consider:

WHERE created_at >= '2026-09-25'
  AND created_at <  '2026-09-26'

This exact syntax is database-dependent, but the principle is important: avoid applying functions to indexed columns when it prevents efficient index use.

32. Keys

Primary Key

Uniquely identifies a row.

Properties:

Unique
Not NULL
One primary-key constraint per table

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Foreign Key

Maintains referential relationships.

FOREIGN KEY (department_id)
REFERENCES departments(department_id)

Candidate Key

A minimal set of attributes that can uniquely identify a row.

Composite Key

Key consisting of multiple columns.

PRIMARY KEY (order_id, product_id)

33. UNIQUE vs PRIMARY KEY

PRIMARY KEY	UNIQUE
Identifies row	Enforces uniqueness
Cannot be NULL in normal relational semantics	NULL behavior varies by DB
One primary-key constraint	Multiple unique constraints possible
Often referenced by foreign keys	Can also be referenced depending on DB

Do not overgeneralize NULL behavior across database engines.

34. Normalization

Purpose:

Reduce unnecessary redundancy
Improve data integrity
Reduce update anomalies

1NF

Atomic values; no repeating groups.

2NF

1NF + no partial dependency on part of a composite candidate key.

3NF

2NF + no problematic transitive dependency of non-key attributes on a key.

BCNF

Every determinant is a candidate key.

Interview point:

Normalization is not "always split everything." Database design balances integrity, maintainability, query patterns, and performance.

35. Transactions

A transaction groups operations into a unit of work.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

Rollback:

ROLLBACK;

36. ACID

Atomicity

All required changes happen or the transaction is rolled back.

Consistency

A committed transaction preserves the database's declared integrity rules and consistency guarantees.

Isolation

Concurrent transactions are controlled so their interactions obey the database's isolation semantics.

Durability

Once committed, changes are intended to survive failures according to the database's durability guarantees.

37. Isolation Levels

Common standard levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Potential phenomena:

Dirty read
Non-repeatable read
Phantom read

The exact behavior and implementation differ between database engines.

Do not memorize a simplistic matrix without knowing the database you are interviewing for.

38. Dirty Read

Transaction A reads uncommitted changes made by Transaction B.

B updates
 ↓
A reads
 ↓
B rolls back
 ↓
A had read data that never committed

39. Non-Repeatable Read

Transaction A reads a row.

Transaction B changes and commits it.

Transaction A reads again and gets a different committed value.

40. Phantom Read

Transaction A repeats a range query and sees a different set of rows because another transaction inserted/deleted matching rows.

41. Deadlock

Two or more transactions wait on resources held by each other.

Example:

Transaction A:
locks Row 1
waits for Row 2

Transaction B:
locks Row 2
waits for Row 1

Database systems typically detect deadlocks and abort one transaction.

Prevention strategies:

Consistent lock ordering
Short transactions
Appropriate indexes
Avoid unnecessary locking
Retry aborted transactions where appropriate

42. Indexes

An index is a data structure that helps the database locate rows efficiently.

MySQL documentation notes that indexes can speed filtering and joins and that composite indexes can be used through their leftmost prefixes. citeturn0search4

Example:

CREATE INDEX idx_employee_department
ON employees(department_id);

43. Composite Index

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

Conceptually useful for queries beginning with:

customer_id
customer_id + order_date

The exact optimizer behavior is engine-specific.

44. Leftmost Prefix

For:

INDEX(a, b, c)

the index can generally support lookups using:

a
a,b
a,b,c

But a query only filtering on b does not get the same direct leftmost-prefix benefit.

MySQL documents this leftmost-prefix behavior explicitly. citeturn0search4

45. When NOT to Add an Index

Indexes have costs.

They can increase:

Storage
INSERT cost
UPDATE cost
DELETE cost
Maintenance work

Do not create an index for every column.

Consider:

Query frequency
Selectivity
Data distribution
Write volume
Column cardinality
Composite access patterns
Execution plan

46. EXPLAIN

Use the optimizer's execution plan.

EXPLAIN
SELECT *
FROM employees
WHERE department_id = 10;

For MySQL:

EXPLAIN ANALYZE
SELECT ...

when supported by the target version.

Look for:

Access type
Possible indexes
Chosen index
Rows examined/estimated
Join order
Filtering
Sorts
Temporary operations

Never claim an index improved a query merely because you created it.

Measure.

47. Query Optimization Checklist

When a query is slow:

1. Understand the query.
2. Check EXPLAIN.
3. Identify expensive operation.
4. Check indexes.
5. Check JOIN cardinality.
6. Reduce unnecessary rows early.
7. Avoid SELECT *.
8. Avoid unnecessary DISTINCT.
9. Check sort/group operations.
10. Check functions on indexed columns.
11. Check data volume and statistics.
12. Test alternatives.
13. Measure before/after.

48. SARGability

A predicate is generally more index-friendly when the database can use the indexed column directly rather than computing a function over every row.

Less index-friendly pattern:

WHERE YEAR(created_at) = 2026

Potentially better:

WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01'

Exact date syntax and optimizer behavior depend on the DBMS.

49. UNION vs UNION ALL

UNION

Combines results and removes duplicates.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

UNION ALL

Combines results without duplicate elimination.

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

If duplicates are valid and not required to be removed, UNION ALL avoids the duplicate-elimination work.

50. DELETE vs TRUNCATE vs DROP

DELETE

DELETE FROM employees
WHERE department_id = 10;

Removes rows and can use a WHERE condition.

TRUNCATE

TRUNCATE TABLE employees;

Removes all rows using database-specific DDL/transaction semantics.

DROP

DROP TABLE employees;

Removes the table object itself.

Do not memorize one universal rollback rule; behavior depends on the DBMS and transaction context.

51. UPDATE Safely

Before:

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 10;

Verify the target set first:

SELECT *
FROM employees
WHERE department_id = 10;

For important changes:

START TRANSACTION;

UPDATE ...;

SELECT ...;

COMMIT;

or:

ROLLBACK;

depending on the operation and environment.

52. Common Interview Queries

Q1. Second highest salary

Simple version:

SELECT MAX(salary)
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

This handles distinct salary values, but returns NULL if no second distinct salary exists.

Window approach:

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) x
WHERE rnk = 2;

Q2. Nth highest salary

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) x
WHERE rnk = 5;

Clarify whether "Nth highest" means:

Nth distinct salary

or:

Nth row after sorting

Those are different questions.

Q3. Highest salary in each department

SELECT *
FROM (
    SELECT
        e.*,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees e
) x
WHERE rnk = 1;

Q4. Employees earning more than department average

SELECT *
FROM (
    SELECT
        e.*,
        AVG(salary) OVER (
            PARTITION BY department_id
        ) AS dept_avg
    FROM employees e
) x
WHERE salary > dept_avg;

Q5. Find duplicate emails

SELECT
    email,
    COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Q6. Find employees without a department

SELECT e.*
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Q7. Customers with no orders

SELECT c.*
FROM customers c
LEFT JOIN orders o
    ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

Alternative:

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Q8. Latest order for each customer

SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders o
) x
WHERE rn = 1;

Include a deterministic tie-breaker such as order_id when required.

Q9. Top 3 products by sales

SELECT
    product_id,
    SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 3;

If the requirement means top 3 including ties, use a ranking strategy instead.

Q10. Employees hired in the last 30 days

The exact expression is DB-specific.

Concept:

WHERE hire_date >= CURRENT_DATE - INTERVAL 30 DAY

Always check the target database's date syntax.

53. More Advanced Query Patterns

Running total

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date, order_id
    ) AS running_total
FROM orders;

Previous row

SELECT
    order_id,
    order_date,
    amount,
    LAG(amount) OVER (
        ORDER BY order_date, order_id
    ) AS previous_amount
FROM orders;

Difference from previous row

SELECT
    order_id,
    amount,
    amount -
    LAG(amount) OVER (
        ORDER BY order_date, order_id
    ) AS difference
FROM orders;

Percentage of department total

SELECT
    employee_id,
    department_id,
    salary,
    salary * 100.0 /
        SUM(salary) OVER (
            PARTITION BY department_id
        ) AS pct_of_department_salary
FROM employees;

54. SQL Injection

Bad:

Application input
      ↓
String concatenation
      ↓
SQL query

Example of dangerous construction:

"SELECT * FROM users WHERE name = '" + userInput + "'"

Use parameterized queries / prepared statements.

Concept:

SQL structure
      +
parameters

rather than concatenating untrusted values into SQL text.

55. Transactions and Application Design

Do not hold transactions open unnecessarily.

Long transactions can:

Hold locks
Increase contention
Delay cleanup
Increase resource usage
Increase deadlock opportunities

Prefer:

Begin
 ↓
Perform required work
 ↓
Validate
 ↓
Commit

Keep transaction scope appropriate to the business operation.

56. Most Asked SQL Interview Questions

Fundamentals

What is SQL?
SQL vs MySQL?
What are DDL, DML, DCL and TCL?
Primary key vs foreign key?
Primary key vs unique key?
What is a composite key?
What is NULL?
What is normalization?
What are constraints?
What is referential integrity?

Querying

WHERE vs HAVING?
GROUP BY?
DISTINCT?
UNION vs UNION ALL?
DELETE vs TRUNCATE vs DROP?
IN vs EXISTS?
JOIN vs subquery?
Correlated subquery?
CTE?
Recursive CTE?

Joins

INNER JOIN?
LEFT JOIN?
RIGHT JOIN?
FULL OUTER JOIN?
CROSS JOIN?
SELF JOIN?
Why do joins create duplicates?
How do you find unmatched rows?
LEFT JOIN + WHERE trap?
How do you identify join cardinality?

Window Functions

What is a window function?
ROW_NUMBER vs RANK?
RANK vs DENSE_RANK?
PARTITION BY?
LAG vs LEAD?
Running total?
Top N per group?
Latest row per group?
Moving average?
Window vs GROUP BY?

Performance

What is an index?
Clustered vs non-clustered index?
Composite index?
Leftmost-prefix rule?
What is selectivity?
What is a covering index?
What is EXPLAIN?
What is a full table scan?
What is SARGability?
Why can an index make writes slower?

Transactions

What is ACID?
What is a transaction?
COMMIT vs ROLLBACK?
Isolation levels?
Dirty read?
Non-repeatable read?
Phantom read?
Deadlock?
Lock?
Optimistic vs pessimistic concurrency?

Real-world scenarios

Query suddenly became slow. What do you check?
Duplicate rows after a JOIN. Why?
Database CPU is high. What do you investigate?
How do you find the latest record per customer?
How do you find customers without orders?
How do you find duplicate records?
How do you delete duplicates safely?
How do you find the second-highest salary?
How do you find top N per department?
How do you optimize a query without changing the result?

57. Scenario Answer: Slow Query

A strong answer:

1. Reproduce the query.
2. Check execution plan.
3. Check rows examined.
4. Check indexes.
5. Check JOIN cardinality.
6. Check filters and selectivity.
7. Check sorting/grouping.
8. Check functions on indexed columns.
9. Check table statistics/data growth.
10. Test an alternative.
11. Measure before/after.

Use:

EXPLAIN ...

and, where supported:

EXPLAIN ANALYZE ...

Do not say:

"I will add an index."

Say:

"I will inspect the execution plan first and determine whether the bottleneck is access path, join strategy, sorting, aggregation, cardinality, or something outside the query."

58. Scenario Answer: Duplicate Rows

Strong answer:

1. Check the JOIN condition.
2. Identify primary/unique keys.
3. Determine relationship cardinality.
4. Check whether one-to-many is expected.
5. Determine whether aggregation is required.
6. Fix the query logic.
7. Use DISTINCT only if duplicate result rows are legitimately intended to collapse.

59. Scenario Answer: Delete Duplicate Records

First identify duplicates:

SELECT
    email,
    COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Then decide which row should survive.

Example pattern:

WITH ranked AS (
    SELECT
        user_id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY user_id
        ) AS rn
    FROM users
)
DELETE FROM users
WHERE user_id IN (
    SELECT user_id
    FROM ranked
    WHERE rn > 1
);

Important: DELETE syntax involving CTEs varies by database. Test the equivalent syntax for your DBMS before production use.

Always:

SELECT candidates
    ↓
Review
    ↓
Transaction
    ↓
Delete
    ↓
Verify

60. Scenario Answer: Database CPU High

Do not assume one query is responsible.

Investigate:

Active queries
Execution plans
Rows scanned
Missing/ineffective indexes
Large sorts
Large joins
Lock contention
Connection volume
Recent deployments
Data growth

MySQL example tools include:

SHOW PROCESSLIST;
EXPLAIN ...;

and Performance Schema depending on the environment.

61. Query Quality Checklist

Before submitting a query:

[ ] Correct tables?
[ ] Correct JOIN condition?
[ ] Correct cardinality?
[ ] NULL handled?
[ ] Duplicate rows expected?
[ ] WHERE vs HAVING correct?
[ ] GROUP BY correct?
[ ] Window function required?
[ ] Tie behavior defined?
[ ] Deterministic ordering?
[ ] Date boundary correct?
[ ] Index-friendly predicate?
[ ] EXPLAIN checked if performance matters?

62. SQL Interview Traps

Trap 1

COUNT(*) ≠ COUNT(column)

Trap 2

NULL = NULL is not TRUE.

Use:

IS NULL

Trap 3

NOT IN + NULL can produce surprising results.

Consider:

NOT EXISTS

Trap 4

WHERE filters rows.

HAVING filters groups.

Trap 5

GROUP BY collapses rows.

Window functions generally preserve the row-level result.

Trap 6

RANK() can produce gaps.

DENSE_RANK() does not.

Trap 7

A LEFT JOIN can effectively behave like an inner join if right-table predicates are placed incorrectly in WHERE.

Trap 8

DISTINCT is not a universal duplicate fix.

It can hide a bad join.

Trap 9

An index does not guarantee a faster query.

The optimizer may choose another access path.

Trap 10

A query that works on 10,000 rows may fail operationally at 100 million rows.

Always think about:

data volume
cardinality
indexes
execution plan
memory
I/O

63. Fast Last-Minute Cheat Sheet

Filtering

WHERE
IN
BETWEEN
LIKE
IS NULL
IS NOT NULL

Aggregation

GROUP BY
HAVING
COUNT
SUM
AVG
MIN
MAX

Joins

INNER
LEFT
RIGHT
FULL
CROSS
SELF

Advanced

CTE
RECURSIVE CTE
EXISTS
NOT EXISTS
CASE
COALESCE
NULLIF
WINDOW FUNCTIONS

Window

ROW_NUMBER
RANK
DENSE_RANK
LAG
LEAD
SUM() OVER
AVG() OVER

Performance

EXPLAIN
EXPLAIN ANALYZE
INDEX
COMPOSITE INDEX
COVERING INDEX
SARGABILITY
CARDINALITY
SELECTIVITY

Transactions

START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT

Must-know concepts

NULL
3-valued logic
JOIN cardinality
ACID
Isolation
Locks
Deadlocks
Indexes
Execution plans
Normalization
SQL injection

64. Reference Links

Official Documentation

MySQL Reference Manual: https://dev.mysql.com/doc/refman/9.7/en/
MySQL JOIN documentation: https://dev.mysql.com/doc/refman/8.4/en/join.html
MySQL Window Functions: https://dev.mysql.com/doc/refman/9.7/en/window-functions.html
MySQL Indexes: https://dev.mysql.com/doc/refman/9.7/en/mysql-indexes.html
PostgreSQL Documentation: https://www.postgresql.org/docs/
SQLite Documentation: https://www.sqlite.org/docs.html

Use the documentation for the specific database engine used by the interview or project. SQL syntax and behavior are not identical across vendors.

65. Final Interview Strategy

When asked to solve a SQL problem:

1. Clarify the output.
2. Identify tables.
3. Identify keys and relationships.
4. Decide JOIN type.
5. Filter rows.
6. Aggregate if needed.
7. Use window functions when row-level context must be preserved.
8. Handle NULL.
9. Handle duplicates.
10. Define tie behavior.
11. Test edge cases.
12. Explain complexity/performance.

For optimization questions:

Don't guess.
Don't blindly add indexes.
Don't blindly add DISTINCT.

EXPLAIN
    ↓
Understand
    ↓
Change
    ↓
Measure
    ↓
Verify

SQL skill is not memorizing syntax. It is translating a business question into correct relational logic and then making that logic efficient enough for real data.

FilesExpand file tree

SQL-Advanced-Last-Minute-Interview-Notes.md

Latest commit

History

SQL-Advanced-Last-Minute-Interview-Notes.md

File metadata and controls

SQL Advanced Last-Minute Interview Notes

0. SQL Interview Mental Model

Logical query processing order

1. SQL vs MySQL

SQL

MySQL

2. SQL Command Categories

3. SELECT Fundamentals

Important

4. DISTINCT

Trap

5. WHERE

6. NULL

Wrong

Correct

Not equal to NULL

7. Three-Valued Logic

8. COUNT() and NULL

9. GROUP BY

10. WHERE vs HAVING

WHERE

HAVING

11. JOINs

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

12. The LEFT JOIN + WHERE Trap

13. Duplicate Rows After JOIN

14. Find Duplicates

15. Remove Duplicates from Results

16. Subqueries

Scalar subquery

Multi-row subquery

Correlated subquery

17. EXISTS vs IN

EXISTS

IN

18. NOT IN + NULL Trap

19. CTE

20. Recursive CTE

21. Window Functions

22. ROW_NUMBER vs RANK vs DENSE_RANK

ROW_NUMBER

RANK

DENSE_RANK

23. Top N Per Group

24. LAG and LEAD

25. Running Total

26. Moving Average

27. GROUP BY vs Window Function

GROUP BY

Window

28. CASE

29. COALESCE

30. NULLIF

31. Date and Time

32. Keys

Primary Key

Foreign Key

Candidate Key

Composite Key

33. UNIQUE vs PRIMARY KEY

34. Normalization

1NF

2NF

3NF

BCNF

35. Transactions

36. ACID

Atomicity

Consistency