--- title: "02 — Core CRUD Syntax: The Four Verbs of SQL" uid: core-crud-syntax tags: ["sql", "roadmap:sql", "insert", "select", "syntax", "delete", "data-types", "update"] excerpt: "SELECT, INSERT, UPDATE, DELETE — four verbs that cover most day-to-day SQL, each addressing a table by name and describing what to read back or what to change." date: 2026-08-13T03:27:38+0000 source: https://www.aveshina.my.id/en/blog/core-crud-syntax --- "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. ```figure ``` ## 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/](https://www.sqltutorial.org/) [2] Mode Analytics, "SQL Tutorial," mode.com, 2024. [Online]. Available: [https://mode.com/sql-tutorial/](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](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](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/](https://dataengineeracademy.com/blog/sql-operators-6-different-types-code-examples/) ```quiz Q: Which clause scopes which rows an UPDATE or DELETE affects? - WHERE - ORDER BY correct: 0 explain: WHERE filters rows. An UPDATE or DELETE with no WHERE operates on every row in the table, which is almost never intended. Q: Why include an explicit column list in an INSERT statement? - It's required syntax - Without it, the insert depends on column order and breaks if the table structure changes correct: 1 explain: Naming columns makes the insert robust to future ALTER TABLE changes; omitting it relies on positional ordering. Q: Which data type is the safe choice for storing monetary values? - REAL - DECIMAL correct: 1 explain: DECIMAL stores exact fixed-point numbers, avoiding the floating-point rounding errors that make REAL unsafe for money. Q: What does SELECT * do? - Returns only indexed columns - Returns every column from the matched rows correct: 1 explain: SELECT * returns all columns. Convenient for exploration, but in production it pulls bytes you may not need. Q: SQL keywords are… - case-sensitive and must be lowercase - case-insensitive, conventionally written uppercase for readability correct: 1 explain: Keywords are case-insensitive. Uppercase is a convention so the verbs stand out from column and table names. ```