SQL Language Families

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.

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.

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','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','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.

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.

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.