02 — Core CRUD Syntax: The Four Verbs of SQL

"I'll just look it up" was my CRUD strategy, which kept the four verbs permanently fuzzy. Writing them down consolidated one idea: SELECT, INSERT, UPDATE, and DELETE each address a table by name and describe either what to read back or what to change, using a small shared grammar of keywords, types, and operators. [1][2]

The framing that clicked is that SQL is a tiny language at its core. There are four operations on data, three building blocks (keywords, types, operators) they all share, and a couple of optional clauses (WHERE, ORDER BY) for shaping the result. Everything advanced later is a refinement of these same primitives.

The shared grammar: keywords, types, operators

Before the four verbs, the three building blocks they all share.

Keywords are the reserved words that give a statement its structure — SELECT, FROM, WHERE, INSERT INTO, VALUES, SET [3]. They're case-insensitive, but I write them uppercase by convention so the verbs stand out from my own column names. A statement is just keywords arranged in a fixed order with my values slotted into the gaps.

Data types define what each column can hold, which determines how values are stored, compared, and indexed [4]:

• Numeric — INTEGER, DECIMAL, REAL
• Character — CHAR(n), VARCHAR(n)
• Date/time — DATE, TIMESTAMP
• Boolean, binary (BLOB)

Picking the right type matters more than it looks: a DECIMAL for money avoids floating-point drift; a VARCHAR(255) caps storage and tells the engine how much room to plan for [4].

Operators combine and compare values inside the clauses [5]:

• Arithmetic — + - * /
• Comparison — = != < > <= >=
• Logical — AND OR NOT
• Set — UNION INTERSECT EXCEPT

Those three blocks — keywords for structure, types for what fits in a column, operators for expressing conditions — are the entire vocabulary the four verbs draw on.

SELECT — read rows back

SELECT retrieves rows from one or more tables. I name the columns I want, say which table, and optionally filter and sort [1]:

SELECT name, email
FROM customers
WHERE active = TRUE
ORDER BY name ASC;

SELECT * returns every column, which is convenient for exploration and costly in production — I name the columns I actually need. WHERE filters rows; ORDER BY sorts them. Most queries are variations on this one shape.

INSERT — add a new row

INSERT adds rows. I name the table and the columns I'm filling, then supply matching values [1]:

INSERT INTO customers (name, email, active)
VALUES ('Ave', 'ave@x.io', TRUE);

The column list is optional but I always include it — without it, INSERT depends on column order, and a future ALTER TABLE silently breaks every insert that relied on position. Multiple rows go in one statement:

INSERT INTO customers (name, email) VALUES
  ('Ave', 'ave@x.io'),
  ('Lin', 'lin@x.io');

UPDATE — change existing rows

UPDATE modifies rows already in the table. I name the table, set the new column values, and — critically — scope it with WHERE [1]:

UPDATE customers
SET active = FALSE
WHERE email = 'lin@x.io';

The golden rule: an UPDATE with no WHERE changes every row in the table. I treat a missing WHERE as a bug, not a shortcut.

DELETE — remove rows

DELETE removes rows, again scoped by WHERE [1]:

DELETE FROM customers
WHERE active = FALSE;

Same rule as UPDATE: no WHERE means every row goes. DELETE logs each row removal, which is safe but slow for bulk clears — that's where TRUNCATE (covered with DDL) earns its place.

How I use this

The habit I keep from these four verbs is the WHERE-first reflex. Before I run any UPDATE or DELETE, I write the WHERE clause and run it as a SELECT first — if the rows it returns are the rows I meant to touch, only then do I swap the verb. And for inserts I always name columns explicitly. Both habits exist to stop the one class of mistake that SQL won't save me from: the statement that succeeds perfectly against the wrong rows.

References

[1] SQLTutorial.org, "SQL Tutorial," 2024. [Online]. Available: https://www.sqltutorial.org/

[2] Mode Analytics, "SQL Tutorial," mode.com, 2024. [Online]. Available: https://mode.com/sql-tutorial/

[3] HubSpot, "SQL Keywords, Operators and Statements," blog.hubspot.com, 2023. [Online]. Available: https://blog.hubspot.com/website/sql-keywords-operators-statements

[4] DigitalOcean, "SQL Data Types," digitalocean.com, 2023. [Online]. Available: https://www.digitalocean.com/community/tutorials/sql-data-types

[5] Data Engineer Academy, "SQL Operators: 6 Different Types," dataengineeracademy.com, 2024. [Online]. Available: https://dataengineeracademy.com/blog/sql-operators-6-different-types-code-examples/