Reading and changing data with SQL

You can do four things to the rows of a database table: read them, add one, change one and delete one. Each has a command in a language called SQL.

In the enlistment system you type a course code into the search box. The list shows matching courses with their open slots. You press Enlist and the course joins your schedule. You change your section, then drop a course and it leaves your schedule. The database behind the screen runs one SQL command for each.

Search for a courseSELECT
Enlist in a courseINSERT
Change your sectionUPDATE
Drop a courseDELETE
Think first. Which of these four commands never changes the table, and which leave it in the same state when run twice?
Collect guesses before anyone opens the next step. Most students pick SELECT for the first question and are unsure about UPDATE and DELETE for the second.

Pick a statement and press Run. Then press Run a second time. A command that leaves the same end state when repeated is called idempotent. Try the two statements marked "no WHERE clause" last.

courses 3 rows
codetitleunitsslots
Pick a statement

A WHERE clause says which rows a command affects. Without it, UPDATE and DELETE apply to every row. The database does not ask for confirmation, and there is no undo. Write a SELECT with the same WHERE first, check the rows it returns, and then change the command.

More detail

UPDATE courses SET slots = 0 is valid SQL. It runs without error and sets every course in the school to zero slots, because the database cannot know you were thinking about one row.

Follow the same habit on production, the live system that real users depend on, for any data you cannot restore, even when you are sure.

Each SQL command matches an HTTP method, because REST was designed around the same four operations. The last column shows what happens when you run the command twice.

What you wantSQLHTTP methodIf you run it twice
Read some rowsSELECTGETchanges nothing, ever
Add a rowINSERTPOSTadds another row
Change a rowUPDATEPUT / PATCHsame end state
Remove a rowDELETEDELETEalready gone, still gone

You can now read a simple SQL command and say what it does when run twice.

More detail

The table in this step is the same one used in HTTP verbs and status codes, this time with no API in front of it. An API that exposes resources is easy to build on top of a relational database.

Your Django code, a Python web framework, eventually turns into one of these four commands. A tool called an ORM (object-relational mapper) writes them for you, so you rarely type them. You should still be able to read them, because when something is slow or wrong, the logs show SQL.