PostgreSQL Fundamentals

-- Phase 07 practice schema - not the final TaskFlow production schema.

CREATE TABLE practice_projects (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE practice_tasks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id bigint NOT NULL REFERENCES practice_projects(id),
  title text NOT NULL,
  completed boolean NOT NULL DEFAULT false,
  priority smallint NOT NULL DEFAULT 2 CHECK (priority BETWEEN 1 AND 3),
  due_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT practice_tasks_title_not_blank CHECK (length(btrim(title)) > 0)
);

CREATE INDEX practice_tasks_project_completed_idx

INSERT INTO practice_projects (name)
VALUES ('Course'), ('Portfolio')
RETURNING id, name;

INSERT INTO practice_tasks (project_id, title, priority)
VALUES
  (1, 'Learn relational modeling', 1),
  (1, 'Practice joins', 2),
  (2, 'Build portfolio project', 1)
RETURNING id, project_id, title, priority;

SELECT id, title, completed, priority
FROM practice_tasks
ORDER BY id;

UPDATE practice_tasks
SET completed = true
WHERE id = 1
RETURNING id, title, completed;

SELECT
  t.id,
  t.title,
  t.priority,
  p.name AS project_name
FROM practice_tasks AS t
JOIN practice_projects AS p ON p.id = t.project_id
WHERE t.completed = false
ORDER BY t.priority, t.created_at;

SELECT
  p.id,
  p.name,
  count(t.id) FILTER (WHERE t.completed = false) AS open_tasks,
  count(t.id) FILTER (WHERE t.completed = true) AS done_tasks
FROM practice_projects AS p
LEFT JOIN practice_tasks AS t ON t.project_id = p.id
GROUP BY p.id, p.name
ORDER BY p.name;

BEGIN;

UPDATE practice_tasks
SET completed = true
WHERE id = 2
RETURNING id, title, completed;

-- Use COMMIT when the complete workflow is correct.
-- Use ROLLBACK while practicing if you want to discard the change.
ROLLBACK;

DROP TABLE IF EXISTS practice_tasks;
DROP TABLE IF EXISTS practice_projects;

Phase 07 SQL Practice

These scripts are educational examples for PostgreSQL fundamentals.
They are intentionally not the final TaskFlow schema.

Suggested order once you have a PostgreSQL database in Phase 08:
1. 00_practice_schema.sql
2. 01_seed_and_crud.sql
3. 02_queries_and_joins.sql
4. 03_transaction_practice.sql
5. 04_cleanup.sql (only when you intentionally want to remove the practice tables)

Do not run destructive SQL against a database you have not verified.