Relational Databases & SQL Basics

Understand the relational model and write foundational SQL: CREATE TABLE, INSERT, SELECT, WHERE, ORDER BY, and basic JOINs.

Easy 55m 3 tasks

Prerequisites: REST APIs & JSON

Learning Objectives

→ Explain the relational model: tables, rows, columns, primary/foreign keys
→ Create tables with appropriate data types and constraints
→ Insert, update, and delete rows
→ Query data with SELECT, WHERE, ORDER BY, and LIMIT
→ Join two tables with INNER JOIN

The Relational Model

A relational database stores data in tables (relations):
- Row (tuple) — one record
- Column (attribute) — one field
- Primary Key — uniquely identifies each row
- Foreign Key — references a primary key in another table

users table                    scans table
┌────┬──────────┬───────────┐  ┌────┬─────────┬────────────┬─────────┐
│ id │ username │ email     │  │ id │ host    │ created_at │ user_id │
├────┼──────────┼───────────┤  ├────┼─────────┼────────────┼─────────┤
│  1 │ alice    │ a@mail.co │  │  1 │ 10.0.0.1│ 2025-01-01 │       1 │
│  2 │ bob      │ b@mail.co │  │  2 │ 10.0.0.2│ 2025-01-02 │       1 │
└────┴──────────┴───────────┘  └────┴─────────┴────────────┴─────────┘
                                              user_id FK → users.id

SQLite Quick Start

sqlite3 cyberlearn.db    # open / create database
.tables                  # list tables
.schema users            # show CREATE TABLE
.mode column             # pretty output
.headers on
.quit

CREATE TABLE

CREATE TABLE users (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    username   TEXT    NOT NULL UNIQUE,
    email      TEXT    NOT NULL UNIQUE,
    role       TEXT    NOT NULL DEFAULT 'student',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE scans (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    host       TEXT    NOT NULL,
    ports      TEXT,
    status     TEXT    NOT NULL DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_id    INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Common SQLite data types:
| Type | Use for |
|------|---------|
| INTEGER | Integers, booleans (0/1) |
| REAL | Floating point |
| TEXT | Strings, dates as ISO string |
| BLOB | Binary data |
| NULL | Missing value |

INSERT

-- Single row
INSERT INTO users (username, email, role)
VALUES ('alice', '[email protected]', 'admin');

-- Multiple rows
INSERT INTO users (username, email) VALUES
  ('bob',   '[email protected]'),
  ('carol', '[email protected]');

-- Insert from SELECT
INSERT INTO scans (host, user_id)
SELECT '10.0.0.1', id FROM users WHERE username = 'alice';

SELECT

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT username, email FROM users;

-- Computed column
SELECT username, LENGTH(email) AS email_length FROM users;

-- Distinct values
SELECT DISTINCT role FROM users;

WHERE

-- Equality
SELECT * FROM users WHERE role = 'admin';

-- Comparison
SELECT * FROM scans WHERE created_at > '2025-01-01';

-- Multiple conditions
SELECT * FROM users WHERE role = 'admin' AND created_at > '2025-01-01';

-- IN list
SELECT * FROM users WHERE role IN ('admin', 'instructor');

-- Pattern match
SELECT * FROM users WHERE username LIKE 'a%';   -- starts with a
SELECT * FROM users WHERE email LIKE '%@example.com';

-- NULL check
SELECT * FROM scans WHERE ports IS NULL;
SELECT * FROM scans WHERE ports IS NOT NULL;

ORDER BY & LIMIT

-- Order ascending (default)
SELECT * FROM users ORDER BY username;

-- Order descending
SELECT * FROM scans ORDER BY created_at DESC;

-- Multiple sort keys
SELECT * FROM users ORDER BY role ASC, username ASC;

-- Pagination
SELECT * FROM scans ORDER BY created_at DESC LIMIT 10 OFFSET 20;

UPDATE & DELETE

-- Update
UPDATE users SET role = 'admin' WHERE username = 'alice';
UPDATE scans SET status = 'complete' WHERE id = 1;

-- Delete specific rows
DELETE FROM scans WHERE status = 'failed' AND created_at < '2024-01-01';

-- Delete all (dangerous!)
DELETE FROM users;  -- no WHERE = deletes everything

-- Safer: check count first
SELECT COUNT(*) FROM users WHERE role = 'student';
DELETE FROM users WHERE role = 'student';

INNER JOIN

-- Get scans with their owner's username
SELECT s.id, s.host, s.status, u.username
FROM   scans s
INNER JOIN users u ON u.id = s.user_id
WHERE  u.role = 'admin'
ORDER  BY s.created_at DESC;

-- Alias tables for readability
SELECT
    u.username,
    COUNT(s.id) AS scan_count
FROM users u
INNER JOIN scans s ON s.user_id = u.id
GROUP BY u.id, u.username;

Constraints

NOT NULL        -- column must have a value
UNIQUE          -- no duplicates
PRIMARY KEY     -- NOT NULL + UNIQUE, identifies row
FOREIGN KEY     -- referential integrity
DEFAULT value   -- used when column omitted on INSERT
CHECK(expr)     -- validate values

-- Example
CREATE TABLE ports (
    id      INTEGER PRIMARY KEY,
    port    INTEGER NOT NULL CHECK(port BETWEEN 1 AND 65535),
    service TEXT
);

Open SQLite. Create the users and scans tables exactly as shown. Insert 3 users (alice/admin, bob/student, carol/instructor) and 4 scans (2 for alice, 1 for bob, 1 for carol). Verify with SELECT * FROM users; and SELECT * FROM scans;

✦ Answer the questions to complete this task

What SQL keyword prevents duplicate values in a column?

What constraint ensures a column always has a value?

Write SQL queries to: (1) list all admin users, (2) find scans created after '2025-01-01', (3) count scans per user, (4) find the 5 most recent pending scans.

✦ Answer the questions to complete this task

What clause filters rows?

What keyword removes duplicate rows from results?

Write a JOIN query that returns: username, host, status, created_at for all scans — joining users and scans. Filter to only show 'complete' scans for 'admin' users. Order by created_at DESC.

✦ Answer the questions to complete this task

What JOIN type returns only rows with matches in BOTH tables?

What syntax aliases a table in a query?