Tables, SELECT and WHERE

This is lesson 0 of a series that starts from nothing. You do not need to know any SQL. By the end you will have PostgreSQL running on your machine, a small shop database to play with, and the core of everyday SQL. The same shop is used in every later lesson, so the examples stay small. For the big picture of how databases behave under an API, see Backend, Lesson 07. This series is the hands-on side: type the queries, read the results.

Words used in this lesson

• A relational database keeps data in tables and links tables with shared values (an order points to the customer who made it).
• A table is a grid about one kind of thing. A row is one item in it (one customer). A column is one property every row has (the name, the city).
• SQL (Structured Query Language) is the language almost every relational database understands. You describe what you want, and the database decides how to get it.
• PostgreSQL (Postgres) is a free, open-source relational database server. psql is its command-line client.
• A query is a SQL command that reads data. A statement is any SQL command.
• A data type says what a column holds: whole numbers, text, dates.
• NULL is the marker for "no value / unknown". It is not zero and not an empty string.
• A primary key is a column whose value is unique for every row. A constraint is a rule the database enforces, such as "cannot be empty" or "price must be zero or more".

Get PostgreSQL running

sudo apt install postgresql                       # Ubuntu / Debianbrew install postgresql@16 && brew services start postgresql@16   # macOSdocker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16   # Dockercreatedb shop && psql shop

(On Ubuntu use sudo -u postgres createdb shop and sudo -u postgres psql shop. With Docker use docker exec -it pg psql -U postgres and run CREATE DATABASE shop; first.) You get a prompt like shop=#. A statement ends with ;. If you forget it, the prompt changes to shop-#, which means "I am waiting for more".

Commands that start with a backslash are psql commands, not SQL:

Useful ones: \l lists databases, \c shop connects to one, \dt lists tables, \d products describes a table, \x shows one column per line (good for wide rows), \timing on prints statement times, \i setup.sql runs a file, \h SELECT shows SQL help, \q quits.

The shop we use all series

Four tables: customers (who buys), products (what we sell), orders (one row per purchase, pointing to a customer) and order_items (the lines in an order, pointing to an order and a product). Save this as setup.sql and run \i setup.sql. It is safe to run again: the first line drops the old tables, so it is your reset button for all lessons.

DROP TABLE IF EXISTS order_items, orders, products, customers CASCADE;CREATE TABLE customers (  id          integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  name        text NOT NULL,  email       text NOT NULL UNIQUE,  city        text,  referred_by integer REFERENCES customers (id));CREATE TABLE products (  id       integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  name     text NOT NULL,  category text NOT NULL,  price    numeric(8,2) NOT NULL CHECK (price >= 0),  stock    integer NOT NULL DEFAULT 0);CREATE TABLE orders (  id          integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  customer_id integer NOT NULL REFERENCES customers (id),  status      text NOT NULL DEFAULT 'new',  ordered_at  timestamp NOT NULL);CREATE TABLE order_items (  order_id   integer NOT NULL REFERENCES orders (id),  product_id integer NOT NULL REFERENCES products (id),  quantity   integer NOT NULL CHECK (quantity > 0),  unit_price numeric(8,2) NOT NULL,  PRIMARY KEY (order_id, product_id));INSERT INTO customers (name, email, city, referred_by) VALUES  ('Asha', 'asha@example.com', 'Pune',   NULL),  ('Ben',  'ben@example.com',  'London', 1),  ('Chen', 'chen@example.com', 'Pune',   1),  ('Dara', 'dara@example.com', 'Lagos',  2),  ('Eli',  'eli@example.com',  'London', NULL),  ('Fay',  'fay@example.com',  NULL,     NULL);INSERT INTO products (name, category, price, stock) VALUES  ('Notebook',   'stationery', 3.50,  120),  ('Pen',        'stationery', 1.20,  300),  ('Backpack',   'bags',       45.00,  15),  ('Desk lamp',  'home',       24.90,   0),  ('Headphones', 'audio',      59.00,   8),  ('Gift card',  'other',      10.00,   0);INSERT INTO orders (customer_id, status, ordered_at) VALUES  (1, 'paid',      '2025-01-05 10:30'),  (1, 'shipped',   '2025-02-11 15:00'),  (2, 'paid',      '2025-02-14 09:15'),  (3, 'cancelled', '2025-02-20 18:45'),  (3, 'shipped',   '2025-03-02 12:00'),  (1, 'paid',      '2025-03-15 20:10'),  (4, 'new',       '2025-03-28 08:05');INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES  (1, 1, 2, 3.50), (1, 2, 5, 1.20), (2, 5, 1, 59.00),  (3, 3, 1, 45.00), (3, 1, 1, 3.50), (4, 4, 1, 24.90),  (5, 2, 10, 1.20), (5, 1, 3, 3.50),  (6, 3, 1, 45.00), (6, 5, 1, 59.00), (6, 2, 2, 1.20),  (7, 1, 1, 3.50);

What to notice: GENERATED ALWAYS AS IDENTITY makes the database count ids for you (1, 2, 3 ...). city has no NOT NULL, so Fay has no city. CHECK and UNIQUE are constraints. REFERENCES makes a foreign key: orders.customer_id must be the id of a real customer (lesson 1 explains this). customers.referred_by points to another row of the same table: Ben and Chen were referred by Asha (id 1), Dara by Ben. Lesson 1 uses it for self joins. Run \dt to see the four tables.

Data types you need first

Why numeric for money? Binary floating point cannot store 0.1 exactly. (::float8 is a cast: it converts the value to that type.)

SELECT 0.1 + 0.2 AS exact, 0.1::float8 + 0.2::float8 AS approximate;

 exact |     approximate     -------+---------------------   0.3 | 0.30000000000000004

SELECT, WHERE, ORDER BY, LIMIT

SELECT chooses columns, FROM chooses the table, WHERE keeps only rows where the condition is true. * means all columns, but in real code name the columns you need.

SELECT name, city FROM customers WHERE city = 'Pune';SELECT name, price, stock FROM products WHERE category = 'stationery' AND stock > 100;

 name | city ------+------ Asha | Pune Chen | Pune(2 rows)   name   | price | stock ----------+-------+------- Notebook |  3.50 |   120 Pen      |  1.20 |   300(2 rows)

Text uses single quotes. Double quotes are for names of tables and columns. Operators are =, <> (not equal), <, <=, >, >=, combined with AND, OR, NOT. You can compute columns and name them with AS (an alias):

SELECT name, price, round(price * 1.2, 1) AS with_tax FROM products WHERE stock = 0;

   name    | price | with_tax -----------+-------+---------- Desk lamp | 24.90 |     29.9 Gift card | 10.00 |     12.0(2 rows)

Without ORDER BY, row order is not defined. DESC is largest first. LIMIT keeps some rows; OFFSET skips rows first, so this is "page 2" with page size 2:

SELECT name, price FROM products ORDER BY price DESC LIMIT 2 OFFSET 2;

   name    | price -----------+------- Desk lamp | 24.90 Gift card | 10.00(2 rows)

NULL and three-valued logic

NULL means unknown. Is an unknown city equal to 'Pune'? Unknown. Is it different from 'Pune'? Also unknown. So SQL has three truth values: true, false and unknown. WHERE keeps a row only when the condition is true. That is why = NULL never works:

SELECT name, city FROM customers WHERE city = NULL;SELECT name, city FROM customers WHERE city IS NULL;

 name | city ------+------(0 rows) name | city ------+------ Fay  | (1 row)

The second surprise: "not equal" also drops the NULL row.

SELECT name, city FROM customers WHERE city <> 'Pune';

 name |  city  ------+-------- Ben  | London Dara | Lagos Eli  | London(3 rows)

Fay is missing. The database cannot say she does not live in Pune, so her answer is "unknown". To include her, say it: WHERE city <> 'Pune' OR city IS NULL returns those three plus Fay. The whole truth table in one query (an empty cell is NULL):

SELECT NULL = NULL AS a, NULL IS NULL AS b, true AND NULL AS c,       false AND NULL AS d, true OR NULL AS e, NOT NULL::boolean AS f;

 a | b | c | d | e | f ---+---+---+---+---+---   | t |   | f | t | 

NULL = NULL is unknown (a). IS NULL is a real yes/no test (b). true AND unknown is unknown (c), but false AND unknown is false (d), because one false is enough. true OR unknown is true (e). NOT unknown is unknown (f).

Two helpers: coalesce(a, b, ...) returns the first value that is not NULL, and CASE WHEN ... THEN ... ELSE ... END is SQL's if/else.

For example coalesce(city, 'unknown') shows unknown for Fay, and CASE WHEN stock = 0 THEN 'sold out' ELSE 'ok' END labels a row by a rule.

In sorting, NULL comes last in ASC order and first in DESC. Use NULLS FIRST or NULLS LAST to choose.

DISTINCT, LIKE and ILIKE

DISTINCT removes repeated rows. It treats all NULLs as the same value, so Fay's missing city shows once (the blank last line):

SELECT DISTINCT city FROM customers ORDER BY city;

  city  -------- Lagos London Pune (4 rows)

LIKE matches a pattern: % is any run of characters (also none), _ is exactly one. LIKE is case sensitive (LIKE 'DESK%' finds nothing here). ILIKE ignores case (PostgreSQL only).

SELECT name FROM customers WHERE name LIKE '_e%';       -- second letter is e: BenSELECT name FROM products WHERE name ILIKE 'DESK%';     -- Desk lamp

 name ------ Ben(1 row)   name    ----------- Desk lamp(1 row)

IN and BETWEEN

IN checks a list (a short way to write several ORs). BETWEEN a AND b includes both ends.

SELECT name, city FROM customers WHERE city IN ('Pune', 'Lagos');

 name | city  ------+------- Asha | Pune Chen | Pune Dara | Lagos(3 rows)

SELECT 1 AS got_row WHERE 1 NOT IN (2, NULL);SELECT timestamp '2025-02-28 18:00' BETWEEN '2025-02-01' AND '2025-02-28' AS found;

 got_row ---------(0 rows) found ------- f

For dates and times, use a half-open range: ordered_at >= '2025-02-01' AND ordered_at < '2025-03-01' returns orders 2, 3 and 4 (start included, end excluded).

INSERT, UPDATE, DELETE and RETURNING

These three change data. Each can end with RETURNING, a PostgreSQL feature that gives back the rows it touched, so you do not need a second query.

INSERT INTO customers (name, email, city)VALUES ('Gus', 'gus@example.com', 'Pune') RETURNING id, name;UPDATE customers SET city = 'Delhi' WHERE name = 'Gus' RETURNING *;DELETE FROM customers WHERE name = 'Gus' RETURNING id, name;

 id | name ----+------  7 | Gus(1 row)INSERT 0 1 id | name |      email      | city  | referred_by ----+------+-----------------+-------+-------------  7 | Gus  | gus@example.com | Delhi |            (1 row)UPDATE 1 id | name ----+------  7 | Gus(1 row)DELETE 1

We did not give an id. The database made 7 and RETURNING told us. UPDATE can use the old value of a column, which is how you reduce stock: UPDATE products SET stock = stock - 1 WHERE name = 'Pen' RETURNING name, stock; returned Pen | 299.

Constraints are the database defending its own data. Three real errors, each from a rule in setup.sql:

INSERT INTO customers (name, email) VALUES ('Dup', 'asha@example.com');INSERT INTO products (name, category, price) VALUES ('Bad', 'x', -1);INSERT INTO customers (name) VALUES ('NoEmail');

ERROR:  duplicate key value violates unique constraint "customers_email_key"DETAIL:  Key (email)=(asha@example.com) already exists.ERROR:  new row for relation "products" violates check constraint "products_price_check"DETAIL:  Failing row contains (7, Bad, x, -1.00, 0).ERROR:  null value in column "email" of relation "customers" violates not-null constraintDETAIL:  Failing row contains (9, NoEmail, null, null, null).

Look at the ids in "Failing row": 7 and 9. A failed insert still used up a number from the counter, so identity columns can have gaps. An id is a label, not a count. To skip a row that already exists instead of failing, add ON CONFLICT (email) DO NOTHING; the result is INSERT 0 0 (zero rows inserted, no error).

Try it

Run setup.sql again for a clean start, then write:

1. Name and price of products that cost less than 10, cheapest first.
2. Customers whose city starts with L.
3. Customers whose city is not London, including those with no city.
4. The distinct order statuses, sorted.
5. Raise stationery prices by 10 percent and show the new prices with RETURNING.
6. Orders placed in March 2025.

Answers

1. SELECT name, price FROM products WHERE price < 10 ORDER BY price; gives Pen 1.20 and Notebook 3.50 (Gift card costs exactly 10.00, so it is out).
2. SELECT name, city FROM customers WHERE city LIKE 'L%'; gives Ben, Dara, Eli.
3. SELECT name, city FROM customers WHERE city <> 'London' OR city IS NULL; gives Asha, Chen, Dara, Fay.
4. SELECT DISTINCT status FROM orders ORDER BY status; gives cancelled, new, paid, shipped.
5. UPDATE products SET price = round(price * 1.1, 2) WHERE category = 'stationery' RETURNING name, price; gives Notebook 3.85, Pen 1.32. Rerun setup.sql to undo.
6. SELECT * FROM orders WHERE ordered_at >= '2025-03-01' AND ordered_at < '2025-04-01'; gives orders 5, 6, 7.

Interview questions

Key takeaways

• A relational database stores data in tables. SQL says what you want; the database picks how.
• Most queries are SELECT columns FROM table WHERE ... ORDER BY ... LIMIT .... Name columns, and add ORDER BY when order matters.
• NULL means unknown. Use IS NULL, expect <> to drop NULL rows, avoid NOT IN with NULLs.
• Use numeric for money, half-open ranges for time, single quotes for text.
• INSERT, UPDATE and DELETE can end with RETURNING. Check your WHERE first.
• Constraints are the database protecting its data. Let them say no.

What's next

Lesson 1: Joins, Combining Tables. Our data is split into four tables on purpose. Next you put an order together with its customer and its items, and see why a LEFT JOIN followed by a WHERE can quietly become an inner join. For Prisma and SQL interview questions see Full-Stack Q&A, Lesson 08.