Relational model and your setup

What a relational database is, the keys that connect tables, and the server, client and sample data you need before writing real queries.

Also called table, row, column, relation, RDBMS, relational database

What: Data stored in tables: each table describes one kind of thing, each row is one instance of it and each column is one attribute with a fixed type. Tables are linked through shared key values rather than pointers.
Why: Because every fact has one obvious home, you can ask new questions of old data with SQL without changing how it is stored. That flexibility is why relational databases still run most business software.
Example: A shop has a customers table, a products table and an orders table. An order row stores customer_id 42 instead of copying the customer name, and a query joins them back together.

Watch out

Treating a table like a spreadsheet tab and packing several facts, such as a comma-separated list of products, into one cell.

Also called PK

What: A column, or set of columns, whose value is unique and never NULL for every row, so it identifies exactly one row. A table has one primary key.
Why: Updates, deletes and relationships all need a reliable way to point at one row. The database also builds a unique index on it, so lookups by key are fast.

Watch out

Using something that looks unique but can change or repeat, such as an email address or a person's name.

Also called FK, referential integrity

What: A column that stores the primary key of a row in another table, declared with a constraint so the database rejects values that do not exist there.
Why: It is how tables relate, and the constraint stops orphaned rows, such as an order for a customer who was never created or has been deleted.

SQL

CREATE TABLE orders (
  id          int PRIMARY KEY,
  customer_id int NOT NULL
    REFERENCES customers (id),
  placed_at   date NOT NULL
);

Watch out

Leaving the constraint off "for speed" and later finding thousands of rows that point at nothing.

Also called data definition language, data manipulation language, data control language, TCL, DDL, DML, DCL

What: The families of SQL statements: DDL defines structure (CREATE, ALTER, DROP), DML reads and changes rows (SELECT, INSERT, UPDATE, DELETE), DCL grants access (GRANT, REVOKE) and transaction control groups changes (COMMIT, ROLLBACK).
Why: Knowing which family a statement belongs to tells you its risk. A wrong SELECT wastes a second; a wrong DROP or UPDATE without WHERE can lose data.

Watch out

Assuming DDL can always be rolled back: MySQL commits implicitly on most DDL, while PostgreSQL and SQL Server can roll it back inside a transaction.

Also called psql, mysql client, sqlcmd, CLI

What: A terminal program that connects to the database server and runs SQL you type or pipe in: psql for PostgreSQL, mysql for MySQL, sqlcmd for SQL Server.
Why: Servers, containers and CI pipelines rarely have a GUI. Running scripts, restoring dumps and checking a production issue all happen in the terminal.

SQL

mysql -h 127.0.0.1 -u root -p shop
SHOW TABLES;
DESCRIBE orders;
SOURCE seed.sql;
EXIT;

Watch out

In sqlcmd, forgetting that nothing runs until you type GO on its own line.

Also called Workbench

What: The official graphical tool for MySQL, rebuilt in 2026 as MySQL Workbench 26 with a script editor, SQL notebooks, a visual EXPLAIN and administration screens.
Why: It gives you a schema browser, visual query plans and a place to keep scripts while you learn, next to the mysql command-line client.

Watch out

Following old tutorials for Workbench 8.0, which is end of life and looks different from the current version.

Also called pagila, sakila, AdventureWorks, WideWorldImporters

What: A ready-made database with realistic tables and thousands of rows that you load locally to practise on, such as pagila for PostgreSQL, sakila for MySQL or AdventureWorks for SQL Server.
Why: Toy tables with five rows hide the problems that matter: duplicates, NULLs, joins that multiply rows and queries that are slow at scale.

SQL

mysql -u root -p < sakila-schema.sql
mysql -u root -p < sakila-data.sql

Go deeper