--- title: 'SQL Fundamentals' source: 'https://academia.sh/en/courses/sql-fundamentals' language: en updated: '2026-08-23T07:00:51+00:00' license: 'CC BY-SA 4.0' lessons: 18 outcomes: - 'Writes statements that query, insert, update, and delete data' - 'Chooses the right join type' - 'Produces summaries with grouping and aggregate functions' --- # SQL Fundamentals The language that queries the relational model: selecting, filtering, sorting, null-value behavior, join types, aggregation and grouping, and the statements that insert and change data. ## Querying SQL's language families, the structure of the SELECT statement, condition syntax, sorting and limiting, how null values behave in a query, and built-in functions. ### SQL Language Families Source: https://academia.sh/en/courses/sql-fundamentals/sql-language-families Definition, manipulation, querying, control, and transaction statements: what each family changes, what declarative syntax means, and setting up the library schema used throughout the course. The Data Modeling and Relational Theory course closed by leaving behind a schema: relations, primary and foreign keys, entity and referential integrity constraints, normalized tables. What that course produced was a design — consistent on paper, but not yet questioned. A schema's value is measured by the questions that can be asked of it. This course builds the language that asks those questions. The language is called Structured Query Language (SQL), the standard interface that puts the relational model's theoretical operations — selection, projection, join — into writing. The first lesson maps the language: which statement family changes what, how a statement is read, and how the data used throughout the course gets set up. #### A Declarative Language Every loop written in the Programming Fundamentals course answered the question of **how**: walk the array from start to end, add the ones that satisfy the condition to an accumulator, return the result. SQL does not ask that question. SQL writes **what** is wanted; the engine decides in which order, with which data structure, and through which index the result gets reached. This is why SQL is a **declarative language**. The practical consequence of the distinction is this: two queries that express the same question with different syntax run at the same speed when the **query plan** the engine picks is the same. The syntax itself does not determine performance; what determines it is the plan the engine derives from the syntax. This distinction returns later in the course — for now it is enough to know that improving a query in SQL is mostly not about speeding up a loop, but about asking the question more precisely. #### Statement Families SQL is taught as a single language, but it holds five statement sets inside it that do five different jobs. These sets are conventionally named as follows: | Family | Expansion | What it does | Example statements | |---|---|---|---| | Definition | data definition language | Builds and changes the schema itself | `CREATE`, `ALTER`, `DROP` | | Manipulation | data manipulation language | Inserts, changes, deletes rows in tables | `INSERT`, `UPDATE`, `DELETE` | | Querying | data query language | Produces a new result set from existing data | `SELECT` | | Control | data control language | Grants and revokes privileges on objects | `GRANT`, `REVOKE` | | Transaction | transaction control language | Commits or rolls back a group of changes as a whole | `COMMIT`, `ROLLBACK` | The distinction is not arbitrary: each family differs in what it affects and in whether it can be rolled back. Querying changes nothing. Manipulation changes rows, and it can be rolled back if it sits inside a transaction. Definition changes the schema, and whether that can be rolled back depends on the engine — some engines treat a schema change as part of the transaction, others commit the open transaction the moment they see a definition statement. This course's first two topics deal only with querying; the third topic moves on to manipulation and definition statements. Control statements are also covered in that topic. #### Building the Schema One example runs throughout the course: a library's loan records. There are four tables — branches, books, members, and loan transactions. This is the same schema that was normalized in the Data Modeling and Relational Theory course. The block below creates a database file and defines the four tables. The engine invoked on the command line is `sqlite3`; the file name is `library.db`. The statements themselves are standard definition statements. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id) ); CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT ); SQL sqlite3 library.db '.tables' ``` ``` book branch loan member ``` Three concepts from the Data Modeling and Relational Theory course appear directly in the definitions. `PRIMARY KEY` states the primary key, `REFERENCES` the foreign key, and `NOT NULL` states that a column does not accept a null value. The absence of `NOT NULL` on the `publication_year`, `email`, and `return_date` columns is deliberate: a book can have an unknown publication year, a member can have given no email address, and a loan record can not yet have been returned. These three columns will be used later in the course to show how null values behave. #### Loading the Data A schema is an empty skeleton. The block below inserts sample rows into the `library.db` file created in the previous block and prints the row count per table. The `.headers on` and `.mode column` lines set the command-line tool's output format; they have nothing to do with the query itself. ```sh sqlite3 library.db <<'SQL' INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); .headers on .mode column SELECT 'book' AS table_name, COUNT(*) AS rows FROM book UNION ALL SELECT 'member', COUNT(*) FROM member UNION ALL SELECT 'loan', COUNT(*) FROM loan; SQL ``` ``` table_name rows ---------- ---- book 7 member 6 loan 12 ``` These two blocks will repeat throughout the course. So that each lesson can run on its own, the same schema and data will be given again at the start of the following lessons, compressed into a single block. #### The Skeleton of a Query The third family — querying — consists of a single statement: `SELECT`. The shortest question directed at the data just set up looks like this. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book WHERE author = 'Oğuz Atay'; SQL ``` ``` title publication_year ----------------- ---------------- The Disconnected 1972 Tehlikeli Oyunlar 1973 ``` The statement consists of three **clauses**: `SELECT` states which columns come back, `FROM` states which table gets read, and `WHERE` states which rows stay. That is the order they are written in, but the order they are evaluated in is different — the engine evaluates `FROM` first, then `WHERE`, and `SELECT` last. The next lesson works out this order in detail. Three syntax rules already apply. Keywords are not case-sensitive; writing them in uppercase is only a readability convention. Text values are enclosed in single quotes — in the standard, double quotes are for quoting an **identifier**, meaning a table or column name. Every statement ends with a semicolon; this is what lets multiple statements be written one after another. #### The Reversibility of a Transaction Manipulation statements change rows, not the schema, and this change can be held temporarily inside a **transaction**. A transaction is a block of statements that either commits as a whole or rolls back as a whole. ```sh sqlite3 library.db <<'SQL' .headers on .mode column BEGIN; DELETE FROM loan WHERE return_date IS NULL; SELECT COUNT(*) AS in_transaction FROM loan; ROLLBACK; SELECT COUNT(*) AS after_rollback FROM loan; SQL ``` ``` in_transaction -------------- 9 after_rollback -------------- 12 ``` Inside the transaction, the table appears to have nine rows; after the rollback, twelve again. The three deleted rows never became permanent. Had `COMMIT` been written, the result would have settled at nine rows. Transaction isolation levels and concurrency behavior are the subject of the Advanced SQL course; here only this much is needed: the safe way to try out a change is to put it inside a block that starts with `BEGIN`. #### Standard and Dialect SQL rests on a standard, but no engine implements the standard in full, and every engine adds its own extensions on top of the standard. These extensions are called a **dialect**. The rules taught in the course belong to the standard; where a behavior depends on the engine, the text marks it explicitly. In practice the distinction shows up in three places. The first is the syntax for limiting a result set — the standard syntax and the common shortcut differ. The second is the case-sensitivity behavior of pattern matching. The third is that some aspects of the outer join are not present in every engine. All three are taken up separately in their own lessons. The stance to take toward dialect differences is to treat portability as a measure, not a goal: choosing dialect-specific syntax when standard syntax is available ties the query to a particular product. This is sometimes a deliberate decision; when it is not, it comes back later as a migration cost. #### Summary - SQL is a declarative language: what the result will be gets written, and the engine decides how to compute it. - Statements fall into five families — definition, manipulation, querying, control, and transaction — and each family differs in what it affects and in whether it can be rolled back. - The schema used throughout the course consists of four tables: branch, book, member, and loan; three columns that accept null values were left deliberately. - A query is built from clauses, and the order they are written in is not the order they are evaluated in. - Manipulation statements can be tried out inside a transaction; `ROLLBACK` makes the change as if it never happened. - Standard SQL and an engine's dialect are separate things; engine-dependent behavior is marked separately throughout the course. #### Next Step In this lesson, `SELECT` appeared only as an example. Yet the entire querying family consists of this single statement, and its power comes from the column list accepting far more than fixed names: computed expressions, renamed columns, the elimination of duplicate rows. The next lesson builds the structure of the `SELECT` clause and the difference between a table's columns and a query's columns. ### SELECT Structure Source: https://academia.sh/en/courses/sql-fundamentals/select-structure What the column list accepts, aliases, computed expressions, the elimination of duplicate rows, and the difference between the writing order and the evaluation order of clauses. The previous lesson separated SQL's five statement families and stated that the querying family consists of a single statement — `SELECT`. That statement was used once as an example: two columns, one table, one condition. Yet what can be written into the `SELECT` clause is not limited to the names of a table's columns. This lesson establishes what the column list accepts. The result a query produces is a new relation; its columns do not have to be the same as the table's columns. Their names can change, their number can change, and a column that does not exist at all can be added by computing it. The block below sets up the schema and sample data used throughout the course. Every query in this lesson runs against the `library.db` file created in this block. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Star and Explicit List The shortest query asks for all of the table's columns. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT * FROM branch; SQL ``` ``` branch_id name city --------- ------------ -------- 1 Central Ankara 2 Bahcelievler Ankara 3 Kadikoy Istanbul 4 Konak Izmir ``` The star means "all of the table's columns as they currently stand." It is useful during exploration and causes three separate problems in code meant to last. First, when a column is added to the schema later, the result set's column count changes silently; a caller that reads columns by position breaks. Second, columns that are not needed get read too — for a wide text column, this means unnecessary disk and network traffic. Third, the person reading the query cannot see which columns are actually needed. An explicit list removes all three problems at once. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author FROM book; SQL ``` ``` title author ----------------- ----------------- Blindness José Saramago The Disconnected Oğuz Atay The Book of Sand Jorge Luis Borges Yaban Yakup Kadri Silent House Orhan Pamuk Motherland Hotel Yusuf Atılgan Tehlikeli Oyunlar Oğuz Atay ``` The order in the column list is the order in the result. The table's physical order is not binding. #### Aliases A column's name in the result set can be replaced with `AS`. This is called an **alias**. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title AS book_title, author AS written_by FROM book WHERE branch_id = 1; SQL ``` ``` book_title written_by ---------------- ------------- Blindness José Saramago The Disconnected Oğuz Atay ``` An alias does two jobs. It gives the column a meaningful name — especially for computed expressions, since a computed column's default name is a string that changes from engine to engine. Second, it lets two columns carrying the same name be told apart in joins; that is the second topic's subject. The word `AS` is optional in most engines — writing `title book_title` also works. Still, writing `AS` prevents a forgotten comma in the column list from silently turning into an alias. The statement `SELECT title author FROM book` does not raise an error; instead of two columns, it returns a single column named `author`. #### Computed Expressions The column list can hold an **expression** instead of a column name. The expression is evaluated per row and appears in the result set as a new column. Text concatenation is written with the `||` operator in the standard. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name || ' ' || last_name AS full_name, registered_at FROM member; SQL ``` ``` full_name registered_at ---------- ------------- Alice Kane 2023-02-14 Ben Ortiz 2023-05-30 Clara Diaz 2024-01-09 Derek Voss 2024-03-22 Grace Kim 2024-11-05 Owen Park 2025-01-18 ``` The syntax of the concatenation operator changes from engine to engine: some engines offer a concatenation function instead of `||` or alongside it, and in some, `||` carries an entirely different meaning. In queries that need to be portable, this is one of the first things to check. Arithmetic expressions are written the same way. The query below computes the decade from the publication year. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year, (publication_year / 10) * 10 AS decade FROM book; SQL ``` ``` title publication_year decade ----------------- ---------------- ------ Blindness 1995 1990 The Disconnected 1972 1970 The Book of Sand 1975 1970 Yaban 1932 1930 Silent House 1983 1980 Motherland Hotel Tehlikeli Oyunlar 1973 1970 ``` There are two observations. First, whether the division of two integers is evaluated as an integer or as a decimal depends on the engine; because integer division applies here, `1995 / 10` produced `199`. The portable way to write it is to state the type explicitly before dividing. Second, the `decade` column is blank in the `Motherland Hotel` row. Because the publication year is unknown, the expression's result is unknown too: an arithmetic expression that includes a null value produces a null value. This behavior has enough consequences to fill the entire fifth lesson. #### Eliminating Duplicate Rows A `SELECT` result is a multiset: the same row can appear more than once. The `DISTINCT` keyword eliminates duplicate rows. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT DISTINCT author FROM book; SQL ``` ``` author ----------------- José Saramago Oğuz Atay Jorge Luis Borges Yakup Kadri Orhan Pamuk Yusuf Atılgan ``` There are seven books, but six authors came back: the same author's two books collapsed into a single row. `DISTINCT` applies not to a column, but to the entire result row. The syntax `SELECT DISTINCT author, title` does not deduplicate authors; it deduplicates author–title pairs, and because the book titles differ, no row gets eliminated. This is the most common mistake made with `DISTINCT`: wanting to deduplicate one column, but adding a second column to the result set leaves the elimination without effect. The output order should not be relied on. The row order of a query with no sort specified is undefined; the order comes out according to whichever method the engine uses to deduplicate. When order is required, the sorting clause from the fourth lesson is written. #### Evaluation Order The order they are written in is `SELECT`, `FROM`, `WHERE`; the order they are evaluated in is `FROM`, `WHERE`, `SELECT`. The engine first decides which table to read from, then filters the rows, and computes the column list last. This has a measurable consequence: an alias defined in the `SELECT` clause is not visible, according to the standard, in the `WHERE` clause that is evaluated before it. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name || ' ' || last_name AS full_name FROM member WHERE full_name = 'Alice Kane'; SQL ``` ``` full_name ---------- Alice Kane ``` The query ran here. This is not standard behavior, though — it is a relaxation the engine in use happens to provide; the same query can raise a "no such column" error on another engine. The behavior depends on the engine. The portable way to write it repeats the expression inside `WHERE`: ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name || ' ' || last_name AS full_name FROM member WHERE first_name || ' ' || last_name = 'Alice Kane'; SQL ``` ``` full_name ---------- Alice Kane ``` Writing the expression twice does not mean the engine computes it twice; in a declarative language, repetition belongs to the syntax, not to the plan. #### Summary - The result set is a new relation; its columns do not have to be the same as the table's columns. - Star syntax is convenient for exploration; it is fragile to schema changes and reads unnecessary data in queries meant to last. - An alias names a column; writing `AS` prevents a forgotten comma from silently turning into an alias. - The column list can hold an expression; an expression that includes a null value produces a null value. - `DISTINCT` applies to the entire result row, not to a single column. - The writing order and the evaluation order are separate; whether an alias defined in `SELECT` is visible in `WHERE` depends on the engine. #### Next Step The `WHERE` clauses in this lesson consisted of nothing but a single equality. Yet filtering is the clause that determines a query's result the most: comparison operators, logical connectives, range and membership checks, text pattern matching. The next lesson builds condition syntax in all its forms and shows why the case-sensitivity behavior of pattern matching depends on the engine. ### Conditions Source: https://academia.sh/en/courses/sql-fundamentals/conditions Comparison operators, the precedence of logical connectives, range and membership checks, pattern matching with escape characters, and how case-sensitivity behavior depends on the engine. The previous lesson determined the result set's columns: which columns, with which names, with which expressions. On the subject of rows, only one tool was used — a single equality condition. The clause that actually determines the number of rows a query returns is `WHERE`. The `WHERE` clause evaluates a logical expression for every row the `FROM` clause produces and lets through only the rows that give a true result. This lesson builds every form that expression can take: comparison, connective, range, membership, and text pattern. The block below sets up the schema and sample data; every query in this lesson runs against the `library.db` file created in this block. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Comparison Operators There are six comparison operators: `=`, `<>`, `<`, `>`, `<=`, `>=`. The standard syntax for the inequality operator is `<>`; most engines also accept `!=`, but `<>` is the portable syntax. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book WHERE publication_year > 1975; SQL ``` ``` title publication_year ------------ ---------------- Blindness 1995 Silent House 1983 ``` Two out of seven books came back. Four of the remaining five do not satisfy the condition; the fifth — `Motherland Hotel` — has an unknown publication year. Whether an unknown value is greater than 1975 is unknown too, so the row does not pass. How null values behave in conditions is the fifth lesson's subject; for now the rule to keep in mind is this: a comparison that includes a null value is neither true nor false, and `WHERE` only lets through the true ones. Text comparison uses the same operators. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author FROM book WHERE author <> 'Oğuz Atay'; SQL ``` ``` title author ---------------- ----------------- Blindness José Saramago The Book of Sand Jorge Luis Borges Yaban Yakup Kadri Silent House Orhan Pamuk Motherland Hotel Yusuf Atılgan ``` In text comparison, whether two strings count as equal is decided by **collation**. Collation defines character order and case and accent sensitivity; the default collation depends on the engine. The same query can count the value `'oğuz atay'` as equal on one engine and not on another. #### Logical Connectives and Precedence Conditions are combined with `AND`, `OR`, and `NOT`. The three have different precedence: `NOT` is highest, then `AND`, and `OR` is lowest. This is the same operator-precedence rule as in the Programming Fundamentals course, and it leads to the same mistake. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author, publication_year FROM book WHERE author = 'Oğuz Atay' OR author = 'Orhan Pamuk' AND publication_year > 1980; SQL ``` ``` title author publication_year ----------------- ----------- ---------------- The Disconnected Oğuz Atay 1972 Silent House Orhan Pamuk 1983 Tehlikeli Oyunlar Oğuz Atay 1973 ``` The result has two pre-1980 books. If the intent had been "either author's books published after 1980," the result would be wrong. Because `AND` binds more tightly, the condition parsed as "author is Oğuz Atay, or (author is Orhan Pamuk and year is greater than 1980)." Parentheses fix the intent. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author, publication_year FROM book WHERE (author = 'Oğuz Atay' OR author = 'Orhan Pamuk') AND publication_year > 1980; SQL ``` ``` title author publication_year ------------ ----------- ---------------- Silent House Orhan Pamuk 1983 ``` The rule: when `AND` and `OR` appear in the same condition, parentheses are written. Relying on the precedence rule, even when it gives the correct result, makes it harder for whoever reads the query to infer the intent. #### Range and Membership A range condition written with two comparisons has a short form. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book WHERE publication_year >= 1970 AND publication_year <= 1980; SQL ``` ``` title publication_year ----------------- ---------------- The Disconnected 1972 The Book of Sand 1975 Tehlikeli Oyunlar 1973 ``` ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book WHERE publication_year BETWEEN 1970 AND 1980; SQL ``` ``` title publication_year ----------------- ---------------- The Disconnected 1972 The Book of Sand 1975 Tehlikeli Oyunlar 1973 ``` The two queries return the same rows. `BETWEEN` is inclusive on both ends; the values 1970 and 1980 are inside the range. This point is often overlooked and causes mistakes especially with dates: when trying to select an entire month with `BETWEEN`, it gets confused whether the bound covering the month's last day is a day or a day-and-time. On columns that carry a time component, the form that writes the lower bound inclusive and the upper bound exclusive (`>=` and `<`) is safer. For a finite set of values, `IN` is written. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author FROM book WHERE author IN ('Oğuz Atay','Orhan Pamuk'); SQL ``` ``` title author ----------------- ----------- The Disconnected Oğuz Atay Silent House Orhan Pamuk Tehlikeli Oyunlar Oğuz Atay ``` `IN` is shorthand for equalities joined with `OR`. It improves readability in long lists and offers the engine a structure that can be planned as a single set check. Its negation is written `NOT IN`, and it behaves unexpectedly when the list contains a null value; this trap will be measured in the fifth lesson. #### The Limit of Negation `NOT` reverses a condition, but the word "reverse" is misleading when a null value is present. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, branch_id FROM book WHERE branch_id = 1; SELECT title, branch_id FROM book WHERE NOT (branch_id = 1); SQL ``` ``` title branch_id ---------------- --------- Blindness 1 The Disconnected 1 title branch_id ---------------- --------- The Book of Sand 2 Yaban 2 Silent House 3 Motherland Hotel 3 ``` The first query returned two rows, the second four. Six in total; yet the table has seven books. The missing row is `Tehlikeli Oyunlar`: because its branch id is null, it satisfies neither the first condition nor its negation. A condition and its negation together giving the whole table only holds when the relevant column has no null value. #### Pattern Matching Searching inside text is done with the `LIKE` operator. There are two wildcard characters: `%` matches zero or more characters, `_` matches exactly one character. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title FROM book WHERE title LIKE '_li%'; SQL ``` ``` title --------- Blindness ``` The pattern says "any character, followed by `li`, followed by anything," and it matches only one title. The real question is: is `LIKE` case-sensitive? The answer depends on the engine, and even within the same engine it can depend on whether the character is ASCII. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title FROM book WHERE title LIKE 't%'; SQL ``` ``` title ----------------- The Disconnected The Book of Sand Tehlikeli Oyunlar ``` The lowercase pattern `t` caught three titles that start with uppercase `T`: on this engine, `LIKE` is case-insensitive for ASCII letters. The same engine is case-sensitive for non-ASCII letters. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE author LIKE '%ğ%'; SELECT author FROM book WHERE author LIKE '%Ğ%'; SQL ``` ``` author --------- Oğuz Atay Oğuz Atay ``` The second query returned no rows at all. The author `Oğuz Atay` contains `ğ`, but the pattern written with `Ğ` did not match — case folding applied only within the ASCII range. In Turkish text, this is a direct source of silent result loss. Fixing the sensitivity within the query itself is not a full solution either, because case-conversion functions run into the same limit. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT UPPER('Körlük') AS upper_form, LOWER('KÖRLÜK') AS lower_form; SQL ``` ``` upper_form lower_form ---------- ---------- KöRLüK kÖrlÜk ``` The conversion changed the ASCII letters; `ö` and `Ö` stayed as they were. The correct fix is not to patch the query, but to define a case- and accent-insensitive collation on the column, or to enable the engine's Unicode support; both depend on the engine. When the pattern itself needs to search for a literal `%` or `_` character, an escape character is declared. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT 'Sale %20' AS text, 'Sale %20' LIKE '%!%%' ESCAPE '!' AS escaped, 'Sale %20' LIKE '%%%' AS unescaped UNION ALL SELECT 'Sale 20', 'Sale 20' LIKE '%!%%' ESCAPE '!', 'Sale 20' LIKE '%%%'; SQL ``` ``` text escaped unescaped -------- ------- --------- Sale %20 1 1 Sale 20 0 1 ``` The `ESCAPE '!'` declaration makes `!%` read as "a literal percent sign." The pattern without escaping matches both texts, because `%%%` means "anything at all." The `1` and `0` values in the check columns represent logical true and false; how a boolean value gets written also depends on the engine. #### Summary - `WHERE` only lets through rows that give a true result; a row that is neither true nor false gets eliminated. - `NOT` is highest precedence, `AND` is in the middle, `OR` is lowest; when both appear together, parentheses are written. - `BETWEEN` is inclusive on both ends; on columns with a time component, an inclusive-lower, exclusive-upper form is safer. - `IN` is shorthand for equalities joined with `OR`. - A condition and its negation give the whole table only when the relevant column has no null value. - `LIKE` treats `%` and `_` as wildcards, and `ESCAPE` turns them into literal characters; case sensitivity depends on the engine and on whether the character is ASCII. #### Next Step Up to this point, which rows will come back has been determined, but the order they come back in has never been determined. The order seen in the outputs comes from how the engine reads the table, and it is not guaranteed. The next lesson takes up the clause that sorts the result set, multi-key sorting, where null values land in the sort, and the difference between the standard and common syntax for limiting the result to the first N rows. ### Sorting and Limiting Source: https://academia.sh/en/courses/sql-fundamentals/sorting-and-limiting Sorting the result set, multi-key sorting, collation's effect on order, where null values land, and the standard versus common syntax for limiting the result to the first rows. The previous three lessons determined which columns and which rows would come back. None of them said in which order the rows would come back. The order seen in the outputs came from how the engine read the table; the same query can come back in a different order when a different plan is chosen. This is not surprising, since a relation in the relational model is an unordered set: order is a property of the query, not of the data. If order is wanted, it has to be written explicitly. This lesson builds the sorting clause and, alongside it, the limiting syntax that only makes sense together with it. The block below sets up the schema and sample data; every query in this lesson runs against the `library.db` file created in this block. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Sorting by a Single Key The `ORDER BY` clause takes the sort key. If no direction is stated, ascending order is assumed; `ASC` and `DESC` state the direction explicitly. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year; SQL ``` ``` title publication_year ----------------- ---------------- Motherland Hotel Yaban 1932 The Disconnected 1972 Tehlikeli Oyunlar 1973 The Book of Sand 1975 Silent House 1983 Blindness 1995 ``` The book with an unknown publication year came first. Where a null value lands in a sort is theoretically undefined — it cannot be said whether an unknown number is greater or less than 1932 — so engines make an assumption, and that assumption depends on the engine. Some place null values first in ascending order, others place them last. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year DESC; SQL ``` ``` title publication_year ----------------- ---------------- Blindness 1995 Silent House 1983 The Book of Sand 1975 Tehlikeli Oyunlar 1973 The Disconnected 1972 Yaban 1932 Motherland Hotel ``` When the direction reversed, the null value switched sides too. There is a standard marker for pinning its place in the query. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year NULLS LAST; SQL ``` ``` title publication_year ----------------- ---------------- Yaban 1932 The Disconnected 1972 Tehlikeli Oyunlar 1973 The Book of Sand 1975 Silent House 1983 Blindness 1995 Motherland Hotel ``` The `NULLS FIRST` and `NULLS LAST` markers can move null values to the end even in ascending order. Support for them depends on the engine; on engines that do not support them, the same result is obtained by making an expression that tests for null the first sort key. #### Collation and Text Order The result of sorting text depends on collation. If the default collation compares characters by their codes rather than alphabetically, the result diverges from Turkish alphabetical order. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book ORDER BY author; SQL ``` ``` author ----------------- Jorge Luis Borges José Saramago Orhan Pamuk Oğuz Atay Oğuz Atay Yakup Kadri Yusuf Atılgan ``` `Orhan Pamuk` came before `Oğuz Atay`. In the Turkish alphabet, `ğ` comes right after `g`, well before `r`; the comparison applied here, though, compares the binary representation of the characters, and `ğ`'s code point is far higher than `r`'s, so `Orhan` came out first. This is not a bug, but the result of a configuration. If language-aware sorting is wanted, a language-specific collation is defined on the column or the query; which collations exist and how they are named depends on the engine. In lists shown to users, this is a defect that is hard to notice once it has been overlooked. #### Multi-Key Sorting Multiple keys are separated by commas and applied in order: rows that are equal on the first key get sorted by the second key. Each key's direction is written separately. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, author, publication_year FROM book ORDER BY author, publication_year DESC; SQL ``` ``` title author publication_year ----------------- ----------------- ---------------- The Book of Sand Jorge Luis Borges 1975 Blindness José Saramago 1995 Silent House Orhan Pamuk 1983 Tehlikeli Oyunlar Oğuz Atay 1973 The Disconnected Oğuz Atay 1972 Yaban Yakup Kadri 1932 Motherland Hotel Yusuf Atılgan ``` The same author's two books were sorted from newest to oldest. `DESC` applies only to its own key; the author column stayed ascending. A sort key can also be an expression, and the result column's ordinal position can be written too. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY 2 DESC; SQL ``` ``` title publication_year ----------------- ---------------- Blindness 1995 Silent House 1983 The Book of Sand 1975 Tehlikeli Oyunlar 1973 The Disconnected 1972 Yaban 1932 Motherland Hotel ``` Writing it by ordinal position is short but fragile: when a column gets added to the column list, the sort silently shifts to a different column. Writing the column name or alias is always safer. #### Deterministic Sorting `ORDER BY` establishes an order only over the keys it is written with. The order among rows that are equal on those keys is undefined, and it can change when the engine changes its plan. In the query above, the publication year was the tiebreaker when the author was equal; had both been equal, the result could have differed from run to run. When the result needs to be the same on every run — reports written to a log, paginated lists, compared outputs — a unique column gets added to the end of the sort keys. The primary key is directly suited to this job. Writing `ORDER BY author, book_id` defines a single order even among rows whose author is the same. #### Limiting When only the first few rows of a sorted result are wanted, limiting is written. The standard syntax and the common shortcut differ, and this is the second dialect distinction encountered in the course. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title FROM book ORDER BY title FETCH FIRST 3 ROWS ONLY; SQL ``` ``` Parse error near line 3: near "FETCH": syntax error SELECT title FROM book ORDER BY title FETCH FIRST 3 ROWS ONLY; error here ---^ ``` The standard syntax is `OFFSET … ROWS FETCH FIRST … ROWS ONLY`, and the engine used here does not recognize it. The syntax the engine accepts is the shortcut common across a wide range of engines. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year DESC LIMIT 3; SQL ``` ``` title publication_year ---------------- ---------------- Blindness 1995 Silent House 1983 The Book of Sand 1975 ``` The number of rows to skip can be stated too. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book ORDER BY publication_year DESC LIMIT 3 OFFSET 3; SQL ``` ``` title publication_year ----------------- ---------------- Tehlikeli Oyunlar 1973 The Disconnected 1972 Yaban 1932 ``` Together, the two queries produced two pages of three rows each. The pagination syntax has two traps. First, writing a limit without writing a sort is meaningless: which rows are the "first three" stays undefined, and two consecutive pages can show the same row twice. Second, pagination by skip count drifts when data changes between pages — a deleted row makes the next page skip its first row. The second of these two traps gets solved with keyset pagination in the Advanced SQL course. The performance cost of the skip count is silent too: the engine still has to produce the skipped rows before discarding them, so large offset values produce lists that get slower as the page number grows. #### Summary - Sorting is a property of the query, not of the data; without `ORDER BY`, row order is undefined. - Where null values land in a sort depends on the engine; `NULLS FIRST` and `NULLS LAST` pin their place, but support for them also depends on the engine. - Text order depends on collation; the default collation may not give Turkish alphabetical order. - In multi-key sorting, each key's direction is written separately, and the keys apply in order. - The order among rows with equal keys is undefined; a unique column is added to the sort for a deterministic result. - The standard syntax for limiting and the common shortcut differ; limiting without sorting is meaningless, and offset-based pagination drifts when data changes. #### Next Step Over the last three lessons, a null value showed up in three separate places: it blanked out a computed expression, left a condition's negation incomplete, and took an engine-dependent place in sorting. These are consequences of a single rule. The next lesson builds that rule — the three-valued logic of an unknown value — introduces the functions that work with null values, and measures the trap that shows up in the negation of a membership condition. ### Working with Null Values Source: https://academia.sh/en/courses/sql-fundamentals/working-with-null-values The consequences of three-valued logic in a query, tests for null values, functions that substitute for null values, filling gaps with a conditional expression, and the trap in the negation of a membership condition. The previous three lessons ran into a null value three times: it blanked out a computed expression, left a condition's negation incomplete, took an engine-dependent place in sorting. Each time, the explanation was deferred. This lesson gives the deferred explanation. A null value is not a value; it is a marker that states the absence of a value. It is not zero, it is not an empty string; it means "this information does not exist for this row." The Data Modeling and Relational Theory course defined what a null value means in the schema. Here its behavior in a query gets built, and that behavior comes from a single source: the result of a comparison made with an unknown value is unknown too. The block below sets up the schema and sample data; every query in this lesson runs against the `library.db` file created in this block. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### The Result of a Comparison How a null value behaves in an equality test can be measured directly. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT NULL = NULL AS equal, NULL <> NULL AS different, NULL IS NULL AS is_null; SQL ``` ``` equal different is_null ----- --------- ------- 1 ``` The first two columns are blank; the third is `1`, meaning true. A null value is neither equal nor unequal to another null value. Whether two unknown numbers are equal to each other cannot be known, because both are unknown. In this case the equality operator produces neither true nor false; it produces a third logical value: **unknown**. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT 1 = 1 AS true_val, 1 = 2 AS false_val, 1 = NULL AS unknown_val; SQL ``` ``` true_val false_val unknown_val -------- --------- ----------- 1 0 ``` The three columns show the logic's three values: true is `1`, false is `0`, and unknown is blank. The rule that `WHERE` only lets through true gets tied to a conclusion here: a comparison that includes a null value lets through no row at all — neither in the condition, nor in its negation. The only correct way to test for a null value is the `IS NULL` and `IS NOT NULL` syntax. These are not comparison operators; they are predicates that test for null directly, and they always produce true or false. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year FROM book WHERE publication_year IS NULL; SQL ``` ``` title publication_year ---------------- ---------------- Motherland Hotel ``` #### Three-Valued Logic Logical connectives work with three values too. The rule is to read unknown as "maybe true, maybe false": the result gets settled if it is the same under both possibilities, and stays unknown if it differs. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT 1 AND NULL AS t_and_u, 0 AND NULL AS f_and_u, 1 OR NULL AS t_or_u, 0 OR NULL AS f_or_u, NOT NULL AS not_u; SQL ``` ``` t_and_u f_and_u t_or_u f_or_u not_u ------- ------- ------ ------ ----- 0 1 ``` Two columns got settled. False `AND` unknown is false: whatever the unknown turns out to be, the result is false. True `OR` unknown is true: one side is already true. The remaining three stayed unknown. Negation does not change unknown — the opposite of unknown is unknown too. This table is the three-valued counterpart of the short-circuit evaluation rule from the Programming Fundamentals course: when a connective's result can be settled from one side, the other side does not affect the result. #### Substituting a Value for Null To show a meaningful value in place of null in the result set, the standard function `COALESCE` is used. It evaluates its arguments left to right and returns the first one that is not null. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name, last_name, COALESCE(email,'(no address)') AS contact FROM member; SQL ``` ``` first_name last_name contact ---------- --------- ------------------ Alice Kane alice@example.test Ben Ortiz ben@example.test Clara Diaz (no address) Derek Voss derek@example.test Grace Kim (no address) Owen Park owen@example.test ``` `COALESCE` can take more than two arguments; it is used to pick the first filled value from a set of prioritized sources. Many engines also offer a two-argument shorthand, but that shorthand's name depends on the engine; the portable syntax is `COALESCE`. The function that works in the opposite direction, `NULLIF`, compares two arguments and returns a null value if they are equal, or the first argument if they are not. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT name, NULLIF(city,'Ankara') AS outside_ankara FROM branch ORDER BY branch_id; SQL ``` ``` name outside_ankara ------------ -------------- Central Bahcelievler Kadikoy Istanbul Konak Izmir ``` `NULLIF` is most useful for converting a meaningless placeholder — zero, an empty string, `-1` — into a real null value. Guarding against division by zero is a common use too: producing a null value when the divisor is zero is more manageable than getting an error. Converting a null value can also be written with a conditional expression. The `CASE` construct is the standard way to turn a column into a readable state. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT loan_id, pickup_date, CASE WHEN return_date IS NULL THEN 'checked out' ELSE 'returned' END AS status FROM loan WHERE loan_id <= 4 ORDER BY loan_id; SQL ``` ``` loan_id pickup_date status ------- ----------- ----------- 1 2025-01-10 returned 2 2025-02-02 returned 3 2025-02-11 checked out 4 2025-03-01 returned ``` Had it been written `CASE WHEN return_date = NULL`, no row would have been labeled checked out — the condition would never be true, and every row would fall into the `ELSE` branch. This is the most insidious form of null value mistakes: the query does not raise an error, it gives a wrong answer. #### The Negation of a Membership Condition The most expensive consequence of three-valued logic shows up in the negation of a membership condition. The branch ids where books are held are 1, 2, and 3; the seventh book's branch has not been set yet, meaning it is null. Let the condition that asks which branch has no books at all first be written without a null value. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT name FROM branch WHERE branch_id NOT IN (1,2,3); SQL ``` ``` name ----- Konak ``` The correct answer: the `Konak` branch has no books at all. Had the same list been produced from the values in the book table, it would have contained a null value too. What happens in that case can be measured. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT name FROM branch WHERE branch_id NOT IN (1,2,3,NULL); SELECT 'rows returned: ' || COUNT(*) AS measurement FROM branch WHERE branch_id NOT IN (1,2,3,NULL); SQL ``` ``` measurement ---------------- rows returned: 0 ``` The first query printed no rows; the second confirmed this by counting. A single null value caused the query to return zero rows, and no error was raised. The reason comes directly from three-valued logic: the `NOT IN` condition expands into "not equal to every item in the list." In the expression `4 <> 1 AND 4 <> 2 AND 4 <> 3 AND 4 <> NULL`, the last factor is unknown; true `AND` unknown is unknown too. The condition is never true for any row. When the same list is used in the positive direction, the problem does not show up. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT name FROM branch WHERE branch_id IN (1,2,3,NULL); SQL ``` ``` name ------------ Central Bahcelievler Kadikoy ``` Because the `IN` condition expands with `OR`, once one side is true, the unknown does not affect the result. The trap is only in the negation, and this asymmetry is the main reason the mistake goes unnoticed: because the positive query works correctly, its negation is assumed to work correctly too. The way to guard against it is to eliminate null values at the source that produces the list, or to write the condition with a null-safe predicate. The standard has the `IS DISTINCT FROM` predicate for this; it treats null values as comparable too. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT 1 IS DISTINCT FROM NULL AS one_and_null, NULL IS DISTINCT FROM NULL AS null_and_null, NULL IS NOT DISTINCT FROM NULL AS null_equals_null; SQL ``` ``` one_and_null null_and_null null_equals_null ------------ ------------- ---------------- 1 0 1 ``` All three columns produced true or false; none of them are unknown. Support for the `IS DISTINCT FROM` predicate depends on the engine; on engines where it is missing, the same effect is achieved by explicitly adding a null test to the condition. How null values behave in aggregate functions follows a separate rule too, and it will be measured in the second topic: aggregate functions do not count null values. #### Summary - A null value is a marker that states the absence of a value; it is not zero or an empty string. - A comparison made with a null value produces neither true nor false, but unknown; because `WHERE` only lets through true, this eliminates the row in both the condition and its negation. - Null is tested only with `IS NULL` and `IS NOT NULL`. - `COALESCE` returns the first argument that is not null, `NULLIF` returns a null value on equality; inside `CASE`, a null test is written with `IS NULL`, not with equality. - A single null value in a `NOT IN` list makes the query return zero rows without an error; the same list used with `IN` does not show the problem. - `IS DISTINCT FROM` gives a null-safe comparison; support for it depends on the engine. #### Next Step In this lesson, `COALESCE`, `NULLIF`, and `CASE` were used in the context of null values; yet all three are members of a wider family. A query does not have to show column values as they are — it can cut text, round a number, extract a year from a date. The next lesson takes up string, number, and date functions and shows why the naming of date functions is the area where the standard is applied the least. ### Built-in Functions Source: https://academia.sh/en/courses/sql-fundamentals/built-in-functions String, number, and date functions that run per row; the distinction between character and byte length, integer division, and how date functions are the area farthest from the standard. The previous lesson used three constructs that convert null values: `COALESCE`, `NULLIF`, and `CASE`. All three are **scalar functions** — they take a single row's values and produce a single value, they do not change the result set's row count, they only transform column values. This lesson builds the rest of the same family: built-in functions that work on text, numbers, and dates. What is instructive about the family is not only what they do, but how variable their naming is. Built-in functions are the area where standard SQL and the engine dialect diverge most visibly. The block below sets up the schema and sample data; every query in this lesson runs against the `library.db` file created in this block. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Length: Characters or Bytes A function that gives text's length has to be clear about what it measures. The distinction built in the How Computers Work course appears directly here: a character can occupy more than one byte. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT LENGTH('Körlük') AS characters, LENGTH(CAST('Körlük' AS BLOB)) AS bytes; SQL ``` ``` characters bytes ---------- ----- 6 8 ``` The six-character word takes eight bytes: each of the letters `ö` and `ü` takes two bytes. Whether the length function counts characters or bytes depends on the engine; some engines have two separately named functions, others look at whether the type is text or binary data. In a field-limit check, this distinction turns directly into a bug — a check that measures in characters but stores in bytes overflows earlier than expected on Turkish text. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, LENGTH(title) AS length FROM book ORDER BY length DESC, title; SQL ``` ``` title length ----------------- ------ Tehlikeli Oyunlar 17 Motherland Hotel 16 The Book of Sand 16 The Disconnected 16 Silent House 12 Blindness 9 Yaban 5 ``` #### Cutting, Searching, Replacing To take a piece of text, a substring function is used. The standard syntax and the common syntax diverge here. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT SUBSTRING('2024-01-09' FROM 1 FOR 4) AS year; SQL ``` ``` Parse error near line 3: near "FROM": syntax error SELECT SUBSTRING('2024-01-09' FROM 1 FOR 4) AS year; error here ---^ ``` The standard's `SUBSTRING(… FROM … FOR …)` syntax is not recognized by this engine. The syntax the engine accepts is the form that separates arguments with commas. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT first_name, SUBSTR(registered_at,1,4) AS registration_year FROM member ORDER BY member_id; SQL ``` ``` first_name registration_year ---------- ----------------- Alice 2023 Ben 2023 Clara 2024 Derek 2024 Grace 2024 Owen 2025 ``` The name of the function that finds a substring's position also depends on the engine. Instead of the standard's `POSITION(… IN …)` syntax, this engine has a separately named function. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT email, INSTR(email,'@') AS position FROM member WHERE email IS NOT NULL ORDER BY member_id; SQL ``` ``` email position ------------------ -------- alice@example.test 6 ben@example.test 4 derek@example.test 6 owen@example.test 5 ``` Position starts at `1`. String positions in SQL are one-based; the zero-based indexing habit from the Programming Fundamentals course does not apply here, and it is a frequent source of an off-by-one mistake. Replacement and trimming functions are less variable. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, REPLACE(title,'Hotel','Inn') AS replaced FROM book WHERE title LIKE '%Hotel%'; SELECT '[' || TRIM(' Blindness ') || ']' AS trimmed; SQL ``` ``` title replaced ---------------- -------------- Motherland Hotel Motherland Inn trimmed ----------- [Blindness] ``` The trim function removes whitespace from both ends by default; there are also forms that state which characters to trim and that trim from only one end. #### Number Functions and Division Most numeric functions are uniform, but division behavior stands apart. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT ABS(-7) AS absolute, ROUND(2.555, 2) AS rounded, 17 % 5 AS remainder, 17 / 5 AS quotient, 17.0 / 5 AS decimal_quotient; SQL ``` ``` absolute rounded remainder quotient decimal_quotient -------- ------- --------- -------- ---------------- 7 2.56 2 3 3.4 ``` `17 / 5` produced `3`, `17.0 / 5` produced `3.4`. What determines whether the division is an integer or a decimal is the type of the operands. This behavior depends on the engine: some engines produce a decimal for the division of two integers too. The portable syntax is to cast at least one operand to a decimal type before dividing. The remainder operator can also be found in two forms, and the forms may not produce the same type. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT 17 % 5 AS with_operator, MOD(17,5) AS with_function; SQL ``` ``` with_operator with_function ------------- ------------- 2 2.0 ``` The same number came back once as an integer and once as a decimal. This difference matters if the result is going to enter a comparison or a grouping. There is a point worth watching in rounding too: decimal numbers cannot be represented exactly in binary — the conclusion of the floating-point lesson in the How Computers Work course holds here as well. In monetary calculations, trust is placed not in the rounding function, but in a decimal type. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, publication_year, publication_year % 10 AS last_digit FROM book WHERE publication_year IS NOT NULL ORDER BY book_id; SQL ``` ``` title publication_year last_digit ----------------- ---------------- ---------- Blindness 1995 5 The Disconnected 1972 2 The Book of Sand 1975 5 Yaban 1932 2 Silent House 1983 3 Tehlikeli Oyunlar 1973 3 ``` #### Date Functions Date is the area where the standard is applied the least. The standard defines a predicate-like syntax for extracting a part from a date, and an interval type for adding a duration to a date. Neither is present in every engine. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT EXTRACT(YEAR FROM pickup_date) AS year FROM loan; SQL ``` ``` Parse error near line 3: near "FROM": syntax error SELECT EXTRACT(YEAR FROM pickup_date) AS year FROM loan; ^--- error here ``` ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT pickup_date + INTERVAL '14' DAY AS due_date FROM loan; SQL ``` ``` Parse error near line 3: near "DAY": syntax error SELECT pickup_date + INTERVAL '14' DAY AS due_date FROM loan; error here ---^ ``` Neither standard syntax worked on this engine. The equivalents the engine offers carry different names. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT loan_id, pickup_date, strftime('%Y', pickup_date) AS year, date(pickup_date,'+14 days') AS due_date FROM loan WHERE loan_id <= 4 ORDER BY loan_id; SQL ``` ``` loan_id pickup_date year due_date ------- ----------- ---- ---------- 1 2025-01-10 2025 2025-01-24 2 2025-02-02 2025 2025-02-16 3 2025-02-11 2025 2025-02-25 4 2025-03-01 2025 2025-03-15 ``` Taking the difference between two dates in days also requires engine-specific syntax. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT loan_id, pickup_date, return_date, CAST(julianday(return_date) - julianday(pickup_date) AS INTEGER) AS days FROM loan WHERE return_date IS NOT NULL AND loan_id <= 5 ORDER BY loan_id; SQL ``` ``` loan_id pickup_date return_date days ------- ----------- ----------- ---- 1 2025-01-10 2025-01-24 14 2 2025-02-02 2025-02-20 18 4 2025-03-01 2025-03-15 14 5 2025-03-18 2025-04-02 15 ``` The third loan record is not in the list: because its return date is null, the difference cannot be computed, and the condition eliminated it. The null value rule holds here too. The engine-dependence of date functions has a practical consequence: once date arithmetic gets embedded inside a query, the query becomes unportable. When portability is required, date arithmetic either moves to the application layer or the engine-specific part gets gathered in a single place. Whether a date column carries a time zone also depends on the engine and the type; a day computation done on a column without a time zone can drift by a day at a time zone boundary. #### Summary - Scalar functions run per row and do not change the result set's row count. - Whether the length function counts characters or bytes depends on the engine; the two diverge on non-ASCII text. - String positions are one-based; the standard syntax for substring and position functions is not present in every engine. - The type of the operands determines whether division is an integer or a decimal, and this behavior depends on the engine. - Date functions are the area that diverges from the standard the most; the syntax for extracting a part and adding a duration changes from engine to engine. - The null value rule holds in date arithmetic too: a difference with one blank end cannot be computed. #### Next Step Up to this point, every query has read from a single table. Yet in a normalized schema, meaningful questions do not fit into a single table: the answer to "which member borrowed which book" is spread across three tables, because the loan table holds only ids. The next topic builds the clause that joins tables, and its first lesson takes up the intersection of matching rows — the inner join. ## Joins and Aggregation Inner and outer joins, cross and self joins, aggregate functions, grouping with group-level filtering, and set operations. ### Inner Join Source: https://academia.sh/en/courses/sql-fundamentals/inner-join Gathering information scattered across a normalized schema into a single result, the join condition, table aliases, the way unmatched rows drop out, and how row count can multiply. The previous topic built queries that read from a single table: column selection, conditions, sorting, null-value behavior, functions. One question remained unanswered by those tools. Looking at the loan table alone does not show which member borrowed which book. The block below builds the schema and the sample data; every query in this lesson runs against the `library.db` file it creates. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT loan_id, book_id, member_id, pickup_date FROM loan WHERE loan_id <= 4 ORDER BY loan_id; SQL ``` ``` loan_id book_id member_id pickup_date ------- ------- --------- ----------- 1 1 1 2025-01-10 2 2 1 2025-02-02 3 1 2 2025-02-11 4 3 3 2025-03-01 ``` All that is visible is identifiers. This is not a shortcoming; it is the direct consequence of normalization: a member's name is written once, in the member table, and the loan record holds only a foreign key pointing to it. The information is deliberately scattered; the query's job is to gather it back together, temporarily. Gathering it back together is called a **join**. #### Join Condition A join matches the rows of two tables against a condition. Its syntax is built from the `JOIN` and `ON` clauses. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT l.loan_id, m.first_name, m.last_name, l.pickup_date FROM loan AS l JOIN member AS m ON l.member_id = m.member_id WHERE l.loan_id <= 4 ORDER BY l.loan_id; SQL ``` ``` loan_id first_name last_name pickup_date ------- ---------- --------- ----------- 1 Alice Kane 2025-01-10 2 Alice Kane 2025-02-02 3 Ben Ortiz 2025-02-11 4 Clara Diaz 2025-03-01 ``` Identifiers turned into names. The condition in the `ON` clause matches the foreign key on the loan record against the primary key in the member table. This is the most common form of join: the one written with an equality condition, carrying the name **inner join**. When the word `JOIN` is written alone, an inner join is understood; the spelling `INNER JOIN` means the same thing. The spellings `AS l` and `AS m` give the tables a **table alias**. When both tables carry a column with the same name — here, `member_id` appears in both — the alias determines which table's column is meant. Single-letter aliases are common, but readability drops as the number of tables grows; meaningful abbreviations serve better. #### Multiple Tables A join can be chained. Because the loan record refers to both the member and the book, two joins together answer the whole question. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT l.loan_id, m.first_name || ' ' || m.last_name AS member, b.title, l.pickup_date FROM loan AS l JOIN member AS m ON l.member_id = m.member_id JOIN book AS b ON l.book_id = b.book_id ORDER BY l.loan_id; SQL ``` ``` loan_id member title pickup_date ------- ---------- ----------------- ----------- 1 Alice Kane Blindness 2025-01-10 2 Alice Kane The Disconnected 2025-02-02 3 Ben Ortiz Blindness 2025-02-11 4 Clara Diaz The Book of Sand 2025-03-01 5 Clara Diaz Yaban 2025-03-18 6 Derek Voss Blindness 2025-04-05 7 Derek Voss Silent House 2025-04-21 8 Grace Kim The Disconnected 2025-05-02 9 Alice Kane Tehlikeli Oyunlar 2025-05-14 10 Grace Kim The Book of Sand 2025-06-03 11 Ben Ortiz Motherland Hotel 2025-06-11 12 Derek Voss Yaban 2025-06-20 ``` All twelve records in the loan table came back. Each join result is a new relation and becomes the input to the next join; the order it is written in is only the order on the page — the order the engine actually applies can differ. The rule of a declarative language holds here as well. #### Unmatched Rows Drop Out The defining property of the inner join is that rows failing the condition never appear in the result at all. The query joining books with branches shows this. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT b.title, br.name AS branch FROM book AS b JOIN branch AS br ON b.branch_id = br.branch_id ORDER BY b.book_id; SQL ``` ``` title branch ---------------- ------------ Blindness Central The Disconnected Central The Book of Sand Bahcelievler Yaban Bahcelievler Silent House Kadikoy Motherland Hotel Kadikoy ``` Six rows came back, though the book table holds seven books. The missing one is `Tehlikeli Oyunlar`: because its branch id is null, the condition `b.branch_id = br.branch_id` is never true for any branch row. The rule from the fifth lesson applies here as well — equality against a null value produces neither true nor false, and a join only lets true through. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT COUNT(*) AS book FROM book; SELECT COUNT(*) AS join_result FROM book AS b JOIN branch AS br ON b.branch_id = br.branch_id; SQL ``` ``` book ---- 7 join_result ----------- 6 ``` The silence of the loss matters: the query raises no error, it returns one row short. When a report's numbers do not add up, one of the first places to look is the rows an inner join has eliminated. When unmatched rows need to be kept as well, an outer join is written; that is the next lesson's subject. #### Row Count Can Grow, Too A join does not only drop rows; it can multiply them. When one row in one table matches several rows in the other table, it appears in the result as many times as it has matches. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT b.title, l.loan_id, l.pickup_date FROM book AS b JOIN loan AS l ON b.book_id = l.book_id WHERE b.title = 'Blindness' ORDER BY l.loan_id; SQL ``` ``` title loan_id pickup_date --------- ------- ----------- Blindness 1 2025-01-10 Blindness 3 2025-02-11 Blindness 6 2025-04-05 ``` The single row for this book in the book table appeared three times once joined with the loan records. This is correct — there really are three separate loan transactions — but it turns into a trap during counting: counting books through this result counts `Blindness` three times over. This distinction returns and is examined more closely when aggregate functions are covered. General rule: in a one-to-many relationship, a join repeats the rows on the "one" side as many times as there are matches on the "many" side. #### Shorthand Syntax Two shorter spellings of a join exist, and both call for caution. The first is the `USING` clause, which bases the join condition on a column carrying the same name in both tables. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, name AS branch FROM book JOIN branch USING (branch_id) ORDER BY book_id; SQL ``` ``` title branch ---------------- ------------ Blindness Central The Disconnected Central The Book of Sand Bahcelievler Yaban Bahcelievler Silent House Kadikoy Motherland Hotel Kadikoy ``` `USING (branch_id)` gives the same result as `ON b.branch_id = br.branch_id` and shows the shared column once in the result. It is useful when naming is consistent; it cannot be used when column names differ. The second is the join that writes no condition at all: the **natural join**. Every column carrying the same name in both tables is taken as the join condition. Its brevity comes at the cost of silent error. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT 'member NATURAL JOIN branch row count: ' || COUNT(*) AS measurement FROM member NATURAL JOIN branch; SQL ``` ``` measurement ---------------------------------------- member NATURAL JOIN branch row count: 24 ``` The member table and the branch table carry no real relationship, and in this schema they also share no column name at all. With nothing to match on, the natural join falls back to a plain cross join: every member row is paired with every branch row, six times four, twenty-four in total. No error message appears; only a suspiciously large result. The real danger runs the other way: a column added to a schema later can happen to share a name with a column in an unrelated table. A natural join that worked correctly one day can silently start computing something else the next, matching rows on a name that carries no real relationship at all. Writing the join condition explicitly removes this entire class of error before it can occur. One more old spelling exists: separating tables with a comma and placing the condition inside `WHERE`. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT b.title, br.name AS branch FROM book AS b, branch AS br WHERE b.branch_id = br.branch_id ORDER BY b.book_id; SQL ``` ``` title branch ---------------- ------------ Blindness Central The Disconnected Central The Book of Sand Bahcelievler Yaban Bahcelievler Silent House Kadikoy Motherland Hotel Kadikoy ``` The result is the same, but it carries two flaws. The join condition and the filter condition mix together in the same clause, and when the condition is forgotten the query produces every matching pair without ever raising an error. The `ON` syntax keeps the condition next to the join and prevents both. #### Summary - In a normalized schema, information is deliberately scattered; a join gathers it back together for the duration of a query. - An inner join returns only the row pairs for which the condition is true; an unmatched row drops out without error. - A foreign key carrying a null value matches no row and is lost in an inner join. - In a one-to-many relationship, a join repeats the row on the "one" side as many times as it has matches. - Table aliases distinguish identically named columns and keep multi-table queries readable. - `USING` shortens a join on an identically named column; a natural join leaves the condition to name similarity and is open to silent error. #### Next Step An inner join dropping unmatched rows is, for some questions, exactly what is not wanted: questions such as "which branch has no books at all" or "which member has never borrowed anything" are asking precisely for the rows that have no match. The next lesson builds the outer joins that keep those rows, shows how the preserved rows fill with null values, and measures how the right and full outer joins depend on engine support. ### Outer Joins Source: https://academia.sh/en/courses/sql-fundamentals/outer-joins Preserving rows that have no match, the way preserved rows fill with null values, the pattern that finds the unmatched, engine support for right and full outer joins, and how the position of a condition changes the result. The previous lesson showed a limit of the inner join: a row that fails the condition never appears in the result at all. Once books were joined with branches, the book with no assigned branch dropped out, and the result came back six rows instead of seven. Some questions ask for exactly those dropped rows. "Which branch has no books at all," "which member has never borrowed anything," "which book has never been picked up by anyone" — all three ask for rows that have no match. An inner join cannot answer them, because it eliminates the very thing being asked about. This lesson builds the join type that keeps unmatched rows. The block below builds the schema and the sample data; every query in this lesson runs against the `library.db` file it creates. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Left Outer Join A **left outer join** keeps every row of the left-hand table. For rows in it that have no match in the right-hand table, the columns coming from the right table are filled with a null value. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT b.title, br.name AS branch FROM book AS b LEFT JOIN branch AS br ON b.branch_id = br.branch_id ORDER BY b.book_id; SQL ``` ``` title branch ----------------- ------------ Blindness Central The Disconnected Central The Book of Sand Bahcelievler Yaban Bahcelievler Silent House Kadikoy Motherland Hotel Kadikoy Tehlikeli Oyunlar ``` All seven books came back. The branch name in the `Tehlikeli Oyunlar` row is empty: the book was kept, but no match means the column coming from the right table could not be filled. The word `OUTER` is optional; `LEFT JOIN` and `LEFT OUTER JOIN` mean the same thing. The source of the null value matters here. The branch name column is declared `NOT NULL`, so no branch row has an empty name. The gap in the result does not come from the data; **the join itself produced it**. A null value in an outer join's result can mean one of two different things: either the source row genuinely held a null value, or there was no match at all. When the distinction matters, it is settled by testing the right table's primary key. Reversing the direction also reverses which side is preserved. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, b.title FROM branch AS br LEFT JOIN book AS b ON b.branch_id = br.branch_id ORDER BY br.branch_id, b.book_id; SQL ``` ``` branch title ------------ ---------------- Central Blindness Central The Disconnected Bahcelievler The Book of Sand Bahcelievler Yaban Kadikoy Silent House Kadikoy Motherland Hotel Konak ``` Now every branch is visible; `Konak`, which has no books, is on the list too, with an empty title column. Same two tables, same condition, different side preserved. #### Finding the Unmatched The most common pattern with an outer join is picking out the preserved-but-unmatched rows. Since a missing match leaves every column of the right table null, testing the right table's primary key for null selects exactly those rows. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS bookless_branch FROM branch AS br LEFT JOIN book AS b ON b.branch_id = br.branch_id WHERE b.book_id IS NULL; SQL ``` ``` bookless_branch --------------- Konak ``` The same pattern applies to members. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name FROM member AS m LEFT JOIN loan AS l ON l.member_id = m.member_id WHERE l.loan_id IS NULL; SQL ``` ``` first_name last_name ---------- --------- Owen Park ``` The test needs to run against the **primary key**. Testing a column that accepts null values instead would give a wrong result: rows that genuinely matched but happened to hold a null in that particular column would slip into the list as well. Because a primary key can never be null, seeing it as null means exactly one thing — no match. #### Right and Full Outer Join The mirror image of a left outer join is the **right outer join**: the rows of the right-hand table are preserved instead. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, b.title FROM book AS b RIGHT JOIN branch AS br ON b.branch_id = br.branch_id ORDER BY br.branch_id, b.book_id; SQL ``` ``` branch title ------------ ---------------- Central Blindness Central The Disconnected Bahcelievler The Book of Sand Bahcelievler Yaban Kadikoy Silent House Kadikoy Motherland Hotel Konak ``` The result is identical to the `branch LEFT JOIN book` query from the earlier section. This is not a coincidence: **every right outer join can be rewritten as a left outer join by reversing the table order.** There is no difference in meaning between the two spellings. This equivalence carries practical weight, because right outer join support **varies by engine** — some engines implement only the left outer join. Writing a query in the left form removes the portability problem before it starts. It is preferred for readability too: the preserved table sits in the `FROM` clause, the first place a reader's eye reaches. A **full outer join** keeps the unmatched rows from both sides at once. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, b.title FROM book AS b FULL OUTER JOIN branch AS br ON b.branch_id = br.branch_id ORDER BY br.branch_id, b.book_id; SQL ``` ``` branch title ------------ ----------------- Tehlikeli Oyunlar Central Blindness Central The Disconnected Bahcelievler The Book of Sand Bahcelievler Yaban Kadikoy Silent House Kadikoy Motherland Hotel Konak ``` Eight rows: six matches, one book with no branch, and one branch with no books. Both directions' gaps were gathered into a single result. Support for the full outer join also **varies by engine**, and it is rarer than support for the right outer join. Where it is missing, the equivalent spelling is the union of a left outer join with its reverse-direction left outer join counterpart; set operations are covered in this topic's last lesson. #### The Position of a Condition Changes the Result The most common mistake in an outer join is writing a condition that belongs to the right table inside `WHERE` instead of `ON`. In an inner join the two spellings give the same result; in an outer join they do not. First with the condition inside the join: ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name, l.loan_id, l.pickup_date FROM member AS m LEFT JOIN loan AS l ON l.member_id = m.member_id AND l.pickup_date >= '2025-05-01' ORDER BY m.member_id, l.loan_id; SQL ``` ``` first_name last_name loan_id pickup_date ---------- --------- ------- ----------- Alice Kane 9 2025-05-14 Ben Ortiz 11 2025-06-11 Clara Diaz Derek Voss 12 2025-06-20 Grace Kim 8 2025-05-02 Grace Kim 10 2025-06-03 Owen Park ``` All six members are on the list. The two members with no loan record meeting the condition were kept, with the loan columns left null. Now the same condition, in the filter clause instead: ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name, l.loan_id, l.pickup_date FROM member AS m LEFT JOIN loan AS l ON l.member_id = m.member_id WHERE l.pickup_date >= '2025-05-01' ORDER BY m.member_id, l.loan_id; SQL ``` ``` first_name last_name loan_id pickup_date ---------- --------- ------- ----------- Alice Kane 9 2025-05-14 Ben Ortiz 11 2025-06-11 Derek Voss 12 2025-06-20 Grace Kim 8 2025-05-02 Grace Kim 10 2025-06-03 ``` Two members disappeared. The reason is the rule from the fifth lesson: the join filled the loan columns of the preserved rows with null values, and the `WHERE` clause then evaluated `NULL >= '2025-05-01'` for those rows, which came out unknown. `WHERE` only lets true through, so the preserved rows were eliminated. The rule can be stated this way: the `ON` clause decides **which rows match**, and the `WHERE` clause decides **which rows remain after the join is done**. A condition that belongs to the right table, when written inside `WHERE`, silently turns an outer join back into an inner join. The one exception to this collapse is a deliberately written null test — the pattern that finds the unmatched relies on exactly that effect. #### Summary - A left outer join keeps every row of the left-hand table and fills the right table's columns with null values where there is no match. - A null value in the result either comes from the source data or was produced by the join itself; the distinction is settled by testing the right table's primary key. - The pattern that finds unmatched rows tests the right table's primary key for null after an outer join. - Every right outer join turns into a left outer join by reversing the table order; support for right and full outer joins varies by engine. - A full outer join keeps the unmatched rows from both sides. - A condition belonging to the right table, when written inside `WHERE`, silently turns an outer join back into an inner join; the condition must be written inside `ON`. #### Next Step Every join covered so far has rested on an equality condition. What happens if the condition is removed entirely, or a table is joined with itself? The next lesson takes up these two special cases: the product an unconditioned join produces, and the comparisons built by connecting a table to itself under two different aliases. ### Cross and Self Join Source: https://academia.sh/en/courses/sql-fundamentals/cross-and-self-join The Cartesian product an unconditioned join produces, the signs of an accidental product, connecting a table to itself under two aliases, and eliminating duplicate pairs. The previous two lessons built a join on an equality condition: the foreign key of the left-hand row equal to the primary key of the right-hand row. The condition does not always have to take this shape — a join with no condition at all is defined too. This lesson looks at two special cases. The first is the unconditioned join: the product in which every row matches every other row. The second is a table joined with itself: queries comparing two rows drawn from the same table. Neither is an exception to the rule; both are edge cases of the same rule. The block below builds the schema and the sample data; every query in this lesson runs against the `library.db` file it creates. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### The Cartesian Product A **cross join** takes no condition at all: every row on the left is matched with every row on the right. The result's row count is the product of the two tables' row counts. Its counterpart in set theory is the **Cartesian product**. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT COUNT(*) AS book_rows FROM book; SELECT COUNT(*) AS branch_rows FROM branch; SELECT COUNT(*) AS product FROM book CROSS JOIN branch; SQL ``` ``` book_rows --------- 7 branch_rows ----------- 4 product ------- 28 ``` Seven times four, twenty-eight. The product looks harmless in a small example; across two tables with ten thousand rows each, it means a hundred million rows. This is the most expensive result a poorly written query can produce, and it is one of the first symptoms looked for when reading a query plan in the Advanced SQL course. The result can be kept to a visible size by filtering the right-hand side. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, b.title FROM branch AS br CROSS JOIN book AS b WHERE b.author = 'Oğuz Atay' ORDER BY br.branch_id, b.book_id; SQL ``` ``` branch title ------------ ----------------- Central The Disconnected Central Tehlikeli Oyunlar Bahcelievler The Disconnected Bahcelievler Tehlikeli Oyunlar Kadikoy The Disconnected Kadikoy Tehlikeli Oyunlar Konak The Disconnected Konak Tehlikeli Oyunlar ``` Four branches, two books, eight rows. No row is claiming "this book is at this branch" — a cross join does not produce a fact, it produces a **list of possibilities**. That is also where it becomes useful: building every possible combination of every branch with every book, then overlaying the ones that actually exist with an outer join, gives a complete report grid in which the empty cells are visible too. #### The Accidental Product A cross join is rarely written on purpose; it appears when a join condition is forgotten. In the old comma-separated syntax, where the condition is left to the `WHERE` clause, this slip is especially easy to make. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT COUNT(*) AS no_condition FROM book, branch; SQL ``` ``` no_condition ------------ 28 ``` The result matches the explicitly written cross join exactly: twenty-eight. The query raised no error. The previous lesson's conditioned join on these same two tables returned six rows. The symptom is recognizable: far more rows than expected, repeated values, and totals that multiply. The remedy is a matter of syntax too — the `JOIN … ON` form keeps the condition next to the join, so forgetting it stands out. Writing `CROSS JOIN` also documents that the product was intentional; a reader of the query can see that the product was the plan. #### Self Join A table can be joined with itself. Doing so requires giving the table two different aliases; from the engine's point of view, this is exactly like two separate tables. The name for the pattern is **self join**, and it is not a separate join type — it is an instance of an inner or outer join in which the same table appears on both sides. The query finding pairs of books by the same author is written first in its unconditioned form. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT a.title AS book_a, b.title AS book_b FROM book AS a JOIN book AS b ON a.author = b.author ORDER BY a.book_id, b.book_id; SQL ``` ``` book_a book_b ----------------- ----------------- Blindness Blindness The Disconnected The Disconnected The Disconnected Tehlikeli Oyunlar The Book of Sand The Book of Sand Yaban Yaban Silent House Silent House Motherland Hotel Motherland Hotel Tehlikeli Oyunlar The Disconnected Tehlikeli Oyunlar Tehlikeli Oyunlar ``` Only two of the nine rows are meaningful, and those two are the same pair seen from both directions. The two typical flaws of a self join show up here. First, every row matches itself — because its author equals itself. Second, the real pair appears twice, with its direction reversed. Both are removed by a single condition: tying the match to the order of the primary key. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT a.author, a.title AS first, b.title AS second FROM book AS a JOIN book AS b ON a.author = b.author AND a.book_id < b.book_id ORDER BY a.book_id; SQL ``` ``` author first second --------- ---------------- ----------------- Oğuz Atay The Disconnected Tehlikeli Oyunlar ``` The condition `a.book_id < b.book_id` does two jobs at once. Because equality is excluded, a row can no longer match itself; because the ordering runs one way, each pair appears only once. Writing the strict inequality as `<>` instead would solve only the first problem, leaving the mirrored duplicates in place. The same pattern applies to another question: pairs of branches in the same city. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT a.city, a.name AS branch_1, b.name AS branch_2 FROM branch AS a JOIN branch AS b ON a.city = b.city AND a.branch_id < b.branch_id; SQL ``` ``` city branch_1 branch_2 ------ -------- ------------ Ankara Central Bahcelievler ``` #### Chaining a Self Join A self join can be combined with other joins. Finding pairs of members who borrowed the same book requires joining the loan table with itself, and then joining that result with the member and book tables. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT b.title, m1.first_name AS member_1, m2.first_name AS member_2 FROM loan AS l1 JOIN loan AS l2 ON l1.book_id = l2.book_id AND l1.member_id < l2.member_id JOIN member AS m1 ON m1.member_id = l1.member_id JOIN member AS m2 ON m2.member_id = l2.member_id JOIN book AS b ON b.book_id = l1.book_id ORDER BY b.book_id, m1.member_id, m2.member_id; SQL ``` ``` title member_1 member_2 ---------------- -------- -------- Blindness Alice Ben Blindness Alice Derek Blindness Ben Derek The Disconnected Alice Grace The Book of Sand Clara Grace Yaban Clara Derek ``` Five table references appear, but the table count is three: the loan table appears twice, the member table appears twice. Aliases are not optional here — nothing else could say which `member_id` column is meant. `Blindness` produced three rows because three separate members borrowed it: the number of pairs chosen from three items is three. A self join's row count grows with the square of the group size; in large groups this can become as expensive as a cross join. #### Summary - A cross join is unconditioned; its row count is the product of the two tables' row counts. - The product is not a list of facts but a list of possibilities; it is useful for building a report grid. - An accidental cross join forms without error when a join condition is forgotten; its symptom is far more rows than expected and multiplying totals. - A self join is not a separate type; it is referencing the same table under two aliases, and the alias is required. - An unconditioned self join matches every row with itself and every pair in both directions; a strict inequality on the primary key removes both at once. - The cost of a pair-producing join grows with the square of the group size. #### Next Step Every query so far has stayed at row level: each result row corresponded to some combination of source rows. Questions like "how many books," "average loan duration," and "earliest publication year" summarize not rows but sets of rows. The next lesson builds the aggregate functions that reduce a row set to a single value, and measures why those functions do not count null values. ### Aggregate Functions Source: https://academia.sh/en/courses/sql-fundamentals/aggregate-functions Reducing a row set to a single value with count, sum, average, and extremum functions; how null values are skipped, the difference between a starred count and a column count, and the result on an empty set. The previous three lessons stayed at row level: each result row corresponded to some combination of source rows. No matter how many tables a query joined, what it produced was still rows. Questions like "how many books are there," "what is the average loan duration," and "what is the earliest publication year" ask for something else: a row set summarized down to a single value. The functions that do this are called **aggregate functions**. Their difference from scalar functions is that their input is not a single row but a set of rows. The block below builds the schema and the sample data; every query in this lesson runs against the `library.db` file it creates. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Three Forms of Count The count function has three spellings, and each counts something different. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT COUNT(*) AS rows, COUNT(publication_year) AS year_known, COUNT(DISTINCT author) AS distinct_authors FROM book; SQL ``` ``` rows year_known distinct_authors ---- ---------- ---------------- 7 6 6 ``` `COUNT(*)` counts **rows**; it never looks at column values, seven rows is seven. `COUNT(publication_year)` counts the **non-null values** in that column; the book with an unknown publication year did not enter the count, so the result came out six. `COUNT(DISTINCT author)` counts the non-null **distinct** values; seven books have six distinct authors. This distinction is the first example of a general rule for aggregate functions: **aggregate functions skip null values**. The one exception is `COUNT(*)`, because it looks at the row itself and not at any one column. The same distinction answers a question directly in the loan table. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT COUNT(*) AS records, COUNT(return_date) AS returned FROM loan; SQL ``` ``` records returned ------- -------- 12 9 ``` Nine of twelve loan records have been returned. The second column did not count the three records with a null return date — no condition was written, only the null-value rule running on its own. #### Totals, Averages, and Extremes Four more functions round out the same family. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT MIN(publication_year) AS earliest, MAX(publication_year) AS latest, SUM(publication_year) AS total, AVG(publication_year) AS average FROM book; SQL ``` ``` earliest latest total average -------- ------ ----- ---------------- 1932 1995 11830 1971.66666666667 ``` All four skipped the null value. The total is the sum of six numbers, and the average is that same total divided by six. What the average is divided by is the null value's most commonly overlooked consequence. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT AVG(publication_year) AS average, SUM(publication_year) * 1.0 / COUNT(*) AS divided_by_rows, SUM(publication_year) * 1.0 / COUNT(publication_year) AS divided_by_filled FROM book; SQL ``` ``` average divided_by_rows divided_by_filled ---------------- --------------- ----------------- 1971.66666666667 1690.0 1971.66666666667 ``` The number in the middle is wrong. The total comes from six values, while `COUNT(*)` counts seven rows; the division behaves as if a nonexistent book had been counted with a publication year of zero. The average function and the third column give the same result, because both count only the filled values. The decision rests on what the null value means. If it means "unknown," excluding it from the average is correct. If it means "zero," the schema is wrong: zero is a value, and a zero belongs in the column. Confusing the two silently corrupts numbers derived from the data. Extremum functions work on text columns too, and the result depends on collation. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT MIN(title) AS first, MAX(title) AS last FROM book; SQL ``` ``` first last --------- ----- Blindness Yaban ``` The rule seen in the fourth lesson applies here too: which text counts as "smallest" is decided by the comparison ordering, and the default ordering **varies by engine**. #### The Result on an Empty Set What do aggregate functions return when they find no row at all? Count and sum answer this question differently. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT COUNT(*) AS rows, SUM(publication_year) AS total, COALESCE(SUM(publication_year), 0) AS safe FROM book WHERE author = 'Unknown Author'; SQL ``` ``` rows total safe ---- ----- ---- 0 0 ``` The query found no rows at all. The count returned `0` — counting zero rows is a defined operation. The sum returned null, not `0`: if there are no numbers at all, the total is undefined too. The distinction is logically consistent but causes trouble in practice, because code that feeds a total into a calculation usually expects a number. There is also a point worth noting beyond this: a query containing an aggregate function with no grouping **always returns exactly one row**, even when it finds no source rows at all. The output above is not empty; it is one row whose contents are empty. This behavior changes once grouping is added, which is the next lesson's subject. The way to guard against this is turning the null value into a placeholder wherever the total is used — the third column's spelling does exactly that. #### Confusing Aggregation with a Column An aggregate function reduces an entire row set to a single row. Asking for an ungrouped column in the same query creates a logical contradiction: whose value would be shown? ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title, MAX(publication_year) AS latest FROM book; SQL ``` ``` title latest --------- ------ Blindness 1995 ``` The query ran and produced a consistent-looking answer: the title of the most recent publication really is `Blindness`. This is nonetheless behavior that **varies by engine**. The standard does not allow an ungrouped column to be selected alongside an aggregate function; some engines raise an error, some return an arbitrary row's value, and this engine picks the matching row when used together with an extremum function. This spelling should be avoided as a rule. When the full row that produced the extreme value is what is wanted, the correct tool is sorting combined with a limit, or the window functions covered in the Advanced SQL course. #### Aggregation Combined with a Join Aggregate functions also work over a joined result. Because loan duration is the difference between two date columns, it is summed over a computed expression. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT COUNT(*) AS records, COUNT(return_date) AS returned, ROUND(AVG(julianday(return_date) - julianday(pickup_date)), 2) AS average_days FROM loan; SQL ``` ``` records returned average_days ------- -------- ------------ 12 9 16.11 ``` Three columns summarize the same table at three different scales: twelve records exist, nine of them are returned, and the average duration of the returned ones is a little over sixteen days. The average's denominator is again nine; for records that have not been returned, the difference cannot be computed, so a null value is produced and skipped. The row-multiplying effect of a join can turn into a trap here. Joining a book with its loan records and counting produces a count in which a book borrowed more than once is counted more than once. In such a query, the spelling `COUNT(DISTINCT book_id)` gives the correct result. #### Summary - Aggregate functions reduce a row set to a single value, and when written without grouping, the result is always exactly one row. - `COUNT(*)` counts rows, `COUNT(column)` counts the non-null values in that column, and a distinct count counts duplicates once. - Aggregate functions skip null values; the average's denominator is the count of filled values, not the row count. - Count returns zero on an empty set; sum returns a null value. - Selecting an ungrouped column together with an aggregate function is not standard, and its behavior varies by engine. - When a join multiplies rows, counts inflate; a distinct count corrects it. #### Next Step Every aggregation so far reduced the whole table to a single result. What is actually useful in practice is usually not "how many in total" but "how many for each branch," "how many for each member." The next lesson builds grouping, which splits rows into sets and produces a separate summary for each set, and measures within the same query why the clause that filters groups is separate from the `WHERE` clause. ### Grouping and Filtering Source: https://academia.sh/en/courses/sql-fundamentals/grouping-and-filtering Splitting rows into sets, producing a summary per group, how a null value forms its own group, the count trap in an outer join, and measuring row filtering against group filtering in the same query. The previous lesson built aggregate functions, but every one of them reduced the whole table to a single result. In practice that is usually not what is wanted: the question "how many books in total" is asked once and answered once, while "how many books does each author have" gets asked over and over. This lesson builds the clause that splits rows into sets and produces a separate summary for each set. It then introduces the clause that filters the groups themselves, and measures why that clause is separate from the clause that filters rows, in the same query. The block below builds the schema and the sample data; every query in this lesson runs against the `library.db` file it creates. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Splitting Rows into Groups The `GROUP BY` clause treats rows carrying the same value in the given columns as one group and produces exactly one result row for each group. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author, COUNT(*) AS book_count FROM book GROUP BY author ORDER BY book_count DESC, author; SQL ``` ``` author book_count ----------------- ---------- Oğuz Atay 2 Jorge Luis Borges 1 José Saramago 1 Orhan Pamuk 1 Yakup Kadri 1 Yusuf Atılgan 1 ``` Seven rows split into six groups, and each group came back as a single row. Sorting is allowed on the column an aggregate function produces — this is valid because sorting is evaluated after grouping. What can be written in the column list of a grouped query is restricted: either one of the grouping columns, or an aggregate function. Writing any other column is meaningless, because there is no single value for that column within the group. Enforcement of this rule **varies by engine** — some raise an error, some return an arbitrary row's value. #### A Null Value Forms Its Own Group The behavior of null values under grouping differs from their behavior under a condition. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT branch_id, COUNT(*) AS book_count FROM book GROUP BY branch_id ORDER BY branch_id; SQL ``` ``` branch_id book_count --------- ---------- 1 1 2 2 2 3 2 ``` The book with a null branch id was not discarded; it became a group of its own. Grouping treats null values as **equal to each other**, while the equality operator does not count them so. This looks inconsistent but is a necessary choice: otherwise every null value would form its own separate group and grouping would be useless. The grouping key can be an expression too; the breakdown by decade shows this. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT (publication_year / 10) * 10 AS decade, COUNT(*) AS count FROM book WHERE publication_year IS NOT NULL GROUP BY decade ORDER BY decade; SQL ``` ``` decade count ------ ----- 1930 1 1970 3 1980 1 1990 1 ``` #### The Count Trap in an Outer Join Grouping is most often used together with an outer join: the book count for every branch, including the branches with no books at all. In this combination, the spelling of the count directly determines the result. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.name AS branch, COUNT(b.book_id) AS book_count, COUNT(*) AS row_count FROM branch AS br LEFT JOIN book AS b ON b.branch_id = br.branch_id GROUP BY br.branch_id, br.name ORDER BY br.branch_id; SQL ``` ``` branch book_count row_count ------------ ---------- --------- Central 2 2 Bahcelievler 2 2 Kadikoy 2 2 Konak 0 1 ``` The two columns split apart on the last row. `Konak` has no books at all, but the outer join produced a row for it to preserve it, with the book columns left null. `COUNT(*)` counted that row and gave `1` — wrong. `COUNT(b.book_id)` skipped the null value and gave `0` — correct. The rule: when counting after an outer join, the column counted should belong to the **matching side, not the preserved side**, and that column should be one that does not accept null values, preferably the primary key. This is the direct application of the previous lesson's rule — aggregate functions skip null values. A grouping key can be made up of more than one column; the group is then formed by the combination of those columns. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT br.city, br.name AS branch, COUNT(b.book_id) AS book_count FROM branch AS br LEFT JOIN book AS b ON b.branch_id = br.branch_id GROUP BY br.city, br.name ORDER BY br.city, br.name; SQL ``` ``` city branch book_count -------- ------------ ---------- Ankara Bahcelievler 2 Ankara Central 2 Istanbul Kadikoy 2 Izmir Konak 0 ``` Every combination of city and branch name became its own group. The two branches in Ankara were not merged into a single row, because the second key separates them. #### Row Filtering versus Group Filtering The `WHERE` clause filters rows; the `HAVING` clause filters groups. The difference between them comes from evaluation order: `WHERE` runs **before** grouping, `HAVING` runs **after** grouping. The order is `FROM`, `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `ORDER BY`. Asking the same question twice is enough to measure the result. First, without filtering: members who have borrowed at least twice. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name, COUNT(*) AS loan_count FROM loan AS l JOIN member AS m ON m.member_id = l.member_id GROUP BY m.member_id, m.first_name, m.last_name HAVING COUNT(*) >= 2 ORDER BY loan_count DESC, m.member_id; SQL ``` ``` first_name last_name loan_count ---------- --------- ---------- Alice Kane 3 Derek Voss 3 Ben Ortiz 2 Clara Diaz 2 Grace Kim 2 ``` The condition `HAVING COUNT(*) >= 2` ran over the groups; groups below two records were eliminated. Now add a row filter to the same query: count only returned loans. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, m.last_name, COUNT(*) AS loan_count FROM loan AS l JOIN member AS m ON m.member_id = l.member_id WHERE l.return_date IS NOT NULL GROUP BY m.member_id, m.first_name, m.last_name HAVING COUNT(*) >= 2 ORDER BY loan_count DESC, m.member_id; SQL ``` ``` first_name last_name loan_count ---------- --------- ---------- Alice Kane 3 Clara Diaz 2 Derek Voss 2 ``` Five rows dropped to three, and the numbers changed. The `WHERE` clause eliminated the three unreturned records before grouping began; the groups were built from this reduced data, and the `HAVING` condition applied to the new counts. `Derek` dropped from three to two, `Ben` and `Grace` fell below the threshold and left the list. The distinction can be summarized this way: `WHERE` answers "which rows will be counted," `HAVING` answers "which groups will be shown." The two cannot swap places. A condition that contains an aggregate function cannot be written inside `WHERE`, because no group exists yet at that stage. The reverse also holds: a condition on an ungrouped column should not be written inside `HAVING`. The standard does not allow it, but enforcement **varies by engine**. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT m.first_name, COUNT(*) AS count FROM loan AS l JOIN member AS m ON m.member_id = l.member_id GROUP BY m.member_id, m.first_name HAVING l.return_date IS NOT NULL; SQL ``` ``` first_name count ---------- ----- Alice 3 Clara 2 Derek 3 Grace 2 ``` The query raised no error, but the result answers no real question: the counts match the unfiltered query, while the row set falls between the two — only `Ben` dropped from the unfiltered set, and `Grace`, which the filtered query removed, stayed. A single row's return date within the group was evaluated, and the fate of the whole group was tied to it. This kind of result is wrong without ever producing an error message; any condition that is not at the group level belongs inside `WHERE`. `HAVING` referencing an aggregate function not present in the `SELECT` list is, by contrast, valid — group filtering is not limited to the columns being displayed. #### Summary - `GROUP BY` splits rows into sets by key value and produces exactly one row per set. - A grouped query's column list should hold only grouping columns and aggregate functions. - Grouping treats null values as equal to each other; a null value forms its own group. - After an outer join, a count should run over a non-null column on the matching side; `COUNT(*)` counts the preserved row too. - `WHERE` filters rows before grouping, `HAVING` filters groups after grouping; the order is `FROM`, `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `ORDER BY`. - Writing a condition that is not at the group level inside `HAVING` produces a wrong result without an error, in some engines. #### Next Step Grouping arranged the rows a single query produces. Some questions instead want to compare the results of two separate queries: what is common to both lists, what is in one but not the other, what the sum of the two is. The next lesson builds the operations that combine result sets as sets, and shows why keeping or discarding duplicate rows is an explicit choice. ### Set Operations Source: https://academia.sh/en/courses/sql-fundamentals/set-operations Combining two result sets as a union, intersection, and difference; whether duplicate rows are kept, the equality of null values in set operations, and how operator precedence depends on the engine. The previous lesson built grouping: the rows a single query produced were split into sets and each set was summarized. The join and the grouping both worked, throughout, inside a single query. Some questions instead ask for the results of two separate queries to be compared against each other. "What is common to these two lists," "what is in the first but not the second," "what is the sum of the two lists" — all three are operations from set theory, and SQL has direct counterparts for them. This lesson builds those operations. The distinction is worth stating up front: a join adds rows **side by side**, increasing the column count. Set operations add rows **one below the other**, leaving the column count unchanged. The block below builds the schema and the sample data; every query in this lesson runs against the `library.db` file it creates. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` #### Two Result Sets Two queries will be used throughout the examples. The first gives the authors of books published before 1980, the second gives the authors of books at the central branch. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE publication_year < 1980 ORDER BY author; SELECT author FROM book WHERE branch_id = 1 ORDER BY author; SQL ``` ``` author ----------------- Jorge Luis Borges Oğuz Atay Oğuz Atay Yakup Kadri author ------------- José Saramago Oğuz Atay ``` The first set has four rows and holds a duplicate — the same author appears twice, once for each of two books published before 1980. The second set has two rows. The value the two sets share is `Oğuz Atay`. For two queries to be combined with a set operation they must be **union compatible**: equal column counts, and corresponding columns of comparable types. If the counts are not equal, the query does not run. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT title FROM book UNION SELECT name, city FROM branch; SQL ``` ``` Parse error near line 3: SELECTs to the left and right of UNION do not have the same number of result columns ``` The column names in the result are taken from the **first query**; the aliases of later queries are ignored. #### Union and Duplicate Rows A union places the rows of two sets one below the other. It has two spellings, and the only difference between them is what happens to duplicate rows. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE publication_year < 1980 UNION SELECT author FROM book WHERE branch_id = 1 ORDER BY author; SQL ``` ``` author ----------------- Jorge Luis Borges José Saramago Oğuz Atay Yakup Kadri ``` Four plus two, six rows were expected; four came back. `UNION` **eliminates** duplicate rows — both the row common to the two sets and the duplicate inside a single set. `Oğuz Atay` appeared three times across the inputs and appeared once. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE publication_year < 1980 UNION ALL SELECT author FROM book WHERE branch_id = 1 ORDER BY author; SQL ``` ``` author ----------------- Jorge Luis Borges José Saramago Oğuz Atay Oğuz Atay Oğuz Atay Yakup Kadri ``` `UNION ALL` eliminated nothing: six rows. The distinction is not only a difference in result but a difference in cost. Eliminating duplicates requires the engine to compare every row against every other — it builds a sort or a hash table. `UNION ALL` does none of that work. The choice should be deliberate: `UNION` when duplicates genuinely need to be eliminated, `UNION ALL` when they do not. Combining two sets known to be disjoint with `UNION` pays an elimination cost that serves no purpose. In the other direction, writing `UNION ALL` when duplicates need to be eliminated silently produces inflated counts. #### Intersection and Difference Intersection returns the rows found in both sets. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE publication_year < 1980 INTERSECT SELECT author FROM book WHERE branch_id = 1; SQL ``` ``` author --------- Oğuz Atay ``` Difference returns the rows found in the first set but not in the second. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE publication_year < 1980 EXCEPT SELECT author FROM book WHERE branch_id = 1; SQL ``` ``` author ----------------- Jorge Luis Borges Yakup Kadri ``` Difference is **directional**: reversing the order changes the result. Intersection and union are not. Both operations eliminate duplicate rows by default. Forms that keep duplicates are defined in the standard, but their support **varies by engine**. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE publication_year < 1980 INTERSECT ALL SELECT author FROM book WHERE branch_id = 1; SQL ``` ``` Parse error near line 3: near "ALL": syntax error FROM book WHERE publication_year < 1980 INTERSECT ALL SELECT author FROM book error here ---^ ``` The **name** of the difference operation also varies by engine: some engines use a different keyword instead of `EXCEPT`. When portability matters, set operations should be tested against the target engine, name and options together. #### Null Values Are Equal in Set Operations The trap measured in the fifth lesson is worth recalling: a single null value inside a `NOT IN` list dropped a query's row count to zero. Set operations do not carry the same trap, because they treat null values as **equal to each other**, the same way grouping does. ```sh sqlite3 library.db <<'SQL' .headers on .mode box SELECT NULL AS union_result UNION SELECT NULL; SELECT NULL AS union_all_result UNION ALL SELECT NULL; SQL ``` ``` ┌──────────────┐ │ union_result │ ├──────────────┤ │ │ └──────────────┘ ┌──────────────────┐ │ union_all_result │ ├──────────────────┤ │ │ │ │ └──────────────────┘ ``` The framed output format makes the row count visible here: two null values collapsed to one row under a union, while the `UNION ALL` spelling kept two rows. A null value is not equal to itself with respect to the comparison operator, but it is equal to itself with respect to set operations. This has a direct consequence: when the question "which branch has no books at all" is written as a difference operation, the null branch id in the book table does not corrupt the result. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT branch_id FROM branch EXCEPT SELECT branch_id FROM book; SQL ``` ``` branch_id --------- 4 ``` The correct answer is a single row. Had the same question been written with `NOT IN`, the null id in the book table would have caused it to return no row at all. A difference operation is a safer choice than `NOT IN` when a column may hold null values. #### Precedence and Ordering When more than one set operator appears in a single statement, evaluation order starts to matter. The standard gives intersection higher precedence than union and difference. Its application **varies by engine**. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE branch_id = 1 UNION SELECT author FROM book WHERE publication_year < 1980 INTERSECT SELECT author FROM book WHERE publication_year > 1980; SQL ``` ``` author ------------- José Saramago ``` This engine applied the operators left to right: union first, then intersection. Had the intersection applied first, the second and third sets would have shared no row, the intersection would have come back empty, the union would have returned the first set unchanged, and the result would have been two rows. Grouping with parentheses is not accepted everywhere either. ```sh sqlite3 library.db <<'SQL' .headers on .mode column SELECT author FROM book WHERE branch_id = 1 UNION (SELECT author FROM book WHERE publication_year < 1980 INTERSECT SELECT author FROM book WHERE publication_year > 1980); SQL ``` ``` Parse error near line 3: near "(": syntax error SELECT author FROM book WHERE branch_id = 1 UNION (SELECT author FROM book WHE error here ---^ ``` Mixing different set operators inside a single statement is, as a result, not a portable spelling. Where a mixed combination is required, structures that name an intermediate result — the common table expressions covered in the Advanced SQL course — both make the order explicit and keep the query readable. There is a rule for ordering too: the `ORDER BY` clause applies not to the individual queries but to the **whole combined result**, and it is written once, at the end of the statement. The sort key is therefore referred to by the column names the first query produces. #### Summary - A join adds rows side by side, set operations add rows one below the other; set operations do not change the column count. - Queries entering a set operation must be union compatible; column names are taken from the first query. - `UNION` eliminates duplicate rows and pays a comparison cost for it; `UNION ALL` eliminates nothing. - Intersection and difference eliminate duplicates by default; support for the forms that keep duplicates and for the name of the difference operator varies by engine. - Set operations treat null values as equal to each other, which makes a difference operation a safer choice than `NOT IN`. - The precedence of mixed set operators in a single statement varies by engine; `ORDER BY` applies to the whole combined result. #### Next Step Everything done throughout this topic was reading: existing rows were selected, filtered, joined, summarized, grouped. The data was there the whole time — written once, in the first lesson, and never changed again. The turn is now to writing the data and the schema that holds it. The next topic's first lesson takes up creating tables: column definitions, type choices, and how constraints are expressed in a schema. The integrity rules designed on paper in the Data Modeling and Relational Theory course become, there, a definition that actually runs. ## Data Manipulation and Definition Table definition and constraints, the limits of altering a schema and the portable path for rebuilding it, single and bulk row insertion, the consequences of unconditional updates and deletes, view updatability, and object-level authorization. ### Creating Tables Source: https://academia.sh/en/courses/sql-fundamentals/creating-tables The parts of a column definition, column-level and table-level constraints, how keys are expressed in a schema, and the error messages a constraint violation produces in the engine. The course so far has been about reading data that already exists: selecting columns, writing conditions, joining tables, grouping rows, and combining result sets with set operations. Every query took an already-built schema as a given. This lesson removes that assumption and asks the question that comes next: how is the schema itself written. The first of the families separated out in the SQL Language Families lesson — data definition language — is this topic's territory. The library schema was set up in that lesson, and three kinds of constraint were introduced there: `PRIMARY KEY`, `NOT NULL`, and `REFERENCES`. That lesson explained what these declarations say; this one shows what they prevent, and adds to the list what lies beyond the three — default values, value-range checks, uniqueness, and named constraints. A table definition sets the rule for which rows may enter that table. If the rule lives in the schema, the engine enforces it on every write attempt; if the rule lives only in application code, every path that bypasses that code punches through it. This lesson's subject is that difference. #### Parts of a Table Definition The body of a `CREATE TABLE` statement is a comma-separated list of items. Each item is either a column definition or a table constraint. A column definition has three parts: a name, a type, and zero or more **constraints**. The smallest table in the schema holds branches. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE branch ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'); INSERT INTO branch (branch_id, name, city) VALUES (3,'Kadikoy','Istanbul'); SELECT * FROM branch; SQL ``` ``` branch_id name city --------- ------------ -------- 1 Central Ankara 2 Bahcelievler Ankara 3 Kadikoy Istanbul ``` The `:memory:` argument on the command line builds the database in memory rather than on disk: the moment the example ends, it disappears. Every block in this lesson runs this way and leaves no file behind. The `library.db` file from the querying lessons is left untouched. #### Forbidding Blanks and Setting a Default The Data Modeling and Relational Theory course showed that a null value means "unknown" and triggers three-valued logic. When a blank makes no sense for a column, the schema says so: `NOT NULL`. `DEFAULT` specifies what a column is filled with when an insert leaves it unspecified. `UNIQUE` requires that the values in a column never repeat. The member table uses all three. Two things change from the definition used in the querying lessons: email must now be unique, and a column holding membership status has been added. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' ); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (2,'Ben',NULL,'ben@example.test','2023-05-30'); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (3,'Clara','Diaz','alice@example.test','2024-01-09'); SELECT * FROM member; SQL ``` ``` Runtime error near line 14: NOT NULL constraint failed: member.last_name (19) Runtime error near line 16: UNIQUE constraint failed: member.email (19) member_id first_name last_name email registered_at status --------- ---------- --------- ------------------ ------------- ------ 1 Alice Kane alice@example.test 2023-02-14 active ``` Three inserts were attempted, one succeeded. The first row got the value `active` for `status` even though it never wrote to that column: the default kicked in. The second left the last name blank and ran into the `NOT NULL` constraint. The third repeated an email address that was already in use. The structure of the error message matters: which kind of constraint, which table, which column. On a constraint violation the engine does not write the row; the statement fails and its effect is undone in full. There is no such thing as a partially written row. #### Uniqueness and Null Values `PRIMARY KEY` is the schema-level counterpart of the primary key defined in the Relational Theory course, and it is a combination of two constraints: uniqueness plus not being null. `UNIQUE` asks for only the first of the two. A null value is what makes the difference visible. In standard SQL, a unique column can hold more than one null, because two unknown values cannot be said to be equal. The `email` column in the schema is an example: some members have no email on file. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL ); INSERT INTO member VALUES (3,'Clara','Diaz',NULL,'2024-01-09'); INSERT INTO member VALUES (5,'Grace','Kim',NULL,'2024-11-05'); INSERT INTO member VALUES (6,'Owen','Park','owen@example.test','2025-01-18'); SELECT count(*) AS rows, count(email) AS with_email FROM member; SQL ``` ``` rows with_email ---- ---------- 3 1 ``` Two rows stood with a blank email at the same time. This behavior is a point where engines can differ: some accept more than one null, others offer additional syntax that leaves the decision to the designer. When a column needs to be both unique and mandatory, the pair `NOT NULL UNIQUE` is written — which is exactly the definition of a primary key. A key can also be made up of more than one column. The rule that stops a member from putting the same book on hold twice is a composite primary key written at the table level. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE reservation ( member_id INTEGER NOT NULL, book_id INTEGER NOT NULL, date TEXT NOT NULL, PRIMARY KEY (member_id, book_id) ); INSERT INTO reservation VALUES (1, 5, '2025-07-01'); INSERT INTO reservation VALUES (2, 5, '2025-07-02'); INSERT INTO reservation VALUES (1, 6, '2025-07-03'); INSERT INTO reservation VALUES (1, 5, '2025-07-04'); SELECT * FROM reservation; SQL ``` ``` Runtime error near line 13: UNIQUE constraint failed: reservation.member_id, reservation.book_id (19) member_id book_id date --------- ------- ---------- 1 5 2025-07-01 2 5 2025-07-02 1 6 2025-07-03 ``` Different members could hold the same book, and the same member could hold different books; the only thing that repeated was the pair itself. In a composite key, uniqueness applies to the value the columns form together, not to each column on its own. #### Narrowing the Value Range `CHECK` writes a condition that gets validated when a row is written. If the condition evaluates to false, the row is rejected. A rule such as membership status being one of three values gets moved into the schema this way, rather than living in application code. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')) ); INSERT INTO member (member_id, first_name, last_name, registered_at, status) VALUES (1,'Alice','Kane','2023-02-14','suspended'); INSERT INTO member (member_id, first_name, last_name, registered_at, status) VALUES (2,'Ben','Ortiz','2023-05-30','inactive'); SELECT * FROM member; SQL ``` ``` Runtime error near line 14: CHECK constraint failed: status IN ('active', 'suspended', 'closed') (19) member_id first_name last_name registered_at status --------- ---------- --------- ------------- --------- 1 Alice Kane 2023-02-14 suspended ``` A check constraint written inside a column definition can only look at that column. When a rule needs to test two columns together, the constraint is written at the end of the column list as a **table constraint**. The prefix `CONSTRAINT name` gives the constraint a name; that name shows up in the error message and makes it readable which rule the error belongs to. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT, CONSTRAINT return_after_checkout CHECK (return_date IS NULL OR return_date >= pickup_date) ); INSERT INTO loan VALUES (1, 1, 1, '2025-01-10', '2025-01-24'); INSERT INTO loan VALUES (3, 1, 2, '2025-02-11', NULL); INSERT INTO loan VALUES (4, 3, 3, '2025-03-01', '2025-02-15'); SELECT * FROM loan; SQL ``` ``` Runtime error near line 15: CHECK constraint failed: return_after_checkout (19) loan_id book_id member_id pickup_date return_date ------- ------- --------- ----------- ----------- 1 1 1 2025-01-10 2025-01-24 3 1 2 2025-02-11 ``` The `return_date IS NULL OR` part of the condition spells out the intent but does not change the behavior. A comparison against a null value evaluates to unknown rather than true, and a constraint rejects a row only on a definite false; it does not count unknown as a violation. Had the constraint consisted only of the second condition, a loan not yet returned would still be accepted — the row that gets rejected is always the one where `return_date < pickup_date` really holds. The three-valued logic of null values filters in a `WHERE` clause and forgives in a constraint; that is the most common place to stumble when writing one. #### Links Between Tables The `REFERENCES` syntax requires that a value in one column be found among the keys of another table. This is referential integrity's expression in the schema: a book cannot be written against a branch that does not exist. Whether foreign key checking is switched on differs from engine to engine. In the command-line tool used here, the check is off by default and is turned on per connection. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100), branch_id INTEGER REFERENCES branch(branch_id) ); INSERT INTO branch VALUES (1,'Central','Ankara'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,9); SELECT * FROM book; SQL ``` ``` book_id title author publication_year branch_id ------- --------- ------------- ---------------- --------- 1 Blindness José Saramago 1995 9 ``` The constraint written into the schema was not enforced; a row carrying a branch number that does not exist entered the table. When the same block is opened with a `PRAGMA foreign_keys = ON;` line, the result changes. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = ON; CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100), branch_id INTEGER REFERENCES branch(branch_id) ); INSERT INTO branch VALUES (1,'Central','Ankara'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,9); INSERT INTO book VALUES (2,'The Disconnected','Oğuz Atay',1972,1); INSERT INTO book VALUES (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); SELECT * FROM book; SQL ``` ``` Runtime error near line 15: FOREIGN KEY constraint failed (19) book_id title author publication_year branch_id ------- ----------------- --------- ---------------- --------- 2 The Disconnected Oğuz Atay 1972 1 7 Tehlikeli Oyunlar Oğuz Atay 1973 ``` The book linked to a branch that does not exist was rejected, and the one linked to a valid branch was accepted. The third row's branch number is null, and that one was accepted too: a foreign key does not check when the column is null — "which branch it is in is unknown" is different from "in a branch that does not exist." The `PRAGMA` syntax is not part of standard SQL; it belongs to the engine in use. The lesson that transfers is this: **a constraint being written into the schema does not mean it is being checked.** When starting work with a new database, the state of foreign key checking is tested; a check left off means orphan rows discovered months later. #### The Library Schema Put together, this lesson's pieces form the schema the rest of the topic uses. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = ON; CREATE TABLE branch ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100), branch_id INTEGER REFERENCES branch(branch_id) ); CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')) ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT, CONSTRAINT return_after_checkout CHECK (return_date IS NULL OR return_date >= pickup_date) ); INSERT INTO branch VALUES (1,'Central','Ankara'),(3,'Kadikoy','Istanbul'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(5,'Silent House','Orhan Pamuk',1983,3); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'),(3,'Clara','Diaz',NULL,'2024-01-09'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(4,5,3,'2025-03-01',NULL); SELECT u.first_name, u.last_name, k.title, s.name AS branch, o.pickup_date FROM loan AS o JOIN member AS u ON u.member_id = o.member_id JOIN book AS k ON k.book_id = o.book_id JOIN branch AS s ON s.branch_id = k.branch_id ORDER BY o.loan_id; SQL ``` ``` first_name last_name title branch pickup_date ---------- --------- ------------ ------- ----------- Alice Kane Blindness Central 2025-01-10 Clara Diaz Silent House Kadikoy 2025-03-01 ``` All four tables have a primary key that is an integer not drawn from the data itself. Books had a natural candidate — the ISBN — but that number names an edition; two copies of the same edition carry the same number, and the key cannot tell the copies apart. A natural field that looks like a key but cannot serve as one is a situation that comes up often in schema design, and it is usually resolved with a generated key like the one in this lesson. #### Summary - A column definition consists of a name, a type, and constraints; if a constraint lives in the schema the engine enforces it on every write path, and if it lives in application code it is enforced only on the path that goes through that code. - `NOT NULL` forbids blanks, `DEFAULT` fills in a column that was not written, and `CHECK` narrows the range of values; on a column that can be null, the check condition must handle the blank separately. - `UNIQUE` asks for uniqueness alone, `PRIMARY KEY` asks for uniqueness together with being mandatory; a key can be made up of more than one column, and uniqueness applies to the value those columns produce together. - `REFERENCES` writes referential integrity into the schema, does not check on a null column, and whether the check is switched on differs from engine to engine. - On a constraint violation the statement fails in full; because the error message gives the constraint type, the table, and the constraint name, the diagnosis can be read directly. #### Next Step A schema does not stay in the shape it was first written: a new column becomes necessary, a column's name turns out to have been chosen badly, a constraint needs to be added after the fact. The next lesson takes up altering the schema and shows the real difficulty: the changes `ALTER TABLE` can make in a single statement are limited, and that limit differs between engines. Past the limit, the table gets rebuilt; that lesson also sets up the portable, data-preserving form of that rebuild. ### Altering Schema Source: https://academia.sh/en/courses/sql-fundamentals/altering-schema Adding, renaming, and dropping columns with ALTER TABLE; the engine-dependent limit on what a single statement can change; and the portable procedure for rebuilding a table. The previous lesson wrote the schema together with its constraints. A real schema does not stay in the shape it was first written: members need a phone field, a column's name turns out to have been chosen badly, a constraint gets added to the email field after the fact — and these changes happen while the table already holds data, not while it is empty. The `ALTER TABLE` statement writes these changes. The statement itself is standard, but which change it can make in a single statement differs by engine — one of SQL's weakest spots for portability. This lesson shows the statement and its limit, then sets up a way past that limit that works on every engine. #### Adding and Renaming Columns The two most commonly needed changes are also the cheapest: adding a column at the end, and changing a column's name. Both can be done without touching the content of existing rows. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL ); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (3,'Clara','Diaz',NULL,'2024-01-09'); ALTER TABLE member ADD COLUMN phone TEXT; ALTER TABLE member ADD COLUMN notification TEXT NOT NULL DEFAULT 'email'; ALTER TABLE member RENAME COLUMN registered_at TO membership_date; SELECT * FROM member; SQL ``` ``` member_id first_name last_name email membership_date phone notification --------- ---------- --------- ------------------ --------------- ----- ------------ 1 Alice Kane alice@example.test 2023-02-14 email 3 Clara Diaz 2024-01-09 email ``` Both existing rows picked up the new columns. The `phone` column has no value, because neither a constraint nor a default was written for it: old rows carry a null. The `notification` column, on the other hand, has a `NOT NULL` constraint, so a blank cannot be accepted — the engine filled the old rows with the default value. When adding a column, one of these two paths has to be chosen: either the column is allowed to stay blank, or what old rows get is written explicitly. `RENAME COLUMN` changes only the name; it does not touch the data or the constraints. This looks harmless but has a wide-reaching effect: every query, every view, and every piece of application code that uses the column's old name breaks. Fixing a naming mistake is cheap when it is caught early; it is not cheap months later. Each block in this lesson runs on its own and sets up only the columns the member table needs for that particular example; the course's actual schema is the definition written in the previous lesson. The rename above is not carried into that definition either — it is there only to show its effect. In the lessons that follow, the column keeps the name `registered_at`. #### The Limits of Adding Adding a column is not always cheap. It is cheap because the engine does not have to touch existing rows. There are three situations where that condition breaks down, and the engine rejects all three. ```sh sqlite3 :memory: <<'SQL' CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL ); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'); ALTER TABLE member ADD COLUMN identity_no TEXT NOT NULL; ALTER TABLE member ADD COLUMN card_no TEXT UNIQUE; ALTER TABLE member ADD COLUMN last_visit TEXT DEFAULT (date('now')); SQL ``` ``` Runtime error near line 10: Cannot add a NOT NULL column with default value NULL Parse error near line 11: Cannot add a UNIQUE column Runtime error near line 12: Cannot add a column with non-constant default ``` The three rejections come with three separate reasons. The first is a mandatory column with no default: what an existing row would hold in that column is undefined, and it cannot be left blank either. The second is a uniqueness constraint: since the engine cannot establish uniqueness without reindexing the existing rows, it refuses a `UNIQUE` column added with `ADD COLUMN` regardless of the row count — even on an empty table. The third is a non-constant default: a default whose value is computed separately for each row leaves what gets written to old rows undefined. The wording of these three messages is engine-specific. The rule that carries over is this: **an addition that requires existing rows to be rewritten cannot be done in a single statement.** The column is first added in a form that can stay blank, the rows are filled in, and the constraint is set up afterward. #### Dropping a Column Dropping a column runs into a similar limit. The operation itself is well defined, but it is rejected if the column being dropped is part of a constraint or an index. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL, phone TEXT ); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14','0312 000 00 00'); ALTER TABLE member DROP COLUMN phone; SELECT * FROM member; ALTER TABLE member DROP COLUMN email; SQL ``` ``` Parse error near line 15: cannot drop UNIQUE column: "email" member_id first_name last_name email registered_at --------- ---------- --------- ------------------ ------------- 1 Alice Kane alice@example.test 2023-02-14 ``` The unconstrained `phone` column came off; `email`, which carries a unique constraint, did not. Dropping a column is also irreversible: every value it held goes with it. On a production table, the work that comes first is to stop using the column and wait; dropping it is the last step. #### Renaming a Table When a table's name changes, what happens to the foreign keys referencing it is a separate question. The engine used here tracks and updates those references. ```sh sqlite3 :memory: <<'SQL' PRAGMA foreign_keys = ON; CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, branch_id INTEGER REFERENCES branch(branch_id) ); ALTER TABLE branch RENAME TO branches; .schema book SQL ``` ``` CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, branch_id INTEGER REFERENCES "branches"(branch_id) ); ``` The definition of the `book` table changed even though it was never touched directly. This behavior differs by engine: some track the reference, others keep holding the old name and leave the schema inconsistent. Before renaming a table, it has to be worked out who references it. #### Rebuilding a Table Changing a column's type, adding a constraint to an existing column, removing a constraint, or fixing the order of columns — none of these can be done in a single statement on the engine used here. Some engines can do part of this, but even where it is possible, the operation rewrites rows if the table is large. The path that works on every engine is rebuilding the table, in four steps: create a new table with the wanted definition, copy the data over, drop the old table, and give the new table the old name. All four happen inside a single transaction, so a step left half-done does not leave the schema broken. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = OFF; CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')), notification TEXT NOT NULL DEFAULT 'email' ); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (3,'Clara','Diaz',NULL,'2024-01-09'); BEGIN; CREATE TABLE member_new ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE CHECK (email IS NULL OR email LIKE '%@%'), registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')), notification TEXT NOT NULL DEFAULT 'email' CHECK (notification IN ('email', 'sms', 'none')) ); INSERT INTO member_new (member_id, first_name, last_name, email, registered_at, status, notification) SELECT member_id, first_name, last_name, email, registered_at, status, notification FROM member; DROP TABLE member; ALTER TABLE member_new RENAME TO member; COMMIT; PRAGMA foreign_key_check; SELECT * FROM member; SQL ``` ``` member_id first_name last_name email registered_at status notification --------- ---------- --------- ------------------ ------------- ------ ------------ 1 Alice Kane alice@example.test 2023-02-14 active email 3 Clara Diaz 2024-01-09 active email ``` Neither of the two check conditions in the new definition could have been added with `ALTER TABLE`, because both attach to existing columns: one to `email`, the other to the `notification` column added at the start of the lesson. That column could be given a default when it was added, but not a constraint; the constraint only enters the definition here, while the table is being rebuilt. The data was preserved, and both rows fit the new definition — including the row with a blank email, because the condition handles the blank separately. The copy step has a side benefit: because the `SELECT` list is written out, columns can be transformed. If a date column is being converted from text to a number, the conversion happens here; if an old column is being dropped, it is left out of the list entirely. During the copy, the new definition's constraints are enforced. If the old data violates the new constraint, the statement fails and the transaction rolls back — which is exactly what is wanted. A schema change is planned together with the work of bringing the data in line with the constraint. The `PRAGMA foreign_key_check` call printed nothing: there is no broken reference. This call is the last step of the procedure, and it should not be skipped. #### The Risk in Rebuilding Foreign key checking is turned off for the whole procedure, because between the `DROP TABLE` step and the `RENAME TO` step the target table is temporarily absent. Having the check off means a mistake made during the copy passes silently. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = OFF; CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, branch_id INTEGER NOT NULL REFERENCES branch(branch_id) ); INSERT INTO branch VALUES (1,'Central','Ankara'),(3,'Kadikoy','Istanbul'); INSERT INTO book VALUES (1,'Blindness',1),(5,'Silent House',3); BEGIN; CREATE TABLE branch_new ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, is_open INTEGER NOT NULL DEFAULT 1 ); INSERT INTO branch_new (branch_id, name, city) SELECT branch_id, name, city FROM branch WHERE city = 'Ankara'; DROP TABLE branch; ALTER TABLE branch_new RENAME TO branch; COMMIT; PRAGMA foreign_key_check; SQL ``` ``` table rowid parent fkid ----- ----- ------ ---- book 5 branch 0 ``` Because of the condition in the copy statement, one branch was not carried over, and the book tied to it was left orphaned. The transaction committed successfully, with no error raised; only the check call revealed the break. This last step of the procedure is the only warning against silent data loss. In practice, three habits cut down this risk. The rebuild procedure is tried first on a copy of the production data. Row counts are compared after the copy statement. And the procedure is run inside a transaction, so that if one of the steps fails, the schema returns to its previous state. #### Summary - `ALTER TABLE` adds, renames, and drops columns in a single statement; which of these changes is supported differs by engine. - Additions that would require existing rows to be rewritten are rejected: a mandatory column with no default, a unique column, and a non-constant default. - Dropping a column is irreversible and is rejected for columns that are part of a constraint; the safe order is to stop using it first and drop it afterward. - For changes that cannot be made in a single statement, the portable path is to rebuild the table: new table, copy, drop, rename — all inside a single transaction. - Because foreign key checking is off during a rebuild, the last step of the procedure is testing referential integrity. #### Next Step The schema is set up and can be changed. The next question is how data goes into it. The next lesson takes up inserting rows: single-row inserts, multiple rows in a single statement, writing a query's result straight into a table, and the point where these diverge — performance. Whether twenty thousand rows are inserted one at a time or inside a single transaction is a measurable difference. ### Inserting Rows Source: https://academia.sh/en/courses/sql-fundamentals/inserting-rows The column-list and positional forms of the INSERT statement, multiple rows in a single statement, writing a query's result into a table, conflict behavior, and the measured effect of batching inserts inside a transaction. The schema is set up and can be changed. The next question is how data goes into it. The `INSERT` statement looks like the plainest statement in the course — a table name, a list of values — but it splits in two places: how values bind to columns, and how many transactions the insert happens inside. The second of these can produce a difference of hundreds of times between two scripts that write the same data. #### Inserting a Single Row The statement has two forms. In the column-list form, which value goes to which column is written directly into the statement. In the positional form, the column list is left out and values are matched by the table's column order. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' ); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'); INSERT INTO member VALUES (2,'Ben','Ortiz','ben@example.test','2023-05-30','suspended'); INSERT INTO member VALUES (3,'Clara','Diaz',NULL,'2024-01-09'); SELECT * FROM member; SQL ``` ``` Parse error near line 15: table member has 6 columns but 5 values were supplied member_id first_name last_name email registered_at status --------- ---------- --------- ------------------ ------------- --------- 1 Alice Kane alice@example.test 2023-02-14 active 2 Ben Ortiz ben@example.test 2023-05-30 suspended ``` The first statement did not write to the `status` column and got the default value. The second gave every column in order. The third used the positional form but sent five values to a table with six columns, and was rejected. In this example the column list looks like nothing more than a style choice; for maintainability, the two are not equal. When a column is added, or the column order changes, the positional form either errors out or — worse — writes values to the wrong columns because the types happen to match. The `ALTER TABLE ADD COLUMN` statement from the previous lesson is exactly the kind of change that does this. If the column list is written out, a schema change does not affect the statement. In code meant to last, insert statements are written with a column list; the positional form stays for quick experiments on the command line. #### Multiple Rows in a Single Statement More than one row, separated by commas, can be written after the `VALUES` keyword. The engine processes this as a single statement. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); INSERT INTO branch (branch_id, name, city) VALUES (1, 'Central', 'Ankara'), (2, 'Bahcelievler', 'Ankara'), (3, 'Kadikoy', 'Istanbul'); SELECT count(*) AS inserted FROM branch; SQL ``` ``` inserted -------- 3 ``` Being a single statement does not just shorten the syntax, it also determines the behavior: the statement is not split apart. If one of the rows violates a constraint, none of them is written. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); INSERT INTO branch VALUES (1,'Central','Ankara'); INSERT INTO branch (branch_id, name, city) VALUES (3, 'Kadikoy', 'Istanbul'), (1, 'Central', 'Ankara'), (4, 'Konak', 'Izmir'); SELECT * FROM branch ORDER BY branch_id; SQL ``` ``` Runtime error near line 6: UNIQUE constraint failed: branch.branch_id (19) branch_id name city --------- ------- ------ 1 Central Ankara ``` The conflicting row was second in the list; even so, neither the Kadikoy row before it nor the Konak row after it made it into the table. A statement is applied either in full or not at all — this is the statement-level face of the property called atomicity in the Relational Theory course. #### Inserting a Query's Result A `SELECT` can be written in place of `VALUES`. This form moves data from one table to another without passing it through the application; the read and the write happen in a single statement. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'), (2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL), (4,3,3,'2025-03-01','2025-03-15'); CREATE TABLE loan_archive ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, day_count INTEGER NOT NULL ); INSERT INTO loan_archive (loan_id, book_id, member_id, day_count) SELECT loan_id, book_id, member_id, CAST(julianday(return_date) - julianday(pickup_date) AS INTEGER) FROM loan WHERE return_date IS NOT NULL; SELECT changes() AS transferred; SELECT * FROM loan_archive; SQL ``` ``` transferred ----------- 3 loan_id book_id member_id day_count ------- ------- --------- --------- 1 1 1 14 2 2 1 18 4 3 3 14 ``` The expressions in the `SELECT` list are matched to the target columns by position; the names do not need to overlap, but the count and types of columns must match. The query's `WHERE` condition decides which rows get transferred: the loan that had not been returned was left out. The `changes()` call gives the number of rows the last statement affected. Its name differs by engine, but every engine has a counterpart, and it is used in bulk operations to confirm the expected count. `julianday` is a date function that computes the difference in days, and `CAST` converts the result to an integer. #### Reading Back an Inserted Row When the engine generates the primary key, the key of an inserted row is not known after the insert. Many engines allow an output list to be attached to the insert statement for exactly this. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' ); INSERT INTO member (first_name, last_name, registered_at) VALUES ('Derek','Voss','2024-03-22') RETURNING member_id, first_name, status; INSERT INTO member (first_name, last_name, registered_at) VALUES ('Grace','Kim','2024-11-05') RETURNING member_id, first_name, status; SQL ``` ``` member_id first_name status --------- ---------- ------ 1 Derek active member_id first_name status --------- ---------- ------ 2 Grace active ``` The key column was never written; the engine generated it and gave it back in the statement's output together with the default status. The `RETURNING` syntax differs by engine: some support it, others offer a separate function that returns the last generated key. When the key is needed right after an insert, the first question to ask is which way the engine in use provides it. #### What Happens on Conflict The default behavior when an insert uses a key that already exists is an error. In some jobs, what is wanted instead is "update if it exists, insert if it does not." ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); INSERT INTO branch VALUES (1,'Central','Ankara'); INSERT INTO branch (branch_id, name, city) VALUES (1, 'Central Branch', 'Ankara') ON CONFLICT (branch_id) DO UPDATE SET name = excluded.name; INSERT INTO branch (branch_id, name, city) VALUES (1, 'Will Be Ignored', 'Ankara') ON CONFLICT (branch_id) DO NOTHING; SELECT * FROM branch; SQL ``` ``` branch_id name city --------- -------------- ------ 1 Central Branch Ankara ``` The first statement caught the conflict and updated the name; the second silently ignored the conflict. `excluded` names the row that was being inserted but could not be written because of the conflict. The name and detail of this syntax differ by engine; some do the same job with a different keyword. What does not change is that writing the conflict behavior into the statement is safer than writing "query first, then insert" in application code: in the time between the query and the insert, another session can insert the same row. #### Batching Inserts Inside a Transaction How many transactions the insert statements run inside is what separates two scripts writing the same data. If no explicit transaction is started, the engine treats every statement as its own transaction and syncs to disk at the end of each one to guarantee durability. Twenty thousand statements means twenty thousand disk syncs. ```sh cd "$(mktemp -d)" generate_rows() { awk 'BEGIN { for (i = 1; i <= 20000; i++) printf "INSERT INTO loan_load VALUES (%d, %d, %d, \0472025-03-01\047);\n", i, 1 + i % 7, 1 + i % 6 }' } schema="CREATE TABLE loan_load (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL);" { echo "$schema"; generate_rows; } > per_statement.sql { echo "$schema"; echo "BEGIN;"; generate_rows; echo "COMMIT;"; } > single_transaction.sql echo "--- each statement its own transaction ---" time sqlite3 a.db ".read per_statement.sql" echo "--- all in a single transaction ---" time sqlite3 b.db ".read single_transaction.sql" sqlite3 a.db "SELECT count(*) FROM loan_load;" sqlite3 b.db "SELECT count(*) FROM loan_load;" ``` ``` --- each statement its own transaction --- real 0m3.758s user 0m0.148s sys 0m2.785s --- all in a single transaction --- real 0m0.025s user 0m0.021s sys 0m0.002s 20000 20000 ``` Same twenty thousand rows, same statements, same result. The difference between them is hundreds of times over. The measurement depends on the disk and the machine's characteristics — the numbers change on every run and every machine — but the size of the ratio shows how decisive the transaction boundary is for performance. The `\047` sequence inside `awk` produces a single-quote character; it lets a SQL string be written without colliding with the shell's own quoting. The same effect can also be reached by writing multiple rows in a single statement: a thousand-row `VALUES` list is already a single transaction. The two techniques are used together — batched statements, inside an explicit transaction. Keeping the transaction boundary too wide also has a cost, and that subject belongs to the locking lesson in the Advanced SQL course. #### Summary - The column-list form of the insert statement holds up against schema changes; the positional form can silently write to the wrong column when the column order changes. - Multiple rows written in a single statement are not split apart: if one of the rows runs into a constraint, none of them is written. - The `INSERT ... SELECT` form transfers data between tables without passing it through the application; the mapping is done by position, not by column name. - There is a way to read back an inserted row's engine-generated values and to specify conflict behavior in the statement, but the syntax differs by engine. - Batching inserts inside an explicit transaction is measurably faster than treating every statement as its own transaction; the difference comes from the number of disk syncs. #### Next Step The data is in place. The next lesson takes up changing and deleting it. The syntax of `UPDATE` and `DELETE` is short, and the danger comes from exactly that: the condition clause is optional. An update that forgets its condition is syntactically flawless and changes every row in the table. That lesson's core is a habit to run while writing these statements — open a transaction first, see the number of rows affected, and roll back if needed. ### Updating and Deleting Source: https://academia.sh/en/courses/sql-fundamentals/updating-and-deleting The structure of UPDATE and DELETE statements, what happens when the condition clause is forgotten, reading the number of affected rows, the habit of opening a transaction and rolling back, and how deletion affects related rows. Data is in the table. The next two statements change it and delete it. Their syntax is among the shortest in the course, and that is exactly where the danger comes from. The `WHERE` clause is optional — meaning an update that forgot its condition is syntactically flawless, is accepted by the engine without a warning, and changes every row in the table. Every example in this lesson runs against the `:memory:` database: a temporary database built in memory that disappears when the command ends. The way to see what a destructive statement does is to run it, but the place it runs is not real data. #### Conditional Update The `UPDATE` statement has three parts: which table, which columns get which values, and which rows. If the third part is left out, the answer to "which rows" becomes "all of them." ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')) ); INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'), (3,'Clara','Diaz','suspended'),(4,'Derek','Voss','active'); SELECT member_id, first_name, last_name FROM member WHERE member_id = 2; UPDATE member SET status = 'suspended' WHERE member_id = 2; SELECT changes() AS affected; SELECT * FROM member; SQL ``` ``` member_id first_name last_name --------- ---------- --------- 2 Ben Ortiz affected -------- 1 member_id first_name last_name status --------- ---------- --------- --------- 1 Alice Kane active 2 Ben Ortiz suspended 3 Clara Diaz suspended 4 Derek Voss active ``` Two habits are built into this block. The first is running a `SELECT` with the same condition before the update: which rows will be affected gets seen first. The second is reading the number of affected rows after the update. If the expected number is known — here, one — a deviation is caught immediately. An update does not skip constraints. An update that writes a value to the `status` column that is not on the list produces the same `CHECK` error as an insert would. Constraints in the schema hold across every write path. #### Forgetting the Condition The block below shows what an update with no condition does. The statement itself is identical to the previous one, only the `WHERE` clause is missing. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')) ); INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'), (3,'Clara','Diaz','suspended'),(4,'Derek','Voss','active'); UPDATE member SET status = 'suspended'; SELECT changes() AS affected; SELECT * FROM member; SQL ``` ``` affected -------- 4 member_id first_name last_name status --------- ---------- --------- --------- 1 Alice Kane suspended 2 Ben Ortiz suspended 3 Clara Diaz suspended 4 Derek Voss suspended ``` Four rows. No error, no warning, no confirmation prompt. Every member of the library was suspended, and the only thing that says so is the number in the `changes()` output. The scale of the damage is proportional to the size of the table. In a four-row example, the result is seen in a second; on a member table with four hundred thousand rows, it turns into the question of where the old values are kept. The old values are not kept anywhere — the update happens in place. The way back is restoring from a backup, and that only goes as far back as the moment the backup was taken. The same holds for deletion, and there it cuts even sharper: an unconditional `DELETE` removes every row in the table. #### A Reversible Habit The answer to this risk is not care, it is a procedure. The write statement runs inside an explicit transaction; the number of affected rows is read; if the number matches expectations, the transaction is committed, and if not, it is rolled back. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' ); INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'), (3,'Clara','Diaz','suspended'),(4,'Derek','Voss','active'); BEGIN; UPDATE member SET status = 'suspended'; SELECT changes() AS affected; ROLLBACK; SELECT * FROM member; SQL ``` ``` affected -------- 4 member_id first_name last_name status --------- ---------- --------- --------- 1 Alice Kane active 2 Ben Ortiz active 3 Clara Diaz suspended 4 Derek Voss active ``` The same unconditional update ran, affected the same four rows, and then the `ROLLBACK` statement undid all of it. The table is in its pre-transaction state: the third member suspended, the rest active. A transaction that begins with `BEGIN` is not durable until a `COMMIT` is seen. This should be the default frame for every write statement written by hand. `COMMIT` is written if the statement is correct, `ROLLBACK` if the number is surprising — and the chance to see a surprising number exists only while the transaction is still open. The detail of transactions — isolation levels, locking, partial rollback — is the subject of the Advanced SQL course. All that is needed here is the frame. #### Taking the Condition From a Query The rows to update can be determined by a query's result. This makes it possible to write a rule like "suspend members with an overdue, unreturned loan" in a single statement. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active'); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'), (3,'Clara','Diaz','active'); INSERT INTO loan VALUES (1, 1, 1, '2024-11-01', NULL), (2, 2, 2, '2025-06-01', NULL), (3, 3, 3, '2024-12-15', '2025-01-02'); BEGIN; UPDATE member SET status = 'suspended' WHERE member_id IN (SELECT member_id FROM loan WHERE return_date IS NULL AND pickup_date < '2025-01-01'); SELECT changes() AS affected; COMMIT; SELECT * FROM member; SQL ``` ``` affected -------- 1 member_id first_name last_name status --------- ---------- --------- --------- 1 Alice Kane suspended 2 Ben Ortiz active 3 Clara Diaz active ``` Only the first member was suspended: the second has an open loan but a recent date, the third's loan is old but has been returned. The subquery applied both conditions together. A side benefit of this syntax is that it can be tested: the subquery can be run on its own as a `SELECT` before the update, and which ids come back can be seen. A detailed treatment of subqueries belongs to the Advanced SQL course; here their use is limited to writing conditions. #### Deletion and Related Rows The structure of the `DELETE` statement is even shorter: which table and which rows. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO loan VALUES (1,1,1,'2023-01-10','2023-01-24'), (2,2,1,'2023-02-02','2023-02-20'), (3,1,2,'2025-02-11',NULL), (4,3,3,'2025-03-01','2025-03-15'); DELETE FROM loan WHERE return_date IS NOT NULL AND return_date < '2024-01-01'; SELECT changes() AS deleted; DELETE FROM loan; SELECT changes() AS deleted; SELECT count(*) AS remaining FROM loan; SQL ``` ``` deleted ------- 2 deleted ------- 2 remaining --------- 0 ``` The second statement had no condition, and the table emptied out. This is why delete statements are written with an even stricter procedure than updates: the condition is counted first with `SELECT count(*)`, then the delete runs inside a transaction. Deleting a row also concerns the rows tied to it. The behavior attached to the foreign key definition decides what the engine does. Under restrictive behavior, the delete is rejected. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = ON; CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL REFERENCES member(member_id) ON DELETE RESTRICT, pickup_date TEXT NOT NULL ); INSERT INTO member VALUES (1,'Alice','Kane'),(2,'Ben','Ortiz'); INSERT INTO loan VALUES (1,1,1,'2025-02-11'); DELETE FROM member WHERE member_id = 1; DELETE FROM member WHERE member_id = 2; SELECT changes() AS deleted; SELECT * FROM member; SQL ``` ``` Runtime error near line 14: FOREIGN KEY constraint failed (19) deleted ------- 1 member_id first_name last_name --------- ---------- --------- 1 Alice Kane ``` The member with a loan could not be deleted; the one without was. Under cascading behavior, related rows are removed too. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = ON; CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL REFERENCES member(member_id) ON DELETE CASCADE, pickup_date TEXT NOT NULL ); INSERT INTO member VALUES (1,'Alice','Kane'),(2,'Ben','Ortiz'); INSERT INTO loan VALUES (1,1,1,'2025-01-10'),(2,2,1,'2025-02-02'),(3,1,2,'2025-02-11'); DELETE FROM member WHERE member_id = 1; SELECT changes() AS deleted_members; SELECT * FROM loan; SQL ``` ``` deleted_members --------------- 1 loan_id book_id member_id pickup_date ------- ------- --------- ----------- 3 1 2 2025-02-11 ``` A single member was deleted, and two loan records went with it. The affected-row count still reads `1`: the counter looks at the table the statement directly targets, and does not count cascading deletes. Choosing cascading behavior means accepting that loan history disappears along with the member. In domains where history has value, the way library records do, marking a row closed — giving the member a `closed` status — is a better design than deleting the row. #### Summary - In `UPDATE` and `DELETE` statements the condition clause is optional; when it is left out, the statement affects every row in the table and the engine gives no warning. - The number of affected rows is read after every write statement; a deviation from the expected number is the only early sign of a wrong condition. - Write statements written by hand run inside a transaction: they are committed if the number is correct and rolled back if it is not. - The rows to update can be determined by a subquery; the subquery is tested by running it on its own before the statement. - The effect of a delete on related rows is written into the foreign key definition; because a cascading delete takes the history with it, closing a record is preferred in domains where records have value. #### Next Step The statements so far have run directly on tables. The next lesson inserts a layer in between: views, which name a query and get used like a table. A view does not duplicate data, it stores the query — and that raises an interesting question: can a row be inserted into a view, can it be updated. The answer depends on how the view is defined and on the engine; part of that lesson shows this by trying it. ### Views Source: https://academia.sh/en/courses/sql-fundamentals/views The view as a named query, its two jobs of hiding complexity and narrowing access, the conditions for a view to be updatable, and how writing through a view differs by engine. All the statements so far have run directly on tables. This lesson's subject is a layer that gets inserted in between: naming a query itself and using that name like a table. In a library database the same query gets written over and over — open loans, suspended members, the count of books per branch. Every time it is written, it gets rethought, each copy can carry a separate mistake, and when the schema changes each copy has to be updated on its own. The **view** is the construct that removes this repetition. #### A View Is a Named Query The `CREATE VIEW` statement gives a query a name. When the name is used, the engine runs the stored query. A view holds no data, which is why it is also called a **virtual table**. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active'); INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','suspended'), (3,'Clara','Diaz','active'); CREATE VIEW active_member AS SELECT member_id, first_name, last_name FROM member WHERE status = 'active'; SELECT * FROM active_member; UPDATE member SET status = 'active' WHERE member_id = 2; SELECT * FROM active_member; SQL ``` ``` member_id first_name last_name --------- ---------- --------- 1 Alice Kane 3 Clara Diaz member_id first_name last_name --------- ---------- --------- 1 Alice Kane 2 Ben Ortiz 3 Clara Diaz ``` The view's definition never changed in between; only the status of one row in the base table changed, and the view reflected that change immediately. This is the fundamental difference between a view and a copied table: a copy freezes a state at one moment, a view stores the query itself. A view behaves like a table when queried. `WHERE`, `ORDER BY`, joins, even use inside another view — all of it works. For whoever is writing the query, there is no difference between a view and a table. #### A View's Two Jobs Views satisfy two separate needs. The first is hiding complexity: a query that joins several tables, groups rows, and uses aggregate functions gets written once, then referred to by a single name. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO member VALUES (1,'Alice','Kane'),(2,'Ben','Ortiz'),(3,'Clara','Diaz'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'), (2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL), (9,7,1,'2025-05-14',NULL); CREATE VIEW member_summary AS SELECT u.member_id, u.first_name, u.last_name, count(o.loan_id) AS total_loans, sum(CASE WHEN o.loan_id IS NOT NULL AND o.return_date IS NULL THEN 1 ELSE 0 END) AS open_loans FROM member AS u LEFT JOIN loan AS o ON o.member_id = u.member_id GROUP BY u.member_id, u.first_name, u.last_name; SELECT * FROM member_summary ORDER BY member_id; SELECT first_name, last_name FROM member_summary WHERE open_loans > 0 ORDER BY member_id; SQL ``` ``` member_id first_name last_name total_loans open_loans --------- ---------- --------- ----------- ---------- 1 Alice Kane 3 1 2 Ben Ortiz 1 1 3 Clara Diaz 0 0 first_name last_name ---------- --------- Alice Kane Ben Ortiz ``` The second query wrote a single condition instead of writing an outer join and a grouping. A `WHERE` condition written on top of a view gets applied to the stored query's result. The `o.loan_id IS NOT NULL` part of the aggregate condition is a consequence of the outer join: for a member with no loans at all, the join produces a row filled with nulls, and that row would count as an open loan if the condition only looked at the blank return date. A view is also a way to write this subtlety correctly once and keep it stored. A view's second job is narrowing access. Whoever queries the `active_member` view never sees the `status` column on the member table. What gets shown at the column and row level is decided in the view's definition; the reader is given the view instead of the base table. This is the most commonly used tool of the authorization that is the next lesson's subject. #### Writing to a View If a view can be queried like a table, can it be written to like one. The answer to this question depends on both the view's definition and the engine. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active'); INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','suspended'); CREATE VIEW active_member AS SELECT member_id, first_name, last_name FROM member WHERE status = 'active'; INSERT INTO active_member (member_id, first_name, last_name) VALUES (3,'Clara','Diaz'); UPDATE active_member SET last_name = 'Kane Ortiz' WHERE member_id = 1; DELETE FROM active_member WHERE member_id = 1; SELECT * FROM active_member; SQL ``` ``` Parse error near line 9: cannot modify active_member because it is a view Parse error near line 10: cannot modify active_member because it is a view Parse error near line 11: cannot modify active_member because it is a view member_id first_name last_name --------- ---------- --------- 1 Alice Kane ``` Views are read-only in the engine used here; all three write attempts were rejected for the same reason. This is a decision that differs by engine — many engines allow direct writes to views that satisfy certain conditions. Standard SQL defines these conditions under the heading of **updatable view**. Their common core is this: every row of the view must be traceable back to a single row in the base table. Constructs that break this make the view read-only: - Joining more than one table — when a row comes from two tables, which one an update should write to is undefined. - Grouping and aggregate functions — a row in the result summarizes more than one base row. - Discarding duplicates and set operations — the correspondence between rows is lost. - Columns produced by an expression in the result — how a value written to a computed column would distribute across its inputs is undefined. What is left — a view selected from a single table, not grouped, with no duplicates discarded — is updatable as a rule. The `active_member` view above fits this definition and was rejected anyway: meeting the condition does not mean the engine supports it. #### Building a Writable View On engines that do not support writing directly, what a write means gets defined explicitly. A trigger set up on the view stands in for the statement and writes to the base table. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active'); INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','suspended'); CREATE VIEW active_member AS SELECT member_id, first_name, last_name FROM member WHERE status = 'active'; CREATE TRIGGER active_member_insert INSTEAD OF INSERT ON active_member BEGIN INSERT INTO member (member_id, first_name, last_name, status) VALUES (NEW.member_id, NEW.first_name, NEW.last_name, 'active'); END; INSERT INTO active_member (member_id, first_name, last_name) VALUES (3,'Clara','Diaz'); SELECT * FROM member; SQL ``` ``` member_id first_name last_name status --------- ---------- --------- --------- 1 Alice Kane active 2 Ben Ortiz suspended 3 Clara Diaz active ``` The `INSTEAD OF` syntax does exactly what its name says: the insert statement is not applied to the view; instead, the statements in the body run. `NEW` names the row being inserted. The trigger filled in the `status` column, which is not part of the view — this is where the meaning of writing to the view got defined. The detail of triggers, including the problems their implicit side effects create, belongs to the Advanced SQL course. What this lesson shows is that this is the non-standard way of making a view writable. #### A Row That Disappears From the View A writable view has a trap of its own. If the view filters rows with a condition, a row that does not satisfy that condition can still be inserted through the view — and once inserted, it does not show up in the view. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, branch_id INTEGER NOT NULL ); INSERT INTO book VALUES (1,'Blindness','José Saramago',1); CREATE VIEW central_book AS SELECT book_id, title, author, branch_id FROM book WHERE branch_id = 1; CREATE TRIGGER central_book_insert INSTEAD OF INSERT ON central_book BEGIN INSERT INTO book (book_id, title, author, branch_id) VALUES (NEW.book_id, NEW.title, NEW.author, NEW.branch_id); END; INSERT INTO central_book VALUES (5,'Silent House','Orhan Pamuk',3); SELECT * FROM central_book; SELECT * FROM book; SQL ``` ``` book_id title author branch_id ------- --------- ------------- --------- 1 Blindness José Saramago 1 book_id title author branch_id ------- ------------ ------------- --------- 1 Blindness José Saramago 1 5 Silent House Orhan Pamuk 3 ``` The insert succeeded, the row was written to the base table — but it is missing from the view that shows the central branch's books. Whoever is using the view will not see the row they just inserted again. Standard SQL defines an option for the view definition to cover this case: a row written through the view is required to satisfy the view's condition. This option is not universal either. ```sh sqlite3 :memory: <<'SQL' CREATE TABLE member (member_id INTEGER PRIMARY KEY, status TEXT NOT NULL); CREATE VIEW active_member AS SELECT member_id FROM member WHERE status = 'active' WITH CHECK OPTION; SQL ``` ``` Parse error near line 2: near "WITH": syntax error ECT member_id FROM member WHERE status = 'active' WITH CHECK OPTION; error here ---^ ``` A clause written into the standard could not even be parsed by the engine in use. Knowing that this behavior differs by engine is a requirement for writing a portable schema: when the view's condition needs to be preserved and the engine does not recognize this option, the check gets written by hand into the trigger's body. #### Summary - A view holds no data, it stores the query; it reflects every change to the base table immediately and behaves like a table when queried. - A view has two jobs: putting join and grouping complexity behind a single name, and showing the reader only a portion of the base table. - For a view to be updatable, every one of its rows must be traceable back to a single base row; joining, grouping, discarding duplicates, and computed columns break this. - Whether a view that meets the conditions is actually writable depends on the engine; where it is not supported, the meaning of a write is defined explicitly with an `INSTEAD OF` trigger. - A row that does not satisfy the condition can be written to a filtering view, and that row will not show up in it; the standard's option to prevent this is not available on every engine. #### Next Step A view's second job — giving the reader a narrowed window instead of the base table — is a thought left unfinished: even when the view is offered, the base table itself is still there. A separate mechanism is needed to say who can see it. The course's last lesson takes up object-level privileges: who a privilege is granted to, on which object, for which operation; revoking that privilege; and counting whether the privileges granted go beyond what the job requires. ### Granting and Revoking Privileges Source: https://academia.sh/en/courses/sql-fundamentals/granting-and-revoking The standard SQL syntax for object-level privileges, the grantee-object-privilege triple, the grant option, and the concept of a role; measuring the principle of least privilege against a privilege matrix. The previous lesson left a view's second job unfinished. A view can show the reader only a portion of the base table — but being able to show that does not mean the base table is hidden. Whoever queries the view can query the base table on the same connection too, if they choose to. What is missing is a separate mechanism that says who can see what. The fourth of the five families separated out in the SQL Language Families lesson — data control language — writes exactly this. Its statements are short, but its concepts carry as many consequences as schema design: a portion of the most expensive mistakes in a system come out of privileges granted more broadly than needed. #### The Parts of the Privilege Model Object-level authorization ties three things together. The **grantee** is the party that receives the privilege: a user account, a role, or a generic name meaning "everyone." The **object** is the thing the privilege is defined on: a table, a view, a column, a schema, or the database itself. The **privilege** names the operation that can be performed on that object: reading, inserting, updating, deleting, referencing, changing the definition. Every combination of this triple is a separate privilege. A grantee with read access on a table does not have write access on the same table; that has to be granted separately. The standard answer to who grants a privilege is the object's owner: the party that creates a table starts out holding every privilege on it, along with the right to grant those privileges to others. #### Granting and Revoking In standard SQL, privileges are granted with `GRANT` and revoked with `REVOKE`. ```sql GRANT SELECT, INSERT, UPDATE ON loan TO clerk; GRANT SELECT ON member_summary TO reporting; GRANT SELECT (member_id, first_name, last_name) ON member TO reporting; GRANT SELECT ON book TO cataloger WITH GRANT OPTION; REVOKE DELETE ON loan FROM clerk; REVOKE ALL PRIVILEGES ON member FROM reporting CASCADE; ``` The statements work exactly as they read: which privileges, on which object, to whom. The object on the second line is not a table but the view defined in the previous lesson — a privilege is granted on views the same way it is on tables. The third line narrows the privilege at the column level: the grantee can query the table but sees only the listed columns, and cannot reach the email address. The **grant option** on the fourth line also gives the grantee the right to grant the privilege it received to others. Revoking has two subtleties. `ALL PRIVILEGES` covers every privilege granted to the grantee on that object. `CASCADE` follows the branching of a privilege granted with the grant option: if the grantee distributed that privilege to others, the revoke removes those distributions too. If this option is left out and branching exists, the standard rejects the revoke — because a branch left standing silently means a privilege that was thought to be revoked is not. The grant option is therefore used sparingly. Who can distribute a privilege is a more important decision than the privilege being distributed itself: every grantee given the transfer right is a single point that can expand the privilege matrix on its own. The concept of a **role** makes the same problem more manageable. Privileges are granted not to individual users but to a role, and users are attached to the role. When a job description changes, a single role's privileges get adjusted; someone new joining the job inherits all its privileges by being attached to the role. Without roles, privileges multiply by the number of users, and nobody can track exactly what anyone can access. It should be stressed that these statements are not found on every engine. Some engines have no concept of a user account; the command-line tool used in this lesson is one of them, and it cannot parse the `GRANT` statement. There, access control is left to filesystem permissions and the application layer. Where authorization happens is one of the questions to ask when choosing an engine. #### Measuring the Principle of Least Privilege The principle of least privilege introduced in the Introduction to Linux course holds here too: no grantee is given more than the privileges its job requires. Compliance with the principle is not claimed, it is **counted**. When the granted privileges and the privileges the job requires are written out as two sets, the difference between them can be computed directly. The script below builds these two sets for the library database, prints the matrix, and counts the differences. It then revokes the excess privileges and prints the matrix again. ```sh cat > privileges.mjs <<'EOF' // Builds an object-level privilege matrix and counts violations of least privilege. const permissions = ["SELECT", "INSERT", "UPDATE", "DELETE"]; const objects = ["member", "book", "loan", "member_summary"]; // Privileges granted with GRANT: "grantee object permission" const granted = new Set([ "clerk member SELECT", "clerk member UPDATE", "clerk book SELECT", "clerk loan SELECT", "clerk loan INSERT", "clerk loan UPDATE", "clerk loan DELETE", "clerk member_summary SELECT", "reporting member SELECT", "reporting book SELECT", "reporting loan SELECT", "reporting loan DELETE", "reporting member_summary SELECT", "cataloger book SELECT", "cataloger book INSERT", "cataloger book UPDATE", ]); // Privileges the job requires. const required = new Set([ "clerk member SELECT", "clerk member UPDATE", "clerk book SELECT", "clerk loan SELECT", "clerk loan INSERT", "clerk loan UPDATE", "clerk member_summary SELECT", "reporting member_summary SELECT", "cataloger book SELECT", "cataloger book INSERT", "cataloger book UPDATE", ]); const grantees = [...new Set([...granted].map((k) => k.split(" ")[0]))]; function report(title, set) { console.log(title); console.log((" " + "grantee".padEnd(11) + "object".padEnd(16) + permissions.map((p) => p.padEnd(8)).join("")).trimEnd()); for (const g of grantees) { for (const o of objects) { const cell = permissions.map((p) => { const v = set.has(`${g} ${o} ${p}`); const r = required.has(`${g} ${o} ${p}`); return (v ? (r ? "ok" : "EXTRA") : r ? "MISSING" : "-").padEnd(8); }); console.log((" " + g.padEnd(11) + o.padEnd(16) + cell.join("")).trimEnd()); } } const excess = [...set].filter((k) => !required.has(k)); const missing = [...required].filter((k) => !set.has(k)); console.log(` excess privileges granted: ${excess.length}`); for (const k of excess) console.log(" " + k); console.log(` missing privileges: ${missing.length}`); return excess; } const excess = report("== before REVOKE ==", granted); const after = new Set([...granted].filter((k) => !excess.includes(k))); report("== after REVOKE ==", after); EOF node privileges.mjs ``` ``` == before REVOKE == grantee object SELECT INSERT UPDATE DELETE clerk member ok - ok - clerk book ok - - - clerk loan ok ok ok EXTRA clerk member_summary ok - - - reporting member EXTRA - - - reporting book EXTRA - - - reporting loan EXTRA - - EXTRA reporting member_summary ok - - - cataloger member - - - - cataloger book ok ok ok - cataloger loan - - - - cataloger member_summary - - - - excess privileges granted: 5 clerk loan DELETE reporting member SELECT reporting book SELECT reporting loan SELECT reporting loan DELETE missing privileges: 0 == after REVOKE == grantee object SELECT INSERT UPDATE DELETE clerk member ok - ok - clerk book ok - - - clerk loan ok ok ok - clerk member_summary ok - - - reporting member - - - - reporting book - - - - reporting loan - - - - reporting member_summary ok - - - cataloger member - - - - cataloger book ok ok ok - cataloger loan - - - - cataloger member_summary - - - - excess privileges granted: 0 missing privileges: 0 ``` Five violations came out, and each can be explained by its own reason. The `clerk loan DELETE` privilege is a consequence of the previous lesson: deleting a loan record destroys history, and a return is handled by updating the record instead. The clerk has no need for delete access. The `reporting` grantee's read access on three base tables is more interesting. The reporting job does need reading — but not on the base tables, on the `member_summary` view. When the privilege is granted on the view, reporting still works, and the email addresses on the member table stop being reachable. The previous lesson's unfinished thought gets completed here: **the view defines what is shown, the privilege makes it binding.** Each one alone is incomplete. The `reporting loan DELETE` privilege cannot be explained by any reason; it is most likely a leftover from copying. As privilege matrices grow by hand, this kind of leftover accumulates and leaves a write path that nobody notices. In the second matrix, after the revoke, both excess and missing privileges are zero: every grantee's privileges equal what its job requires. Computing this number on a regular basis is a different thing from claiming the principle is followed. #### Summary - Object-level authorization ties together the grantee, object, and privilege triple; every combination is a separate privilege and has to be granted on its own. - `GRANT` grants a privilege, `REVOKE` takes it back; a privilege can be narrowed at the column level, widened with the grant option, and a revoke can be written to follow branching. - Roles separate privileges from users; when a job description changes, a single role's privileges get adjusted and access stays trackable. - A view defines what gets shown, a privilege makes it binding; the two are used together to narrow access. - The principle of least privilege is not claimed, it is counted: the difference between granted privileges and the privileges a job requires is a computable quantity. #### Course Wrap-Up This course built SQL's core statements. The Querying topic covered `SELECT` structure, conditions, sorting, working with null values, and built-in functions. The Joins and Aggregation topic brought tables together with inner and outer joins, produced summaries with aggregate functions and grouping, and combined result sets with set operations. This topic moved from reading to writing: defining the schema and protecting it with constraints, altering the schema, inserting rows, the risks of updating and deleting, views, and object-level privileges. One example ran through the entire course — a library database with branches, members, books, and loan transactions. Both queries and write statements were written on the same schema; what a constraint blocks, what an unconditional update does, and what a view does and does not hide were all seen on the same tables. The next course, Advanced SQL, deepens these statements in three directions. Its Compound Queries topic covers subqueries, common table expressions, recursive queries, and window functions — where in this course a subquery was used only to write a condition, there it becomes a construct in its own right. Its Transactions topic opens up the mechanism underneath the `BEGIN`-`ROLLBACK` pair used as a habit in this course: savepoints, isolation levels, locking, and deadlocks. Its Query Performance topic covers indexes, reading a query plan, and the constructs that defeat a plan — alongside the insert difference measured in this course, the read side's cost model gets added.