SELECT, INSERT, UPDATE, and DELETE

≈ 3 min · beginner prerequisite

Objectives

By the end

SELECT describes which rows/columns to read; WHERE filters; ORDER BY makes sequence explicit.
INSERT adds rows; UPDATE/DELETE without intended WHERE conditions can affect far more data than expected.
RETURNING can provide changed rows in PostgreSQL.

SELECT, INSERT, UPDATE, and DELETE

Why this matters SELECT describes which rows/columns to read; WHERE filters; ORDER BY makes sequence explicit. This lesson is intentionally framework-light. You are building a mental model that will be reused when Next.js, NestJS, Prisma, TanStack Query, and deployment tooling add more vocabulary. The goal is not to memorize definitions. The goal is to predict what the computer will do and know where to look when reality differs from that prediction.

Mental model • SELECT describes which rows/columns to read; WHERE filters; ORDER BY makes sequence explicit. • INSERT adds rows; UPDATE/DELETE without intended WHERE conditions can affect far more data than expected. • RETURNING can provide changed rows in PostgreSQL. • Practice SQL directly so ORM-generated queries later remain understandable.

Step-by-step walkthrough 1. Write the concept in your own words before using a tool. If your explanation depends on framework jargon, simplify it one level. 2. Create the smallest runnable example that demonstrates only this concept. Keep it outside the main TaskFlow repository if experimentation could create noise. 3. Predict the result before running the command or clicking the UI. Prediction makes the output evidence instead of entertainment. 4. Run it and capture what actually happened: terminal output, browser state, request details, or file changes. 5. Change one assumption and repeat. Try an invalid input, wrong path, missing field, reordered data, or other safe failure relevant to the topic. 6. Explain the difference between the successful and failing runs. That explanation is the transferable skill.

TaskFlow connection Later in the course, databases and sql primer stops being an isolated topic and becomes infrastructure for real features. When you encounter select, insert, update, and delete again, ask which layer owns it, what its inputs are, what output proves success, and what failure evidence is available. That prevents you from copying framework snippets without understanding the underlying browser, runtime, operating-system, or language behavior.

Guided exercise Create a tiny example from scratch without copying the final code from a tutorial. Keep the example under about 30–50 lines where possible. After it works, deliberately break one assumption. Write the exact symptom and the smallest fix. Then delete the experiment or commit it in a dedicated practice folder so it does not pollute production project structure.

Check your understanding • Can you describe the concept without naming a framework? • Can you point to the exact input and output? • Can you name one common incorrect assumption? • Can you reproduce the idea in a tiny example? • Can you diagnose one intentional failure using evidence rather than guesses?

Instructor note Do not rush because the syntax looks easy. Most difficult full-stack bugs are not caused by forgetting syntax; they come from an incorrect model of ownership, execution environment, identity, time, or data flow. The foundation track exists so later lessons can move quickly without becoming mysterious.

Hands-on foundation studio

Predict → build → run → break → explain

Objectives

Use SELECT, INSERT, UPDATE, and DELETE in a runnable example rather than only describing it.
Predict a result before execution and compare prediction with evidence.
Introduce one safe failure and diagnose the cause systematically.

Setup

Create or reuse a disposable practice folder so experiments do not pollute the production TaskFlow tree.
Open the terminal and editor side-by-side. Keep browser DevTools visible for browser work.
Write the expected behavior in one sentence before adding code.

Do the work

01
Build the smallest runnable example for “SELECT, INSERT, UPDATE, and DELETE”, predict its output, change one assumption, and explain the difference using concrete evidence.
02
Run the smallest version first. Capture exact terminal/browser/database output before adding a second concept.
03
Change one input, ordering, state transition, or configuration assumption. Predict the new output, then run it.
04
Explain the result in terms of runtime behavior, data flow, identity, or ownership—not “the framework did it”.
05
Rebuild the example once without looking at the completed version.

sqlGuided practice: Create and query relational TaskFlow data

CREATE TABLE tasks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  done boolean NOT NULL DEFAULT false
);

INSERT INTO tasks (title) VALUES ('Learn SQL');
SELECT id, title, done FROM tasks ORDER BY id;

Type it, predict it, run it, then change one assumption. The goal is not finishing the snippet; the goal is being able to explain the result.

Expected evidence

The SQL executes from a clean schema.
You can show the exact rows before and after the statement.
At least one invalid operation is rejected for a reason you can explain.

Intentional failure

Deliberately violate one assumption relevant to “SELECT, INSERT, UPDATE, and DELETE”. Keep the failure small enough that you can identify a single causal change.

Explain it back

What is the simplest accurate explanation of SELECT, INSERT, UPDATE, and DELETE?
Which input/state is the source of truth?
What exact evidence distinguishes success from accidental success?
What failure did you reproduce and how did you isolate it?

Practice before completion

Small experiment, intentional failure, explain-back

Build the smallest example that demonstrates this lesson. Then break one relevant assumption and diagnose it using terminal/DevTools evidence. Finally explain the concept without using a framework name.

← Lab — Primary Keys, Foreign Keys, and Relationships Lab — SELECT, INSERT, UPDATE, and DELETE →