This is the chapter we skipped earlier —
Database System Concepts Chapter 3, "Introduction to SQL" (book lines
1945–3512). It's the foundation that Chapter 4 (now Lesson 4) builds on, so slotting it
in here puts the book track in the correct reading order: Lesson 2 (relational model) →
this (basic SQL) → Lesson 4 (intermediate SQL). If you only ever memorize one SQL
chapter, make it this one: it's the actual vocabulary — create table,
select ... from ... where, aggregation, subqueries, and the insert/update/delete
statements — that every whiteboard SQL question is built from.
The chapter's organizing idea (which it shares with your Lesson 1) is that SQL is
declarative: you say what you want, and the system figures out how.
The book's own "meaning of a query" sequence (Cartesian product → filter → project)
is explicitly not how it executes — the optimizer rewrites it, which is exactly
the optimizer story from your storage lesson and
Lesson 2's relational algebra. Keep that split in mind: SQL reads top-to-bottom as
select/from/where, but it means from → where → select.
SQL has several parts: DDL (schema definition), DML (queries + updates), integrity, view definition, transaction control, embedded/dynamic SQL, and authorization. This chapter covers basic DML and DDL. The DDL types you'll actually use:
| Type | Meaning | Gotcha |
|---|---|---|
char(n) |
Fixed-length string; pads with spaces to length n. |
Space-padding makes char vs varchar comparisons unreliable
— the book says always prefer varchar.
|
varchar(n) |
Variable-length string, max length n. | The safe default for text. |
int / smallint |
Integer (machine-dependent range). | — |
numeric(p,d) |
Exact fixed-point: p total digits, d after the decimal. | numeric(3,1) stores 44.5 exactly but not 444.5. |
real / double precision /
float(n)
|
Approximate floating point. | Use for measured quantities, never for money (rounding). |
create table declares the schema and inline integrity constraints. The canonical
university snippet:
create table instructor ( ID varchar(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department);
The constraints available in basic DDL are exactly the ones from your Lesson 2:
primary key (non-null + unique), foreign key ... references (values
must exist in the referenced relation's primary key), and not null. The book's
key practical point: SQL rejects any update that violates a constraint —
inserting a course with a non-existent dept_name fails. Schema surgery uses
drop table r (drops data and schema), delete from r (keeps
schema, empties data), alter table r add A D (new column gets null for existing
rows), and alter table r drop A (unsupported by many systems).
Every query has three clauses. select lists output columns/expressions,
from lists input relations, where filters rows. The book is emphatic
about the meaning order: (1) Cartesian product of from relations, (2)
apply where predicate, (3) output select columns. That's how you
reason about correctness; the engine does something smarter.
select name from instructor returns one column. SQL keeps duplicates by default
— unlike the formal relational model, where a relation is a set. That default matters:
select dept_name from instructor lists "Comp. Sci." once per instructor. To
dedupe, add distinct: select distinct dept_name from instructor. The
opposite keyword all exists but is the default, so it's rarely written. The
select clause also takes arithmetic (salary * 1.1
for a hypothetical 10% raise — note this
doesn't change the table). The where clause uses
= <> < <= > >= plus
and/or/not.
Cross-relation queries list both tables in from and equate the join column in
where:
select name, instructor.dept_name, building from instructor, department where
instructor.dept_name = department.dept_name. The from first computes the Cartesian product (12 instructors × 13
departments = 156 rows in the sample!), then where keeps only the matching pairs.
The book's warning is the interview trap:
forget the where predicate and you get a giant Cartesian product. With realistic counts (200 instructors, 600 enrollments) that's 120,000 rows of garbage.
Instructors who taught nothing drop out — that's an inner join, and the book points
forward to outer joins (Lesson 4) for keeping them.
as)
as renames attributes (select name as instructor_name) or relations.
Relation renaming is essential for self-joins: "instructors earning more than at least one
Biology instructor" needs instructor as T, instructor as S so
T.salary and S.salary are distinguishable. The book notes Oracle
omits as in the from clause. Those aliases are called
correlation names (or table aliases / tuple variables).
Strings are single-quoted; a literal apostrophe is two apostrophes ('It''s right'). Equality is case-sensitive per the standard, but MySQL/SQL Server ignore case by default.
Pattern matching uses like with % (any substring) and
_ (any single char); '%Watson%' finds buildings containing "Watson".
An escape character (like 'ab\%cd%' escape '\') matches a literal percent.
not like finds mismatches. Functions like upper, lower,
trim, concatenation exist but vary by system.
order by sorts output (desc/asc, multi-column:
order by salary desc, name asc). * in select means all
columns. between is sugar for a two-sided range (salary between 90000 and 100000). Row constructors compare tuples lexicographically:
(ID, dept_name) = (teaches.ID, 'Biology') — handy but unsupported in
Oracle.
union, intersect, except mirror the algebra's ∪,
∩, −. Crucial defaults:
union deduplicates automatically; use
union all to keep duplicates. (Contrast with select, which keeps
them — the asymmetry trips people up.)
intersect / except also dedupe; their
... all variants keep duplicates and use min / (count1 − count2)
semantics.
intersect/except (use subqueries);
Oracle spells except as minus.
Set ops vs the where clause.
"Courses in Fall 2017 or Spring 2018" is union; "both semesters" is
intersect; "Fall but not Spring" is except. Every one of these can
also be written with in/not in subqueries (Section 3.8) —
SQL gives you redundant ways to say the same thing, which is a feature, not a bug.
Nulls break the neat true/false logic by adding a third value,
unknown. Arithmetic with a null input is null. Any comparison with null
(except is null/is not null) is unknown, not true and
not false. The three-valued logic extends and/or/not:
true and unknown = unknown, false and unknown = false,
true or unknown = true. A where row is kept only if the predicate is
true — false or unknown both drop it.
Test for null with salary is null, never salary = null (that's
unknown). One subtle twist: for distinct and set operations, two
nulls are treated as equal (so they collapse), even though
null = null as a predicate is unknown.
Five built-ins: avg, min, max, sum,
count. They take a collection and return one value.
select avg(salary) as avg_salary from instructor where dept_name = 'Comp. Sci.'.
Use distinct inside an aggregate to dedupe first (count(distinct ID)
counts each instructor once regardless of sections taught). count(*) counts rows;
distinct with count(*) is illegal.
having
group by partitions rows into groups; the aggregate runs per group. "Average
salary per department" =
select dept_name, avg(salary) from instructor group by dept_name. The hard rule:
every column in select that isn't aggregated must appear in
group by
— otherwise the output for a group with many IDs has no defined single
ID to print, and the query is rejected. having filters
groups (after grouping), so it can use aggregates:
... group by dept_name having avg(salary) > 42000. The evaluation order is from
→ where → group by → having → select.
Nulls in aggregates. Every aggregate except
count(*) ignores null inputs. So sum(salary) skips the
null-salaried rows rather than becoming null. If the input is empty,
count returns 0 and the rest return null. This is why a sum over a
column with some nulls isn't "poisoned" — but a avg of an all-null group
is just null.
Subqueries are select-from-where expressions nested inside a larger query. They
appear in where, from, and even as scalar values.
in / not in test membership in a subquery's
result set (or an explicit list). "Fall courses not in Spring" =
course_id not in (select ... Spring ...). Equivalent to except.
> some / > all
("greater than at least one" / "greater than every").
> all is the tool for "highest average salary":
having avg(salary) >= all (select avg(salary) ...). Note
= some ≡ in, but <> some ≠
not in; and <> all ≡ not in.
exists / not exists
test whether a subquery returns any rows. A subquery referencing an outer correlation name
(e.g. S.ID from the outer student) is a
correlated subquery, re-evaluated per outer row. "Students who took
all Biology courses" =
not exists ( (Biology courses) except (courses taken by S) )
— the classic set-containment trick.
unique / not unique
test for absence / presence of duplicate rows in a subquery (poorly supported; often
rewritten with count).
from and with
A subquery in the from clause is just a relation you can join and select from
— this is how you avoid having when you need to filter on an aggregate:
wrap the grouped query, then where avg_salary > 42000 on the outer query. Name it
with as (MySQL/PostgreSQL require a name). The
with clause defines a temporary named relation visible only to
that query — great for readability and for reusing an intermediate result (e.g. compute
department totals, then compare to their average). Viewed through Lesson 2's algebra,
with is just assignment (←); a view (Lesson 4) is the same idea but
persisted.
A subquery that returns exactly one value can go anywhere an expression can —
select dept_name, (select count(*) from instructor where ...) as num. If it
returns more than one row at runtime, you get an error. "No from clause" queries like
(select count(*) from teaches) / (select count(*) from instructor)
work in SQL Server but need a dummy dual relation in Oracle.
The three write statements, each allowing a where (or nested subquery) to pick
rows:
| Statement | Form | Notes |
|---|---|---|
delete |
delete from r where P |
Deletes whole tuples (never single columns). One relation at a time, but the
where can reference others. All tests run before any deletion, so
the average doesn't shift mid-delete.
|
insert |
insert into r values (...) or
insert into r select ...
|
Can insert a literal tuple or a query's result set. The
select is fully evaluated before inserting — otherwise
insert into student select * from student would loop forever. Omitted columns
become null.
|
update |
update r set A = expr where P |
Uses case for conditional updates; a subquery in the set clause
(e.g. recompute tot_cred) is common. All tests run before any update.
|
The case construct is the one non-obvious gem here: "3% raise above $100k, else
5%" is one statement with two ordered when branches, which avoids the ordering
bug you'd hit writing two separate updates. Scalar subqueries in
set (recompute a student's total credits from their grades) and
coalesce (null → 0) round out the toolkit.
When an interviewer says "write the SQL for X," this chapter is your alphabet:
from (tables) →
where (joins + filters, never forget the join predicate) →
group by / having (if aggregating) →
select (output) → order by.
select columns must be in
group by; filter groups with having, rows with where.
exists /
not exists (set containment via except) or > all /
> some.
is null; remember unknown drops
rows just like false.
1. In SQL's meaning of a query, the order is:
select, then from, then where from, then where, then select where, then select, then from2. Forgetting the join predicate in a multi-table from clause gives:
A syntax error A Cartesian product of all rows Only the matched rows3. select union vs select distinct:
Both keep duplicates union deduplicates, plain select keeps duplicates union keeps, select removes4. A comparison like '1 < null' evaluates to:
false unknown (row dropped) true5. 'Students who took ALL Biology courses' is best expressed with:
course_id in (Biology courses) not exists (Biology except taken) unique (subquery)6. group by dept_name, then select dept_name, ID, avg(salary):
Erroneous: ID not in group by and not aggregated Valid: ID is just picked arbitrarily Valid only if you add a having clause7. sum(salary) when some rows have null salary:
Ignores the null rows Becomes null Returns 0
DDL (schema) + DML (query/update) + integrity + views + txn + auth
?
declarative: say what, system finds how (optimizer rewrites)
char(n) pads spaces ; varchar(n) safe default ; numeric(p,d) exact
real/double/float = approximate ; ? money (rounding)
create table : PK (not null+unique), FK refs PK, not null
drop table = data+schema ; delete from = empty only
alter add A (null for old rows) ; alter drop A (? many systems)
? constraint violation = update rejected
meaning: from (?) ? where (filter) ? select (project)
written order select/from/where ; engine does better
distinct = dedupe ; default keeps dups (vs union which dedupes)
? forget join pred = Cartesian product (huge!)
like % _ ; escape '\' ; is null (? = null)
order by desc/asc multi-col ; between = range sugar
union / intersect / except = ? ? ? ; all dedupe (union all keeps)
MySQL ? intersect/except (use subq) ; Oracle minus = except
3rd value unknown ; arithmetic w/ null = null
true?unknown=unknown ; false?unknown=false ; true?unknown=true
where keeps only true ; unknown dropped like false
distinct/set ops treat 2 nulls as equal (collapse)
avg/min/max/sum/count ; count(distinct X)
group by : non-aggregated select cols MUST be in group by
having filters groups (post-group) ; where filters rows
eval: from?where?group?having?select
null: all aggregates ? count(*) ignore nulls ; empty?count 0 else null
in/not in = membership (? except) ; >some / >all
exists/not exists = nonempty ; correlated subq re-runs per row
"all X" = not exists (X except taken)
from-subq = relation ; with = temp named rel (? view but local)
scalar subq = 1 value, anywhere expr allowed ; >1 row = runtime err
delete from r where P (whole tuples; 1 rel; tests before delete)
insert : values(...) or select... ; select fully eval before insert
update r set A=expr where P ; case for conditional ; subq in set
Primary source: Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020).
Database System Concepts, 7th ed., Chapter 3: “Introduction to SQL.”
McGraw-Hill.
Note: this is the Chapter 3 lesson that was skipped earlier; it now sits
between Lesson 2 (relational model) and the former Lesson 3 (now renumbered Lesson 4,
intermediate SQL) so the book track reads in order.
Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this maps onto a SQL whiteboard question.