--- title: 'SQL Language Families' source: 'https://academia.sh/en/courses/sql-fundamentals/sql-language-families' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # 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.