Environment, SQL Fundamentals & Your First Real Schema

Environment, SQL Fundamentals & Your First Real Schema

Step 1 — Set Up Your Environment

Option A: VS Code + Local PostgreSQL (recommended for learning)

1. Download PostgreSQL 16 from postgresql.org/download. Run the installer with default options. Remember the password you set for the postgres user.
2. In VS Code, install the "PostgreSQL" extension by Chris Kolkman (search "postgresql" in the Extensions panel).
3. Click the PostgreSQL icon in the sidebar. Click "Add Connection". Enter: host localhost, port 5432, user postgres, your password, database postgres.
4. Click Connect — you now have a live SQL editor connected to a real Postgres database.

Option B: Supabase (free cloud Postgres, no install needed)

1. Create a free project at supabase.com. Wait ~2 minutes for provisioning.
2. Install the same PostgreSQL VS Code extension. Your connection string is at: Settings → Database → Connection string (URI format).
3. Paste that connection string into the extension. Done — you have a cloud Postgres database.

Both options let you run SQL queries directly in VS Code. Write a query, press F5 (or click Run), see results instantly.

What IS a Relational Database? — First Principles

Before writing SQL, understand why relational databases exist.

The problem they solve: Imagine tracking student enrollments in a spreadsheet. If Jordan takes 10 courses, Jordan's name, email, and phone number appear 10 times across 10 rows. When Jordan's email changes, you update 10 rows — miss one and your data is inconsistent. This is data redundancy, and it causes update anomalies.

The relational solution: Separate data into tables and link them by reference. Jordan's info lives in exactly one row in a users table. Enrollments live in an enrollments table that says "user ID abc123 enrolled in course ID cc-postgres-m01." Jordan's email is stored once. Change it once and it's updated everywhere.

Tables = Spreadsheets + Rules

A table is like a spreadsheet where:

• Columns are typed fields: TEXT, INTEGER, BOOLEAN, UUID, TIMESTAMPTZ
• Rows are individual records
• Constraints enforce rules: NOT NULL (required field), UNIQUE (no duplicates), CHECK (must satisfy a condition)

Primary keys uniquely identify each row. Common patterns:

id UUID PRIMARY KEY DEFAULT gen_random_uuid()  -- modern pattern
id SERIAL PRIMARY KEY                           -- auto-increment integer

Foreign keys create relationships between tables. A foreign key column in one table points to the primary key column in another. The database enforces this — you cannot insert a row with a foreign key referencing a non-existent parent row.

Design a Real 3-Table Schema

A learning platform needs three tables: users (who), courses (what), and enrollments (who completed what). Here is the schema from scratch:

-- Create the foundation tables first (DDL)

CREATE TABLE users (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email        TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  total_xp     INT  NOT NULL DEFAULT 0 CHECK (total_xp >= 0),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE courses (
  id       TEXT PRIMARY KEY,           -- e.g. 'cc-postgres-m01'
  title    TEXT NOT NULL,
  track    TEXT NOT NULL,
  xp       INT  NOT NULL DEFAULT 0 CHECK (xp >= 0),
  duration INT  NOT NULL CHECK (duration > 0),
  level    TEXT NOT NULL CHECK (level IN ('Basic', 'Masters', 'PhD', 'Next-Gen AI')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Junction table — links users to courses
CREATE TABLE enrollments (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_id   TEXT NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
  xp_earned   INT  NOT NULL DEFAULT 0 CHECK (xp_earned >= 0),
  quiz_score  INT  CHECK (quiz_score BETWEEN 0 AND 100),
  completed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, course_id)    -- each user completes each course at most once
);

Why the constraints matter:

• REFERENCES users(id) ON DELETE CASCADE — delete a user and their enrollments are automatically removed
• REFERENCES courses(id) ON DELETE RESTRICT — prevents deleting a course that has enrollments
• UNIQUE (user_id, course_id) — composite unique constraint; prevents duplicate completions

The Four DML Operations

With the schema created, run the four CRUD operations:

-- INSERT — add rows. RETURNING gives back generated values immediately.
INSERT INTO users (email, display_name)
VALUES ('jordan@example.com', 'Jordan')
RETURNING id, created_at;

INSERT INTO courses (id, title, track, xp, duration, level)
VALUES ('cc-postgres-m01', 'SQL Fundamentals', 'crash', 150, 12, 'Basic');

-- Use the UUID returned by the first INSERT here
INSERT INTO enrollments (user_id, course_id, xp_earned, quiz_score)
VALUES ('your-uuid-from-above', 'cc-postgres-m01', 150, 92)
RETURNING id, completed_at;

-- SELECT — query data
SELECT * FROM users WHERE email = 'jordan@example.com';

SELECT * FROM courses
WHERE track = 'crash'
ORDER BY xp DESC
LIMIT 10;

-- UPDATE — always use WHERE or you update every row
UPDATE users
SET display_name = 'Jordan Morris'
WHERE id = 'your-uuid'
RETURNING id, display_name;

-- DELETE — always use WHERE
DELETE FROM enrollments
WHERE user_id = 'your-uuid' AND course_id = 'cc-postgres-m01'
RETURNING id;

The RETURNING Clause — PostgreSQL Superpower

Unlike other databases, PostgreSQL lets you get modified rows back immediately after INSERT/UPDATE/DELETE without a separate SELECT query:

-- Get the new user's auto-generated UUID instantly
INSERT INTO users (email, display_name)
VALUES ('alex@example.com', 'Alex')
RETURNING id;

-- Update and confirm what changed
UPDATE users
SET total_xp = total_xp + 150
WHERE email = 'alex@example.com'
RETURNING id, total_xp;

This pattern appears in every production Postgres app — create a parent record, get its ID, use that ID to create child records in one atomic flow.