Introduction to SQL

SQL (Structured Query Language) is how you talk to a relational database. It is declarative: you describe the rows you want and the database works out how to fetch them. Every example in this series runs against PostgreSQL; the setup lesson gets you a server, and createdb sqldemo gives you a scratch database to run these files in.

Tables, Rows, and Keys

A relational database stores data in tables. This file creates two related tables and fills them with a few rows.

-- setup.sql
CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    city  TEXT NOT NULL
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    amount      NUMERIC(10, 2) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Alice', 'Berlin'), ('Bob', 'Paris'), ('Chen', 'Berlin');

INSERT INTO orders (customer_id, amount) VALUES
    (1, 120.00), (1, 35.50), (2, 80.00);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3

psql echoes one status line per statement. INSERT 0 3 means three rows were inserted; the 0 is a leftover field (an OID) that is always zero on modern PostgreSQL. SERIAL filled in id values 1, 2, 3 for you, and REFERENCES customers(id) makes customer_id a foreign key: every order must point at a customer that exists.

Describe the Result, Not the Steps

Here is the declarative idea in one query: ask for Berlin customers without saying how to search for them.

-- berlin.sql
SELECT name, city
FROM customers
WHERE city = 'Berlin';

$ psql -d sqldemo -f berlin.sql
 name  |  city  
-------+--------
 Alice | Berlin
 Chen  | Berlin
(2 rows)

There is no loop and no index lookup in the query. PostgreSQL chose how to scan the table; you only stated the condition. The (2 rows) footer is psql’s, not part of the data.

Keys Connect the Tables

Because each order stores only a customer_id, you rebuild the full picture with a JOIN.

-- join.sql
SELECT c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.amount DESC;

$ psql -d sqldemo -f join.sql
 name  | amount 
-------+--------
 Alice | 120.00
 Bob   |  80.00
 Alice |  35.50
(3 rows)

Alice’s name appears twice in the result but is stored once, in customers. If Alice changed her name, one UPDATE would fix every order at once. Storing each fact once and linking by key is the whole point of the relational model. Chen has no orders, so Chen does not appear at all; the joins lesson shows how to keep such rows.

The Database Enforces the Rules

Constraints are not comments. Try to insert an order for a customer who does not exist.

-- bad_order.sql
INSERT INTO orders (customer_id, amount) VALUES (99, 10.00);
SELECT count(*) AS orders FROM orders;

$ psql -d sqldemo -f bad_order.sql
psql:bad_order.sql:2: ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "customers".
 orders 
--------
      3
(1 row)

The foreign key rejected the row, and the count shows nothing was written. psql kept running the file after the error, which is why the SELECT still printed. An application that relied on “we’ll check it in the code” would have stored an order pointing at nobody.

Where SQL Fits

SQL is a standard (first published by ANSI in 1986), but every engine adds its own extensions. PostgreSQL is this series’ engine; MySQL, SQLite, SQL Server, and Oracle share the same core of SELECT, JOIN, GROUP BY, and transactions. Relational databases are the right default when your data has a known shape, relationships between entities matter, and you need correctness guarantees. Later lessons cover filtering, aggregation, joins, window functions, and reading query plans.

Practice

1. Add a fourth customer, Dana from Paris, and rerun berlin.sql changed to find Paris customers.

-- practice_paris.sql
INSERT INTO customers (name, city) VALUES ('Dana', 'Paris');
SELECT name, city FROM customers WHERE city = 'Paris' ORDER BY name;

$ psql -d sqldemo -f practice_paris.sql
INSERT 0 1
 name | city  
------+-------
 Bob  | Paris
 Dana | Paris
(2 rows)

2. Change join.sql so it lists only orders over 50, smallest first.

-- practice_join_filter.sql
SELECT c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.amount > 50
ORDER BY o.amount;

$ psql -d sqldemo -f practice_join_filter.sql
 name  | amount 
-------+--------
 Bob   |  80.00
 Alice | 120.00
(2 rows)

3. Try to insert a customer with no city. Which constraint stops it, and what does the error name?

-- practice_null_city.sql
INSERT INTO customers (name) VALUES ('Eve');

$ psql -d sqldemo -f practice_null_city.sql
psql:practice_null_city.sql:2: ERROR:  null value in column "city" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (5, Eve, null).