Part III — SQL: Structured Query Language

Chapter 8. Introduction to SQL

The last five chapters built theory: models, algebra, architecture, design. From here to Chapter 13, the book is about the tool you will use daily — SQL, Structured Query Language. SQL is the most successful declarative language ever deployed: fifty years old, standardized across vendors, spoken by every relational system from PostgreSQL to Oracle, and ranked at or near the top of developer surveys for half a century. It is also the only language in this book that you can begin using productively in an afternoon.

This chapter is the guided tour before the deep dives: what SQL is and where it came from, how its commands are categorized, the shape of its syntax and its conventions, and the client tools you will type into. Every later SQL chapter (9–13) assumes this one; every platform chapter (14–17) builds on it.

After studying this chapter you will be able to:

• Describe SQL's purpose, origins, and standards history.
• Explain what a dialect is and how this book marks platform differences.
• Classify any SQL statement into DDL, DQL, DML, DCL, or TCL.
• Write and run your first DDL, DML, and query statements against the university database.
• Read SQL's syntax conventions: keywords, identifiers, literals, comments, and expressions.
• Use psql and the mysql client interactively, and know where pgAdmin and Workbench fit.

8.1 Purpose and evolution of SQL

SQL exists to do one thing: let people state what data they want and what must hold of it, without stating how to fetch or enforce it. That declarativity is Chapter 1's data abstraction made linguistic. Three design choices explain its longevity:

• One language, all jobs. SQL defines structure (tables, constraints), manipulates data (insert, update, delete), queries, controls access, and manages transactions — no separate definition and manipulation languages to bridge.
• Set-at-a-time. One statement processes whole tables, not single records — the relational model's algebra realized as syntax.
• English-ish keyword skeleton. SELECT full_name FROM student WHERE gpa > 3.8; reads like a sentence. The resemblance is cosmetic but made adoption by non-programmers historically important.

The name is a compressed history. IBM researchers Donald Chamberlin and Raymond Boyce designed SEQUEL (Structured English QUEry Language) in 1974 for the System R prototype — the project that proved Codd's model implementable. Renamed SQL (a trademark conflict), it shipped in commercial products by 1979 (Oracle, then IBM), reached its first ANSI/ISO standard in 1986 (SQL-86), and has been extended by standards roughly every few years since: SQL-92 made joins first-class, SQL:1999 added recursion, triggers, and user-defined types, SQL:2003 brought window functions and XML, SQL:2016 JSON, and the current editions continue with property-graph extensions. This book teaches the widely implemented core through PostgreSQL 16 and MySQL 8.0 — both far closer to the standard than popular belief suggests.

8.2 SQL standards and dialects

A standard is a written agreement of what compliant SQL must do; a dialect is a vendor's implementation — standard core plus extensions plus (rarely, but notoriously) divergences. The practical landscape:

• Portable core (95% of this book): SELECT with joins, GROUP BY, subqueries, UNION; CREATE TABLE with PRIMARY KEY, FOREIGN KEY, CHECK; INSERT/UPDATE/DELETE; transactions; GRANT.
• Extension hot spots: auto-incrementing keys (SERIAL/IDENTITY versus AUTO_INCREMENT), string and date function names, limit/pagination clauses, full-text search, JSON functions, stored-program languages (PL/pgSQL versus MySQL's SQL/PSM-based syntax).

This book's convention, stated once and followed throughout: listings are standard SQL or PostgreSQL where the standard is silent; platform notes marked "In MySQL, ..." flag every divergence that matters. The goal is dialect awareness, never dialect dependence — and Chapter 17 makes the comparison systematic.

Two habits make portability real in practice. First, let the catalog tell you what your engine actually implements (Chapter 4's information_schema). Second, when you learn a feature, learn whether it is standard or vendor — it is the vendor features that deserve a second thought before committing to them in a schema that might outlive the current platform choice.

8.3 SQL command categories

Every statement belongs to a functional category. The vocabulary is stable and worth memorizing, because documentation, tools, and exams are organized by it:

The five categories preview the rest of the book: DDL fills Chapter 9, DQL and DML fill Chapters 10–13, DCL fills Chapter 20, and TCL fills Chapter 18. The next five sections walk one statement from each category — the smallest possible live tour. Run them in your university database (Chapter 14 installs everything; Appendix H loads it).

8.4 Data Definition Language (DDL)

DDL declares structure. The statement that creates one of our six tables, exactly as the canonical schema defines it:

CREATE TABLE department (
    dept_id     INTEGER       PRIMARY KEY,
    dept_name   VARCHAR(50)   NOT NULL UNIQUE,
    building    VARCHAR(30),
    budget      NUMERIC(12,2) CHECK (budget >= 0)
);

Four lines of definition and four lessons: dept_id is declared the primary key in line with the column it describes (inline constraint syntax); dept_name must exist (NOT NULL) and never repeat (UNIQUE) — the alternate-key rule of Chapter 6; building may be NULL (a department can lack a building); budget carries a domain rule — a CHECK constraint — making negative budgets impossible to store. DDL is the design work of Chapters 5–7 made executable, and Chapter 9 is its full treatment, column type by constraint.

8.5 Data Manipulation Language (DML)

DML changes data. One insert, one update, one delete:

INSERT INTO department (dept_id, dept_name, building, budget)
VALUES (6, 'Physics', 'Academic Building E', 1600000.00);

UPDATE department
SET    building = 'Academic Building B2'
WHERE  dept_id = 6;

DELETE FROM department
WHERE  dept_id = 6;

Notice what the three statements share: the table named explicitly, the columns unambiguous, and the WHERE clause precise. The UPDATE and DELETE both carry WHERE dept_id = 6 — a missing or wrong WHERE on an update or delete is the classic SQL accident, modifying every row in the table. Professional habit: write the WHERE first, or wrap risky changes in a transaction and roll back (Section 8.8) until the effect is verified. Chapter 10 covers DML in full, including the multi-row INSERT form that loads Appendix H's dataset.

8.6 Data Query Language (DQL)

SELECT is the language's heart and 90% of its daily use. The skeleton — three clauses in fixed logical order:

SELECT columns to appear          (the projection π)
FROM   tables involved           (the relations)
WHERE  rows must satisfy          (the selection σ)

Two first queries, both used again in later chapters:

SELECT COUNT(*) AS students
FROM   student;

 students
----------
       12

SELECT full_name, gpa
FROM   student
WHERE  gpa > 3.80;

  full_name   | gpa
--------------+------
 Sadia Afrin  | 3.88
 Arif Mahmud  | 3.90

The algebra mapping of Chapter 3 is literal: WHERE is σ, the select list is π, and the FROM clause names the operands. Query writing is the craft of Chapters 10–13; for now, read both queries aloud and confirm each makes sense before moving on — from this page forward, the book assumes you can read a SELECT.

8.7 Data Control Language (DCL)

DCL governs access — the database enforcing Chapter 1's least-privilege principle:

GRANT  SELECT ON course, course_section, enrollment TO portal_app;
REVOKE SELECT ON enrollment FROM portal_app;

The first statement lets the student-portal account read course and section data; the second takes one table back (enrollments contain grades — sensitive). DCL statements are few and regular; the judgment is policy — which account needs which table, and nothing more — and that is Chapter 20's whole subject. What matters here is the shape: DCL names a privilege, an object, and a principal, exactly as authorization was defined in Chapter 1.

8.8 Transaction Control Language (TCL)

TCL groups changes into all-or-nothing units — the ACID preview of Chapter 18. The safest way to practice destructive SQL:

BEGIN;

DELETE FROM enrollment
WHERE  grade IS NULL;

SELECT COUNT(*) AS remaining FROM enrollment;   -- 20

ROLLBACK;

SELECT COUNT(*) AS restored FROM enrollment;    -- 28

The BEGIN opens a transaction; the delete destroys the 8 in-progress rows; the count confirms 20 remain; the ROLLBACK restores all 8, as the final count proves. Until COMMIT, nothing is permanent — the property that makes trial-and-error safe, and the reason this book's exercises can say "delete all enrollments" without fear. SAVEPOINT marks partial positions inside a transaction; all of it is Chapter 18's material, introduced here because using it now is what makes practice safe.

8.9 SQL syntax, conventions, and expressions

Statements, clauses, keywords. A SQL statement is a complete command ending in a semicolon; clauses are its labeled parts (SELECT ... FROM ... WHERE ...); keywords are the reserved words that label clauses and operations. Statements are free-format: line breaks and indentation are for humans — the book's convention is one clause per line for anything longer than a trivial statement.

Identifiers and case. Keywords and identifiers (student, GPA, dept_id) are case-insensitive for lookup, but string values are case-sensitive: WHERE dept_name = 'english' matches nothing because the stored name is 'English'. The book writes keywords UPPERCASE, identifiers in lower_snake_case — matching Appendix A's reference.

Literals. Numbers bare (3.90, 4000000); strings in single quotes ('Arif Mahmud'); dates as string literals ('2026-10-09') or typed literals (DATE '2026-10-09'); NULL is written NULL. Double quotes in standard SQL delimit identifiers (not strings!) — a classic cross-language slip; in MySQL the default is backquotes for identifiers instead.

Expressions. A column reference, literal, function call, or any combination with operators — arithmetic (gpa * 25), comparison (= <> < <= > >=), logical (AND, OR, NOT), string concatenation (|| standard; MySQL CONCAT()). Expressions follow Chapter 2's NULL rule: any NULL operand makes the result NULL, and comparisons yield UNKNOWN — the three-valued logic that Section 10.8 turns into a survival skill.

Comments. -- to end of line and /* spanning lines */ — use them in scripts; they survive into the docx'd world of stored procedures (Chapter 22).

8.10 SQL clients and database tools

SQL is spoken through a client — interactive, scripted, or embedded in a program (Chapter 23). The two you must know:

• psql — PostgreSQL's command-line client: prompt university=>, ; executes, \d student describes a table, \dt lists tables, \timing shows run times, \i file.sql runs a script. Chapter 14 installs and tours it.
• mysql — MySQL's client: prompt mysql>, SHOW TABLES; and SHOW CREATE TABLE student; describe, source file.sql runs scripts, \c cancels a half-typed statement.

Both are scriptable, both have a \.-style escape hatch for meta-commands, and fluency in a CLI client is a professional differentiator — the GUI cannot always be there, but a terminal almost always can. The GUI tools — pgAdmin for PostgreSQL, MySQL Workbench for MySQL, and vendor-neutral tools like DBeaver — earn their place with schema diagrams, data editing, and plan visualization (Chapter 19 uses them), and Chapter 14 tours both flagship GUIs. What no GUI replaces is the mental model you now have: the client is two-tier (Chapter 4) — whatever you type travels to the server, and the catalog, optimizer, and storage layers of Chapter 4 do the rest.

Chapter Summary

• SQL is the declarative, set-at-a-time language of the relational model: SEQUEL (Chamberlin and Boyce, 1974) → SQL-86 → SQL-92 joins → SQL:1999 recursion → window functions, JSON, and beyond.
• Standards define the portable core; dialects extend and occasionally diverge; this book shows standard SQL with flagged MySQL/PostgreSQL differences.
• Statement categories: DDL (structure), DQL (query), DML (change), DCL (privilege), TCL (transactions) — each maps to a later chapter.
• DDL declares structure (CREATE TABLE department with PK, NOT NULL, UNIQUE, CHECK); DML changes rows (always with a deliberate WHERE); SELECT's FROM-WHERE-SELECT skeleton mirrors algebra.
• DCL grants and revokes per least privilege; TCL makes practice safe: BEGIN, verify, ROLLBACK.
• Conventions: uppercase keywords, single-quoted strings, NULL-aware expressions, semicolon-terminated statements, comments for scripts.
• Clients: psql and mysql CLIs are the professional baseline; pgAdmin and Workbench are the guided GUIs; all are two-tier clients of Chapter 4's architecture.

Key Terms

Laboratory Exercises

1. Connect to your university database in both clients; run SELECT version(); (PostgreSQL) and SELECT VERSION(), @@version_comment; (MySQL). Record version strings. Expected results: PostgreSQL 16 or newer; MySQL 8.0 or newer (exact suffixes vary by installation).
2. List the database's tables three ways: \dt (psql), SHOW TABLES; (mysql), and the standard information_schema.tables query from Chapter 4. Confirm all three list the same six tables. Expected result: department, instructor, student, course, course_section, enrollment.
3. Run the TCL experiment of Section 8.8 verbatim and record all three counts. Expected results: 12 students untouched; enrollment counts 20 then 28.
4. Run the DQL examples of Section 8.6 and the DML examples of Section 8.5 wrapped in BEGIN ... ROLLBACK; verify the department count is 5 before and after. Expected result: department count 5 at both checks.
5. Demonstrate identifier versus string case rules: run SELECT full_name FROM STUDENT WHERE full_name = 'arif mahmud'; and explain the result, then fix the literal. Expected result: zero rows — identifiers are case-insensitive but 'arif mahmud' does not match the stored 'Arif Mahmud'.
6. Expressions and NULL: run SELECT full_name, gpa, gpa * 25 AS scaled FROM student WHERE student_id = 21600001; and explain the scaled column. Expected result: Zara Hossain, NULL gpa, NULL scaled — NULL propagates through arithmetic.

Review Questions and Exercises

1. Who created SQL's predecessor, in what year and project, and what does the acronym expand to? Chamberlin and Boyce, 1974, IBM System R; Structured English QUEry Language.
2. Which standard first made joins first-class, and which introduced window functions? SQL-92; SQL:2003.
3. Define "dialect" and give one PostgreSQL and one MySQL dialect feature from this chapter. *A vendor implementation of the standard plus extensions — e.g., PostgreSQL's || typed-literal style and MySQL's backquoted identifiers and CONCAT().*
4. Classify: TRUNCATE, MERGE, REVOKE, SAVEPOINT, SELECT. DDL (structure-removing), DML, DCL, TCL, DQL.
5. Why is a missing WHERE on UPDATE or DELETE called the classic SQL accident, and what two habits prevent it? It applies to every row; write the WHERE first, and run inside BEGIN ... ROLLBACK until verified.
6. Map SELECT/FROM/WHERE to relational algebra. The select list is π, FROM names operand relations, WHERE is σ.
7. What does DCL statement shape always name, and which principle governs its use? A privilege, an object, a principal; least privilege.
8. Explain literal versus identifier case sensitivity with one example each. *Identifiers: STUDENT and student are the same table; string literals: 'english' ≠ 'English' (a query returning zero rows).*
9. What is the value of 'Arif' || ' ' || 'Mahmud', and what is MySQL's spelling? 'Arif Mahmud'; CONCAT('Arif', ' ', 'Mahmud').
10. Write the DQL statement for the number of Fall 2026 sections.

    SELECT COUNT(*) AS fall_2026_sections
    FROM   course_section
    WHERE  semester = 'Fall' AND section_year = 2026;

    Expected output: 3.
11. Your GUI tool fails on a locked-down server but SSH works. Which client type saves you, and why is CLI fluency a professional differentiator? The command-line client over SSH; the terminal is nearly always available when GUI ports are blocked.
12. Predict the result of SELECT COUNT(*) FROM enrollment WHERE grade IS NULL; and justify it from the dataset's "now." 8 — sections 11, 12, 13 are running in Fall 2026 and their enrollments are ungraded.

Mini-Project

Write and run your first SQL script, first_steps.sql, containing: (1) a comment header with your name and date; (2) the DQL, DML-wrapped-in-rollback, and TCL examples of this chapter, each with a comment explaining its expected output; (3) three original SELECT questions about the university data with their answers as comments — one count, one filtered list, one expression (e.g., scaled budget); (4) verification queries at the end proving the database is unchanged (the six table counts: 5, 6, 12, 10, 13, 28). Run it in both clients — psql -f first_steps.sql and source first_steps.sql — and note in comments what differed (e.g., \dt versus SHOW TABLES). This script becomes your template for every chapter's laboratory from here to Chapter 30.