Self-checkedTier 1 11 min readdbms.sql-joins-aggregates

Joins, grouping and aggregates

Joins in SQL (cross, inner with ON, USING and NATURAL, left/right/full outer, self joins) with the number of rows each produces, the aggregate functions COUNT, SUM, AVG, MIN and MAX and how they treat NULL and empty sets, GROUP BY and HAVING, the logical order of evaluation of a query (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY), and typical traps such as COUNT(*) against COUNT(column) with outer joins; every statement shown was executed on a real SQL engine.

Why it exists

Most real queries combine several tables and summarise data. The ideas here (join variants, GROUP BY, HAVING and NULL-aware aggregates) produce most of the numerical questions in GATE: how many rows does this join return, what does this aggregate print, in what order is a query evaluated.

Prerequisites

You need SELECT–FROM–WHERE and the bag semantics of SQL (SQL basics); joins correspond to the algebra operators (advanced algebra).

Precise definitions

Joins in the FROM clause.

A CROSS JOIN B (or FROM A, B): all pairs, $|A| \cdot |B|$ rows.
A [INNER] JOIN B ON condition: the pairs that satisfy the condition (a theta join); an equality on the keys is an equijoin.
A JOIN B USING (c): equijoin on the column(s) c, which appears once in the result.
A NATURAL JOIN B: equijoin on all columns with the same name in both tables (dangerous: see the trap).
A LEFT [OUTER] JOIN B ON ...: inner join plus the rows of A without a match, padded with NULL; RIGHT keeps the rows of B; FULL keeps both.

Aggregate functions reduce a set of rows to one value: COUNT(*) (number of rows), COUNT(c) (number of non-NULL values of c), COUNT(DISTINCT c), SUM(c), AVG(c), MIN(c), MAX(c). Except for COUNT(*), NULLs are ignored. On an empty set COUNT returns $0$ and the others return NULL.

GROUP BY splits the rows into groups with equal values of the grouping columns; each group yields one output row. In standard SQL every column in the SELECT list must be a grouping column or inside an aggregate. HAVING keeps the groups for which a condition (usually on aggregates) is true. WHERE filters the rows before grouping.

Logical order of evaluation: FROM (joins) $\to$ WHERE $\to$ GROUP BY $\to$ HAVING $\to$ SELECT (compute expressions and aggregates; DISTINCT) $\to$ ORDER BY $\to$ LIMIT. This is why, in standard SQL, a column alias defined in SELECT cannot be used in WHERE (SQLite and some other systems accept it as an extension).

How it works

Joins

SELECT s.sname, e.cid, e.grade FROM Student s JOIN Enrolled e ON s.sid = e.sid WHERE s.dept = 'EE';

sname | cid | grade
------+-----+------
Bilal | C1  | B
Bilal | C5  | A
Farid | C5  | B
(3 rows)

ON is the join condition; WHERE filters the joined rows. For an inner join the two are interchangeable, for an outer join they are not (below). The same join with USING:

SELECT sname, cid FROM Student JOIN Enrolled USING (sid) WHERE cid = 'C5';

sname | cid
------+----
Bilal | C5
Farid | C5
(2 rows)

Natural join joins on every column with the same name. Student and Course both have a column dept, so Student NATURAL JOIN Course silently joins students with courses of their own department:

SELECT COUNT(*) AS rows_in_natural_join FROM Student NATURAL JOIN Course;

rows_in_natural_join
--------------------
20
(1 row)

The result has 20 rows (4 CS students times 4 CS courses, 2 EE students times 1 EE course, 2 ME students times 1 ME course). That is not what a person writing "students and courses" usually means. Natural join on Student and Enrolled (only sid in common) is fine. Outer joins keep unmatched rows; the unmatched side is padded with NULL:

SELECT s.sname, e.cid FROM Student s LEFT JOIN Enrolled e ON s.sid = e.sid WHERE s.dept = 'ME';

sname | cid
------+-----
Dara  | NULL
Gita  | C6
(2 rows)

Dara (ME) has no enrolment but appears with NULL. Where the condition is written matters for outer joins. Which CS students are enrolled in C2, keeping the other CS students?

SELECT s.sname, e.cid FROM Student s LEFT JOIN Enrolled e ON s.sid = e.sid AND e.cid = 'C2' WHERE s.dept = 'CS' ORDER BY s.sid;

sname | cid
------+-----
Asha  | C2
Chen  | C2
Esha  | C2
Hari  | NULL
(4 rows)

With the course test inside ON, Hari (who is not in C2) is kept with a NULL. If the same test is moved to WHERE, the padded row fails it (NULL = 'C2' is UNKNOWN) and Hari disappears: the outer join has turned into an inner join.

SELECT s.sname, e.cid FROM Student s LEFT JOIN Enrolled e ON s.sid = e.sid WHERE s.dept = 'CS' AND e.cid = 'C2' ORDER BY s.sid;

sname | cid
------+----
Asha  | C2
Chen  | C2
Esha  | C2
(3 rows)

The number of rows of each join of the sample database (Student has 8 rows, Enrolled has 14):

Number of rows of each kind of join: start

Computed by a script, not typed

join	rows
CROSS JOIN
INNER JOIN
LEFT JOIN (Student first)
RIGHT JOIN (Student first)
FULL JOIN
Course LEFT JOIN Enrolled

Step 0

Student has 8 rows and Enrolled 14. The counts come from running COUNT(*) on each join in SQLite. An inner join keeps matches only; outer joins add the unmatched rows of the preserved side(s).

0 / 6

computed by script: the SQL statements are executed by SQLite, the calculus formulas are evaluated over a finite domain (scripts/db_sql.py, scripts/db_lib.py). python3 scripts/gen-db2-traces.py

In general: cross join $nm$ rows; inner join the matching pairs; left outer join = inner + the unmatched rows of the left table; full outer join = inner + unmatched left + unmatched right (checked on random tables).

A self join needs aliases to tell the two copies apart:

SELECT a.sname AS s1, b.sname AS s2 FROM Student a JOIN Student b ON a.dept = b.dept AND a.sid < b.sid WHERE a.dept = 'EE';

s1    | s2
------+------
Bilal | Farid
(1 row)

The condition a.sid < b.sid lists each unordered pair once and excludes a student paired with himself or herself.

Aggregates and NULL

SELECT COUNT(*) AS n, COUNT(age) AS n_age, COUNT(DISTINCT dept) AS n_dept, SUM(age) AS total, AVG(age) AS mean, MIN(age) AS youngest, MAX(age) AS oldest FROM Student;

n | n_age | n_dept | total | mean    | youngest | oldest
--+-------+--------+-------+---------+----------+-------
8 | 7     | 3      | 149   | 21.2857 | 20       | 23
(1 row)

There are 8 students but only 7 ages: COUNT(age), SUM and AVG ignore Dara's NULL (the average is $\text{total}/7 = 149/7 \approx 21.2857$ , not $\text{total}/8 = 18.625$ ). On an empty set:

SELECT COUNT(*) AS n, SUM(age) AS total, MAX(age) AS oldest FROM Student WHERE dept = 'XX';

n | total | oldest
--+-------+-------
0 | NULL  | NULL
(1 row)

GROUP BY and HAVING

SELECT dept, COUNT(*) AS students, AVG(age) AS mean_age FROM Student GROUP BY dept ORDER BY dept;

dept | students | mean_age
-----+----------+---------
CS   | 4        | 21
EE   | 2        | 21.5
ME   | 2        | 22
(3 rows)

One row per department. Note AVG(age) for ME is that of Gita alone, since Dara's NULL is ignored. Filtering groups with HAVING:

SELECT sid, COUNT(*) AS courses FROM Enrolled GROUP BY sid HAVING COUNT(*) > 2 ORDER BY sid;

sid | courses
----+--------
3   | 3
8   | 3
(2 rows)

The evaluation order explains the difference between WHERE and HAVING. The animation follows a query step by step: join, row filter, groups, aggregates, group filter.

How GROUP BY and HAVING are evaluated: start

Computed, not typed

SELECT s.dept, COUNT(*) AS n FROM Student s JOIN Enrolled e ON s.sid = e.sid WHERE e.grade IS NOT NULL GROUP BY s.dept HAVING COUNT(*) >= 3

Student8 tuples

sid	sname	dept	age
1	Asha	CS	20
2	Bilal	EE	22
3	Chen	CS	21
4	Dara	ME	NULL
5	Esha	CS	23
6	Farid	EE	21
7	Gita	ME	22
8	Hari	CS	20

Enrolled14 tuples

sid	cid	grade
1	C1	A
1	C2	B
2	C1	B
2	C5	A
3	C1	NULL
3	C2	A
3	C3	A
5	C1	A
5	C2	C
6	C5	B
7	C6	A
8	C1	B
8	C3	A
8	C4	B

Step 0

A query is evaluated in a different order from the one it is written in: FROM (with its joins), WHERE, GROUP BY, HAVING, SELECT, ORDER BY. We follow that order.

0 / 5

computed by script: the SQL statements are executed by SQLite, the calculus formulas are evaluated over a finite domain (scripts/db_sql.py, scripts/db_lib.py). python3 scripts/gen-db2-traces.py

COUNT(*) against COUNT(column) with an outer join

SELECT s.sname, COUNT(*) AS wrong, COUNT(e.cid) AS right_count FROM Student s LEFT JOIN Enrolled e ON s.sid = e.sid WHERE s.dept = 'ME' GROUP BY s.sid ORDER BY s.sid;

sname | wrong | right_count
------+-------+------------
Dara  | 1     | 0
Gita  | 1     | 1
(2 rows)

Dara has no enrolment. The padded row still counts for COUNT(*) (1), but not for COUNT(e.cid) (0, since the padded value is NULL). To count enrolments of students including zero, count a column of the inner table.

A column that is neither grouped nor aggregated

SELECT sname, dept, COUNT(*) FROM Student GROUP BY dept is rejected by standard SQL (sname is not determined by the group) and by most systems. SQLite accepts it and returns an arbitrary name from each group, which is why this is a bug to avoid, not a feature (stated from the standard; not run here).

Worked examples

EX 1Row counts of joins

Easy

Student (8 rows) and Enrolled (14 rows): cross join $112$ rows; inner join on sid $14$ rows (each enrolment has a student); left join $15$ ( $14 +$ Dara); full join $15$ .

EX 2Average with NULL

Easy

Ages: $20, 22, 21, \text{NULL}, 23, 21, 22, 20$ . AVG(age) $= (20 + 22 + 21 + 23 + 21 + 22 + 20)/7 = 149/7 \approx 21.2857$ , as the engine printed above. The key fact: the divisor is $7$ (the non-NULL ages), not $8$ .

EX 3WHERE against HAVING

Medium

Departments with more than 2 students: SELECT dept FROM Student GROUP BY dept HAVING COUNT(*) > 2 gives CS only (4 students). WHERE COUNT(*) > 2 is an error: an aggregate cannot appear in WHERE because WHERE is evaluated before the groups exist.

EX 4Number of rows with a join and a condition

Medium

SELECT * FROM Student s, Enrolled e WHERE s.sid = e.sid AND s.dept = 'CS': the CS students are Asha (2 enrolments), Chen (3), Esha (2) and Hari (3); the result has $2 + 3 + 2 + 3 = 10$ rows. The product has $112$ rows, the equality keeps $14$ , the department test $10$ .

EX 5Typical questions

GATE level

(a) "SELECT COUNT(*) from a table of 100 rows, 20 of which have NULL in column c; what do COUNT(*), COUNT(c) and COUNT(DISTINCT c) return?" $100$ , $80$ , and the number of different non-NULL values. (b) "The result of AVG over an empty table:" NULL. (c) "HAVING can use:" aggregates and grouping columns. (d) "Which join returns all rows of the left table?" left outer join. (e) "A query with GROUP BY and no aggregate in the SELECT list:" equivalent to SELECT DISTINCT of the grouping columns. (f) "SELECT a, SUM(b) ... GROUP BY a ORDER BY SUM(b)" valid; ORDER BY is evaluated last.

Cheat box

Cross $nm$ ; inner = matches; left outer = inner + unmatched left; full = inner + unmatched left + unmatched right.
Natural join joins on ALL common column names; ON for the condition; USING (c).
Aggregates ignore NULL (except COUNT(*)); empty set: COUNT 0, others NULL.
Order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
WHERE filters rows, HAVING filters groups; non-aggregated SELECT columns must be grouped.
Outer join: a test on the optional side in WHERE cancels the outer join.

Traps and GATE patterns

Connections

Builds on

SQL: DDL and basic queries

Unlocks

Joins, division and expression treesimplemented by SQL: DDL and basic queriesimplemented by Nested queries, views and NULLPrerequisite of Nested queries, views and NULLuses

Across subjects

No cross-subject links yet.

Within this subject

SQL: DDL and basic queriesuses Joins, division and expression treesuses Nested queries, views and NULLimplemented-by

Subqueries, views and the full treatment of NULL come next (nested queries and views); these joins are the algebra operators of the earlier page (joins and division).

PYQs

13 previous year questions from your book. I re-solved each one myself (10 by running or simulating, 3 by reasoning only); the book's key is shown next to my answer. All PYQs

GATE 2008 · MCQ · 2 marksQ24 · page 392 of the book

Consider the following relational schema:

• Student(school-id, sch-roll-no, sname, saddress)
 • School(school-id, sch-name, sch-address, sch-phone)
 • Enrolment(school-id, sch-roll-no, erollno, examname)
 • ExamResult(erollno, examname, marks)

Consider the following tuple relational calculus query.

DATABASE MANAGEMENT  SYSTEM

{ t | ∃E ∈ Enrolment ( t = E.school-id ∧
 |{ x | x ∈ Enrolment ∧x.school-id = t ∧
 (∃B ∈ExamResult ( B.erollno =
x.erollno∧B.examname = x.examname∧B.marks> 35 ))
}| ÷ |{ x | x ∈ Enrolment ∧x.school-id = t }| * 100 > 35
) }

If a student needs to score more than 35 marks to pass
an exam, what does the query return?

(a)The empty set
(b)schools with more than 35% of its students enrolled in some exam or the other
(c)Schools with a pass percentage above 35% over all exams taken together
(d)schools with a pass percentage above 35% over each exam

GATE 2021 · Set 2 · MCQQ6 · page 402 of the book

The relation scheme given below is used to store
information about the employees of a company, where
empId is the key and deptId indicates the department to
which the employee is assigned. Each employee is
assigned to exactly one department.

emp (EMPID, name, gender, salary, deptId)

Consider the following SQL query:

select deptId, count(*)

from emp

where gender = "female" and salary > (select
avg(salary) from emp)

group by deptId;

The above query gives, for each department in the
company, the number of female employees whose
salary is greater than the average salary of:

(a)employees in the company
(b)female employees in the department.
(c)employees in the department.
(d)female employees in the company.

GATE 2020 · NAT · 2 marksQ7 · page 402 of the book

Consider a relational database containing the following
schemas.

Catalogue

Sno
Sname
location
S1
M/s Royal furniture
Delhi
S2
M/s Balaji furniture
Bangalore
S3
M/s Premium furniture
Chennai

pno
Sname
Location
P1
Table
Wood
P2
Chair
Wood
P3
Table
Steel
P4
Almirah
Steel
P5
Almirah
Wood

The primary key of each table is indicated by underlying
the constituent fields.

SELECT s.sno, s.sname

FROM Suppliers s, Catalogue c

WHERE s.sno = c.sno AND

Cost > (SELECT AVG (cost)

FROM Catalogue

WHERE pno = 'P4'

GROUP BY pno);

The number of rows returned by the above SQL query is
__________.

GATE 2019 · NAT · 2 marksQ8 · page 402 of the book

A relational database contains two tables Student and
Performance as shown below

Student

Roll_no. Student_name
1
Amit
2
Priya
3
Vinit
4
Rohan
5
Smita

Performance

Roll_no. Subject_Code Marks
1
A
86
1
B
95
1
C
90
2
A
89
2
C
92
3
C
80

The primary key of the Student table is Roll_no. For the
Performance table, the columns Roll_no. and
Subject_code together from the primary Key. Consider
the SQL query given below.
Sno
Pno
Parts
Suppliers

DATABASE MANAGEMENT  SYSTEM

SELECT S.Student.name, sum (P.Marks)

FROM Student S, Performance P

WHERE P.Marks> 84

GROUP BY S.Student_name;

The number of rows returned by the above SQL query
is _________?

GATE 2018 · MCQQ9 · page 403 of the book

Consider the following two tables and four queries in
SQL .

Book (isbn, bname), Stock (isbn, copies)

Query 1:      SELECT B.isbn, S.copies

FROM BOOK B INNER JOIN Stock S

ON B.isbn = S.isbn;

Query 2:      SELECT B.isbn, S.copies

FROM BOOK B LEFT OUTER JOIN Stock S

ON B.isbn=S.isbn;

Query 3: SELECT B.isbn, S.copies

FROM BOOk B RIGHT OUTER JOIN Stock S

ON B.isbn = S.isbn;

Query 4:   SELECT B.isbn, S.copies

FROM BOOk B FULL OUTER JOIN Stock S

ON B.isbn = S.isbn;

Which one of the queries above is certain to have an
output that is a superset of the outputs of the other three
queries?

(a)Query 1
(b)Query 2
(c)Query 3
(d)Query 4

GATE 2017 · Set 1 · NAT · 1 markQ11 · page 403 of the book

Consider a database that has the relation schema EMP
(EmpId, EmpName, and DeptName). An instance of the
schema EMP and a SQL query on it are given below.

SELECT AVG (EC.Num)

FROM EC

WHERE (DeptName, Num) IN

(SELECT DeptName, COUNT(EmpId) AS
EC(DeptName, Num)

FROM EMP

GROUP BY DeptName);
The output of executing the SQL query is ______?

EMP
Empld Emp Name
DeptName
1
XYA
AA
2
XYB
AA
3
XYC
AA
4
XYD
AA
5
XYE
AB
6
XYF
AB
7
XYG
AB
8
XYH
AC
9
XYI
AC
10
XYJ
AC
11
XYK
AD
12
XYL
AD
13
XYM
AE

DATABASE MANAGEMENT  SYSTEM

GATE 2016 · Set 2 · NAT · 2 marksQ12 · page 404 of the book

Consider the following database table named
water_schemes:

water-schemes
scheme_no district_name Capacity
1
1
2
3
1
2
1
Ajmer
Bikaner
Bikaner
Bikaner
Churu
Churu
Dungargarh
20
10
10
20
10
20
10
The number of tuples returned by the following SQL
query is _______.

with total (name, capacity) as

select district_name, sum(capacity)

from water_schemes

group by district_name

with total_avg(capacity) as

select avg(capacity)

from total

select name

from total, total_avg

where total.capacity>total_avg.capacity;

GATE 2015 · Set 1 · NATQ15 · page 404 of the book

Consider the following relations:

            Student

Roll_No
Student_Name
1
Raj
2
Rohit
3
Raj

Performances

Roll_No Course Marks
1
Math
80
1
English 70
2
Math
75
3
English 80
2
Physics 65
3
Math
80

SELECT S. student_Name, sum(P.Marks)

FROM Student S, Performance P

WHERE S.Roll_No = P.Roll_no

GROUP BY S.Student_Name

The number of rows that will be returned by the SQL
query is _______?

GATE 2012 · MCQQ21 · page 406 of the book

Which of the following statements are TRUE about an
SQL query?

P: An SQL query can contain a HAVING clause even
if it does not have a GROUP BY clause.

Q: An SQL query can contain a HAVING clause only
if it has a GROUP BY clause.

R: All attributes used in the GROUP BY clause must
appear in the SELECT clause.

S: Not all attributes used in the GROUP BY clause
need to appear in the SELECT clause.

(a)P and R
(b)P and S
(c)Q and R
(d)Q and S

GATE 2011 · MCQ · 2 marksQ23 · page 406 of the book

Database table by name Loan_Records is given below.

Borrower
Bank_Manager
   Loan_Amount
Ramesh
Sunderajan
10000.00
Suresh
Ramgopal
5000.00
Mahesh
Sunderajan
7000.00

What is the output of the following SQL query?

SELECT Count(*)

FROM ( ( SELECT Borrower, Bank_Manager

FROM Loan_Records) AS S

NATURAL JOIN ( SELECT
Bank_Manager,

Loan_Amount

FROM Loan_Records) AS T );

(a)3
(b)9
(c)5
(d)6 DATABASE MANAGEMENT SYSTEM

GATE 2008 · MCQ · 2 marksQ26 · page 407 of the book

Consider the following relational schema:
Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-
phone)
Enrolment (school-id, sch-rollno, erollno,
examname)
ExamResult (erollno, examname, marks)
What does the following SQL query output?

SELECT sch-name, COUNT(*)
FROM School C, Enrolment E, ExamResult R
WHERE E.school-id = C.school-id
                 AND E.examname = R.examname
                  AND E.erollno = R.erollno
                  AND R.marks = 100
                   AND S.school-id IN
                   (SELECT school-id
                   FROM student
                   GROUP BY school-id
                    HAVING COUNT(*) > 200)
                    GROUP BY school-id;

DATABASE MANAGEMENT  SYSTEM

(a)for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students.
(b)for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students.
(c)for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam.
(d)nothing; the query has a syntax error.

GATE 2006 · MCQ · 2 marksQ30 · page 409 of the book

Consider the relation account (customer, balance)
where customer is a primary key and there are no null
values. We would like to rank customers according to
decreasing balance. The customer with the largest
balance gets rank 1. ties are not broke but ranks are
skipped: if exactly two customers have the largest
balance they each get rank 1 and rank 2 is not assigned

Query1:

select A.customer, count(B.customer)

from account A, account B

where A.balance<=B.balance

group by A.customer

Query2:

select A.customer, 1+count(B.customer)

from account A, account B

where A.balance<B.balance

group by A.customer

Consider these statements about Query1 and Query2.

1. Query1 will produce the same row set as Query2 for
some but not all databases.

2. Both Query1 and Query2 are correct implementation
of the specification

3. Query1 is a correct implementation of the
specification but Query2 is not

4. Neither Query1 nor Query2 is a correct
implementation
of the specification

5. Assigning rank with a pure relational query takes
less time than scanning in decreasing balance order

 assigning ranks using ODBC.

Which two of the above statements are correct?

(a)2 and 5
(b)1 and 3
(c)1 and 4
(d)3 and 5

GATE 2005 · 2 marksQ34 · page 411 of the book

A table 'student' with schema (roll, name, hostel,
marks), and another table 'hobby' with schema (roll,
hobbyname) contains records as shown below:

The following SQL query is executed on the above
tables:

select hostel

from student natural join hobbywhere marks > = 75 and
roll between 2000 and 3000;

Relations S and H with the same schema as those of
these two tables respectively contain the same
information as tuples. A new relation S' is obtained by
the following relational algebra operation:

S' = ∏Hostel ((σ s.roll = H.roll(σmarks > 75 and roll > 2000 and roll <
3000  (S)) X (H))

The difference between the number of rows output by
the SQL statement and the number of tuples in S' is .

(a)6
(b)4
(c)2
(d)0

Self-check

Question 1

What is the difference between COUNT(*) and COUNT(c)?

Show answer

COUNT(*) counts rows; COUNT(c) counts the rows where c is not NULL.

Question 2

Why can't you write WHERE COUNT(*) > 2?

Show answer

WHERE is evaluated before grouping, so no aggregate exists yet; use HAVING.

Question 3

A left outer join of $A$ (10 rows) and $B$ on a key of $B$ (every row of $A$ matches exactly one row of $B$ ): how many rows?

Show answer

$10$ : each $A$ row matches once and nothing needs padding.

Question 4

What does SELECT COUNT(*), SUM(x) FROM T return when $T$ is empty?

Show answer

$0$ and NULL.

Revision card

One-screen revision

Joins: cross, inner ON/USING, natural (all common names), left/right/full outer.
Aggregates ignore NULL; empty set: COUNT 0, others NULL.
GROUP BY, HAVING for groups, WHERE for rows.
Evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

References

·Silberschatz, Korth, Sudarshan, Database System Concepts (7th edition), Section 3.4 (additional basic operations), Section 3.5 (set operations), Sections 3.7 (aggregate functions) and 4.1 (join expressions)
·Elmasri, Navathe, Fundamentals of Database Systems (7th edition), Sections 6.1.7 (joined tables), 7.1 (aggregate functions and grouping)

Verification

scripts/db_sql.py executes every verified SQL block of this page on a fresh SQLite database with the sample data; scripts/enum-checks/db_sqlcheck.py re-runs them and compares the printed output. The row counts of the joins (112 for the cross join, 14 inner, 15 left and full, 14 right) and the animation of the evaluation order were produced by running the queries; the formulas for the number of rows (cross join n m, left join inner + unmatched left rows, full join inner + unmatched on both sides) were checked on random relations with SQLite in scripts/enum-checks/db_sqlcheck.py. SQLite accepts a SELECT list with ungrouped columns in a grouped query (taking an arbitrary row of the group); the SQL standard and most systems reject it, so that rule is stated from the standard and is not run. Textbook chapter references are from memory. SECOND PASS (2026-11-02, done by the assistant, not a human check): every Self-check question was solved before the printed answer was read and all agreed; the definitions, worked examples, cheat box and traps were read claim by claim and re-derived against the textbook definitions and the sample database. DEFECT FOUND AND FIXED: the prose said that the average age is the total divided by 7 equals 21 (it is 149/7 = 21.2857). Status raised to self-checked; verified needs a textbook or human check.

last verified: never

My notes (saved in this browser)

SQL: DDL and basic queries

Nested queries, views and NULL