Chapter 1: What is SQL?

Structured Query Language (SQL) is the standard language for describing, querying, and changing data in relational databases. It is declarative, which means you describe the result you want and the database engine decides how to produce it. With SQL you can define tables, state relationships, add and modify rows, and ask questions that combine and filter many rows at once. The goal is consistent, reliable data that many applications can share.

This chapter introduces the purpose of relational databases, the difference between declarative and procedural thinking, and how SQL fits inside a typical application stack. You will see the core ideas that guide everything that follows in later chapters.

Throughout the book you will see code blocks and small schemas. Names, sizes, and data are examples. Real projects will have different columns and constraints…

Purpose and scope of relational databases

Relational databases store data in tables that relate to each other by keys. Each table models a single subject (for example, customers or orders). Rows are individual records. Columns are attributes with fixed types. Keys and constraints preserve correctness, while transactions keep changes reliable even when many users work at the same time.

Relational basics

A table has a defined set of columns with types and constraints. A primary key uniquely identifies each row. Foreign keys relate rows between tables. Queries combine tables by matching key values and return a new table as the result set.

-- A tiny, portable demo schema
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total       NUMERIC NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

With this shape you can ask questions such as which customers placed orders, totals per customer, and which orders match given criteria.

Integrity and transactions

Constraints prevent invalid data from entering the system. Transactions group statements so they succeed or fail together. This produces consistent state, even with concurrent sessions and failures.

BEGIN;

INSERT INTO orders (order_id, customer_id, total)
VALUES (1001, 1, 49.99);

UPDATE customers
SET name = 'Robin N.'
WHERE customer_id = 1;

COMMIT;  -- Or ROLLBACK; to undo the whole unit

Declarative vs procedural thinking

Procedural code tells the computer step by step how to get a result. Declarative SQL states the result shape (columns, filters, groupings) and leaves the steps to the optimizer. Thinking in sets, rather than loops, is the key shift.

Set thinking in practice

Instead of looping through rows and testing conditions, write one statement that operates on the whole set. The database uses indexes and joins to find matches efficiently.

-- Declarative: return names of customers with orders over 50
SELECT c.name
FROM customers AS c
JOIN orders    AS o ON o.customer_id = c.customer_id
WHERE o.total > 50;

-- Procedural mindset (illustrative pseudocode)
for customer in customers:
  for order in orders:
    if order.customer_id == customer.customer_id and order.total > 50:
      emit(customer.name)

Both describe the same intent. The declarative version lets the engine choose an efficient plan.

The role of the optimizer

The optimizer examines your SELECT statement, available indexes, statistics, and constraints. It then chooses a strategy (join order, join method, index use) that should run quickly for the current data. You express filters and joins; the engine works out the path.

How SQL fits into application stacks

Applications call the database through a driver or library. The app sends statements such as SELECT, INSERT, UPDATE, and DELETE over a connection. The database parses and plans the statement, executes it, and returns a result set or a status.

Typical request flow

A common path is: UI or service layer → application code → database driver → database server → result set → application. Connection pooling keeps a small number of open connections that many requests can share.

-- Parameterized query (safe pattern)
SELECT name, total
FROM orders_view
WHERE customer_id = :customer_id
  AND total > :min_total;

Parameters avoid string concatenation and reduce the risk of injection. The driver binds values before execution.

Data definition and data manipulation

SQL statements fall into broad families. DDL (data definition) creates or changes schema objects. DML (data manipulation) reads or changes row data. DCL (access control) grants privileges. TCL (transaction control) manages COMMIT and ROLLBACK. You will use all of these across real projects.

With these foundations in place, you can begin to write clear, portable SQL that supports reliable applications at any scale.