Stop 06 · SQL DatabasesTopics · 2/14

SQL Basics (SELECT, INSERT, UPDATE, DELETE)

In short

Four commands do most of the work in any backend: SELECT, INSERT, UPDATE and DELETE. Get these right and you can build the data side of almost any app.

The four fundamental SQL operations for reading and writing data — the vocabulary you'll use constantly no matter which relational database or ORM you work with.

The blob writes in a kirana credit notebook behind a shop counter.

Think of it like this

The kirana khata

Sharma uncle's credit notebook works the same way. New customer? Write a line (INSERT). Who still owes money? Read down the page (SELECT). Paid half? Fix the amount (UPDATE). Moved away? Strike the line out (DELETE).

Watch it happen

A day in the khata table

API

PostgreSQL

INSERT INTO khata …

1/6A new customer, Aman, buys on credit. The API adds one row for him.

The big ideas

What you need to know

INSERT adds rows

List the columns, then the values. Leave out id and the database fills it in for you.

SELECT reads rows

Pick columns, filter with WHERE, sort with ORDER BY, cap with LIMIT. Ask only for what you need.

UPDATE changes rows

SET the new values, and always add WHERE. Without it, every single row changes.

DELETE removes rows

Same rule: WHERE first. Many teams soft-delete instead, by setting a deleted_at time.

See it in code

Walk through the code

sql

1INSERT INTO khata (name, due) VALUES ('Aman', 400);2 3SELECT name, due4FROM khata5WHERE due > 06ORDER BY due DESC7LIMIT 10;8 9UPDATE khata SET due = 200 WHERE id = 3;10 11DELETE FROM khata WHERE id = 3;

1/4Add Aman with 400 rupees due. The id is filled in for you.

Try these in psql or any online Postgres playground. Run your WHERE as a SELECT first to preview which rows you'd hit.

Save yourself the pain

Common mistakes

Oops

Running UPDATE khata SET due = 0; and forgetting the WHERE.

Do this instead

Write the WHERE first, or run the same filter as a SELECT to check the rows before changing them.

Oops

Using SELECT * everywhere in app code.

Do this instead

Name the columns you need. It sends less data and won't surprise you when someone adds a column.

Your mission

Run your own khata

~15 min

Quick check

What happens if you run UPDATE khata SET due = 0; with no WHERE?

Go deeper

1Practice each of the four operations against a local database with sample data.
2Learn `WHERE`, `ORDER BY` and `LIMIT` clauses alongside `SELECT`.
3Write a query combining filtering, sorting and pagination together.