SQL + MySQL + MongoDB — Complete QA Interview Guide¶
Comprehensive reference for the database round of QA / SDET interviews. Every query has: problem statement → query → step-by-step explanation → common follow-ups. Designed so you can read each query out loud, understand what each clause does, and defend it under probing.
What's covered¶
| Part | Topic | Why it matters |
|---|---|---|
| 1 | SQL fundamentals (SELECT, WHERE, ORDER BY, LIMIT) | Every interview opens here |
| 2 | DDL / DML / DCL / TCL — the 4 SQL languages | Conceptual question, asked often |
| 3 | JOINs — INNER / LEFT / RIGHT / FULL / CROSS / SELF | Most-tested SQL topic in QA |
| 4 | GROUP BY + HAVING + aggregations | Always asked |
| 5 | Subqueries (correlated + non-correlated) | Common at mid-level |
| 6 | CTEs (WITH clause) — modern alternative to subqueries | Standard now |
| 7 | Window functions — RANK / ROW_NUMBER / LEAD / LAG | SDET-2 favourite |
| 8 | CASE expressions + pivot | Common |
| 9 | UNION / UNION ALL / INTERSECT / EXCEPT | Basic but trapped |
| 10 | Indexes + EXPLAIN + query optimization | SDET-2 |
| 11 | Constraints + normalization | Conceptual |
| 12 | Transactions + ACID + isolation levels | Senior-level |
| 13 | Stored procedures, functions, triggers, views | Sometimes asked |
| 14 | 50+ classic interview SQL questions with full explanations | The drill section |
| 15 | MySQL-specific features (LIMIT, ON DUPLICATE KEY, GROUP_CONCAT) | MySQL shops |
| 16 | MongoDB fundamentals — document model, BSON, ObjectId | NoSQL roles |
| 17 | MongoDB CRUD + query operators | Hands-on |
| 18 | MongoDB aggregation pipeline | The biggest Mongo topic |
| 19 | MongoDB indexes + schema design | Performance |
| 20 | SQL vs MongoDB — when to use which | Architectural Q |
| 21 | QA-specific patterns — backend data validation queries | What you actually do daily |
| 22 | 40+ interview Q&A | Self-quiz |
1. SQL FUNDAMENTALS¶
1.1 The structure of every query¶
SELECT column_list -- WHAT to return
FROM table_name -- WHERE the data lives
WHERE row_filter -- which rows to keep (before grouping)
GROUP BY column_list -- bucket the rows
HAVING group_filter -- which groups to keep (after grouping)
ORDER BY column_list -- sort the result
LIMIT N -- how many to return
OFFSET N -- skip first N
The execution order (different from the write order!)¶
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT ← yes, SELECT runs 5th
6. ORDER BY
7. LIMIT / OFFSET
Why this matters: column aliases defined in SELECT can't be used in WHERE (because WHERE runs first). They CAN be used in ORDER BY.
-- WRONG
SELECT salary * 12 AS annual FROM employees WHERE annual > 1000000;
-- ERROR: column "annual" does not exist
-- RIGHT
SELECT salary * 12 AS annual FROM employees WHERE salary * 12 > 1000000;
-- ORDER BY can use alias (it runs after SELECT)
SELECT salary * 12 AS annual FROM employees ORDER BY annual DESC;
1.2 Schema we'll use throughout this file¶
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(100)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10, 2) NOT NULL,
dept_id INT,
manager_id INT,
hire_date DATE,
FOREIGN KEY (dept_id) REFERENCES departments(id),
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
city VARCHAR(50)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2),
category VARCHAR(50)
);
1.3 Basic SELECT¶
SELECT * FROM employees; -- all columns, all rows
SELECT name, salary FROM employees; -- specific columns
SELECT DISTINCT dept_id FROM employees; -- unique values
SELECT name AS employee_name, salary AS pay FROM employees; -- aliases
1.4 WHERE — filtering rows¶
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE name = 'Rohan';
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
SELECT * FROM employees WHERE dept_id IN (1, 2, 3);
SELECT * FROM employees WHERE dept_id NOT IN (4, 5);
SELECT * FROM employees WHERE name LIKE 'R%'; -- starts with R
SELECT * FROM employees WHERE name LIKE '%han'; -- ends with han
SELECT * FROM employees WHERE name LIKE '%oh%'; -- contains oh
SELECT * FROM employees WHERE name LIKE 'R___'; -- R + exactly 3 chars
SELECT * FROM employees WHERE manager_id IS NULL; -- top-level (CEO)
SELECT * FROM employees WHERE manager_id IS NOT NULL;
LIKE wildcards¶
%— zero or more characters_— exactly one character
Combined conditions¶
SELECT * FROM employees
WHERE salary > 50000
AND dept_id = 1
AND (hire_date > '2020-01-01' OR manager_id IS NULL);
1.5 ORDER BY¶
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY dept_id ASC, salary DESC; -- multiple cols
SELECT * FROM employees ORDER BY 2; -- by 2nd column
SELECT * FROM employees ORDER BY salary DESC NULLS LAST; -- Postgres/Oracle
1.6 LIMIT and OFFSET (pagination)¶
SELECT * FROM employees ORDER BY salary DESC LIMIT 10; -- top 10
SELECT * FROM employees ORDER BY salary DESC LIMIT 10 OFFSET 20; -- page 3
-- MySQL shorthand: LIMIT 20, 10 (offset, count)
2. DDL / DML / DCL / TCL — THE 4 SQL LANGUAGES¶
Common interview opener: "Can you classify SQL commands?"
| Category | Stands for | Commands | Purpose |
|---|---|---|---|
| DDL | Data Definition | CREATE, ALTER, DROP, TRUNCATE, RENAME |
Schema (tables, indexes) |
| DML | Data Manipulation | SELECT, INSERT, UPDATE, DELETE, MERGE |
Row-level data changes |
| DCL | Data Control | GRANT, REVOKE |
Permissions |
| TCL | Transaction Control | COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION |
Transaction boundaries |
Quick examples¶
-- DDL
CREATE TABLE test (id INT);
ALTER TABLE test ADD COLUMN name VARCHAR(100);
DROP TABLE test;
TRUNCATE TABLE test; -- delete all rows, reset auto_increment (faster than DELETE)
-- DML
INSERT INTO employees (id, name, salary) VALUES (1, 'Rohan', 50000);
UPDATE employees SET salary = 55000 WHERE id = 1;
DELETE FROM employees WHERE id = 1;
-- DCL
GRANT SELECT, INSERT ON employees TO 'qa_user'@'%';
REVOKE INSERT ON employees FROM 'qa_user'@'%';
-- TCL
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or
ROLLBACK;
DELETE vs TRUNCATE vs DROP — common interview question¶
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Type | DML | DDL | DDL |
| Removes | Rows (optionally filtered) | All rows | Table + structure |
| Rollback? | Yes (within transaction) | No (auto-commits in MySQL) | No |
| Speed | Slower (row-by-row, fires triggers) | Faster (deallocates pages) | Fastest |
| Resets AUTO_INCREMENT? | No | Yes | N/A (table gone) |
| WHERE clause? | Yes | No | No |
| Triggers fired? | Yes | No | No |
3. JOINS — THE MOST-TESTED TOPIC¶
3.1 The 6 join types — visualized¶
Two tables: A and B
A: 1, 2, 3
B: 2, 3, 4
INNER JOIN → 2, 3 (intersection)
LEFT JOIN → 1, 2, 3 (all of A, matching B)
RIGHT JOIN → 2, 3, 4 (all of B, matching A)
FULL OUTER JOIN → 1, 2, 3, 4 (everything)
CROSS JOIN → 9 rows (A × B; every pair)
SELF JOIN → join A with itself (employees + managers in one table)
3.2 INNER JOIN — only matched rows¶
Query¶
SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
Step-by-step¶
- FROM employees e — start with employees table, alias as
e - INNER JOIN departments d — bring in departments table, alias as
d - ON e.dept_id = d.id — join condition: match rows where employee's
dept_idequals department'sid - SELECT e.name, d.name — return employee name and department name from matched rows
- Rows in
employeeswithdept_idNULL or with no matching department disappear
Common bug¶
"I forgot the join condition" — without
ON, this becomes a CROSS JOIN. Some DBs require ON for INNER JOIN; others (MySQL) silently produce a cartesian product. Always include ON.
3.3 LEFT JOIN — all of LEFT, matched of RIGHT¶
Query: Customers and their orders (including customers with NO orders)¶
Step-by-step¶
- Start with
customers c(LEFT side) - Try to match each customer with rows in
orderswhereo.customer_id = c.id - Customers with no orders are kept —
o.amountwill beNULLfor those - Customers with multiple orders appear multiple times (once per matching order)
Real QA use case¶
"Find customers who never ordered" — LEFT JOIN + filter on NULL.
The classic LEFT JOIN trap¶
-- LOOKS like LEFT JOIN but behaves like INNER JOIN
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100; -- ← this filter kills the NULL rows!
-- FIX: move the condition into the ON clause
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.amount > 100;
ON.
3.4 RIGHT JOIN¶
Mirror of LEFT JOIN. Rarely used because you can swap the table order and use LEFT JOIN (more readable).
SELECT c.name, o.amount
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;
-- Equivalent to:
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
3.5 FULL OUTER JOIN — all rows from both¶
Returns: - All customers (NULL amount if no orders) - All orders (NULL name if customer deleted)
MySQL doesn't support FULL OUTER JOIN directly. Workaround:
SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.amount FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;
3.6 CROSS JOIN — Cartesian product¶
SELECT p.name AS product, s.name AS size
FROM products p
CROSS JOIN sizes s;
-- Every product × every size = product variants matrix
If products has 10 rows and sizes has 5 rows → result has 50 rows.
Use case: generating all combinations (test data, matrix tables). Warning: accidentally CROSS JOINing two million-row tables = trillion-row result.
3.7 SELF JOIN — join a table to itself¶
Query: employees and their managers (both in the same employees table)¶
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Step-by-step¶
- Use the
employeestable twice — once ase(the employee), once asm(the manager) - Join on
e.manager_id = m.id - LEFT JOIN keeps employees with no manager (CEO has
manager_id = NULL)
Real interview question: find employees earning more than their manager¶
SELECT e.name AS employee, m.name AS manager,
e.salary AS emp_sal, m.salary AS mgr_sal
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
3.8 Multi-table JOIN — 3 or more tables¶
-- Customers, their orders, the products in each order
SELECT c.name, o.id AS order_id, p.name AS product, oi.quantity
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'COMPLETED';
Tip: start with the most-restrictive table to minimize intermediate result size. Query planners often do this automatically, but for complex queries, ordering matters.
4. GROUP BY + HAVING + AGGREGATIONS¶
4.1 Aggregate functions¶
COUNT(*) -- total rows (including NULLs)
COUNT(column) -- rows where column IS NOT NULL
COUNT(DISTINCT col) -- unique non-NULL values
SUM(salary)
AVG(salary)
MIN(salary)
MAX(salary)
GROUP_CONCAT(name) -- MySQL: concatenate strings (',' separator default)
STRING_AGG(name, ',') -- Postgres equivalent
4.2 GROUP BY¶
-- Count employees per department
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id;
Step-by-step¶
- Take all rows from
employees - Bucket them by
dept_id— all rows with samedept_idgo in one bucket - For each bucket, compute
COUNT(*) - Return one row per bucket
Rule of thumb¶
Every column in SELECT must either be in GROUP BY or be inside an aggregate function.
-- BAD — name is neither in GROUP BY nor aggregated
SELECT dept_id, name, COUNT(*) FROM employees GROUP BY dept_id;
-- ERROR (or undefined behavior in MySQL with sql_mode loose)
-- GOOD
SELECT dept_id, COUNT(*) AS emp_count FROM employees GROUP BY dept_id;
-- GOOD: aggregate the non-grouped column
SELECT dept_id, GROUP_CONCAT(name) AS employees, COUNT(*) AS cnt
FROM employees GROUP BY dept_id;
4.3 HAVING — filter groups¶
-- Departments with more than 5 employees
SELECT dept_id, COUNT(*) AS cnt
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;
WHERE vs HAVING — the classic question¶
| WHERE | HAVING | |
|---|---|---|
| Filters | Rows | Groups |
| Runs | Before GROUP BY | After GROUP BY |
| Can use aggregates? | No | Yes |
-- Combined: high-salary employees grouped by dept, only depts with > 3 such employees
SELECT dept_id, COUNT(*) AS high_earners
FROM employees
WHERE salary > 100000 -- 1. Filter rows
GROUP BY dept_id -- 2. Bucket
HAVING COUNT(*) > 3; -- 3. Filter buckets
4.4 Aggregations with JOINs¶
-- Total revenue per customer
SELECT c.name, COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total DESC;
Why COALESCE: customers with no orders have SUM(NULL) = NULL. COALESCE replaces NULL with 0 for readability.
4.5 ROLLUP — subtotals at multiple levels (MySQL/Postgres)¶
SELECT dept_id, category, SUM(salary)
FROM employees JOIN job_categories USING (id)
GROUP BY dept_id, category WITH ROLLUP;
-- Returns: rows per (dept, category), subtotals per dept, grand total
5. SUBQUERIES¶
5.1 Non-correlated subquery (independent, runs once)¶
-- Second highest salary using a subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Step-by-step¶
- Inner query runs first:
SELECT MAX(salary) FROM employees→ returns e.g., 200000 - Outer query runs:
WHERE salary < 200000→ all employees except the top earner MAX(salary)of that = second highest
5.2 Correlated subquery (depends on outer query, runs per row)¶
-- For each employee, find how many people earn more than them
SELECT e.name, e.salary,
(SELECT COUNT(*) FROM employees e2 WHERE e2.salary > e.salary) AS rank_from_top
FROM employees e
ORDER BY rank_from_top;
Step-by-step¶
- Outer query iterates each employee
e - For each
e, inner query runs — countinge2employees with salary higher - Inner query references the outer
e.salary— that's what makes it "correlated"
Performance note: correlated subqueries can be slow on large tables (run N times for N outer rows). Often rewritable as JOIN or window function.
5.3 Subquery in FROM (derived table)¶
SELECT dept_name, avg_sal
FROM (
SELECT d.name AS dept_name, AVG(e.salary) AS avg_sal
FROM employees e JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
) AS dept_stats
WHERE avg_sal > 50000;
The subquery produces a temporary result set called dept_stats, used as a table.
5.4 EXISTS vs IN¶
IN — list-based¶
EXISTS — existence check¶
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'COMPLETED');
| IN | EXISTS | |
|---|---|---|
| Returns | List of values | Boolean |
| Stops on first match | No | Yes (faster on large subqueries) |
| NULL handling | IN (NULL, 1) → unpredictable |
Always works |
| Best when | Subquery small | Subquery large or correlated |
Rule of thumb: for "does at least one related row exist?", prefer EXISTS.
6. CTEs — COMMON TABLE EXPRESSIONS¶
CTEs (WITH clause) are the modern way to write what used to be subqueries. They're more readable and can be referenced multiple times.
6.1 Basic CTE¶
WITH dept_stats AS (
SELECT d.name AS dept_name, AVG(e.salary) AS avg_sal
FROM employees e JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
)
SELECT * FROM dept_stats WHERE avg_sal > 50000;
Same result as the derived-table example above, but more readable.
6.2 Multiple CTEs¶
WITH
high_earners AS (
SELECT * FROM employees WHERE salary > 100000
),
low_earners AS (
SELECT * FROM employees WHERE salary < 30000
)
SELECT
(SELECT COUNT(*) FROM high_earners) AS high_count,
(SELECT COUNT(*) FROM low_earners) AS low_count;
6.3 Recursive CTE — hierarchy traversal¶
-- Get the entire reporting chain under a given manager
WITH RECURSIVE reports AS (
-- Anchor: start with the given manager
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE id = 100
UNION ALL
-- Recursive: find employees whose manager is in the current set
SELECT e.id, e.name, e.manager_id, r.level + 1
FROM employees e
JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports ORDER BY level, name;
Step-by-step¶
- Anchor query runs once — returns the starting manager (
id = 100) withlevel = 1 - Recursive query runs repeatedly — each iteration finds direct reports of employees from the previous iteration
- Recursion ends when an iteration returns zero rows
- Final result = union of all iterations
Use case: org charts, category trees, dependency graphs.
7. WINDOW FUNCTIONS¶
The single most powerful modern SQL feature. Every SDET-2 interview tests these.
7.1 What a window function does¶
Like GROUP BY, but returns one row per input row (instead of one row per group).
Without window function (GROUP BY)¶
With window function (OVER)¶
SELECT name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;
-- One row per employee, each with their dept's average
7.2 The OVER clause anatomy¶
function() OVER (
PARTITION BY col1, col2 -- group rows for the calculation
ORDER BY col3 [DESC] -- order within each partition
ROWS BETWEEN n PRECEDING AND CURRENT ROW -- frame (advanced)
)
- PARTITION BY — like GROUP BY but for the window
- ORDER BY — required for ordering-sensitive functions (RANK, LAG, etc.)
- Frame — restrict the rows the function sees (advanced; defaults are usually right)
7.3 RANK vs DENSE_RANK vs ROW_NUMBER¶
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
Differences with ties (3 employees with salary 100000)¶
| name | salary | row_number | rank | dense_rank |
|---|---|---|---|---|
| A | 100000 | 1 | 1 | 1 |
| B | 100000 | 2 | 1 | 1 |
| C | 100000 | 3 | 1 | 1 |
| D | 90000 | 4 | 4 | 2 |
| E | 80000 | 5 | 5 | 3 |
- ROW_NUMBER: unique per row (1, 2, 3, 4, 5)
- RANK: ties get same rank, next rank skips (1, 1, 1, 4, 5)
- DENSE_RANK: ties get same rank, no skip (1, 1, 1, 2, 3)
Common interview use — "Nth highest salary"¶
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2; -- 2nd highest (unique, regardless of ties)
7.4 PARTITION BY — per-group ranking¶
-- Top 3 highest-paid employees per department
SELECT * FROM (
SELECT name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk <= 3;
Step-by-step¶
- Each employee row keeps a
rnkfield PARTITION BY dept_idresets the ranking for each department- Within each dept,
ORDER BY salary DESCranks - Outer query keeps only rank 1, 2, 3 — top 3 per dept
7.5 LEAD and LAG — peek next/previous row¶
SELECT name, hire_date,
LAG(name) OVER (ORDER BY hire_date) AS prev_hire,
LEAD(name) OVER (ORDER BY hire_date) AS next_hire
FROM employees;
Use case: salary change from one employee to next¶
SELECT name, salary,
salary - LAG(salary) OVER (ORDER BY salary DESC) AS diff_from_prev
FROM employees;
7.6 FIRST_VALUE, LAST_VALUE, NTH_VALUE¶
-- Each employee's salary alongside the highest in their dept
SELECT name, dept_id, salary,
FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS top_in_dept
FROM employees;
7.7 Running total / cumulative sum¶
With PARTITION¶
-- Running total per customer, ordered by date
SELECT customer_id, amount, created_at,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY created_at
) AS customer_running_total
FROM orders;
7.8 NTILE — divide into N buckets¶
-- Quartiles of salary
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
-- quartile 1 = lowest 25%, 4 = highest 25%
8. CASE EXPRESSIONS + PIVOT¶
8.1 Basic CASE¶
SELECT name, salary,
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary < 80000 THEN 'Mid'
ELSE 'High'
END AS salary_band
FROM employees;
8.2 CASE in aggregation (the pivot pattern)¶
-- Count employees per gender per department (pivot)
SELECT dept_id,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female,
SUM(CASE WHEN gender = 'O' THEN 1 ELSE 0 END) AS other
FROM employees
GROUP BY dept_id;
Step-by-step¶
- Bucket rows by
dept_id - For each bucket, for each row, check gender — if matches, count as 1; else 0
- SUM the 1s → count of that gender in the bucket
- Three SUMs → three columns of counts
Equivalent in MySQL with shorthand¶
SELECT dept_id,
SUM(gender = 'M') AS male,
SUM(gender = 'F') AS female
FROM employees
GROUP BY dept_id;
8.3 CASE in ORDER BY — custom sort¶
-- Sort with custom priority: VIP first, then GOLD, then SILVER, then others
SELECT name, tier
FROM customers
ORDER BY CASE tier
WHEN 'VIP' THEN 1
WHEN 'GOLD' THEN 2
WHEN 'SILVER' THEN 3
ELSE 4
END;
9. UNION, UNION ALL, INTERSECT, EXCEPT¶
9.1 UNION — combine result sets, remove duplicates¶
9.2 UNION ALL — combine, keep duplicates (faster)¶
When to use which¶
- UNION — when duplicates matter and you want them removed (slower; does a sort)
- UNION ALL — when duplicates can't occur, OR when you actively want to keep them (faster)
Rule of thumb: default to UNION ALL unless you specifically need deduplication.
9.3 INTERSECT — common rows¶
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;
-- Emails that are both customers AND subscribers
MySQL doesn't support INTERSECT directly — use INNER JOIN or EXISTS.
9.4 EXCEPT (MINUS in Oracle) — rows in A not in B¶
SELECT email FROM customers
EXCEPT
SELECT email FROM newsletter_subscribers;
-- Customers who haven't subscribed
9.5 Column rules for UNION¶
- Same number of columns
- Compatible data types (column 1 of all SELECTs must be same type)
- Column names from the first SELECT are used in the result
-- Combining employees and customers as "all_users"
SELECT id, name, 'employee' AS type FROM employees
UNION ALL
SELECT id, name, 'customer' AS type FROM customers;
10. INDEXES + EXPLAIN + OPTIMIZATION¶
10.1 What an index is¶
A separate data structure (usually B-tree) that maps column values to row locations. Trades disk space and write speed for read speed.
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_email_unique ON users(email);
CREATE INDEX idx_dept_salary ON employees(dept_id, salary); -- composite
DROP INDEX idx_email ON users;
10.2 When indexes help¶
- WHERE clauses on indexed columns
- JOIN ON clauses
- ORDER BY on indexed columns
- Aggregations using indexed columns
10.3 When indexes don't help¶
WHERE UPPER(name) = 'ROHAN'— function on indexed column kills the indexWHERE name LIKE '%han'— leading wildcard kills the index- Tiny tables (full scan is faster than index lookup)
- Columns with low cardinality (gender = 'M'/'F' — useless to index)
10.4 Composite (multi-column) index¶
Leftmost prefix rule¶
This index helps:
- WHERE dept_id = 5 ← uses index
- WHERE dept_id = 5 AND salary > 50000 ← uses index
- ORDER BY dept_id, salary ← uses index
This index doesn't help:
- WHERE salary > 50000 ← skips leftmost col, can't use index
Memory rule: composite index (A, B, C) works for queries that filter on A, A+B, or A+B+C. Not on B, C, or B+C alone.
10.5 EXPLAIN — see the query plan¶
Key columns to read¶
| Column | What it tells you |
|---|---|
| type | Access type — ALL (full table scan, bad), index (full index scan), range, ref, eq_ref, const (best) |
| rows | Estimated rows the engine will examine |
| key | Which index was used (NULL = none) |
| Extra | Using filesort, Using temporary, Using where, etc. |
Common red flags¶
type = ALL— full table scan when an index should applyUsing filesort— sorting without an index helpsUsing temporary— creating a temp table mid-query- Very high
rowsfor what should be a small result
10.6 Real optimization story¶
Slow query:
SELECT * FROM orders WHERE LOWER(customer_email) = 'rohan@x.com';
-- Takes 12 seconds on a 5M-row table
EXPLAIN shows type=ALL, rows=5000000, full table scan.
Why slow: LOWER(customer_email) is a function on the indexed column, so the engine can't use the index — it scans every row.
Fix: store emails normalized (lowercase on INSERT), then query without LOWER. Or create a functional index:
After fix: 0.01 seconds.
11. CONSTRAINTS + NORMALIZATION¶
11.1 The 5 main constraints¶
| Constraint | Purpose | Example |
|---|---|---|
| PRIMARY KEY | Unique + NOT NULL row identifier | id INT PRIMARY KEY |
| FOREIGN KEY | References PK of another table | FOREIGN KEY (dept_id) REFERENCES departments(id) |
| UNIQUE | No duplicates (NULLs allowed once or many — varies) | email VARCHAR(100) UNIQUE |
| NOT NULL | Column can't be NULL | name VARCHAR(100) NOT NULL |
| CHECK | Custom validation | CHECK (salary >= 0) |
| DEFAULT | Default value if not provided | created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
11.2 Foreign key actions¶
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE CASCADE -- delete employees when dept is deleted
ON UPDATE CASCADE -- update employees' dept_id when dept's id changes
-- Other options: SET NULL, RESTRICT, NO ACTION
11.3 Normalization (1NF, 2NF, 3NF, BCNF)¶
1NF — atomic values, no repeating groups¶
-- VIOLATES 1NF
phones: '9876543210, 9876543211' -- comma-separated values
-- 1NF
employee_phones (employee_id, phone) -- one row per phone
2NF — 1NF + no partial dependency on composite PK¶
-- VIOLATES 2NF: order_items has composite PK (order_id, product_id) but product_name only depends on product_id
order_items: (order_id, product_id, product_name, quantity)
-- 2NF: move product_name to products table
products: (product_id, product_name)
order_items: (order_id, product_id, quantity)
3NF — 2NF + no transitive dependency¶
-- VIOLATES 3NF: employee → dept_id → dept_name (transitive)
employees: (id, name, dept_id, dept_name)
-- 3NF: dept_name belongs in departments
employees: (id, name, dept_id)
departments: (id, name)
When to denormalize¶
For read-heavy reporting or analytics, joining 7 tables every query is expensive. Denormalize (add redundant columns) for speed. Standard pattern in data warehouses.
12. TRANSACTIONS + ACID + ISOLATION¶
12.1 ACID — memorize the 4 letters¶
| Stands for | What it guarantees | |
|---|---|---|
| A | Atomicity | All operations in the transaction succeed, or none do |
| C | Consistency | DB moves from one valid state to another |
| I | Isolation | Concurrent transactions don't see each other's mid-progress changes |
| D | Durability | Once committed, changes survive crashes |
Atomicity example¶
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If second UPDATE fails, ROLLBACK reverses the first.
12.2 Isolation levels (4 standard)¶
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | ✗ Yes | ✗ Yes | ✗ Yes |
| READ COMMITTED | ✓ No | ✗ Yes | ✗ Yes |
| REPEATABLE READ | ✓ No | ✓ No | ✗ Yes (MySQL handles via gap locks) |
| SERIALIZABLE | ✓ No | ✓ No | ✓ No |
The three concurrency problems¶
- Dirty read — read uncommitted changes from another transaction
- Non-repeatable read — read same row twice, get different values
- Phantom read — same WHERE returns different number of rows on second query
Set isolation¶
Defaults¶
- MySQL (InnoDB): REPEATABLE READ
- PostgreSQL: READ COMMITTED
- SQL Server: READ COMMITTED
- Oracle: READ COMMITTED
12.3 SAVEPOINT — partial rollback¶
START TRANSACTION;
INSERT INTO orders VALUES (...);
SAVEPOINT sp1;
INSERT INTO order_items VALUES (...);
-- something went wrong with items
ROLLBACK TO sp1; -- order still there, items rolled back
INSERT INTO order_items VALUES (...); -- try again
COMMIT;
13. STORED PROCEDURES, FUNCTIONS, TRIGGERS, VIEWS¶
13.1 View — a saved query (virtual table)¶
CREATE VIEW active_orders AS
SELECT o.id, o.amount, c.name AS customer
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'ACTIVE';
-- Use like a table
SELECT * FROM active_orders WHERE amount > 1000;
Use cases: simplify complex joins, restrict access (grant SELECT on view, not underlying table), abstract schema changes.
13.2 Stored procedure — saved logic, no return value required¶
DELIMITER //
CREATE PROCEDURE give_raise(IN emp_id INT, IN pct DECIMAL(5,2))
BEGIN
UPDATE employees
SET salary = salary * (1 + pct / 100)
WHERE id = emp_id;
END //
DELIMITER ;
-- Call
CALL give_raise(42, 10);
13.3 Function — returns a value¶
DELIMITER //
CREATE FUNCTION annual_salary(emp_id INT) RETURNS DECIMAL(12, 2)
DETERMINISTIC
BEGIN
DECLARE result DECIMAL(12, 2);
SELECT salary * 12 INTO result FROM employees WHERE id = emp_id;
RETURN result;
END //
DELIMITER ;
-- Use in SELECT
SELECT name, annual_salary(id) FROM employees;
13.4 Trigger — auto-runs on INSERT/UPDATE/DELETE¶
CREATE TRIGGER audit_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END IF;
END;
Use cases: audit logs, derived field maintenance (e.g., updated_at), referential integrity beyond foreign keys.
14. 50+ CLASSIC INTERVIEW QUESTIONS¶
Q1. Second highest salary¶
-- Method 1
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Method 2 (handles ties via DENSE_RANK)
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk = 2;
-- Method 3 (MySQL LIMIT)
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
Q2. Nth highest salary (parameterized)¶
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk = N;
Q3. Find duplicate emails¶
Q4. Delete duplicates, keep one (lowest ID)¶
-- MySQL
DELETE u1 FROM users u1
INNER JOIN users u2
ON u1.email = u2.email AND u1.id > u2.id;
-- Postgres / using window function
DELETE FROM users WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) t WHERE rn > 1
);
Q5. Employees earning more than their manager¶
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
Q6. Department with highest average salary¶
SELECT d.name, AVG(e.salary) AS avg_sal
FROM employees e JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
ORDER BY avg_sal DESC
LIMIT 1;
Q7. Customers who never ordered¶
SELECT c.name FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
-- OR
SELECT name FROM customers WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);
-- OR
SELECT name FROM customers c WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
Q8. Top 3 highest-paid per department¶
SELECT * FROM (
SELECT name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk <= 3;
Q9. Count employees per department, include departments with 0¶
SELECT d.name, COUNT(e.id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.name;
Q10. Find employees with no manager¶
Q11. Average salary excluding the highest and lowest¶
SELECT AVG(salary) AS adjusted_avg
FROM employees
WHERE salary NOT IN (
(SELECT MAX(salary) FROM employees),
(SELECT MIN(salary) FROM employees)
);
Q12. Employees hired in the last 30 days¶
SELECT * FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '30 days';
-- MySQL: hire_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
Q13. Cumulative salary by hire date¶
SELECT name, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;
Q14. Find duplicate rows (entire row, not just one column)¶
Q15. Most recent order per customer¶
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders
) t WHERE rn = 1;
Q16. Difference between two consecutive rows (sales growth)¶
Q17. Pivot: count of orders per month per status¶
SELECT MONTH(created_at) AS month,
SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS pending,
SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled
FROM orders
WHERE YEAR(created_at) = 2026
GROUP BY MONTH(created_at);
Q18. Find 3 consecutive numbers in a sequence¶
SELECT DISTINCT l1.num
FROM logs l1
JOIN logs l2 ON l1.id + 1 = l2.id AND l1.num = l2.num
JOIN logs l3 ON l1.id + 2 = l3.id AND l1.num = l3.num;
Q19. Find the median salary¶
-- Using window function
SELECT AVG(salary) AS median
FROM (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS total
FROM employees
) t
WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));
Q20. Self-join: find pairs of employees with same salary¶
SELECT e1.name, e2.name, e1.salary
FROM employees e1
JOIN employees e2 ON e1.salary = e2.salary AND e1.id < e2.id;
e1.id < e2.id prevents duplicate pairs and self-pairs.
Q21. Department with highest total salary spent¶
SELECT d.name, SUM(e.salary) AS total
FROM employees e JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
ORDER BY total DESC LIMIT 1;
Q22. Employees whose name has 5+ characters¶
Q23. Concatenate employees per department¶
-- MySQL
SELECT dept_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY dept_id;
-- Postgres
SELECT dept_id, STRING_AGG(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY dept_id;
Q24. Find Nth row from a table¶
Q25. Update salary by 10% for employees in 'Engineering'¶
UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.salary = e.salary * 1.10
WHERE d.name = 'Engineering';
Q26. Swap values in two columns¶
UPDATE employees
SET col1 = col2, col2 = col1;
-- Works because both right-hand sides are evaluated before assignment
Q27. Copy data from one table to another¶
INSERT INTO employees_archive (id, name, salary)
SELECT id, name, salary FROM employees WHERE hire_date < '2020-01-01';
Q28. Conditional aggregation¶
SELECT
COUNT(*) AS total,
COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
AVG(CASE WHEN status = 'active' THEN salary END) AS active_avg_salary
FROM employees;
Q29. Find odd / even rows¶
-- MySQL
SELECT * FROM employees WHERE MOD(id, 2) = 0; -- even
SELECT * FROM employees WHERE MOD(id, 2) = 1; -- odd
-- Using ROW_NUMBER
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM employees
) t WHERE rn % 2 = 0;
Q30. Get current date / extract parts¶
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, NOW();
SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date) FROM employees;
SELECT DATE_FORMAT(hire_date, '%Y-%m') AS month FROM employees;
Q31. Find employees with age between two values (date arithmetic)¶
SELECT name,
TIMESTAMPDIFF(YEAR, dob, CURRENT_DATE) AS age
FROM employees
WHERE TIMESTAMPDIFF(YEAR, dob, CURRENT_DATE) BETWEEN 25 AND 40;
Q32. Find employees who joined in a specific month¶
Q33. Find departments with no employees¶
Q34. Find duplicate orders for same customer on same day¶
SELECT customer_id, DATE(created_at) AS order_date, COUNT(*) AS dup_count
FROM orders
GROUP BY customer_id, DATE(created_at)
HAVING COUNT(*) > 1;
Q35. Top spending customer per month¶
SELECT * FROM (
SELECT customer_id,
DATE_FORMAT(created_at, '%Y-%m') AS month,
SUM(amount) AS total,
RANK() OVER (PARTITION BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY SUM(amount) DESC) AS rnk
FROM orders
GROUP BY customer_id, DATE_FORMAT(created_at, '%Y-%m')
) t WHERE rnk = 1;
Q36. Find employees not in any project (assuming projects table)¶
SELECT * FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM project_members pm WHERE pm.employee_id = e.id
);
Q37. Show employees grouped with their reporting chain depth¶
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY depth, name;
Q38. Find customers with their order count + recent order¶
SELECT c.name,
COUNT(o.id) AS order_count,
MAX(o.created_at) AS most_recent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;
Q39. Find products that have NEVER been ordered (assuming order_items)¶
SELECT * FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);
Q40. Find orders with amount > average order amount¶
Q41. Find days where no orders were placed (gap detection)¶
Requires a calendar table or generate_series:
-- Postgres
SELECT d::date AS day FROM generate_series(
(SELECT MIN(created_at)::date FROM orders),
(SELECT MAX(created_at)::date FROM orders),
'1 day'
) d
WHERE NOT EXISTS (
SELECT 1 FROM orders WHERE created_at::date = d::date
);
Q42. Percentage of each status¶
SELECT status,
COUNT(*) AS cnt,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM orders
GROUP BY status;
Q43. Find employees with same name (duplicates by name)¶
Q44. Get the second-most-recent order per customer¶
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders
) t WHERE rn = 2;
Q45. Find employees whose salary is in top 10% of their dept¶
SELECT name, dept_id, salary FROM (
SELECT *,
NTILE(10) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS bucket
FROM employees
) t WHERE bucket = 1;
Q46. Print "FizzBuzz" up to N using SQL¶
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n,
CASE
WHEN n % 15 = 0 THEN 'FizzBuzz'
WHEN n % 3 = 0 THEN 'Fizz'
WHEN n % 5 = 0 THEN 'Buzz'
ELSE CAST(n AS VARCHAR(10))
END AS output
FROM nums;
Q47. Find managers with more than 5 reports¶
SELECT manager_id, COUNT(*) AS report_count
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING COUNT(*) > 5;
Q48. Find the longest streak of consecutive days with orders¶
WITH ordered_days AS (
SELECT DISTINCT DATE(created_at) AS d FROM orders
),
grouped AS (
SELECT d,
d - INTERVAL ROW_NUMBER() OVER (ORDER BY d) DAY AS grp
FROM ordered_days
)
SELECT grp, MIN(d) AS start_d, MAX(d) AS end_d, COUNT(*) AS streak_length
FROM grouped
GROUP BY grp
ORDER BY streak_length DESC LIMIT 1;
Q49. Update with JOIN (give all admins a 20% raise)¶
UPDATE employees e
JOIN user_roles r ON r.user_id = e.id
SET e.salary = e.salary * 1.20
WHERE r.role = 'admin';
Q50. Find employees who joined in same month as their manager¶
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE YEAR(e.hire_date) = YEAR(m.hire_date)
AND MONTH(e.hire_date) = MONTH(m.hire_date);
15. MYSQL-SPECIFIC FEATURES¶
15.1 LIMIT shorthand¶
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users LIMIT 20, 10; -- MySQL shorthand: (offset, count)
15.2 ON DUPLICATE KEY UPDATE (upsert)¶
INSERT INTO settings (user_id, key, value) VALUES (1, 'theme', 'dark')
ON DUPLICATE KEY UPDATE value = VALUES(value);
15.3 INSERT ... SELECT¶
15.4 REPLACE INTO¶
REPLACE INTO users (id, name) VALUES (1, 'Rohan');
-- If id 1 exists, DELETE the row, then INSERT. Different from ON DUPLICATE KEY UPDATE
15.5 GROUP_CONCAT¶
SELECT dept_id,
GROUP_CONCAT(name ORDER BY name DESC SEPARATOR ' | ') AS members
FROM employees
GROUP BY dept_id;
15.6 IFNULL / COALESCE¶
SELECT IFNULL(commission, 0) FROM employees;
SELECT COALESCE(commission, bonus, 0) FROM employees; -- first non-NULL
15.7 Engine types¶
- InnoDB (default since MySQL 5.5): ACID transactions, row-level locking, foreign keys
- MyISAM (legacy): faster reads, no transactions, table-level locking, no foreign keys
- Memory: in-memory only, lost on restart
- Archive: compressed, append-only
15.8 EXPLAIN ANALYZE (MySQL 8.0.18+)¶
Actually runs the query and shows real timings, not estimates.
15.9 Date / time functions¶
NOW() -- current timestamp
CURDATE() -- current date
CURTIME() -- current time
DATE_ADD(NOW(), INTERVAL 7 DAY)
DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
DATEDIFF(end, start) -- days between
TIMESTAMPDIFF(YEAR, dob, CURDATE()) -- age in years
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
STR_TO_DATE('21-03-2026', '%d-%m-%Y')
16. MONGODB FUNDAMENTALS¶
16.1 The document model¶
MongoDB stores data as BSON documents (binary JSON) inside collections, inside databases.
Example document¶
{
"_id": ObjectId("507f1f77bcf86cd799439011"),
"name": "Rohan",
"email": "rohan@x.com",
"age": 28,
"address": {
"city": "Bangalore",
"pincode": "560001"
},
"skills": ["Java", "Python", "Playwright"],
"orders": [
{ "id": 1, "amount": 500 },
{ "id": 2, "amount": 1200 }
],
"created_at": ISODate("2026-01-01T00:00:00Z")
}
16.2 Schema-less (sort of)¶
MongoDB doesn't enforce a schema by default — different documents in the same collection can have different fields. This is flexibility you pay for in inconsistency. Production apps usually enforce schemas via:
- Mongoose (Node.js) schemas
- MongoDB schema validation ($jsonSchema)
- Application-level validation
16.3 ObjectId — the default _id¶
12-byte unique identifier: - 4 bytes: timestamp - 5 bytes: random per-process value - 3 bytes: incrementing counter
You can also use custom _id values (strings, numbers) — just be careful about uniqueness.
16.4 BSON vs JSON¶
- JSON: text format, human-readable
- BSON: binary, faster to parse and traverse, supports more types (
Date,ObjectId,Binary,Decimal128)
17. MONGODB CRUD + QUERY OPERATORS¶
17.1 Insert¶
db.users.insertOne({ name: "Rohan", age: 28 });
db.users.insertMany([
{ name: "Asha", age: 25 },
{ name: "Bob", age: 30 }
]);
17.2 Find¶
db.users.find(); // all
db.users.find({ age: 25 }); // exact match
db.users.find({ age: { $gt: 25 } }); // age > 25
db.users.findOne({ email: "r@x.com" }); // first matching
db.users.find().limit(10).skip(20); // pagination
db.users.find().sort({ age: -1 }); // sort desc
db.users.find({}, { name: 1, email: 1, _id: 0 }); // projection
db.users.countDocuments({ age: { $gt: 25 } });
17.3 Comparison operators¶
| Operator | Meaning | Example |
|---|---|---|
$eq |
equals | { age: { $eq: 25 } } (same as { age: 25 }) |
$ne |
not equals | { age: { $ne: 25 } } |
$gt, $gte |
greater than | { age: { $gt: 18 } } |
$lt, $lte |
less than | { age: { $lt: 65 } } |
$in |
in list | { status: { $in: ["active", "pending"] } } |
$nin |
not in list | { status: { $nin: ["banned"] } } |
17.4 Logical operators¶
db.users.find({
$and: [{ age: { $gt: 18 } }, { age: { $lt: 65 } }]
});
db.users.find({
$or: [{ city: "Bangalore" }, { city: "Mumbai" }]
});
db.users.find({ age: { $not: { $gt: 65 } } }); // not older than 65
db.users.find({
$nor: [{ status: "banned" }, { status: "deleted" }]
});
17.5 Element operators¶
db.users.find({ email: { $exists: true } }); // has email field
db.users.find({ email: { $exists: false } });
db.users.find({ age: { $type: "number" } }); // type-check
db.users.find({ age: { $type: "string" } }); // misformed records!
17.6 String / regex matching¶
db.users.find({ name: /^Ro/ }); // starts with Ro
db.users.find({ name: { $regex: /han$/, $options: "i" } }); // case-insensitive
db.users.find({ $text: { $search: "playwright testing" } }); // requires text index
17.7 Array operators¶
db.users.find({ skills: "Python" }); // contains "Python"
db.users.find({ skills: { $all: ["Python", "Java"] } }); // contains both
db.users.find({ skills: { $size: 3 } }); // exactly 3 skills
db.users.find({ skills: { $in: ["Python", "Ruby"] } }); // either
db.users.find({ "skills.0": "Python" }); // first skill is Python
db.users.find({ orders: { $elemMatch: { amount: { $gt: 1000 } } } });
// at least one order with amount > 1000
17.8 Nested field access¶
db.users.find({ "address.city": "Bangalore" });
db.users.find({ "address.pincode": { $regex: /^560/ } });
17.9 Update¶
// updateOne / updateMany
db.users.updateOne(
{ _id: ObjectId("...") },
{ $set: { age: 29 } }
);
db.users.updateMany(
{ city: "Bangalore" },
{ $set: { country: "India" } }
);
// Common update operators
{ $set: { name: "Rohan" } } // set field
{ $unset: { tempField: "" } } // remove field
{ $inc: { age: 1 } } // increment
{ $mul: { salary: 1.10 } } // multiply
{ $rename: { oldField: "newField" } }
{ $push: { skills: "Go" } } // append to array
{ $addToSet: { skills: "Java" } } // append if not present
{ $pull: { skills: "PHP" } } // remove from array
{ $pop: { skills: 1 } } // remove last (-1 first)
17.10 Upsert¶
db.users.updateOne(
{ email: "r@x.com" },
{ $set: { name: "Rohan", age: 28 } },
{ upsert: true } // insert if not found
);
db.users.replaceOne(
{ email: "r@x.com" },
{ name: "Rohan", email: "r@x.com", age: 28 },
{ upsert: true }
);
17.11 Delete¶
db.users.deleteOne({ _id: ObjectId("...") });
db.users.deleteMany({ status: "inactive" });
db.users.drop(); // drop entire collection
17.12 findAndModify variants¶
db.users.findOneAndUpdate(
{ _id: ObjectId("...") },
{ $set: { age: 29 } },
{ returnNewDocument: true } // returns updated doc, not the old
);
db.users.findOneAndDelete({ _id: ObjectId("...") });
db.users.findOneAndReplace({ ... }, { ... });
18. MONGODB AGGREGATION PIPELINE¶
The biggest MongoDB topic. The aggregation pipeline transforms documents through a series of stages — like Unix pipes.
18.1 The shape¶
Each stage takes documents in, transforms them, passes them to the next stage.
18.2 The most-used stages¶
| Stage | What it does | SQL analog |
|---|---|---|
$match |
Filter documents | WHERE |
$project |
Reshape fields (include/exclude/compute) | SELECT |
$group |
Group by + aggregate | GROUP BY |
$sort |
Sort | ORDER BY |
$limit, $skip |
Pagination | LIMIT, OFFSET |
$lookup |
Join another collection | LEFT JOIN |
$unwind |
Split array into separate documents | (no SQL analog) |
$count |
Count documents | COUNT(*) |
$facet |
Multiple pipelines in parallel | (no SQL analog) |
$addFields / $set |
Add computed fields | SELECT with derived columns |
$replaceRoot |
Promote a sub-document to root | (no SQL analog) |
18.3 $match + $group + $sort (the classic combo)¶
Total revenue per customer¶
db.orders.aggregate([
{ $match: { status: "COMPLETED" } },
{ $group: {
_id: "$customer_id",
total: { $sum: "$amount" },
order_count: { $sum: 1 }
}},
{ $sort: { total: -1 } }
]);
Step-by-step¶
$match— filter only completed orders (faster than filtering later)$group— bucket bycustomer_id, compute total amount and count$sort— sort by total descending
Performance tip: put $match and $limit as early as possible to reduce documents in the pipeline.
18.4 $group with aggregate operators¶
db.orders.aggregate([
{ $group: {
_id: "$customer_id",
total: { $sum: "$amount" },
count: { $sum: 1 },
avg: { $avg: "$amount" },
max: { $max: "$amount" },
min: { $min: "$amount" },
first: { $first: "$amount" },
last: { $last: "$amount" },
all_ids: { $push: "$_id" }, // array of all order IDs
unique_statuses: { $addToSet: "$status" }
}}
]);
18.5 $lookup — the JOIN¶
Get orders with customer info¶
db.orders.aggregate([
{ $lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer"
}},
{ $unwind: "$customer" } // flatten the array $lookup produces
]);
Step-by-step¶
$lookup— for each order, find matching customers; add them as an array incustomerfield$unwind— split the array (usually 1 element) socustomerbecomes a single object instead of array-of-one
$lookup with pipeline (Mongo 3.6+, more flexible)¶
db.orders.aggregate([
{ $lookup: {
from: "customers",
let: { custId: "$customer_id" },
pipeline: [
{ $match: { $expr: { $eq: ["$_id", "$$custId"] } } },
{ $project: { name: 1, email: 1 } }
],
as: "customer"
}}
]);
18.6 $project — reshape¶
db.users.aggregate([
{ $project: {
_id: 0,
fullName: { $concat: ["$firstName", " ", "$lastName"] },
ageGroup: {
$switch: {
branches: [
{ case: { $lt: ["$age", 18] }, then: "Minor" },
{ case: { $lt: ["$age", 65] }, then: "Adult" }
],
default: "Senior"
}
},
primaryEmail: { $arrayElemAt: ["$emails", 0] }
}}
]);
18.7 $unwind — explode arrays¶
// Document: { _id: 1, skills: ["Java", "Python"] }
db.users.aggregate([{ $unwind: "$skills" }]);
// Result:
// { _id: 1, skills: "Java" }
// { _id: 1, skills: "Python" }
Common use: count occurrences of array elements¶
db.users.aggregate([
{ $unwind: "$skills" },
{ $group: { _id: "$skills", count: { $sum: 1 } } },
{ $sort: { count: -1 } }
]);
// Top skills across all users
18.8 $facet — multiple pipelines in parallel¶
db.orders.aggregate([
{ $facet: {
"summary": [
{ $group: { _id: null, total: { $sum: "$amount" }, count: { $sum: 1 } } }
],
"byStatus": [
{ $group: { _id: "$status", count: { $sum: 1 } } }
],
"topCustomers": [
{ $group: { _id: "$customer_id", total: { $sum: "$amount" } } },
{ $sort: { total: -1 } },
{ $limit: 5 }
]
}}
]);
// Returns ONE document with 3 fields, each an array of pipeline results
Great for dashboards — one query, multiple analyses.
18.9 $bucket — group by range¶
db.users.aggregate([
{ $bucket: {
groupBy: "$age",
boundaries: [0, 18, 30, 50, 65, 100],
default: "Other",
output: {
count: { $sum: 1 },
names: { $push: "$name" }
}
}}
]);
18.10 Real interview pipeline example¶
Q: For each city, get top 3 highest-spending customers (by completed orders).
db.orders.aggregate([
{ $match: { status: "COMPLETED" } },
{ $group: {
_id: "$customer_id",
total: { $sum: "$amount" }
}},
{ $lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customer"
}},
{ $unwind: "$customer" },
{ $group: {
_id: "$customer.city",
topCustomers: {
$push: {
name: "$customer.name",
total: "$total"
}
}
}},
{ $project: {
city: "$_id",
_id: 0,
topCustomers: { $slice: [
{ $sortArray: { input: "$topCustomers", sortBy: { total: -1 } } },
3
]}
}}
]);
19. MONGODB INDEXES + SCHEMA DESIGN¶
19.1 Index types¶
db.users.createIndex({ email: 1 }); // single field, ascending
db.users.createIndex({ email: 1 }, { unique: true }); // unique
db.users.createIndex({ city: 1, age: -1 }); // compound
db.users.createIndex({ skills: 1 }); // multikey (array values indexed individually)
db.posts.createIndex({ title: "text", body: "text" }); // text search
db.places.createIndex({ location: "2dsphere" }); // geospatial
db.users.createIndex({ email: 1 }, { sparse: true }); // only docs with email field
db.users.getIndexes();
db.users.dropIndex("email_1");
19.2 Compound index leftmost prefix rule¶
Index { city: 1, age: 1, salary: 1 } supports:
- find({ city: "BLR" })
- find({ city: "BLR", age: 25 })
- find({ city: "BLR", age: 25, salary: 50000 })
Does NOT support:
- find({ age: 25 }) — skips city
- find({ salary: 50000 }) — skips city and age
19.3 explain() — see the plan¶
Key fields:
- winningPlan.stage — IXSCAN (index scan, good) or COLLSCAN (collection scan, bad)
- executionStats.totalDocsExamined — should be close to nReturned
- executionStats.totalKeysExamined — for indexed queries
19.4 Schema design — embed vs reference¶
Embed¶
Use when: - 1:1 or 1:few relationship - Embedded data always read with parent - Embedded data doesn't grow unboundedlyReference¶
// users collection
{ _id: 1, name: "Rohan" }
// orders collection
{ _id: 101, user_id: 1, amount: 500 }
Rule of thumb¶
Embed for performance, reference for flexibility. MongoDB recommends embedding when in doubt — fewer queries, faster reads.
19.5 The 16MB document limit¶
Single document max size = 16MB. If you might exceed this, you have to reference.
20. SQL vs MONGODB — WHEN TO USE WHICH¶
| SQL (RDBMS) | MongoDB (NoSQL) | |
|---|---|---|
| Data model | Tables with fixed schema | Flexible JSON-like documents |
| Schema | Strict, defined upfront | Flexible, evolves |
| Joins | First-class | Possible via $lookup, but slower |
| Transactions | Full ACID | ACID at single-doc level always; multi-doc since 4.0 |
| Scaling | Vertical (bigger box); harder horizontal | Horizontal-native (sharding built-in) |
| Best for | Structured data, complex queries, relations | Hierarchical / nested data, schema flexibility, large unstructured datasets |
| Examples | MySQL, PostgreSQL, SQL Server, Oracle | MongoDB, Couchbase, DynamoDB |
| Schema migration | Required, often painful | Add fields freely |
| Consistency | Strong by default | Tunable (per-write writeConcern) |
| Query language | SQL (declarative) | JS-like syntax + aggregation pipeline |
When to pick MongoDB¶
- Hierarchical or nested data (catalog with many varying attributes per product)
- Rapid prototype phase — schema will evolve
- High write throughput, partition-friendly data (user profiles, events)
- Geospatial / time-series
When to pick SQL¶
- Strong relational data (orders, transactions, invoices with strict integrity)
- Reporting / BI / analytics with complex joins
- Regulatory environments (banking, healthcare) where ACID matters
- Established BI tools (most assume SQL)
21. QA-SPECIFIC PATTERNS — BACKEND DATA VALIDATION QUERIES¶
This is what you do daily as an SDET — validate that the backend wrote what you expect.
21.1 Pre/post API call validation¶
-- Before:
SELECT count(*) FROM orders WHERE customer_id = 42;
-- Run the test that creates an order
-- After:
SELECT count(*) FROM orders WHERE customer_id = 42; -- expect +1
21.2 Audit trail validation¶
-- After updating user role to admin, verify the audit table has the change
SELECT * FROM user_role_audit
WHERE user_id = 42
ORDER BY changed_at DESC LIMIT 1;
-- Expected: latest entry has new_role = 'admin'
21.3 Cascade verification¶
-- After deleting a user, verify cascading deletes happened
SELECT COUNT(*) FROM orders WHERE customer_id = 42; -- expect 0
SELECT COUNT(*) FROM user_sessions WHERE user_id = 42; -- expect 0
SELECT COUNT(*) FROM user_audit WHERE user_id = 42; -- still > 0 (audit kept)
21.4 Orphan detection¶
-- Order rows with no matching customer (referential integrity broken)
SELECT o.* FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
-- order_items with no matching order
SELECT oi.* FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
21.5 Data consistency check¶
-- Orders should sum to the customer's lifetime value
SELECT c.id, c.lifetime_value,
(SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = c.id) AS computed
FROM customers c
WHERE c.lifetime_value != (SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = c.id);
-- Expected: zero rows. Any rows = data drift.
21.6 Find stuck rows¶
-- Orders in PENDING for more than 30 minutes
SELECT * FROM orders
WHERE status = 'PENDING'
AND created_at < NOW() - INTERVAL 30 MINUTE;
21.7 Find rate-limit violations (test setup verification)¶
-- Users with > 100 API calls in last hour
SELECT user_id, COUNT(*) AS calls
FROM api_audit
WHERE created_at >= NOW() - INTERVAL 1 HOUR
GROUP BY user_id
HAVING COUNT(*) > 100;
21.8 MongoDB equivalent — pre/post check¶
// Before
db.orders.countDocuments({ customer_id: ObjectId("42") });
// Run the test
// After
db.orders.countDocuments({ customer_id: ObjectId("42") });
// Validate created document
db.orders.findOne({ customer_id: ObjectId("42") }, {}, { sort: { created_at: -1 } });
21.9 Test data seeding pattern¶
-- Idempotent seed: insert if not exists
INSERT INTO users (email, name)
VALUES ('test1@x.com', 'Test User 1')
ON DUPLICATE KEY UPDATE name = VALUES(name);
-- MongoDB upsert
db.users.updateOne(
{ email: "test1@x.com" },
{ $set: { name: "Test User 1" } },
{ upsert: true }
);
21.10 Test data cleanup pattern¶
-- Delete all test data older than 1 day (test cleanup)
DELETE FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE email LIKE 'test_%')
AND created_at < NOW() - INTERVAL 1 DAY;
22. INTERVIEW Q&A¶
22.1 SQL fundamentals¶
Q1. Difference between DELETE, TRUNCATE, DROP.¶
[See section 2 table]
Q2. WHERE vs HAVING.¶
[See section 4.3 table]
Q3. UNION vs UNION ALL.¶
"Both combine result sets from multiple queries. UNION removes duplicates by doing an implicit DISTINCT — slower because it does a sort or hash. UNION ALL keeps all rows including duplicates — faster. Default to UNION ALL unless you specifically need deduplication."
Q4. INNER JOIN vs LEFT JOIN.¶
[See section 3.3]
Q5. Why is WHERE annual > 1000000 invalid when annual is an alias from SELECT?¶
"Because of SQL's logical execution order. WHERE runs before SELECT, so the alias doesn't exist yet. Either repeat the expression in WHERE, wrap the query in a subquery, or use ORDER BY which runs after SELECT and can use the alias."
Q6. What's a self-join?¶
"Joining a table with itself. The classic example is the employees table where each row has a manager_id pointing to another employee row. To get employee plus manager name, I alias the table twice —
eandm— and join one.manager_id = m.id. Useful for hierarchy queries, pair comparisons, sequence analysis."
Q7. EXISTS vs IN.¶
[See section 5.4 table]
Q8. What is a correlated subquery?¶
[See section 5.2]
Q9. What's a CTE? When would you prefer it over a subquery?¶
"A Common Table Expression is a named temporary result set defined with WITH. Three reasons I prefer CTEs over subqueries. First, readability — the temporary result has a name. Second, reusability — I can reference the CTE multiple times in the main query. Third, recursive CTEs handle hierarchies (org charts, category trees) elegantly. Modern SQL style strongly favors CTEs."
Q10. RANK vs DENSE_RANK vs ROW_NUMBER.¶
[See section 7.3 table]
22.2 JOINs deep¶
Q11. How does LEFT JOIN turn into INNER JOIN?¶
[See section 3.3 — the WHERE clause trap]
Q12. What's a CROSS JOIN and when would you use it?¶
"Cartesian product — every row from table A paired with every row from table B. Use cases: generating combinations like product-size matrices, populating calendar tables, creating test data. Warning: if either table is large, the result explodes. Two 10k-row tables = 100 million rows."
Q13. How would you find missing rows between two tables?¶
"Three ways. NOT IN with a subquery:
WHERE id NOT IN (SELECT id FROM other)— careful with NULLs in the subquery, they break NOT IN. LEFT JOIN + WHERE IS NULL: more robust. EXCEPT (or MINUS in Oracle): cleanest. I prefer LEFT JOIN + IS NULL for portability."
Q14. How do you handle NULLs in JOINs?¶
"NULL never equals anything, including itself —
NULL = NULLis NULL, not true. So a row with NULL in the join column never matches in INNER JOIN. For LEFT JOIN, the right-table columns are NULL when no match. To filter for NULL, useIS NULL, not= NULL. To find rows where both sides are NULL, you have to be explicit:WHERE a.col IS NULL AND b.col IS NULL."
22.3 GROUP BY + aggregates¶
Q15. Can you SELECT a column that's neither in GROUP BY nor aggregated?¶
"In standard SQL, no — you'll get an error. In MySQL with sql_mode set loose (the old default), you get an unspecified value — usually the first row's value. This was a major footgun. Modern MySQL (5.7+ with ONLY_FULL_GROUP_BY enabled by default) rejects this. Best practice: always either GROUP BY the column or aggregate it."
Q16. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column).¶
"COUNT() counts all rows including those with NULLs. COUNT(column) counts rows where that column IS NOT NULL — so it can be less than COUNT() if there are NULLs. COUNT(DISTINCT column) counts unique non-NULL values. Common mistake: assuming COUNT(*) and COUNT(some_col) are the same."
22.4 Indexes + performance¶
Q17. How does a B-tree index work?¶
"A balanced tree where leaf nodes store the indexed column value and a pointer to the row. The tree is kept balanced on inserts and deletes, so lookup, insert, and delete are all O(log N). Range queries are fast because leaves are linked. The index is sorted by the indexed column."
Q18. When does an index NOT help?¶
[See section 10.3]
Q19. What is a composite index and the leftmost prefix rule?¶
[See section 10.4]
Q20. EXPLAIN — what do you look for?¶
[See section 10.5]
Q21. Real story: query was slow. What was the cause and the fix?¶
[See section 10.6 — the LOWER(email) story]
22.5 Transactions + ACID¶
Q22. Explain ACID.¶
[See section 12.1]
Q23. What's a dirty read?¶
"Reading data from another transaction that hasn't been committed yet. If the other transaction rolls back, you read data that never officially existed. Possible only under READ UNCOMMITTED. Most DBs default to higher isolation."
Q24. What's the difference between non-repeatable read and phantom read?¶
"Non-repeatable read: same query returns different values for the same row across two reads in your transaction, because another transaction committed an UPDATE between them. Phantom read: same query returns different number of rows, because another transaction committed an INSERT or DELETE matching your WHERE clause. REPEATABLE READ prevents non-repeatable reads but not phantom reads in standard SQL. MySQL InnoDB uses gap locks to also prevent phantoms in REPEATABLE READ."
Q25. What's the default isolation level in MySQL?¶
"REPEATABLE READ in InnoDB. Most other databases default to READ COMMITTED. MySQL's choice is to prevent non-repeatable reads in OLTP workloads, but the trade-off is more locking, which can hurt throughput."
22.6 MongoDB¶
Q26. SQL vs MongoDB — when to use which?¶
[See section 20]
Q27. What is an aggregation pipeline?¶
[See section 18]
Q28. Embed vs Reference in MongoDB schema design.¶
[See section 19.4]
Q29. What's $lookup and how is it different from SQL JOIN?¶
"$lookup is MongoDB's equivalent of LEFT JOIN. It takes documents from one collection and adds an array of matching documents from another collection. The differences from SQL JOIN: it's always a LEFT JOIN (or LEFT OUTER JOIN — adds an empty array when no match), the result is nested as an array field rather than flattened columns, and it's much slower than SQL JOIN because MongoDB isn't optimized for joins. Best practice: use $lookup sparingly; if you do lots of joins, the data was probably better suited to SQL."
Q30. What is $unwind?¶
"$unwind takes a document with an array field and outputs one document per array element. Used after $lookup to flatten the join-result array, or for analytical queries on array fields — for example, counting how often each skill appears across all users by unwinding their skills array, then grouping."
Q31. How does MongoDB ensure consistency?¶
"At single-document level, MongoDB has always been ACID — a write to a single document is atomic. Since version 4.0, multi-document transactions are supported on replica sets, and since 4.2, on sharded clusters. Plus there's tunable consistency via writeConcern (how many replicas must acknowledge) and readConcern (what consistency level reads see)."
Q32. What is ObjectId?¶
[See section 16.3]
Q33. Explain MongoDB's sharding.¶
"Horizontal partitioning across multiple servers. You pick a shard key — say
customer_id— and MongoDB distributes documents across shards based on that key's hash or range. Reads and writes are automatically routed to the right shard. The shard key choice is critical — a bad shard key creates hotspots where one shard gets all the load."
22.7 QA-specific¶
Q34. As a QA, when do you write SQL queries?¶
"Several scenarios. First, backend data validation — after an API test creates an order, I query the orders table to confirm it was actually stored with the right values, not just that the API returned 201. Second, test data setup — seeding users and reference data via SQL is faster than via UI. Third, test data cleanup — deleting test rows in afterMethod. Fourth, audit verification — confirming that user actions wrote audit trail entries. Fifth, edge-case probing — using SQL to find orphans, duplicates, stuck rows that ad-hoc UI exploration would miss."
Q35. How do you validate cascading delete works?¶
[See section 21.3]
Q36. How do you write idempotent test-data seeding queries?¶
[See section 21.9]
Q37. How do you find orphaned data?¶
[See section 21.4]
Q38. How would you compare data between two databases (e.g., staging vs production)?¶
"Three approaches. Light: COUNT(*) per critical table, schema dump comparison. Medium: per-table checksums — for example
SELECT MD5(GROUP_CONCAT(col1, col2 ORDER BY id))per table; mismatched checksums tell you which table drifted. Heavy: row-by-row comparison via tools like Percona Toolkit's pt-table-checksum, or a federated query using dblink in Postgres. For test environments, I usually start light and only escalate when needed."
Q39. Have you used window functions in your QA work?¶
"Yes. For example, validating that for every customer, only the most-recent order shows
is_latest = true. The query is:WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders) SELECT * FROM ranked WHERE (rn = 1 AND is_latest = 0) OR (rn > 1 AND is_latest = 1). Zero rows = invariant holds. Any rows = bug. Window functions let me express invariants concisely instead of writing application-level checks."
Q40. How do you debug a slow query?¶
"Five-step process. One:
EXPLAIN ANALYZEto see the actual execution plan and where time is being spent. Two: look for full table scans where indexes should apply — usually a function on the WHERE column or a leading wildcard in LIKE. Three: check if the query plan is using the indexes I expect, and if not, why — often statistics are stale (run ANALYZE). Four: check joins for cardinality estimates — bad estimates can lead to wrong join orders. Five: rewrite to help the planner — break a complex query into CTEs, push down filters, materialize intermediate results in temp tables for huge queries. Last resort: hint the planner directly if the DB supports it."
QUICK CHEAT SHEET¶
Top 15 SQL queries to memorize¶
| # | Query | Section |
|---|---|---|
| 1 | Second highest salary | Q1 (14) |
| 2 | Nth highest salary | Q2 (14) |
| 3 | Find duplicate emails | Q3 (14) |
| 4 | Delete duplicates keep one | Q4 (14) |
| 5 | Employees earning more than manager (self-join) | Q5 (14) |
| 6 | Top 3 per dept (window function) | Q8 (14) |
| 7 | Customers who never ordered (LEFT JOIN + IS NULL) | Q7 (14) |
| 8 | Pivot rows to columns (CASE in SUM) | Q17 (14) |
| 9 | Most recent record per group (ROW_NUMBER) | Q15 (14) |
| 10 | Cumulative sum (window function) | Q13 (14) |
| 11 | LEFT JOIN with right-table filter in ON (the trap) | 3.3 |
| 12 | Recursive CTE for hierarchy | 6.3 |
| 13 | Update with JOIN | Q49 (14) |
| 14 | Upsert (INSERT ON DUPLICATE KEY UPDATE) | 15.2 |
| 15 | Pagination (LIMIT + OFFSET) | 1.6 |
Top 5 MongoDB queries to memorize¶
| # | Query |
|---|---|
| 1 | $match + $group + $sort aggregation |
| 2 | $lookup + $unwind for join |
| 3 | upsert: true for idempotent insert |
| 4 | $inc, $push, $addToSet, $pull array/counter updates |
| 5 | Compound index with leftmost prefix |
Five lines that signal seniority¶
- "In LEFT JOIN, never filter the right-table in WHERE — that turns it into INNER JOIN."
- "Default to UNION ALL unless you specifically need deduplication."
- "Functions on indexed columns kill the index —
WHERE LOWER(email)won't use an index on email." - "For aggregations needing one row per input row, window functions beat subqueries."
- "In MongoDB, prefer embedding for 1:few read-together data; reference for 1:many or unbounded growth."
Owner: Rohan Dsouza | Use case: SQL/MySQL/MongoDB round of QA / SDET interviews | Updated: 2026