Introduction to Databases — SQL Tutorial with Examples

What You'll Learn

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.

Syntax

-- Conceptual: no syntax yet
-- Table: Employees
-- ID (PK) | Name      | Department | Salary
-- 1       | Alice     | Eng        | 90000
-- 2       | Bob       | Sales      | 70000

Example

-- Primary Key: uniquely identifies a row
CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(100),
  department VARCHAR(50),
  salary     DECIMAL(10,2)
);

Beyond the Basics

Data in play — reference tables for this topic

-- 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-08

Constraints do the enforcing — not discipline

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');

Data-quality rules enforced by the database are the cheapest data quality you will ever get — no application code can be forgotten or bypassed.

Why department is an ID, not a string

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;

Facts should store identifiers; dimension tables should own names. Topic 15 (JOINs) is where this design pays off.

The sample data every topic builds on

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-08

Note Edsger: no department yet. He reappears in Topic 3 (WHERE), Topic 7 (NULL Handling), and Topic 15 (JOINs) — deliberately.

Common Mistakes

Confusing a Database with a Spreadsheet. Spreadsheets are for individuals; Databases handle millions of rows, multiple concurrent users, and enforce data rules (constraints, foreign keys).

Interview Tips

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.

Official References

Test Yourself — 7 questions

Self-check · 0/7 answered

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?

Practice

Return every column for every row in the employees table.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Why does every table need a Primary Key?

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.

Primary Key vs UNIQUE constraint — what's the difference?

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.