Aptitude Prep Handbook — Section 6: Database and SQL

Query construction, normalization, and the traps that assessments test

Aarav — Personal Placement Prep Handbook

July 2026

Table of Contents

How to use this section

Database questions in placement tests come in two very different shapes, and they need
different preparation.
Shape 1 — MCQ theory. Definitions of keys, normal forms, ACID properties, join types, ER
cardinality. These are recall questions and appear heavily in IT-services aptitude rounds.
Chapters 2, 12, 13 and 14 cover them.
Shape 2 — Query writing and output prediction. You are given a schema and asked
what a query returns, or asked to write one. These dominate product-company
assessments and technical interviews. Chapters 3–9 cover them.
Everything in this section is executable and was executed. I built the schema below in a
real database, ran every query, and pasted the actual result sets. Where a query returns
something surprising — and several deliberately do — the surprise is real, not a claim.

1. The working schema

Every example in this section uses these four tables. Learn the shape of them; it makes the
rest of the section much faster to read.
CREATE TABLE departments (
dept_id INTEGER PRIMARY KEY,
dept_name TEXT NOT NULL UNIQUE,
location TEXT NOT NULL
);

CREATE TABLE employees (

emp_id INTEGER PRIMARY KEY,
emp_name TEXT NOT NULL,
 dept_id INTEGER REFERENCES departments(dept_id),
manager_id INTEGER REFERENCES employees(emp_id),
salary NUMERIC(10,2) NOT NULL CHECK (salary > 0),
hire_date DATE NOT NULL
);

CREATE TABLE projects (

project_id INTEGER PRIMARY KEY,
project_name TEXT NOT NULL,
dept_id INTEGER REFERENCES departments(dept_id),
budget NUMERIC(12,2)
);

CREATE TABLE assignments (

emp_id INTEGER REFERENCES employees(emp_id),
project_id INTEGER REFERENCES projects(project_id),
hours INTEGER NOT NULL,
PRIMARY KEY (emp_id, project_id)
);

departments

dept_id
10
20
30
40

employees

emp_id
1
2
3
4
5
6
7
8
9

projects

project_id
100
101
102
103

assignments

emp_id
1
2
3
2
1
4
5
7
8

Three deliberate features of this data, each of which drives a classic exam question:
• Farah Khan has a NULL dept_id — she is an employee belonging to no department.
This makes inner joins lose a row and breaks NOT IN.
• Research (dept 40) has no employees — this is what distinguishes a LEFT JOIN
from an INNER JOIN.
• Divya Rao and Karan Malhotra earn identical salaries, and Karan is on no project
— these drive the “Nth highest salary” and NOT EXISTS questions.

2. The relational model and keys

Core vocabulary
Term
Relation
Tuple
Attribute
Domain
Degree
Cardinality

The key hierarchy — a common MCQ

Key Definition
Super key Any set of attributes that uniquely
identifies a row (may contain redundant
attributes)
Candidate key A minimal super key — remove any
attribute and it stops being unique
Primary key The candidate key chosen by the designer;
cannot be NULL
Alternate key A candidate key not chosen as primary
Composite key A key made of two or more attributes
(e.g. assignments’ primary key)
Foreign key An attribute referencing the primary key of
another table; may be NULL
Surrogate key An artificial key with no business meaning
(e.g. an auto-increment id)

The distinction most often tested: every candidate key is a super key, but not every
super key is a candidate key — minimality is the difference. In employees, {emp_id} is a
candidate key; {emp_id, emp_name} is a super key but not a candidate key.
Integrity constraints:
• Entity integrity — no part of a primary key may be NULL.
• Referential integrity — a foreign key must either match an existing primary key
value or be NULL.
• Domain integrity — values must be from the attribute’s domain (enforced by data
type and CHECK).

Traps
• A foreign key CAN be NULL (Farah Khan’s dept_id); a primary key cannot. This is a
favourite true/false question.
• A primary key is chosen, not discovered — a table can have several candidate keys.
• UNIQUE permits one NULL in most systems; PRIMARY KEY permits none.

3. SELECT, WHERE, and NULL semantics

Logical query processing order
This is the single most useful thing to memorise in the whole section, because it explains
almost every “why doesn’t this work?” question:
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY ->
LIMIT
Consequences that get tested directly:
• WHERE cannot use a column alias defined in SELECT, because WHERE runs
first.
• ORDER BY can use an alias, because it runs after SELECT.
• WHERE filters rows; HAVING filters groups. HAVING may reference aggregates;
WHERE may not.

NULL semantics — the highest-yield topic here

NULL means unknown, not zero and not empty string. Three rules follow:
1. Any comparison with NULL yields UNKNOWN, not TRUE or FALSE. salary =
NULL never matches. Use IS NULL / IS NOT NULL.
2. Aggregate functions ignore NULLs — except COUNT(*).
3. NULL propagates through arithmetic: NULL + 100 is NULL.
Verified demonstration:
SELECT COUNT(*) AS count_star,
COUNT(dept_id) AS count_dept,
COUNT(manager_id) AS count_mgr
FROM employees;

count_star
9

COUNT(*) counts rows (9). COUNT(dept_id) skips Farah Khan’s NULL (8).
COUNT(manager_id) skips both Asha Menon and Farah Khan (7).

The same effect on averages:

SELECT COUNT(*) AS rows_, COUNT(budget) AS non_null_budget, AVG(budget)
AS avg_budget
FROM projects;

rows_
4

Note the average is 4,500,000 ÷ 3 = 1,500,000, not ÷ 4. If you wanted NULL treated as
zero, you would need AVG(COALESCE(budget, 0)), which gives 1,125,000. Assessment
questions exploit this difference constantly.

Traps
• WHERE dept_id = NULL returns no rows, ever. It is not a syntax error, which is
what makes it dangerous.
• COUNT(column) ≠ COUNT(*) whenever the column is nullable.
• AVG divides by the count of non-NULL values.
• NULL sorts as either first or last depending on the database — never assume.

4. Aggregates, GROUP BY and HAVING

Method
• Every column in the SELECT list must either be inside an aggregate or listed in
GROUP BY. (Some databases relax this; strict ones reject it, and exam questions
assume strict.)
• WHERE filters rows before grouping; HAVING filters groups after.
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX. COUNT(DISTINCT col)
counts distinct non-NULL values.

Worked query
“Show the headcount and average salary for departments with at least three
employees, highest average first.”
SELECT d.dept_name,
COUNT(e.emp_id) AS headcount,
ROUND(AVG([Link]),2) AS avg_salary
FROM departments d
JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) >= 3
ORDER BY avg_salary DESC;

Actual output:

dept_name
Engineering
Sales

Finance (2 employees) and Research (0) are excluded by the HAVING clause — and
Research would have been excluded anyway by the inner join.
A second query — hours per project:
SELECT p.project_name,
SUM([Link]) AS total_hours,
COUNT(DISTINCT a.emp_id) AS people
FROM projects p
JOIN assignments a ON a.project_id = p.project_id
GROUP BY p.project_name
ORDER BY total_hours DESC;

project_name
Atlas
Cirrus
Borealis
Delta

Traps
• Putting an aggregate in WHERE — WHERE COUNT(*) > 3 is invalid. It must go in
HAVING.
• Forgetting a non-aggregated column in GROUP BY.
• COUNT(*) in a LEFT JOIN counts the NULL-filled row as 1 — use
COUNT(right_table.key) to get 0. This is exactly the difference in the next chapter.
• HAVING without GROUP BY is legal and treats the whole table as one group.

5. Joins
The join types
Join Returns
INNER JOIN Only rows matching in both tables
LEFT (OUTER) JOIN All rows from the left table; NULLs where
the right has no match
RIGHT JOIN All rows from the right table; the mirror
image
FULL OUTER JOIN All rows from both, NULL-padded where
unmatched
CROSS JOIN Cartesian product — every left row with
every right row
SELF JOIN A table joined to itself, using aliases

Inner versus left — the difference made concrete

SELECT COUNT(*) AS inner_rows
FROM departments d JOIN employees e ON e.dept_id = d.dept_id;

inner_rows
8

Eight, not nine — Farah Khan (NULL dept) is dropped. And departments with no
employees vanish entirely:
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY d.dept_name;

dept_name
Engineering
Finance
Research
Sales

Research appears with a headcount of 0 only because of the LEFT JOIN and because we
counted e.emp_id rather than *. Had we written COUNT(*), Research would show 1 —
counting its single NULL-padded row. That pair of decisions is one of the most commonly
tested points in SQL.

Self join — the manager hierarchy

SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

employee
Asha Menon
Rohit Verma
Neha Kulkarni
Imran Sheikh
Divya Rao
Karan Malhotra
Sunita Pillai
Vikram Bose
Farah Khan

The LEFT JOIN is essential — with an inner join, Asha Menon (who has no manager)
would disappear from her own report.

Cross join
SELECT (SELECT COUNT(*) FROM departments) * (SELECT COUNT(*) FROM
employees) AS cross_rows;

cross_rows
36

4 departments × 9 employees = 36 rows. A cross join is what you accidentally produce

when you forget the join condition — and the giveaway is a result far larger than either
table.
Traps
• Filtering an outer join in WHERE turns it back into an inner join. LEFT JOIN
employees e ... WHERE [Link] > 50000 discards the NULL-padded rows, because
NULL > 50000 is UNKNOWN. Put the condition in the ON clause instead if you want to
preserve them.
• COUNT(*) versus COUNT(right_column) in a left join — as shown above.
• A join without an ON clause is a cross join.

• RIGHT JOIN is just a LEFT JOIN with the tables swapped; some databases
do not support FULL OUTER JOIN at all.

6. Subqueries and EXISTS

The families
Type
Scalar subquery
Row/column subquery
Correlated subquery
Derived table
CTE

Scalar subquery
“Who earns more than the company average?”
SELECT emp_name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

The company average is 90,555.56, and the result is:

emp_name
Asha Menon
Imran Sheikh
Sunita Pillai
Rohit Verma

Correlated subquery
“Who earns more than the average for their own department?” The inner query now
references the outer row, so it is recomputed for each employee.
SELECT e.emp_name, e.dept_id, [Link]
FROM employees e
WHERE [Link] > (SELECT AVG([Link]) FROM employees e2 WHERE
e2.dept_id = e.dept_id)
ORDER BY e.dept_id;

emp_name
Asha Menon
Imran Sheikh
Sunita Pillai

Note Rohit Verma (92,000) appears in the first result but not the second — he is above the
company average but below his own department’s average of 118,333. That contrast is the
point of the topic.
Farah Khan is absent from both: her department is NULL, so the correlated subquery
compares against an empty group.

EXISTS and NOT EXISTS

“Which employees are on no project at all?”
SELECT e.emp_name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM assignments a WHERE a.emp_id =
e.emp_id)
ORDER BY e.emp_id;

emp_name
Karan Malhotra
Farah Khan

EXISTS stops at the first matching row, so SELECT 1 is conventional — the select list is
never evaluated.

The NOT IN with NULL trap

This is the most important single gotcha in SQL, and it is silent.
SELECT COUNT(*) AS rows_returned FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);

rows_returned
0

Zero rows — even though Research (dept 40) genuinely has no employees. The subquery
returns a list containing a NULL (Farah Khan’s). 40 NOT IN (10, 20, 30, NULL) evaluates
to 40 <> 10 AND 40 <> 20 AND 40 <> 30 AND 40 <> NULL, and that last
comparison is UNKNOWN — so the whole expression is UNKNOWN, never TRUE, and no
row qualifies.
The fix:
SELECT dept_name FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS
NOT NULL);

dept_name
Research

Traps
• NOT IN with a nullable subquery column silently returns nothing. Use NOT
EXISTS, or filter NULLs explicitly. NOT EXISTS is immune to this problem, which is
why experienced developers prefer it.
• IN with NULL is safe for finding matches but not for excluding them — the asymmetry
is the whole trap.
• A correlated subquery runs once per outer row and can be slow; a join is often
equivalent and faster.
• A scalar subquery returning more than one row is a runtime error, not a silent one.

7. Set operations and CTEs

Set operators
Operator
UNION
UNION ALL
INTERSECT
EXCEPT / MINUS

All require the same number of columns with compatible types. Column names come from
the first query.

Common table expressions

A CTE names a subquery, making multi-step logic readable:
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
)
SELECT e.emp_name, [Link], ROUND(da.avg_sal, 2) AS dept_average
FROM employees e
JOIN dept_avg da ON da.dept_id = e.dept_id
WHERE [Link] > da.avg_sal;

This returns the same three employees as the correlated subquery in Chapter 6, but
computes each department’s average once instead of once per row.
Recursive CTEs walk hierarchies — the standard use is an org chart:
WITH RECURSIVE chain AS (
SELECT emp_id, emp_name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, e.manager_id, [Link] + 1
FROM employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT * FROM chain ORDER BY level, emp_id;

Traps
• UNION sorts and deduplicates, which costs time. If you know there are no
duplicates, UNION ALL is correct and faster — this is a standard interview question.
• ORDER BY may appear only once, at the very end of a set operation.
• A recursive CTE without a terminating condition runs forever; the anchor member
must not be recursive.

8. Window functions
Increasingly tested, because they distinguish candidates who have written real queries.
A window function computes across a set of rows without collapsing them — unlike
GROUP BY.

Function
ROW_NUMBER()
RANK()
DENSE_RANK()
LAG / LEAD
SUM() OVER (…)

Worked query — rank within department

SELECT emp_name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, rnk;
emp_name
Asha Menon
Rohit Verma
Neha Kulkarni
Imran Sheikh
Divya Rao
Karan Malhotra
Sunita Pillai
Vikram Bose

Divya and Karan tie on 64,000, so both get rank 2. Had there been a fourth person in Sales,
RANK() would give them 4 (skipping 3) while DENSE_RANK() would give 3.
ROW_NUMBER() would have arbitrarily assigned 2 and 3 to the tied pair.

Traps
• RANK skips, DENSE_RANK does not, ROW_NUMBER never ties. The
difference is asked constantly.
• Window functions are evaluated after WHERE and GROUP BY, so you cannot filter
on a window function in WHERE — wrap the query in a CTE or derived table first.
• PARTITION BY divides the rows; ORDER BY inside OVER defines the ordering
within each partition. They are independent of the outer ORDER BY.

9. The query classics

These specific problems appear in assessment after assessment.

Nth highest salary

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

second_highest
110000

For a general N, the window-function form is cleaner and handles ties explicitly:
WITH ranked AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 3;
Find duplicate values
SELECT salary, COUNT(*) AS n FROM employees
GROUP BY salary HAVING COUNT(*) > 1;

salary
64000

Rows in A with no match in B

Prefer NOT EXISTS (see the NOT IN trap in Chapter 6):
SELECT d.dept_name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id =
d.dept_id);

Returns Research — correctly, unlike the NOT IN version.

Delete duplicates, keeping one

DELETE FROM employees
WHERE emp_id NOT IN (
SELECT MIN(emp_id) FROM employees GROUP BY emp_name, salary
);

Traps
• The MAX(... WHERE salary < MAX) trick gives the second distinct salary. If the
question wants the second-highest employee including ties, use DENSE_RANK.
• For “Nth highest”, LIMIT 1 OFFSET N-1 works but ties behave differently — read
the question’s intent.
• DELETE without WHERE empties the table and is a favourite trick option in MCQs.

10. DDL, constraints and referential actions

The statement families
Category
DDL — Data Definition
DML — Data Manipulation
DCL — Data Control
TCL — Transaction Control

DELETE vs TRUNCATE vs DROP — a guaranteed MCQ

Type
Removes
WHERE allowed
Rollback
Resets identity counter
Speed on large tables

Constraints
NOT NULL · UNIQUE · PRIMARY KEY · FOREIGN KEY · CHECK · DEFAULT

Referential actions on a foreign key define what happens when the parent row changes:

Action
NO ACTION / RESTRICT
CASCADE
SET NULL
SET DEFAULT

Traps
• TRUNCATE cannot be rolled back in most systems and takes no WHERE.
• A CHECK constraint is not violated by NULL — CHECK (salary > 0) passes when
salary is NULL, because the condition evaluates to UNKNOWN rather than FALSE.
• UNIQUE allows NULLs (usually more than one); PRIMARY KEY does not.
• You cannot drop a table that is referenced by a foreign key without dropping or
altering the referencing constraint first.

11. Views and indexes

Views
A view is a stored query, not stored data.
CREATE VIEW dept_summary AS
SELECT d.dept_name, COUNT(e.emp_id) AS headcount, AVG([Link]) AS
avg_salary
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

• Advantages: simplify complex queries, restrict column access (a security tool),

present a stable interface over a changing schema.
• Updatable views must map rows one-to-one to a single base table. A view containing
GROUP BY, DISTINCT, aggregates, or a join is generally not updatable.
• A materialized view does store its results and must be refreshed — that is the
distinction usually tested.

Indexes
An index is a separate structure (typically a B-tree) that speeds lookups at the cost of
storage and slower writes.
• Clustered index — determines the physical row order. One per table.
• Non-clustered index — a separate structure pointing at rows. Many per table.
• Creating a PRIMARY KEY or UNIQUE constraint automatically creates an index.
When an index does not help:
• A query returning most of the table (a full scan is cheaper).
• A column wrapped in a function: WHERE UPPER(emp_name) = 'ASHA MENON'
cannot use an index on emp_name.
• A leading wildcard: LIKE '%menon' cannot use a B-tree index; LIKE 'menon%' can.
• A composite index on (a, b) helps queries filtering on a, or on a and b — but not on b
alone. (The “leftmost prefix” rule.)

Traps
• Indexes slow down INSERT, UPDATE and DELETE. “Index every column” is always
the wrong MCQ answer.
• Only one clustered index per table.
• The leftmost-prefix rule for composite indexes is a standard interview question.

12. Normalization
Why
Redundancy causes three anomalies:
• Insertion anomaly — you cannot record a fact without also recording an unrelated
one (cannot add a course with no students enrolled).
• Update anomaly — one fact stored in many rows must be changed in all of them, or
the data becomes inconsistent.
• Deletion anomaly — deleting one fact destroys another (deleting the last enrolment
loses the course’s existence).

Functional dependencies
X -> Y means: for any two rows agreeing on X, they must agree on Y. “X determines Y.”
• Partial dependency — a non-key attribute depends on only part of a composite key.
• Transitive dependency — a non-key attribute depends on another non-key attribute.

The normal forms

Form Requirement
1NF All attribute values are atomic; no
repeating groups or multi-valued cells
2NF In 1NF and no partial dependency on the
key
3NF In 2NF and no transitive dependency on
the key
BCNF For every non-trivial dependency X -> Y, X
is a super key
4NF In BCNF and no non-trivial multivalued
dependencies

The memorable summary: every non-key attribute must depend on the key, the whole key,
and nothing but the key — which is 1NF, 2NF and 3NF in order.

Worked decomposition
Start with an unnormalized enrolment table:
STUDENT_COURSE (student_id, student_name, course_id, course_name,
instructor, instructor_office, grade)

Functional dependencies:
• student_id -> student_name
• course_id -> course_name, instructor
• instructor -> instructor_office
• (student_id, course_id) -> grade

The primary key is the composite (student_id, course_id).

Step 1 — 1NF. Assume each cell holds a single value and there are no repeating groups.
Satisfied.
Step 2 — 2NF: remove partial dependencies. student_name depends only on
student_id, and course_name/instructor only on course_id — both are parts of the key,
so both are partial dependencies. Split:
• STUDENTS (student_id, student_name)
• COURSES (course_id, course_name, instructor, instructor_office)
• ENROLMENT (student_id, course_id, grade)
Step 3 — 3NF: remove transitive dependencies. In COURSES, the key is course_id, and
course_id -> instructor -> instructor_office. instructor_office depends on a non-key
attribute, which is transitive. Split again:
• STUDENTS (student_id, student_name)
• COURSES (course_id, course_name, instructor)
• INSTRUCTORS (instructor, instructor_office)
• ENROLMENT (student_id, course_id, grade)

Step 4 — BCNF check. Every determinant is now a key of its own table: student_id,
course_id, instructor, (student_id, course_id). All are super keys of their relations, so the
schema is in BCNF.
What this bought us: an instructor’s office is now stored once. Changing it is a single
update rather than one per course row, and a new course can be recorded before any
student enrols.

Traps
• A table can be in 3NF but not BCNF. The classic case is a relation with two
overlapping candidate keys where a non-key attribute determines part of a key.
• 2NF violations require a composite key — a table with a single-attribute primary key
is automatically in 2NF.
• Denormalization is deliberate and legitimate — reporting and analytics systems
trade redundancy for read speed. “Always normalize to the highest form” is the wrong
answer in a design question.
• Decomposition should be lossless (rejoining recovers the original) and ideally
dependency-preserving. 3NF can always achieve both; BCNF can sometimes achieve
only losslessness.

13. ER modelling
Components
Concept
Entity
Weak entity
Attribute
Key attribute
Multivalued attribute
Derived attribute
Relationship
Cardinality and how it maps to tables
Cardinality Example Implementation
1:1 Employee — Parking space Foreign key in either table,
with a UNIQUE constraint
1:N Department — Employees Foreign key on the N side
M:N Employees — Projects A junction table with both
foreign keys as a composite
primary key

Our schema demonstrates two of these: employees.dept_id implements the 1 : N between

departments and employees, and assignments is the junction table implementing the M : N
between employees and projects.
Participation:
• Total participation (double line) — every entity instance must participate. Modelled
with NOT NULL on the foreign key.
• Partial participation — participation is optional. employees.dept_id is nullable, so
participation is partial, which is exactly why Farah Khan can exist without a
department.

Traps
• Every M : N relationship needs a third table. You cannot implement it with a foreign
key alone — a standard exam question.
• Attributes on a relationship (the hours in assignments) belong in the junction table,
not either entity.
• A weak entity’s primary key includes the owner’s key.

14. Transactions, ACID and concurrency

ACID
Property Meaning
Atomicity All operations in the transaction succeed,
or none do
Consistency The database moves from one valid state to
another, respecting all constraints
Isolation Concurrent transactions do not interfere in
observable ways
Durability Once committed, changes survive a crash
The concurrency anomalies
Anomaly What happens
Dirty read Reading data another transaction has
written but not committed
Non-repeatable read Re-reading the same row gives a different
value, because another transaction
committed an update
Phantom read Re-running the same query returns a
different set of rows, because another
transaction inserted or deleted
Lost update Two transactions read, then both write,
and one overwrites the other

Isolation levels
Level
Read Uncommitted
Read Committed
Repeatable Read
Serializable

Learn this table as a staircase — each level prevents one more anomaly than the last, at
the cost of concurrency. That structure makes it far easier to recall than the four levels
individually.

Traps
• Higher isolation costs throughput. Serializable is not “the right answer” by default.
• Repeatable Read prevents non-repeatable reads but not phantoms — this precise
distinction is the most-asked question in the topic.
• Consistency in ACID means constraint consistency, not the “eventual consistency” of
distributed systems. The words are the same; the meanings differ.
• A deadlock is resolved by the database aborting one transaction — it is not prevented
by higher isolation.

Section 6 revision checklist

# Must-recall item
1 Query order: FROM -> WHERE -> GROUP
BY -> HAVING -> SELECT -> ORDER BY
2 WHERE cannot use SELECT aliases;
ORDER BY can
3 WHERE filters rows, HAVING filters
 groups
4 col = NULL never matches — use IS
NULL
5 COUNT(*) counts rows; COUNT(col)
skips NULLs
6 AVG divides by the non-NULL count
7 INNER JOIN drops unmatched rows on
both sides
8 LEFT JOIN + COUNT([Link]) gives 0;
COUNT(*) gives 1
9 A WHERE filter on the right table turns a
LEFT JOIN into an INNER JOIN
10 NOT IN with a nullable subquery returns
no rows — use NOT EXISTS
11 Correlated subqueries re-evaluate per
outer row
12 UNION deduplicates; UNION ALL does
not
13 RANK skips, DENSE_RANK does not,
ROW_NUMBER never ties
14 Cannot filter a window function in WHERE
— wrap it in a CTE
15 DELETE (DML, rollback-able) vs
TRUNCATE (DDL, fast) vs DROP
16 CHECK passes on NULL; UNIQUE allows
NULL; PRIMARY KEY does not
17 Foreign keys may be NULL; primary keys
may not
18 Candidate key = minimal super key
19 Indexes speed reads, slow writes; one
clustered index per table
20 Composite index: leftmost-prefix rule
21 1NF atomic; 2NF no partial; 3NF no
transitive; BCNF every determinant is a
super key
22 2NF violations need a composite key
23 M : N relationships require a junction table
24 ACID: Atomicity, Consistency, Isolation,
Durability
25 Repeatable Read stops non-repeatable
 reads but not phantoms

How to prepare this section

If you are targeting product companies or a technical interview: spend your time on
Chapters 3–9. Write queries against a real database rather than reading about them —
install SQLite (it needs no server) and recreate the schema at the top of this section in ten
minutes. Then answer the questions in this document before reading the results. The gap
between what you predicted and what the database returned is precisely your revision list.
If you are targeting IT-services aptitude rounds: the MCQ theory in Chapters 2, 10, 12,
13 and 14 carries more marks than query writing. Learn the comparison tables —
DELETE/TRUNCATE/DROP, the normal forms, the isolation staircase — since those are
almost always asked as direct recall.
The five things most likely to appear regardless of format: the difference between
WHERE and HAVING; inner versus left join; the normal forms up to 3NF; ACID; and the
NOT IN NULL trap.