SQL Editor and Practice Lab

FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 08 SQL Editor and Practice Lab

VOLUME 03 - DATABASE ENGINEERING

SQL Editor and the Phase 07 Practice Lab Move the relational model from paper into a real database

The purpose of this lab is not to build TaskFlow production tables. It is to prove that relational rules survive outside the textbook.

Figure 1 - Lab loop

Write SQL → Run → Inspect

→ Change → Re-run

Database learning becomes durable when every concept is observed in a real server.

Volume 03 - Database Engineering

FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 08 SQL Editor and Practice Lab

Book 6 of Phase 08

Two equivalent learning paths SQL Editor psql Convenient in the dashboard, visually shows Exercises real client/server connectivity and is result sets, and avoids local connection closer to automated migration/tooling workflows. troubleshooting while learning SQL.

Use both. The SQL should remain PostgreSQL SQL in either tool.

Practice 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() );

What to inspect after creation • Table names appear in Table Editor and \dt . • Primary keys exist. • project_id has a foreign key. • priority has a check constraint. • timestamps have defaults.

Volume 03 - Database Engineering Book 6 of Phase 08

FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 08 SQL Editor and Practice Lab

Seed and query insert into practice_projects (name) values ('Course'), ('Personal');

insert into practice_tasks (project_id, title, priority) select id, 'Connect to Supabase', 1 from practice_projects where name = 'Course';

select p.name as project, t.title, t.priority, t.completed from practice_projects p join practice_tasks t on t.project_id = p.id order by t.priority, t.id;

Observe constraint behavior Try invalid operations intentionally in the practice tables:

-- should fail: priority outside allowed range insert into practice_tasks (project_id, title, priority) values (1, 'Invalid priority', 9);

-- should fail if project 999999 does not exist insert into practice_tasks (project_id, title) values (999999, 'Orphan task');

THE LESSON

Application validation improves user experience, but database constraints protect durable state even when data arrives through another tool or buggy code path.

Transaction exercise begin;

update practice_tasks set completed = true where title = 'Connect to Supabase';

select title, completed from practice_tasks;

rollback;

select title, completed from practice_tasks;

The second query should show the original value because the transaction was rolled back.

Volume 03 - Database Engineering Book 6 of Phase 08

FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 08 SQL Editor and Practice Lab

Clean up drop table if exists practice_tasks; drop table if exists practice_projects;

PRACTICE BOUNDARY

Do not evolve these temporary tables into the final application model. Phase 10 will design TaskFlow's real schema from requirements.

Official references used for this phase (verified 2026-09-17): Supabase Database Overview - https://supabase.com/docs/guides/database/overview Connect to your database - https://supabase.com/docs/guides/database/connecting-to-postgres Connecting with psql - https://supabase.com/docs/guides/database/psql Tables and data - https://supabase.com/docs/guides/database/tables Securing your data - https://supabase.com/docs/guides/database/secure-data PostgreSQL 18 psql - https://www.postgresql.org/docs/18/app-psql.html

Volume 03 - Database Engineering Book 6 of Phase 08