This is the next lesson from Database System Concepts, covering book lines
3512–4973. A heads-up on sequencing: that range is
Chapter 4, "Intermediate SQL," not Chapter 3. Chapter 3 (basic
select, insert/update/delete, basic DDL)
sits between your Lesson 2 and this one and hasn't been covered yet. I'm building exactly the
range you gave, so treat this as the book's Chapter 4. When we loop back to Chapter 3, Lesson
2's "Next" link (to this file) will need to be re-pointed — I'll flag that at the end.
For now, this chapter assumes you already know basic select ... where and
create table, and pushes into the mechanics that actually trip people up in
interviews: join variants, views, transactions, constraints, and authorization.
The thread through the whole chapter is the one from your Lesson 1: the DBMS exists to take responsibilities off your shoulders. Here those responsibilities are preserving rows a plain join would drop (outer joins), hiding data (views), guaranteeing all-or-nothing (transactions), enforcing consistency (integrity constraints), and controlling access (authorization). Each section below is one of those guarantees, expressed in SQL.
In Chapter 3 the book combined relations with the Cartesian product plus a
where predicate. That works but is clumsy, and for the common case it's
error-prone. SQL therefore gives you dedicated join syntax. All the examples in this section
use two relations: student (13 rows) and takes (22 rows of
enrollments). Note immediately that takes.grade is null for student
98988's BIO-301 enrollment — that null will matter the moment we hit outer joins.
The query "for each student, the courses they've taken" was written in Chapter 3 as
select name, course_id from student, takes where student.ID = takes.ID. That
equates the one attribute both relations share (ID) and, because it's an inner
join, drops students who've taken nothing. The natural join is shorthand for
exactly that common case:
select name, course_idfrom student natural join takes;
Natural join considers only pairs of tuples with equal values on
every attribute that appears in both schemas, lists the shared attributes
once, and orders the result columns shared-first, then left-only, then right-only. The result
is 22 tuples — one per actual enrollment — and still omits students with no
enrollments. (For symmetry, SQL also lets you write the old Cartesian product as
cross join instead of a comma.)
The natural join trap. Because natural join equates all shared
attribute names, adding a column can silently change your query's meaning. The book's
example:
student natural join takes natural join course
fails, because student/takes and course share
both course_id and dept_name — so it
wrongly requires a student's department to equal the course's department, dropping every
cross-department enrollment. The safe fix is join ... using, which equates only
the columns you name: ... join course using (course_id). That's why natural
join is convenient but risky on schemas that evolve.
using and onTwo safer join forms:
join ... using (A1, A2) — like natural join, but you
specify exactly which attributes to equate. Pairs match only on those attributes, even if
other shared attributes differ. This avoids the cross-department bug above.
join ... on <predicate>
— a fully general predicate, written like a
where clause but attached to the join.
from student join takes on student.ID = takes.ID is equivalent to the
comma-plus-where form, except the result shows ID twice (once per
relation).
The on clause looks redundant — you can always move its predicate into
where — but it earns its place with outer joins (where on and
where behave differently) and with readability: put the join condition in
on, the rest in where.
The joins so far are inner joins: they discard any row with no match. Outer joins preserve unmatched rows by padding the missing side with nulls. Three forms:
| Form | Preserves | Example effect |
|---|---|---|
| Left outer join | All rows of the left relation, matched or not. |
student natural left outer join takes keeps Snow (ID 70557), who took no
courses, with takes's columns set to null.
|
| Right outer join | All rows of the right relation. |
Symmetric;
takes natural right outer join student is the same result with columns in a
different order.
|
| Full outer join | All rows of both relations. | The union of left and right; in MySQL you must write it as left union right. |
Outer joins are how you answer "find the ones that don't match."
"All students who haven't taken a course" is just
student natural left outer join takes where course_id is null
— the unmatched left rows are exactly the ones with nulls on the right side. That's a
far cleaner pattern than anti-joins in many dialects.
The on-vs-where distinction is the subtle part interviewers love.
With an outer join, on is part of the join specification, so it decides which
rows get matched before padding; where is applied after. The
book's killer example: student Snow (no enrollments).
student left outer join takes on student.ID = takes.ID
includes Snow with nulls, because no match exists. But
student left outer join takes on true where student.ID = takes.ID
generates the full Cartesian product (the
on true matches everything, so no null-padding happens), and then
where deletes Snow because no takes row has Snow's ID. Same words,
opposite outcome — the placement of the predicate is the whole difference.
"Normal join" is called inner join to distinguish it from outer joins;
inner is optional. Any join type (inner / left / right / full) can be combined
with any join condition (natural / using / on). The chapter's Figure 4.7 is a 4×3 matrix
— and every cell is a legal SQL join.
A view is a "virtual relation" defined by a query. The DBMS stores the query, not the result; whenever you reference the view, it re-runs the query. This is the logical-level abstraction from your Lesson 1 made concrete: you show a user a tailored subset without copying data.
create view faculty asselect ID, name, dept_namefrom instructor;
A clerk can be granted select on faculty but not on
instructor, so they see ID/name/dept but never salary. Views differ from the
Chapter 3 with clause: with is a named subquery local to one query;
a view persists until dropped and can be referenced anywhere, even inside other view
definitions.
Once defined, a view name goes anywhere a relation name does:
select course_id from physics_fall_2017 where building = 'Watson'. You can name
the view's columns explicitly when the query uses an expression, e.g.
create view departments_total_salary(dept_name, total_salary) as select dept_name,
sum(salary) ... group by dept_name. Views can stack — physics_fall_2017_watson can be defined in terms of
physics_fall_2017.
A materialized view is actually stored on disk. That makes queries against it fast (the aggregate is precomputed), at the cost of keeping it in sync — a process called view maintenance. Strategies vary by system: immediate (update on every change to the base tables), lazy (update when read), periodic (may serve stale data), or DBA-chosen. The trade-off is explicit: fast reads and small results vs storage cost and update overhead. Tie this to your Lesson 12 "write path / read path" framing — a materialized view is a derived dataset precomputed on the write path.
Views are great for reading but treacherous for writes, because an update must be translated
back to the base relations. The book shows why this breaks:
insert into faculty values ('30765','Green','Music')
has no salary, and instructor needs one — the system either rejects it or
inserts a null salary. Worse, a join-based view like instructor_info (instructor
joined to department) can make an insert that satisfies neither base relation as the user
intended. So SQL permits updates only on updatable views, which require: a
single base relation in the from clause, only bare attribute names in
select (no expressions/aggregates/distinct), any omitted column
nullable and not part of a key, and no group by/ having. Even then,
an inserted row may not satisfy the view's own where — which
with check option prevents by rejecting such inserts. For anything complex, the
book recommends triggers (Chapter 5) with an instead of rule.
A transaction is a sequence of queries and updates treated as one unit. In
SQL a transaction begins implicitly with the first statement and ends with either
commit work (make changes permanent) or rollback work (undo all
changes since the transaction started). The bank transfer is the canonical example: debit A,
credit B — if the system crashes between the two, you've lost money unless the whole
thing is one transaction that rolls back. This is exactly the atomicity story from your Lesson
1 and your transactions lesson: either all the effects
show up, or none do.
Autocommit changes everything. In MySQL and PostgreSQL, each statement is
its own transaction and commits immediately — so a multi-statement transfer is
not atomic unless you turn autocommit off (set autocommit off) or wrap
the statements in begin ... commit. Oracle does the opposite: DML isn't
committed until you say so, and disconnecting rolls back. The standard's
begin atomic ... end (supported by SQL Server) makes a block one transaction.
Know your engine — this is a classic "why did my data disappear" bug.
Integrity constraints guard against authorized users corrupting the
data; they're distinct from security constraints, which block
unauthorized users. Examples: name not null, no two instructors share an ID, every
course's department exists, budget > 0. Arbitrary predicates are allowed in theory but too
costly to check in practice, so systems support only cheaply-testable ones. They're declared
in create table or added later with
alter table ... add constraint (which fails if the existing data already violates
it).
| Constraint | Meaning | Null handling |
|---|---|---|
| not null | Forbids null in the attribute (a domain constraint). | Primary-key columns are implicitly not null already. |
| unique (A, B, ...) | The listed columns form a superkey — no two rows equal on all of them. | Nulls are allowed (and don't equal anything, so two nulls don't violate it) unless also declared not null. |
| check (P) | Every tuple must satisfy predicate P; effectively a powerful type system. | Satisfied if not false — a check that evaluates to unknown (due to null) is not a violation, so add not null if you want to forbid nulls. |
The check clause is more expressive than most programming-language type systems.
check (budget > 0) on department, and
check (semester in ('Fall','Winter','Spring','Summer'))
on section — the latter simulates an enumerated type. The book shows the full university
DDL (Figure 4.9) with
check (salary > 29000) on instructor, composite primary keys, and foreign keys
all declared inline. Note the quirk: a check with a subquery isn't supported by
real systems, despite the standard allowing it.
The foreign key is a referential-integrity constraint where the referenced
columns form a primary key. Declared inline as
foreign key (dept_name) references department, or explicitly
foreign key (dept_name) references department(dept_name)
— but then those referenced columns must themselves be a superkey (primary or unique).
The FK must reference a compatible set: same number of columns, compatible types. The book
notes MySQL requires the explicit referenced-column list. A more general referential-integrity
constraint (referencing non-key columns) can't be stated directly in SQL and needs triggers.
This is the formal machinery behind the "referencing vs referenced" distinction your Lesson 2
established — now you see the actual DDL.
An assertion is a declarative predicate the whole database must always satisfy — e.g. "no instructor teaches two sections in the same time slot." It's part of the SQL standard but, the book admits, not supported by current production systems, so it's enforced via triggers instead. Worth knowing it exists, but don't expect to use it.
Beyond the basic types, SQL has date, time (optionally
time(p) or with time zone), and timestamp (optionally
timestamp(p)). Literals use ISO format: date '2018-04-25',
timestamp '2018-04-25 10:29:01.45'. extract(field from d) pulls
year/month/day/etc., and interval lets you do date arithmetic (x - y
is a number of days). current_timestamp, localtime, etc. give the
now-value.
Type conversion is explicit: cast(e as t). The book's neat example:
ID is varchar(5), so order by ID sorts
lexicographically ('11111' before '9'); order by cast(ID as numeric(5)) fixes it.
For display, coalesce(salary, 0) replaces null with 0 (all args must share a
type); Oracle's decode is the looser alternative that can turn a null salary into
the string 'N/A'. These are the everyday null-handling tools.
default 0 on an attribute supplies a value when an insert omits it. Large objects
use clob (character) and blob (binary), declared with a size like
image blob(10MB). You don't pull a multi-MB object into memory; you fetch a
locator and stream it in pieces — the JDBC pattern from your Lesson 1's "access
from application programs" section.
SQL's distinct types (create type Dollars as numeric(12,2)) give
strong typing: you can't accidentally add a Dollars to a
Pounds, catching currency bugs at compile time. A domain (create domain DDollars as numeric(12,2) not null) is similar but not strongly typed and can carry constraints and defaults.
The distinction matters: types prevent mixed-domain arithmetic; domains attach rules to an
underlying type. (Note 4.3 warns most engines implement these differently — PostgreSQL
supports create domain, Oracle neither, etc.)
For synthetic IDs,
ID number(5) generated always as identity lets the system assign a unique value
(insert must then omit ID). Dialects vary: PostgreSQL serial, MySQL
auto_increment, SQL Server identity. by default instead
of always lets you supply your own. For IDs unique across
multiple relations, a create sequence counter object is the portable
tool. This is the practical answer to "how do I pick a stable primary key" from your Lesson 2.
create table temp_instructor like instructor clones a schema;
create table t1 as (select ...) materializes a query result once (like a view,
but the data is frozen at creation). For naming, contemporary systems use a three-level
hierarchy: catalog → schema → object, so
catalog5.univ_schema.course names a relation uniquely — the database analog
of a filesystem path. Each connection has a default catalog/schema (like a home directory),
and the SQL environment carries the user's authorization identifier. This is the "schema" word
made precise: a container for relations and views, distinct from both the logical schema
(table designs) of Lesson 1 and the catalog above it.
An index is a redundant data structure that lets the system find tuples with
a given attribute value without scanning the whole relation — e.g.
create index dept_index on instructor(dept_name). Indexes belong to the
physical schema (Lesson 1's three levels), not the logical one, and are optional for
correctness. They speed up queries and help enforce primary/foreign-key constraints, but cost
space and slow down updates, so most systems let you decide rather than auto-picking.
create unique index ... enforces a candidate key (fails if the column isn't
already unique). The query processor uses an index automatically when a query can benefit;
drop index name removes it. This is the missing link between your Lesson 3
(storage engines) and real SQL: an index is the on-disk structure (B+-tree or hash, Chapter
14) the optimizer chooses to avoid a full scan.
SQL grants four data privileges — select,
insert, update, delete — plus schema privileges
(create/alter/drop). A creator gets all privileges on their own relation. This is the same
authorization concept from your Lesson 1's DDL section, now with syntax.
grant select on department to Amit, Satoshi;grant update (budget) on department to Amit, Satoshi;revoke select on department from Amit, Satoshi;
update(budget) scopes the privilege to one column; insert can too,
forcing other columns to default or null. public means every current and future
user. By default a grantee can't re-grant — that needs the grant-option
mechanics below. Crucially, authorization is at the level of an entire relation (or named
columns), never specific tuples — row-level control needs a different mechanism (4.7.7).
A role bundles privileges so you grant them to people by job function, not
one by one:
create role instructor; grant select on takes to instructor; grant instructor to
Satoshi;. Roles compose — grant instructor to dean, and a dean
inherits instructor's privileges. This beats sharing one login (which destroys accountability)
and matches role-based access control used far beyond SQL. At login, a user gets their direct
privileges plus everything inherited through roles, directly or transitively.
Views are the standard way to grant
row/column-scoped access the base privilege model can't express. A staffer who may
see only Geology instructors gets select on a geo_instructor view,
not on instructor. The catch: the system checks authorization
before expanding the view into its base-relation query, so the user must also hold
the needed privileges on the underlying relations (the creator doesn't automatically get
them). This is the "view as security boundary" idea from Lesson 1's abstraction levels, now
enforced in SQL.
grant ... with grant option lets a grantee re-grant. Revoking is the interesting
part: it follows an authorization graph and cascades by
default — if A granted B who granted C, revoking from A drops it from B and C too,
because the path from the root is gone. revoke ... restrict refuses to revoke if
it would cascade; revoke grant option for ... removes only the re-grant right.
The granted by current role clause matters when a role (not a person) should be
the grantor — so revoking the person's login doesn't strip privileges they legitimately
keep via their role. (The book's Satoshi-leaves example makes this concrete.)
When you truly need per-row control, some systems offer it: Oracle's
Virtual Private Database (VPD) attaches a predicate to a relation that's
automatically appended to every query, e.g.
ID = sys_context('USERENV','SESSION_USER') so each student sees only their own
takes rows. The documented pitfall: the injected predicate can silently change a
query's meaning — "average grade of all courses" becomes "average of my grades" —
so row-level security is powerful but easy to misuse.
Chapter 4 is the "write correct SQL under pressure" chapter. The patterns interviewers actually probe:
join ... using/on
over natural join on evolving schemas; reach for
outer joins the moment the question says "including those that don't match." Know
that on filters pre-padding, where post-padding.
create table; explain null's weirdness in check and
unique.
1. Why is natural join risky on a schema that may gain columns?
It's slower than using/on A new shared column changes the join condition It always does a full outer join2. student left outer join takes on true where student.ID = takes.ID (Snow took no courses):
Snow appears with nulls on the right Snow is dropped by the where clause The query is a syntax error3. A check(budget > 0) clause with budget = null:
Violates the constraint Is allowed (unknown != false) Forces the row to be deleted4. In MySQL/PostgreSQL, each standalone SQL statement:
Commits immediately (autocommit on) Waits for an explicit commit Rolls back on disconnect5. Materialized views vs regular views:
Regular views are stored, materialized are not Materialized are stored and need view maintenance Materialized views are always updatable6. Revoking a privilege from A, who granted it to B who granted C:
It cascades, removing it from B and C Only A loses it; B and C keep it revoke always uses restrict by default7. An index in SQL belongs to the:
Logical schema (required for correctness) Physical schema (optional for correctness) Transaction managerinner = default ; drops unmatched rows
a) natural join ? equate ALL shared attrs ; shared-first cols
? risky: new shared col changes semantics (eg cross-dept bug)
b) join using(A) ? equate only named attrs (safe)
c) join on pred ? general predicate ; ? comma+where
outer: left / right / full ? pad unmatched w/ null
? "find those with no match" = left outer join where right is null
on vs where ? on filters pre-padding, where post-padding (Snow example)
any type ? any condition = 4?3 matrix
virtual relation = stored query, recomputed on use (not result)
vs with ? with is local to 1 query ; view persists
views stack ; security boundary (grant on view, not base)
materialized ? stored on disk ; needs view maintenance (immediate/lazy/periodic)
updatable only if: 1 base rel, bare attrs, omitted cols nullable, no group by
? with check option rejects inserts failing view's where
commit work ? permanent ; rollback work ? undo all
?
atomic: all-or-nothing (transfer A?B)
autocommit ? MySQL/PG on by default (each stmt = txn) ; Oracle off
? multi-stmt must set autocommit off / begin...commit
vs security ? integrity = authorized users corrupt ; security = unauthorized access
a) not null ? domain constraint ; PK implicitly not null
b) unique(A) ? superkey ; nulls allowed (null?null)
c) check(P) ? satisfied if not false ; unknown (null) = pass
d) foreign key ? refs PK (or unique) of other rel ; compatible cols
assertion ? DB-wide predicate ; standard but unimplemented (use triggers)
date/time/timestamp ; cast(e as t) ; coalesce(x,0) for null display
default 0 ; clob/blob + locator streaming
distinct type ? strong typing (Dollars?Pounds) ; domain ? not strong, has constraints
ID gen: identity/serial/auto_increment ; sequence for cross-rel uniq
catalog ? schema ? object (path-like naming)
index ? physical schema ; optional for correctness ; speeds queries + FK checks
privileges: select/insert/update/delete (+ column scope) ; public = all users
role ? bundle privileges ; compose transitively ; beats shared login
grant/revoke ; with grant option ; revoke cascades (restrict to block)
row-level: Oracle VPD injects predicate ; can change query meaning (pitfall)
Primary source: Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020).
Database System Concepts, 7th ed., Chapter 4: “Intermediate SQL.”
McGraw-Hill.
Note on sequencing: this lesson covers book lines 3512–4973, which is
Chapter 4. Chapter 3 (basic SQL — select/insert/update/delete, basic DDL) precedes it
and has not yet been covered; re-point Lesson 2's "Next" link once Chapter 3 is written.
Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this maps onto a SQL whiteboard question.