In the previous lesson we drew BiblioRed's blueprint: seven relations, their keys and their links. Today we learn the language used to explain that blueprint to a management system, and we use it to actually build it inside biblioredb.

SQL is, by some distance, the longest-lived and most profitable special-purpose language in computing: it was born in the seventies, survived every fashion, and today it is spoken not only by PostgreSQL, MySQL, SQLite or Oracle, but also by analytical engines, cloud warehouses and even query layers on top of NoSQL databases. What you learn here will serve you in all of them.

This lesson is the hinge of the course: it starts by explaining the language and ends with a concrete deliverable, the complete CREATE TABLE script for BiblioRed's seven tables, which you must run before moving on to the next lesson. Keep psql or sqlite3 open while you read.

1. What SQL is and why it is declarative
2. The standard and why dialects exist
3. The five sublanguages: DDL, DML, DQL, DCL and TCL
4. Writing rules: identifiers, capitalization, quotes and comments
5. The data types we need right now
6. CREATE TABLE and the basic constraints
7. Auto-incrementing keys in PostgreSQL and in SQLite
8. Deliverable: the complete BiblioRed schema
9. ALTER TABLE: modifying what already exists
10. DROP TABLE and the order of destruction
11. Common mistakes and tips
12. Exercises
13. Conclusion

SQL (Structured Query Language) is the standard language for defining, manipulating and querying relational databases. Its most characteristic trait is that it is declarative: you describe what result you want, not how to obtain it.

Compare. This is how "the open loans at the North branch" would be solved in an imperative language such as Python, working with files:

You have had to decide the order of the loops, which file is scanned first and how the lookup is done. Now in SQL:

There are no loops, no traversal order, no data structures. Only the description of the result. The query optimizer —the component we studied in lesson 01-04— is the one that decides whether to scan loans or copies first, whether to use an index or read the whole table, and with which algorithm to pair up the rows. And it makes that decision afresh every time, with the statistics of the moment: the same query that is resolved one way today may be resolved another, faster way tomorrow, without you changing a single letter.

The three practical consequences of this:

• You write less, and more clearly. A five-line query replaces fifty lines of imperative code.
• You do not hand-optimize what the manager optimizes better. Reordering the tables in the FROM to "go faster" is, in general, wasted time: the optimizer reorders them on its own.
• When something is slow, you diagnose it by looking at the plan, not at the SQL. That is what we will do with EXPLAIN in lesson 06-03.

SQL is neither purely declarative nor purely relational (we already saw that it allows duplicates), but that pragmatic mixture is precisely what made it succeed.

SQL has been standardized by ISO and ANSI since 1986, with successive revisions (SQL-92, SQL:1999, SQL:2003, SQL:2011, SQL:2023) that we saw in lesson 01-03. However, no manager implements the standard exactly, and they all add things of their own. The reasons:

• The standard arrives late: vendors invent a useful feature, it becomes popular and years later it is standardized with different syntax.
• The standard leaves areas optional or undefined (date types, auto-increment, result limits), and each vendor fills the gap its own way.
• Every engine has capabilities of its own that the standard does not contemplate.

The result: a broad common core —SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, JOIN, GROUP BY— that works everywhere, and a periphery specific to each dialect.

In this course we use PostgreSQL as the reference and flag SQLite's differences where they matter. A permanent piece of advice: write SQL as standard as you can and use dialect extensions only when they bring something real; your SQL will be easier to port and, above all, easier for someone else to understand.

Although people speak of "SQL" in the singular, its statements are grouped by function. Knowing the groups helps you find your bearings and know which lesson of the course covers what.

Two nuances worth having clear from the start:

• Many people consider DQL part of DML, which is why you will sometimes see "four sublanguages". The count does not matter; what matters is the function.
• In PostgreSQL, DDL is transactional: you can create tables inside a transaction and undo it with ROLLBACK. In many other managers (MySQL with InnoDB, Oracle) a DDL statement implicitly commits whatever was pending. It is a difference with real consequences when writing migration scripts.

Today we work exclusively with DDL.

• Keywords (SELECT, FROM, CREATE TABLE) are part of the language.
• Identifiers are the names you choose: tables, columns, constraints, indexes.

SQL is case-insensitive for keywords: select, SELECT and SeLeCt are equivalent. The universal convention is to write keywords in UPPERCASE and identifiers in lowercase, because it makes the query readable at a glance:

PostgreSQL, following the standard, folds unquoted identifiers to lowercase. So JoinDate, joindate and JOINDATE are the same column. But if you write the identifier between double quotes, it is respected literally, and from then on you will always have to quote it:

Firm advice: do not use double quotes in identifiers. Name everything in lowercase_with_underscores, with no accents, no ñ and no spaces. It is the PostgreSQL convention and it saves you an entire category of problems. In this course, every BiblioRed name follows that rule.

This distinction is a constant source of errors for anyone coming from other languages:

SQLite is more permissive and in some cases accepts double quotes as text; do not get used to it, because that SQL will not work in PostgreSQL.

To include an apostrophe inside a literal, you double it:

The ; ends a statement. In psql and in sqlite3 it is mandatory: without it, the client understands that the statement continues on the next line and sits there waiting. If you see a prompt like biblioredb-# instead of biblioredb=#, that is exactly it: you are missing the semicolon.

Write multi-line queries with one clause per line. It costs the same and reads infinitely better:

Every column has a type, and that type is the implementation of the domain integrity rule from the previous lesson. Here we see only what is needed to create BiblioRed's tables; the full catalog and the fine criteria for choosing are lesson 04-04.

In SQLite there is a single integer type, INTEGER, which holds up to 8 bytes; the names SMALLINT or BIGINT are accepted but end up being the same thing.

Here is a decision that separates professionals from amateurs.

Floating point cannot represent values such as 0.10 exactly in binary, just as in decimal we cannot write 1/3 with a finite number of digits. That produces results like this:

In BiblioRed the surcharge column stores euros. With floating point, adding up ten thousand surcharges of €0.10 can give 999.9998 instead of 1000.00, and the library's annual accounts will never balance.

Rule: for money, always NUMERIC(p, s). NUMERIC(6, 2) allows up to 9999.99, more than enough for a library surcharge. Save floating point for scientific magnitudes where approximate precision is acceptable (temperatures, coordinates, physical measurements).

SQLite warning: it has no exact decimal type. NUMERIC(6,2) is accepted, but internally it may be stored as floating point. For practice it makes no difference; in production with money, it is one more argument in favor of PostgreSQL.

In PostgreSQL, VARCHAR(n) and TEXT have the same performance: VARCHAR(n) is not faster, it merely adds a length check. So the criterion is semantic: use VARCHAR(n) when the limit is a real rule (an ISBN has 13 characters, not one more) and TEXT when there is no natural limit (a review, some notes).

The universal format is ISO 8601: YYYY-MM-DD. Always use it and you will avoid the eternal ambiguity of 03/04/2026 (3 April or 4 March?).

In BiblioRed, loan and return dates are DATE: the library cares about the day, not the exact time. If in the future surcharges by the hour were wanted, it would have to migrate to TIMESTAMP.

SQLite has no date type: it stores dates as text '2026-07-14', as a Julian day number or as a Unix integer. Declaring DATE is legal and works as documentation, but SQLite will not validate that the content is a real date. It is a fundamental difference: PostgreSQL uses strict typing and SQLite, type affinity.

BOOLEAN accepts TRUE, FALSE and NULL. In BiblioRed, members.active indicates whether the library card is still valid.

SQLite has no BOOLEAN: it uses integers 0 and 1. It accepts the word BOOLEAN in the CREATE TABLE and recognizes TRUE/FALSE from version 3.23, storing them as 1 and 0.

The statement that creates a relation:

The four constraints we will use today:

There are two more, CHECK and DEFAULT, studied in depth in 04-04; we do not use them today so as not to get ahead of design decisions.

A commented example with BiblioRed's simplest table:

An important point: phone is VARCHAR, not a number. Phone numbers are not added up or averaged, they can start with a zero and they carry spaces or the +34 prefix. The general rule is: if you are not going to do arithmetic with it, it is not a number. The same applies to ISBNs and postal codes.

Constraints can be written next to the column or at the end, as a standalone element. The following two forms are equivalent:

The table form is mandatory when the constraint affects several columns (a composite primary key, for example) and advisable when you want to give it a readable name. Why does the name matter? Because when the constraint is violated, the error message will mention it:

A self-explanatory name turns a cryptic error into an immediate diagnosis. The convention we will follow: fk_<table>_<reference>, uq_<table>_<column>, pk_<table>.

In the previous lesson we decided to use surrogate primary keys. Somebody has to generate those numbers, and we do not want to do it by hand. Each manager solves it its own way.

Differences between the two:

For BiblioRed we will use GENERATED BY DEFAULT AS IDENTITY: it is standard and, on top of that, it lets us insert explicit identifiers, which is exactly what we will need in the next lesson to load the data set with the member_id and book_id values we already have fixed (14, 15, 16, 331…).

In SQLite, a column declared exactly as INTEGER PRIMARY KEY is an alias for the internal rowid and auto-increments automatically if you give it no value. The word AUTOINCREMENT is optional and only adds the guarantee that a deleted identifier is never reused, at the cost of an extra internal table. SQLite's official documentation recommends not using it unless that guarantee is needed.

Watch the detail: it has to be INTEGER, in upper or lower case but that exact word. INT PRIMARY KEY does not activate the behavior.

The moment has come. This is the script that turns lesson 02-01's blueprint into real tables.

A table cannot reference another that does not exist yet. They have to be created following the dependencies:

One valid order: branches, authors, members, books, copies, loans, reservations.

First connect to the database you created in lesson 01-04:

And run:

Expected output, one line per statement:

And the detail of one specific table:

If you see this, the schema is standing.

The same script, with three changes: the auto-incrementing key, TEXT instead of VARCHAR (SQLite treats them the same, but it is its convention) and one indispensable line at the beginning.

Verification:

PRAGMA foreign_keys = ON is so important that lesson 02-06 comes back to it.

Schemas change. ALTER TABLE modifies the structure without losing the data.

BiblioRed wants to record members' mobile phone numbers:

The column is added at the end and every existing row is left with NULL. That is why adding a NOT NULL column to a table with data fails: there is no value to put in the rows that are already there.

The usual solution has three steps: add it nullable, fill it in with an UPDATE (lesson 02-03) and then impose the NOT NULL.

Widening the size is safe. Narrowing it or changing type family can fail if the existing data does not fit or cannot be converted; in that case PostgreSQL aborts and touches nothing.

Here you see the practical payoff of having named the constraints: to remove one you have to name it, and uq_books_isbn is a good deal more manageable than the automatic name books_isbn_key.

This deletes that column's data and there is no undo (outside a transaction). Think twice.

SQLite supports only a subset of ALTER TABLE:

SQLite's official procedure for what it does not support is: create a new table with the correct structure, copy the data with INSERT INTO ... SELECT, drop the old one and rename the new one. It is tedious, and it is another argument in favor of PostgreSQL for a schema that is going to evolve.

Removes the table and all its data, immediately and without confirmation. In PostgreSQL, if you run it inside a transaction you can still ROLLBACK (lesson 06-01); in SQLite and in most managers, you cannot.

Two useful variants:

If you try to drop a table that others point at, the manager stops you:

This is referential integrity working exactly as it should. To empty the schema and start over, you have to go in the reverse of the creation order: children first, parents afterwards.

Save these seven lines at the top of your creation script, commented out. When you want to rebuild the schema from scratch —and you will want to several times during the course— you uncomment them and run the whole file.

• Using double quotes for text values. WHERE status = "available" makes PostgreSQL look for a column called available. Values go between single quotes, always.
• Forgetting the semicolon. If psql shows biblioredb-# instead of biblioredb=#, the statement is still open. Type ; and press Enter.
• Naming columns with capitals or accents. "JoinDate" or "año" condemn you to quoting in every query you write for the rest of the database's life. join_date, year_.
• Creating the tables in the wrong order. REFERENCES books fails if books does not exist yet. Follow the dependency tree.
• Using FLOAT or REAL for money. Sums that do not add up, cents that evaporate, impossible year-end closings. NUMERIC(p, s).
• Storing phone numbers, ISBNs or postal codes as numbers. You lose the leading zeros, the prefixes and the hyphens. If you do not do arithmetic with it, it is text.
• Forgetting PRAGMA foreign_keys = ON in SQLite. The schema is created looking correct but validates nothing, and you discover the orphan rows months later. It has to be run in every session.
• Adding a NOT NULL column to a table with data. It always fails. Add it nullable, fill it in and then impose the constraint.
• Tip: save the schema script in a file (biblioredb_schema.sql) and run it with psql -U student -d biblioredb -f biblioredb_schema.sql or with .read biblioredb_schema.sql in SQLite. A schema that exists only in the console history is a lost schema.
• Tip: name every constraint. The day an error fires, the name will be half the diagnosis.

State which sublanguage each statement belongs to and which lesson of the course covers it:

1. SELECT title FROM books;
2. ALTER TABLE members ADD COLUMN phone VARCHAR(20);
3. UPDATE copies SET status = 'available' WHERE copy_id = 1;
4. GRANT SELECT ON books TO front_desk;
5. ROLLBACK;
6. DROP TABLE reservations;
7. INSERT INTO authors (first_name, last_name) VALUES ('Marina', 'Escolá');

BiblioRed wants a new table, fines, for the penalties each member has accumulated. A colleague has written this:

Find at least six problems and rewrite the table correctly for PostgreSQL, following the course conventions.

Write the ALTER TABLE statements needed for each change BiblioRed's management asks for. Also state, where relevant, whether the operation can fail on a table that already has data and how to avoid it.

Warning: this exercise is about writing, not running. If you decide to try it out, do it on a separate database, or undo the changes afterwards: the rest of the module assumes the schema exactly as it stood in section 8, with the books.language column included.

1. Add to branches an optional contact_email column of up to 120 characters.
2. Add to books an optional integer page_count column.
3. The books.publisher column is too short: widen it to 150 characters.
4. branches.opening_date must become mandatory.
5. Rename reservations.expiry_date to expiration_date.
6. Remove from books the language column, which nobody uses.

Solution 1

Solution 2

Problems found:

1. Fines, ID, Member… in mixed case. PostgreSQL folds them to lowercase, so it "works", but it breaks the convention of the rest of the schema. Names in lowercase_with_underscores.
2. ID with no PRIMARY KEY. The table would have no primary key: entity integrity would be violated and indistinguishable duplicate rows could be inserted.
3. ID with no auto-increment. The number would have to be invented by hand on every insertion.
4. Amount FLOAT. It is money: it must be NUMERIC(6,2).
5. Date VARCHAR(10). It is a date: it must be DATE. As text it could not be sorted reliably, nor subtracted, nor validated.
6. Paid VARCHAR(2). It is a yes/no: it must be BOOLEAN. With VARCHAR(2) you would end up with 'Y', 'yes', 'YES', '1' and 'no' all coexisting.
7. "Nº Notice" in double quotes, with a space and a º. It condemns you to quoting forever and it is neither URL-safe nor portable. notice_number.
8. Column name Member is not descriptive: the schema convention is member_id.
9. Unnamed constraints and the final semicolon is missing.

Corrected version:

Solution 3

A note on SQLite: points 3 and 4 cannot be done with ALTER TABLE; you would have to recreate the table, copy the data and rename.

This lesson has turned the blueprint into a building. Let's review:

• SQL is declarative: you describe the result and the optimizer —the one from lesson 01-04— decides the route. That means you write less and the manager can improve performance without you touching the query.
• There is a standard and there are dialects: a very broad common core and a periphery specific to each engine. PostgreSQL is our reference; SQLite, the lightweight alternative.
• The five sublanguages: DDL (structure), DML (data), DQL (queries), DCL (permissions) and TCL (transactions).
• The writing rules: keywords in uppercase, identifiers in lowercase_with_underscores and without double quotes, single quotes for text literals, a semicolon at the end and comments with -- or /* */.
• The types we needed: INTEGER/SMALLINT for integers, NUMERIC(p, s) for money (never floating point), VARCHAR(n)/TEXT for text, DATE/TIMESTAMP for dates in ISO format and BOOLEAN for yes/no, with SQLite's peculiarities in each case.
• CREATE TABLE with PRIMARY KEY, NOT NULL, UNIQUE and REFERENCES, in column form or in table form with a proper name; auto-incrementing keys (GENERATED ... AS IDENTITY or SERIAL in PostgreSQL, INTEGER PRIMARY KEY in SQLite); ALTER TABLE to add, rename, retype and drop columns; and DROP TABLE, which demands walking the dependencies in reverse order.
• And, above all, the complete BiblioRed schema: branches, authors, members, books, copies, loans and reservations, created and verified with \dt.

The seven tables exist and they are empty. In lesson 02-03, Basic SQL Operations, we fill them: you will see INSERT, SELECT with WHERE, ORDER BY and LIMIT, UPDATE and DELETE, all on a single table. That lesson's deliverable will be BiblioRed's test data set —the four branches, the members, the books, the copies and the fictional loans— which we will use until the end of the module. Do not drop your tables: from now on, every lesson builds on the previous one.