SQL, CRUD Operations

Two ways into the same idea

Required Fundamentals

SQL is a new domain, but three things you already know make the jump easier:

Core Concepts

SQL is declarative: you state what you want from the data, not how to fetch it step by step. And crucially, it works on sets of rows at once, not row after row like a Python for loop.

1The four statements

2Conditions: choosing rows

3Ordering and limiting

Without ORDER BY the row order is not guaranteed, even if it looks stable today. ORDER BY price_halalas DESC, name sorts by price descending and alphabetically on ties. LIMIT 3 takes the first three rows after sorting; LIMIT 3 OFFSET 3 skips the first three. In SQLite, NULL sorts as smaller than any other value.

4Aggregate functions and GROUP BY

An aggregate function collapses many rows into one value. With GROUP BY you get one output row per group.

5Constraints, NULL and types

NULL means “no value / unknown”, not zero and not an empty string. Any comparison with it yields NULL, not “true”: NULL = NULL is not true, which is why we write IS NULL. Aggregates ignore NULL except COUNT(*).

In SQLite types are flexible: a column’s declared type is a preference (type affinity), not a hard rule, so text may be stored in an INTEGER column if it cannot be converted. Tables declared STRICT (since SQLite 3.37.0) enforce types rigidly, but do not assume your grading environment has it. Always write logically correct SQL and never lean on the engine’s leniency.

How Do These Statements Actually Work?

The safe workflow for changing and deleting

Diagram 21, SELECT is the “preview” of the dangerous statement: what it returns is exactly what will be affected.

The logical order a SELECT runs in

Diagram 22, we write SELECT first, but logically it runs near the end: that is why WHERE cannot see aggregate results.

What happens inside an UPDATE?

1. The engine picks the rows where WHERE is true only (false and NULL are skipped).
2. For each chosen row it evaluates every SET expression on the old values.
3. It checks the constraints (NOT NULL, UNIQUE, CHECK…) on the new row.
4. On any violation the whole statement is cancelled (the default ABORT behavior) and nothing changes; otherwise the changes are kept.

A Complete Applied Example: a Café Drink Menu

We will build one table for a café’s drinks and take it through every operation. The domain is entirely different from your project’s data, so you practice applying the same approach yourself later.

Step 1, create the table with protective constraints

CREATE TABLE drinks (
    id            INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL UNIQUE,
    category      TEXT    NOT NULL CHECK (category IN ('hot', 'cold')),
    price_halalas INTEGER NOT NULL CHECK (price_halalas > 0),
    stock         INTEGER NOT NULL DEFAULT 0,
    note          TEXT
);

The price is stored in halalas (an integer), not as a decimal riyal amount: binary floating-point can introduce rounding errors in money.

Step 2, insert: one row, many rows, and omitted columns

INSERT INTO drinks (name, category, price_halalas, stock)
VALUES ('Flat White', 'hot', 1600, 40);

INSERT INTO drinks (name, category, price_halalas, stock, note)
VALUES ('Cold Brew',     'cold', 1800, 25, '12h steeped'),
       ('Spanish Latte', 'cold', 1950, 18, NULL),
       ('Cortado',       'hot',  1400, 30, NULL),
       ('Matcha',        'cold', 2100, 12, 'ceremonial grade');

INSERT INTO drinks (name, category, price_halalas)
VALUES ('Americano', 'hot', 1200);   -- stock omitted -> DEFAULT 0, note omitted -> NULL

Result: SELECT * FROM drinks ORDER BY id;

Notice Americano: we did not mention stock so it took 0 (the default), and we did not mention note so it became NULL. The id column filled itself in.

Step 3, read: condition, ordering and limit

SELECT name, price_halalas
FROM drinks
WHERE category = 'cold' AND stock >= 15
ORDER BY price_halalas DESC;

SELECT name, price_halalas
FROM drinks
ORDER BY price_halalas DESC
LIMIT 3;

Matcha is excluded from the first query because its stock is only 12. The second query sorts first and cuts afterwards, change the sort and the three rows change.

Step 4, update: preview, then execute

SELECT id, name, price_halalas
FROM drinks
WHERE category = 'cold';

UPDATE drinks
SET price_halalas = price_halalas + 100
WHERE category = 'cold';   -- 3 rows changed

Step 5, delete: preview first

SELECT id, name, stock FROM drinks WHERE stock = 0;

DELETE FROM drinks WHERE stock = 0;   -- 1 row removed

In many real systems a row is not physically removed but flagged with a column like is_active = 0 (a soft delete) so it can be recovered or audited; a hard delete is irreversible.

Step 6, summarize: aggregates and GROUP BY

SELECT category,
       COUNT(*)            AS n,
       AVG(price_halalas)  AS avg_price,
       MAX(price_halalas)  AS max_price,
       SUM(stock)          AS total_stock
FROM drinks
GROUP BY category
ORDER BY category;

SELECT COUNT(*) AS all_rows, COUNT(note) AS with_note
FROM drinks;

COUNT(*) counted all five rows, while COUNT(note) counted only two because the other notes are NULL.

SELECT category, COUNT(*) AS n
FROM drinks
GROUP BY category
HAVING COUNT(*) > 2;

Step 7, what do the constraints say when violated?

Step 8, from Python

import sqlite3

conn = sqlite3.connect("cafe.db")
try:
    with conn:   # commits on success, rolls back on any exception
        cur = conn.execute(
            "UPDATE drinks SET price_halalas = ? WHERE name = ?",
            (1700, "Flat White"),          # ? = value placeholder, never glue strings yourself
        )
        print("rows changed:", cur.rowcount)   # 1
except sqlite3.IntegrityError as err:
    print("rejected:", err)                    # e.g. CHECK constraint failed
finally:
    conn.close()

Myth vs. Reality

Where Are These Tools Actually Used?

Try it yourself

Quick Review Flashcards

Check Your Understanding