BiblioRed's seven tables exist and they are empty. This lesson fills them and teaches you how to interrogate them. It is the most hands-on lesson of the module: here you learn the four operations that cover 90% of daily work with a database —insert, query, modify and delete—, known collectively as CRUD (Create, Read, Update, Delete).

We deliberately restrict ourselves to a single table per query. Reassembling information spread across several tables is a topic with enough substance for its own lesson (02-04), and grouping and summarizing, for the one after that (02-05). Here we lay the foundations: without mastering WHERE and ORDER BY on one table, no JOIN will ever come out right.

The lesson's deliverable is BiblioRed's test data set: four branches, eight authors, ten members, nine books, fifteen copies, twelve loans and five reservations. All fictional and all consistent with one another. We will use them until the end of the course, so run them carefully and save them in a file.

1. INSERT: adding rows
2. Deliverable: the BiblioRed data set
3. Resetting the identifier generators
4. SELECT: projection, aliases and DISTINCT
5. WHERE: filtering rows
6. ORDER BY: sorting the result
7. LIMIT and OFFSET: pagination
8. UPDATE: modifying existing rows
9. DELETE: removing rows
10. TRUNCATE: emptying a whole table
11. Common mistakes and tips
12. Exercises
13. Conclusion

Let's start with BiblioRed's branches. The network operates in the fictional city of Vallmar and has four libraries.

PostgreSQL's output reads like this: INSERT <oid> <number of rows inserted>. The first number is a historical relic and is always 0; the second is the one that matters.

Notice three things:

• We did not supply branch_id. The column is GENERATED BY DEFAULT AS IDENTITY, so PostgreSQL assigns it 1.
• The date goes between single quotes, in ISO format YYYY-MM-DD. The manager converts it to DATE because the column is of that type.
• The phone number goes between quotes, even though it looks like a number: it is VARCHAR, as we decided in the previous lesson.

SQL lets you omit the column list if you supply a value for every column, in the exact order of the table definition:

Why avoid it? Because the day somebody adds a column with ALTER TABLE, or reorders the definition, every INSERT without a column list will break or, worse, insert values into the wrong column without raising an error. Always write the column list. It is the first rule of hygiene in professional SQL.

Let's do it properly:

This is the North branch, which from now on will have branch_id = 2.

You can chain several value tuples separated by commas. It is faster (a single operation instead of N) and more readable:

The East branch does not have its own phone number yet: we write NULL without quotes. If we wrote 'NULL' we would be storing the four-letter string N-U-L-L, which is not the same thing at all.

Another option is to omit the column from the list: whatever is not mentioned is left at NULL (or at its default value, if it had one).

When you let the manager generate the key, an immediate practical problem arises: which number did it get? You will need it to insert the child rows. PostgreSQL solves it with RETURNING:

RETURNING works the same way with UPDATE and DELETE, and can return any expression, including *. It is a very convenient PostgreSQL extension that avoids the classic "insert and then query".

SQLite has no RETURNING until version 3.35 (2021); in earlier versions you use the last_insert_rowid() function:

Before accepting the row, the manager verifies the three integrity rules from lesson 02-01:

This is exactly what BiblioRed's spreadsheet did not do. Here the error is not a nuisance: it is the system doing its job.

This is the load script. Run it whole and in this order (children need their parents) and save it as biblioredb_data.sql.

One nuance about the identifiers you will see:

• In branches, authors, copies, loans and reservations we let the manager generate them.
• In members and books we impose them: BiblioRed is migrating from the spreadsheet and wants to keep the card numbers (Marta Alsina has been member 14 since 2021) and the shelfmarks from the old catalog (331 is "The Map of Time"). It is a thoroughly realistic case, and in section 3 we will see the side effect it causes.

(COUNT and UNION ALL belong to lessons 02-05 and 02-04; here we only use it as a control count.)

And the correspondence between codes and copy identifiers, which you will need in order to understand the loans:

If your counts match, you have the same data set as the rest of the course.

Three changes to the script:

• TRUE/FALSE in members.active → 1/0.
• The ALTER TABLE ... RESTART WITH lines from step 0 do not exist in SQLite: remove them. With INTEGER PRIMARY KEY and no AUTOINCREMENT, the next identifier is worked out on its own from the existing maximum.
• Dates are written the same way ('2026-03-02'), but they are stored as text. As long as you use the ISO format, comparisons and sorting will keep working, because the alphabetical order of YYYY-MM-DD matches the chronological one. That is exactly why that format is the right one.

Here comes the promised side effect. In members and books we inserted explicit identifiers, but the column's generator never found out: it still points at 1. Let's check it by signing up a member without stating an identifier:

The new member has been given 1, when BiblioRed's card numbers start at 11. Nothing has failed, but the mismatch is already sown: the next ten sign-ups would take 2, 3… and the eleventh would collide:

It is one of the most baffling errors for a beginner, because it shows up weeks after the load and with no apparent connection to it. The cause is always the same: keys were inserted by hand without resynchronizing the generator. Let's delete the test member and fix it:

The solution in PostgreSQL:

Or, automatically and without having to eyeball the maximum:

In SQLite the problem does not exist if the key was declared as INTEGER PRIMARY KEY without AUTOINCREMENT: the next value is computed as the current maximum plus one, so it adjusts itself.

SELECT is SQL's most used statement and the one that takes up the most lessons in this course. Its minimal form:

This is relational algebra's projection π. Remember that the order in which the rows appear is not guaranteed without ORDER BY: here they come out in insertion order because the table is small and freshly loaded, but do not count on it.

* means "all the columns". It is perfect for exploring in the console, but do not use it in application code: you pull data you do not need over the network, and if tomorrow somebody adds a column, your program receives something it was not expecting. In a saved script, explicit columns.

Notice that the NULL in East's phone appears as an empty cell in psql. You can make it visible:

An alias renames a column in the result, without touching the table. It is relational algebra's rename operator ρ.

Points to remember:

• AS is optional (code label works just as well), but writing it makes the SQL far more readable.
• For an alias to carry spaces, capitals or accents it has to go between double quotes: it is an identifier. Here it is acceptable, because the alias only lives in the output.

You can also compute new columns:

|| is the standard concatenation operator (it works in PostgreSQL and SQLite; MySQL uses CONCAT()).

Beware of NULL in concatenations: 'Pau' || NULL gives NULL, not 'Pau'. If last_name could be null, the full name would disappear entirely. In 02-05 we will see COALESCE, which solves exactly this.

It returns 15 rows with a lot of repetition. With DISTINCT:

DISTINCT applies to the complete combination of the selected columns, not to the first one:

Those are the eight distinct combinations that exist, out of the 15 original rows.

Remember from lesson 02-01: in relational algebra, projection always removes duplicates; in SQL you have to ask for it. DISTINCT forces the manager to sort or to build a hash table, so it has a cost: do not add it "just in case".

WHERE is the algebra's selection σ: it keeps the rows whose condition is TRUE (remember: UNKNOWN does not pass).

Comparisons also work on text (alphabetical order according to the locale) and on dates:

AND binds more tightly than OR, just as multiplication does over addition. This causes silent errors:

It returns 7 rows: the six from branch 1 plus EJ-3081. With parentheses:

Advice: use parentheses whenever you mix AND and OR, even if you know the precedence. Whoever reads your query a year from now will be grateful.

BETWEEN a AND b is syntactic sugar for >= a AND <= b: both endpoints are included. It is the source of a classic error with dates and times: BETWEEN '2026-03-01' AND '2026-03-31' on a TIMESTAMP column leaves out everything that happened on the 31st after midnight, because 2026-03-31 09:00 is greater than 2026-03-31 00:00. With DATE columns like BiblioRed's there is no problem.

IN is equivalent to a chain of ORs, but it is far more readable. There is also NOT IN:

Same result as before.

Important warning about NOT IN and NULL: if the list contains a NULL, NOT IN returns no rows at all, because of the three-valued logic from lesson 02-01. x NOT IN (1, 2, NULL) is equivalent to x <> 1 AND x <> 2 AND x <> NULL, and that last term is always UNKNOWN. With hand-written lists it does not happen; with lists coming from a subquery (lesson 02-04) it is a common trap.

Two wildcards:

LIKE is case-sensitive in PostgreSQL:

No title starts with a lowercase the . To ignore case, PostgreSQL offers ILIKE (the I stands for insensitive), which is not standard but is extremely handy:

A portable alternative, valid in SQLite too:

In SQLite, LIKE is already case-insensitive for ASCII characters (but not for accented vowels), and ILIKE does not exist. It is one of the differences that most surprise people when porting queries.

Example with _:

Specifically it returns EJ-3081 to EJ-3089: nine rows, because EJ-3080 does not exist.

Here we come back to the trap we announced in 02-01.

And its complement, which in BiblioRed has a very specific meaning:

Four open loans, which match exactly the four copies with status on_loan. The database is consistent.

And a check on the other NULL trap, the one with inequalities:

Neither query sees the NULLs. If you want the loans "with no surcharge outstanding", you have to say so: WHERE surcharge = 0 OR surcharge IS NULL.

Because a relation is a set and has no order, ORDER BY is the only way to guarantee one.

ASC (ascending) is the default; DESC reverses it.

It sorts by the first one and, within ties, by the second:

Each criterion carries its own ASC/DESC: ORDER BY branch_id ASC, join_date DESC is perfectly valid.

Where does a NULL go when sorting? The standard leaves it open, and PostgreSQL places them last in ASC and first in DESC (equivalent to treating them as the largest value). SQLite does the opposite: it puts them first in ASC.

Since we do not want to depend on the manager, you specify it:

Notice the second criterion, loan_id: without it, the order among the four NULLs would be arbitrary. When order really matters, always finish with a criterion that breaks ties unambiguously, typically the primary key.

NULLS FIRST/NULLS LAST is standard syntax and works in PostgreSQL; SQLite supports it from version 3.30.

Sorting by alias is legitimate and readable. Sorting by position number works, but it is fragile: if somebody reorders the SELECT list, the query silently changes meaning. Avoid it.

ORDER BY title puts "Algebra for the Impatient" first under an en_US.UTF-8 locale, which sorts case-insensitively and ignores punctuation; with the C collation (byte by byte) every uppercase letter comes before every lowercase one, so a title typed in lower case would end up after all the rest. It is not a bug: it is the collation. You can see yours with SHOW lc_collate; in PostgreSQL.

OFFSET skips rows before it starts counting:

This is the mechanics behind the pagination of any web catalog: page N is obtained with LIMIT size OFFSET (N-1) * size.

Two warnings:

1. LIMIT without ORDER BY makes no sense. "Give me 3 rows out of the 9" without saying which means "give me any 3", and they can be different on every run. Worse still: if you paginate without sorting, the same row can show up on page 1 and on page 3, and another one may never show up at all.
2. A large OFFSET is slow. To reach row 100,000 the manager has to produce and throw away the previous 100,000. In large catalogs, key-based pagination techniques are used; that is a performance topic (lesson 06-03).

The standard syntax is OFFSET 3 ROWS FETCH FIRST 3 ROWS ONLY, more verbose and less used. LIMIT/OFFSET works in PostgreSQL, SQLite and MySQL.

Before we start: the examples in this section and the next one modify the data set we have just loaded, and lessons 02-04 and 02-05 take it as given. At the end of each example we include the statement that undoes the change. Run it.

Before any UPDATE or DELETE, run the same condition with a SELECT. If the SELECT returns the rows you expected, the modification will touch those same rows.

UPDATE 1 confirms that exactly one row was touched. If you see a larger number than expected, something went wrong, and in PostgreSQL, if you are inside a transaction, you are still in time (lesson 06-01).

An UPDATE can compute the new value from the current one:

Important: surcharge + 0.50 on a NULL gives NULL. If we had run that UPDATE without a WHERE, the four open loans would have gone from NULL to… NULL, and the other eight would have got more expensive. Silently.

When Marta Alsina returns EJ-3081, two tables have to be touched:

This raises an uncomfortable question: what happens if the first statement works and the second one fails? The database would be left in an inconsistent state: a closed loan and a copy marked as on loan. The answer is the transaction, and it is the content of lesson 06-01. For now, we undo:

And it would also violate uq_members_email, so in this particular case the constraint would save us. There will not always be a constraint to save you. An UPDATE copies SET status = 'withdrawn'; would have withdrawn BiblioRed's 40,000 copies without a word of protest.

Habits that avoid disaster:

1. Write the WHERE before the SET. Start by typing UPDATE table WHERE ... and then go back and insert the SET. It sounds odd, it works.
2. Try it with a SELECT first. Always.
3. Work inside a transaction for delicate operations (06-01).
4. In psql, turn on \set ON_ERROR_STOP on in your scripts so they stop at the first error.

Let's create a throwaway row so we do not spoil the data set:

In PostgreSQL you can use RETURNING here too, to leave a record of what was swept away:

Try to delete a member who has loans:

The manager is protecting you. If it allowed the deletion, Marta Alsina's three loans would be left orphaned: pointing at a member who no longer exists. That behavior —and its alternatives, such as cascading deletion— is the entire subject of lesson 02-06.

In SQLite, if you forgot PRAGMA foreign_keys = ON, that DELETE will work and leave you three orphan rows without saying a word. It is exactly the scenario lesson 02-06 will teach you to detect and clean up.

Without a WHERE, DELETE empties the entire table, without asking and with no recycle bin. If it happens to you outside a transaction, the only way out is the backup (lesson 06-04). The four habits from the previous section apply here with even more reason.

When what you really want is to empty a complete table, there is a dedicated statement:

SQLite has no TRUNCATE; use DELETE FROM table without a WHERE, which it optimizes internally.

Do not run any TRUNCATE on your biblioredb: you would lose the data set you have just loaded. If you already have, run the script from section 2 again.

• UPDATE or DELETE without a WHERE. The most expensive mistake anyone makes with SQL. Always try the condition with a SELECT first.
• Writing 'NULL' instead of NULL. With quotes it is a four-letter text string; without quotes, the absence of a value. A column that mixes both is a ruined column.
• Using = NULL or <> NULL. Zero rows, no error. Always IS NULL / IS NOT NULL.
• Forgetting that <> value excludes NULLs. WHERE surcharge <> 0 does not return the loans with a null surcharge. If you want them, add OR surcharge IS NULL.
• Mixing AND and OR without parentheses. AND wins. Always add parentheses.
• INSERT without a column list. It breaks as soon as somebody touches the schema, sometimes silently.
• Inserting explicit identifiers and not resynchronizing the sequence. It causes duplicate-key errors much later, when nobody remembers the initial load.
• LIMIT without ORDER BY. The result is not reproducible and pagination can repeat or skip rows.
• Trusting that LIKE is case-sensitive. In PostgreSQL it is, in SQLite (ASCII) it is not, in MySQL it depends on the collation. If you need certainty, LOWER(column) LIKE ....
• Tip: in psql, \pset null '(null)' makes NULLs visible and \x on shows results vertically, ideal for wide rows.
• Tip: save all your SQL in files (biblioredb_schema.sql, biblioredb_data.sql) and run them with \i file.sql in psql or .read file.sql in sqlite3. Being able to rebuild the database in ten seconds gives you the freedom to experiment without fear.

They are all solved on a single table. Write the query before looking at the solution and compare the results.

1. The titles and publishers of the books published before 2010, from oldest to most recent.
2. The books with no ISBN on record.
3. The distinct publishers in the collection, in alphabetical order.
4. The titles containing ar in any position, ignoring case.
5. The codes of the copies at branch 2 that are not available.
6. The three most recent books in the collection.

1. The members who signed up in 2021 or 2022, with first and last name in a single column called member.
2. The members who are not active.
3. The loans returned late (the actual return date is after the due date), sorted by days late… or, if you cannot compute the difference yet, simply by loan date.
4. The open loans that were due back before 10 August 2026.
5. The second page of a loan listing sorted by loan date descending, with 5 loans per page.
6. The loans whose surcharge is not zero, including those whose surcharge has not been calculated yet.

Write the statements for each operation, preceded by the checking SELECT and followed by the statement that undoes the change.

1. Copy EJ-3088 comes back from the workshop: set it to available.
2. Fix the publisher of book 337: change it from "Marlia Editions" to "Marlia Editions Ltd".
3. Sign up a new member, Berta Colomer (berta.colomer@example.org), at the South branch, with today's date (use '2026-08-02'), active, letting the manager assign the identifier. Then delete her.
4. Reservation 4 was cancelled by mistake: reactivate it.

Solution 1

Notice that "The Pillars" gets in through Pillars and "Gardening" through Gardening: LIKE searches for substrings, not whole words.

Solution 2

Note that the condition compares two columns of the same row, which is perfectly legitimate. And that the four open loans do not appear: NULL > date is UNKNOWN. In PostgreSQL, return_date - due_date would give the days late directly (7, 7 and 21).

(The second criterion loan_id DESC guarantees that pagination is stable even if there were repeated dates.)

Without the OR surcharge IS NULL you would have got only three rows and lost sight of the four loans in progress.

Solution 3

A final check that everything is back as it was:

This lesson has taken BiblioRed from an empty schema to a live, queryable database:

• INSERT with an explicit column list (always), with one or several rows, with NULL unquoted and with RETURNING in PostgreSQL to find out the generated identifiers. Plus the automatic checking of uniqueness and foreign keys that the spreadsheet never had.
• The BiblioRed data set: 4 branches, 8 authors, 10 members, 9 books, 15 copies, 12 loans and 5 reservations, all fictional and consistent. It is the working material for the rest of the course.
• The side effect of inserting keys by hand and how to resynchronize the generator with RESTART WITH or setval.
• SELECT with column projection, AS aliases (with double quotes when they carry spaces), concatenation with || and DISTINCT applied to the complete combination of columns.
• WHERE with comparisons, AND/OR/NOT and their precedence, BETWEEN (endpoints included), IN and NOT IN (with its NULL trap), LIKE/ILIKE with % and _, and IS NULL, which is the only way to ask about absence.
• ORDER BY with several criteria, ASC/DESC, NULLS FIRST/NULLS LAST and the importance of a tie-breaking criterion.
• LIMIT/OFFSET for pagination, always with ORDER BY.
• UPDATE and DELETE with the discipline of a prior SELECT, reading the number of affected rows and the respect that a forgotten WHERE demands; plus TRUNCATE as a fast DDL-level emptying.

So far, every query has looked at a single table, and that leaves questions unanswered: we do not know who has EJ-3081, nor which title is the most borrowed, nor which members have never taken anything out. The data is spread on purpose —that is the essence of the relational model— and now it is time to reassemble it.

In lesson 02-04, Multi-Table Queries: JOINs and Subqueries, you will learn to join members with loans, loans with copies, copies with books and books with authors, all in a single query; to use LEFT JOIN to find what has no match; and to nest queries inside queries. That is where SQL starts to become truly powerful.