FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 SQL CRUD, Filtering and Aggregates
SQL CRUD, Filtering and Aggregates Read and change rows with deliberate query semantics
Persistence becomes understandable when the database stops being magic.
understand the database before Prisma
Figure - Phase 07 isolates PostgreSQL fundamentals so later ORM code has a clear relational meaning.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 SQL CRUD, Filtering and Aggregates
CRUD in SQL is explicit data manipulation Our HTTP API uses GET, POST, PATCH and DELETE. The database has a different language. SQL commands describe data retrieval and changes. Prisma later translates typed method calls into SQL concepts like these.
INSERT - create persistent rows INSERT with RETURNING
INSERT INTO projects (name) VALUES ('Full Stack Course') RETURNING id, name;
RETURNING lets PostgreSQL return the inserted or modified rows without a second SELECT. This is useful for generated IDs and server-side defaults.
SELECT - ask a question Filtered and ordered query
SELECT id, title, completed FROM tasks WHERE project_id = 10 AND completed = false ORDER BY created_at DESC LIMIT 20;
FROM / JOIN which rows and relationships participate?
WHERE which rows survive filtering?
GROUP BY which rows become groups?
HAVING which groups survive?
SELECT which expressions are returned?
ORDER BY / LIMIT how is the final result shaped?
Figure - A useful conceptual order for reasoning about SELECT queries. SQL syntax is written in a different order, but this mental pipeline helps debugging.
UPDATE - change selected rows Targeted update
UPDATE tasks SET completed = true, completed_at = now() WHERE id = 42 RETURNING id, completed, completed_at;
DELETE - remove selected rows Targeted delete
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 SQL CRUD, Filtering and Aggregates
DELETE FROM tasks WHERE id = 42 RETURNING id;
UPDATE tasks SET completed = true; affects every row. DELETE FROM tasks; deletes every row. The absence of WHERE is valid SQL, which is why query review and transactions matter.
Filtering operators you will use constantly Pattern Example
Equality status = 'OPEN'
Range priority >= 2
Pattern matching title ILIKE '%postgres%'
Date/time comparison created_at >= now() - interval '7 days'
Aggregates answer questions across rows Aggregate query
SELECT project_id, count(*) AS open_task_count FROM tasks WHERE completed = false GROUP BY project_id HAVING count(*) >= 5 ORDER BY open_task_count DESC;
WHERE filters rows before grouping. GROUP BY creates groups. HAVING filters groups after aggregation. This distinction becomes important for dashboards and reports.
Select only what the caller needs During learning it is tempting to write SELECT * everywhere. Production queries are often clearer and more stable when they select the columns the caller actually needs. Explicit projections also make API contracts and performance tradeoffs more visible.
ORMs do not remove the database. SQL literacy lets you reason about correctness, constraints, query cost, transactions, and the evidence stored in PostgreSQL.
Restate the goal of “SQL CRUD Filtering and Aggregates” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: application intent → SQL statement → query planner → table/index → rows returned. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: A TaskFlow relational sketch plus SQL exercises that create, query, join, update, and constrain representative data.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
SELECT t.id, t.title, u.name AS assignee
FROM tasks AS t
LEFT JOIN users AS u ON u.id = t.assignee_id
WHERE t.workspace_id = $1
ORDER BY t.created_at DESC, t.id DESC;Do not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Capture EXPLAIN ANALYZE, row counts, filters, sort keys, and indexes before adding an index.
Inspect join cardinality and constraints; do not hide a modeling bug with DISTINCT until you understand it.
Without copying the final source, create a second small scenario that applies “SQL CRUD Filtering and Aggregates” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Add unique/check/not-null constraints and intentionally violate each to observe database enforcement.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Adding indexes without understanding query patterns
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.