SQL fundamentals

Why databases?

A backend must remember things: users, orders, products. Keeping them in memory loses everything on restart; writing files by hand breaks as soon as two requests write at once. A database stores data durably, lets many clients read and write safely at the same time, and answers questions about it quickly.

The most widely used kind is the relational database — PostgreSQL, SQL Server, MySQL, SQLite — which you talk to in SQL (Structured Query Language). SQL is one of the most valuable skills in backend development, and it's the same in every language.

Tables, rows and keys

Data lives in tables. Each column has a name and a type; each row is one record.

CREATE TABLE products (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  category  TEXT NOT NULL,
  price     REAL NOT NULL,
  stock     INTEGER NOT NULL
);

• The primary key (id) uniquely identifies each row.
• NOT NULL forces a value. Other constraints: UNIQUE, CHECK (price >= 0), DEFAULT.
• A foreign key points to another table's primary key: orders.customer_id REFERENCES customers(id). The database then refuses orders for customers that don't exist.

The sandbox below has a small shop: customers, products, orders and order_items. Click a table name to peek at its rows.

SELECT: reading data

SELECT name, price           -- which columns (* for all)
FROM products                -- which table
WHERE price < 100            -- which rows
ORDER BY price DESC          -- sort (ASC is the default)
LIMIT 5;                     -- at most 5 rows

Useful operators in WHERE:

Changing data

INSERT INTO products (name, category, price, stock)
VALUES ('Lamp', 'Furniture', 39.99, 12);
 
UPDATE products
SET price = price * 0.9
WHERE category = 'Furniture';
 
DELETE FROM orders
WHERE status = 'cancelled';

NULL

NULL means "no value / unknown". It's not zero and not an empty string, and comparisons with it are never true — NULL = NULL isn't true either. Use IS NULL / IS NOT NULL, and COALESCE(column, fallback) to substitute a default.

Practice

Solve the challenges — your query's result is compared with the expected one. You can always click Reset data if you change something by accident.

Key takeaways

• Relational databases store rows in tables with typed columns, primary keys and foreign keys.
• SELECT … FROM … WHERE … ORDER BY … LIMIT reads data.
• INSERT, UPDATE, DELETE change it — never forget the WHERE.
• NULL means unknown: use IS NULL, not = NULL.