Loading…
Loading…
SQL tutorial · Beginner
⏱️ 10 mins read
A database is an organized collection of structured data stored electronically. Relational databases (RDBMS) store data in tables — grids of rows (records) and columns (fields). Tables are linked via Foreign Keys. A Primary Key uniquely identifies each row and cannot be NULL or duplicate. Popular RDBMS: MySQL, PostgreSQL, SQL Server, SQLite.
-- Conceptual: no syntax yet
-- Table: Employees
-- ID (PK) | Name | Department | Salary
-- 1 | Alice | Eng | 90000
-- 2 | Bob | Sales | 70000-- Primary Key: uniquely identifies a row
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2)
);| id | name | location |
|---|---|---|
| 1 | Engineering | Berlin |
| 2 | Sales | London |
| 3 | HR | Berlin |
| id | name | department_id | salary | hire_date |
|---|---|---|---|---|
| 1 | Ada | 1 | 145000 | 2021-03-01 |
| 2 | Grace | 1 | 115000 | 2022-06-13 |
| 3 | Alan | 2 | 92000 | 2020-11-02 |
| 4 | Edsger | NULL | 78000 | 2023-08-19 |
| 5 | Barbara | 3 | 64000 | 2024-01-08 |
A beginner stores data and hopes it stays correct. A well-designed database makes incorrect data impossible. PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, and CHECK are rules the engine itself enforces on every INSERT and UPDATE — long after the author has left the company.
-- The ShopCo schema this roadmap uses from here on
CREATE TABLE departments (
id INT PRIMARY KEY, -- no duplicates, no NULL
name VARCHAR(50) NOT NULL UNIQUE, -- two departments can't share a name
location VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department_id INT REFERENCES departments(id), -- FK: must exist in departments
salary DECIMAL(10,2) CHECK (salary >= 0),
hire_date DATE NOT NULL
);
-- The engine rejects this — department 99 does not exist:
-- INSERT INTO employees VALUES (1, 'Ada', 99, 90000, '2021-03-01');Storing 'Engineering' as text in every employee row invites trouble: misspellings ('Enginering'), case drift ('ENG' vs 'Eng'), and painful mass renames. Moving the name into its own table and referencing it by ID is the seed idea behind all of normalization.
-- Fragile: the name IS the identity
-- department column values: 'Eng', 'ENG', 'Enginering', 'Engineering' ...
-- Robust: the name lives in exactly ONE row; employees point at its id
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id;From the next topic onward, every example runs against this small ShopCo dataset. Keeping the same rows in your head makes each new concept concrete — and lets later topics deliberately reuse earlier edge cases.
-- departments
-- id | name | location
-- 1 | Engineering | Berlin
-- 2 | Sales | London
-- 3 | HR | Berlin
-- employees
-- id | name | department_id | salary | hire_date
-- 1 | Ada | 1 | 145000 | 2021-03-01
-- 2 | Grace | 1 | 115000 | 2022-06-13
-- 3 | Alan | 2 | 92000 | 2020-11-02
-- 4 | Edsger | NULL | 78000 | 2023-08-19
-- 5 | Barbara | 3 | 64000 | 2024-01-08Confusing a Database with a Spreadsheet. Spreadsheets are for individuals; Databases handle millions of rows, multiple concurrent users, and enforce data rules (constraints, foreign keys).
Always mention ACID properties when discussing why relational DBs are used. Explain that Primary Keys enable fast lookups via indexes. Be ready to design a simple schema on a whiteboard.
1. What two things does a PRIMARY KEY guarantee for each row?
2. How many PRIMARY KEYs can a single table have?
3. Why does ShopCo store department_id in employees instead of the department's name?
4. An employee row references department_id 7, but the departments table has no id 7. Which constraint would have prevented this row from ever being written?
5. Edsger's employees row has department_id = NULL. What does that NULL mean?
6. Which statement about databases vs spreadsheets is true?
7. A CHECK constraint and an application-side validation both reject bad input. What is the difference?
Return every column for every row in the employees table.
⚡ Solve it in the SQL playground →Without one, there is no reliable way to address a single row — updates, deletes, and deduplication all become guesses. The PK is also what other tables reference as a Foreign Key; it is the identity of the row.
Both prevent duplicates. A table has exactly one PRIMARY KEY (which also forbids NULL), but can have many UNIQUE constraints (which in standard SQL allow one NULL). Use UNIQUE for alternate identities like email, and the PK for the row's canonical identity.