--- name: sql-basics-checks description: 15 rules from the Noesa course "SQL Basics, day by day". For beginners to databases — anyone who has written a little code or lived in spreadsheets and wants to query and shape real data. --- # SQL Basics, day by day — the rules Use with: Claude Code or Claude (save as a skill), Cursor (save under .cursor/rules as .mdc), ChatGPT or any other assistant (paste the text below into custom instructions or a project's instructions). 15 rules, taken from the course at https://noesa.leafsoft.online/c/sql-basics Each heading is one thing the course teaches. Most are checks to run on your own output before presenting it as done; a few are background you are expected to have. 15 also name a mistake models make by default, under "Watch for". Apply these to the thing you are producing — the type, the schema, the query, the copy — not only to how you explain it. Where a rule names a field, a format or an identifier, that name belongs in the output. ## Open the SQLite bench Install SQLite, create library.db, and run one query against your first table. **Watch for:** If you asked an AI for the commands to set up a local SQLite database, what could it not know? It hands you a snippet with a bare filename, because it cannot know which folder you will run it from. SQLite creates the file wherever the command runs, so the same command in a different directory quietly opens a second, empty database — which looks exactly like your data disappearing. Check the path before you conclude anything is lost. ## Name the table shape Explain tables, rows, columns, and the SQLite types used by `books`. **Watch for:** If you asked an AI what a table's `available` column means, what would it assume? That `1` means yes and `0` means no — because that is the common convention and the column's name invites it. But a flag's meaning is a local decision: the same column could as easily count copies currently out on loan. The schema records the type, never the intent. Confirm what the values mean against the data or a written definition before you trust a summary built on top of them. ## Choose columns with SELECT Write `SELECT` queries that return only the columns you need. **Watch for:** If you asked an AI to pull book data for a report, which columns would it hand you? Usually all of them, via `SELECT *` — the shortest query that satisfies "book data". It cannot know which few facts your report actually needs, so the shape of your answer becomes whatever the table happens to hold, and it changes silently the day someone adds a column. Name the columns yourself; that list is the part that carries your intent. ## Filter rows with WHERE Use `WHERE` to return rows that match a condition. **Watch for:** If you asked an AI for available fantasy or history books, which books could show up that you never asked for? The unavailable fantasy ones. Your sentence is ambiguous about what "available" applies to, and SQL binds `AND` tighter than `OR`, so the natural translation reads as fantasy books plus available history books. Nothing errors and every row looks plausible. Read the parentheses rather than the prose, and add them yourself whenever a filter mixes `AND` with `OR`. ## Sort and slice results Use `ORDER BY` and `LIMIT` to control result order and size. **Watch for:** If you asked an AI for the top three books in a table, what would it have to guess? What "top" measures. Nothing in the request says pages, year, or times borrowed, so it picks a plausible column — or returns `LIMIT 3` with no sort at all, which is three arbitrary rows wearing the word "top". Name the measure and the direction when you ask, then check that the `ORDER BY` actually contains it. ## Add rows with INSERT Insert new book rows and verify what changed. **Watch for:** If you asked an AI to add a book row, why might the row land wrong with no error at all? It tends to write the shortest insert — values in positional order, no column list — and SQLite is permissive about types, so a value in the wrong position is stored rather than rejected. The statement succeeds and the row is nonsense. Name the columns in the `INSERT`, then select the row back by id; that verification query is what turns a silent write into an observable one. ## Change rows safely Use `UPDATE` and `DELETE` with a `WHERE` clause and verify before committing to a change. **Watch for:** If you asked an AI to update one book's availability, what safety check could it miss? It may reach for the shortest valid update and omit the `WHERE`, or choose a broad filter when your request does not name the row precisely. Preview the target rows and inspect the exact `WHERE` before you run any generated mutation. ## Connect tables with keys Create `members` and `loans` tables and use ids to connect rows. **Watch for:** If you asked an AI to record which member borrowed which book, what shortcut would cost you later? Writing the member's name and the book's title straight into the loan row. It reads well immediately, and it is the shape a spreadsheet would take — which is exactly why it is the default to reach for. But the same fact now lives in two places, and a corrected name updates only one of them. Store the ids and join for the readable version. ## Join related rows Use `JOIN` to combine `books`, `members`, and `loans` into one readable result. **Watch for:** If you asked an AI to join loans to books and members, what relationship error could survive a successful query? It may infer the wrong matching columns from familiar names, or omit one relationship when your schema context is incomplete. The SQL can run and still pair unrelated rows, so name each relationship in words and check the result count against it. ## Summarize with aggregates Use `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX` to summarize rows. **Watch for:** If you asked an AI for the average page count, what would it not tell you about that number? Which rows it was computed over. `AVG` skips missing values, so the denominator is the rows that have a page count, not the rows in the table — and the answer arrives as one confident number with no note about what it left out. Ask for a `COUNT` beside every average; when the two disagree with the size of the table, missing data is inside your answer. ## Group rows by key Use `GROUP BY` and `HAVING` to summarize rows per category. **Watch for:** If you asked an AI for a report of books per genre, what could it misunderstand even when the SQL runs? It may choose the wrong grain because phrases such as "per genre" carry business meaning that a vague request leaves unstated. Check what one output row represents, which raw rows enter each group, and whether `HAVING` filters the finished groups you intended. ## Handle NULL on purpose Query `NULL` values with `IS NULL` and explain why normal equality does not work. **Watch for:** If you asked an AI to find loans that have not been returned, how could it return zero rows and no error? By testing `returned_on = NULL`, which is the shape your sentence suggests and the shape almost every other comparison takes. SQL then compares an unknown to an unknown, gets unknown rather than true, and filters out every row. An empty result is not evidence that nothing matched. Missing-ness is tested with `IS NULL` and `IS NOT NULL`, never with equals. ## Protect data with constraints Create a constrained `holds` table with primary keys, required values, checks, and foreign keys. **Watch for:** If you asked an AI to create the `holds` table, why might valid-looking foreign keys still fail to protect it? It may generate the common table definition without your SQLite connection context. The references look correct, but enforcement is off unless that connection enables foreign keys. Verify both the schema rule and the runtime setting that makes it active. ## Speed lookups with indexes Create an index and read a simple query plan that uses it. **Watch for:** If you asked an AI which columns to index, what crucial evidence would it lack? It cannot infer your real workload from the table shape alone. It may recommend familiar-looking indexes that add write cost but help no important query. Start from actual filters, joins, and sorts, then inspect the query plan to verify the route. ## Answer library questions Combine filtering, joining, grouping, `NULL` handling, and sorting to answer real library questions. **Watch for:** If you asked an AI to write a library report, what can be wrong even when every clause is valid? It may fill in missing business meaning with a common interpretation and answer a nearby question. State what one row should represent, which records qualify, and how ties are ordered; then read the generated query clause by clause against that contract.