SQL Fundamentals

What is it? #

SQL is how you ask PostgreSQL for things. Four verbs do almost all the work: SELECT reads, INSERT adds, UPDATE changes and DELETE removes.

Everything else in this lesson refines those four — which rows (WHERE), in what order (ORDER BY), grouped how (GROUP BY), how many (LIMIT).

SQL is declarative. You describe the result you want, not the steps to get it. You never write "loop over every row and check" — you write the condition, and PostgreSQL decides how to find the rows. That is why the same query can get dramatically faster when you add an index, without changing a character of the SQL.

The one genuinely unusual idea is NULL, which means "unknown". It does not behave like zero or an empty string, and it surprises everybody once.

Think of it like this #

Think of a large filing cabinet and a very literal assistant.

SELECT ... WHERE city = 'Pune' is "bring me the sheets where the city field says Pune". You do not tell the assistant which drawer to open or in what order to search — that is their business. If they later install an index card system, they get faster and your instruction does not change.

NULL is a field left blank. Asked "is this blank field equal to Pune?", the honest answer is not "no" — it is "I cannot tell". That is exactly how SQL treats it.

Simple example #

An orders table holds customer orders with a total, a status and a date.

You want: the ten largest completed orders from this year, newest first; then the total revenue per status; then to mark one specific order as refunded. That is three queries covering most of what this lesson teaches.

Code #

-- ---------- Setup you can paste in ----------
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer    text    NOT NULL,
    city        text,                          -- may be NULL (unknown)
    total       numeric(10,2) NOT NULL,
    status      text    NOT NULL DEFAULT 'pending',
    placed_at   timestamptz NOT NULL DEFAULT now()
);

-- ---------- INSERT: adding rows ----------

INSERT INTO orders (customer, city, total, status)
VALUES ('Asha Verma', 'Pune', 450.00, 'completed');

-- Several rows in one statement (faster than several statements):
INSERT INTO orders (customer, city, total, status) VALUES
    ('Ravi Nair',  'Kochi',  120.00, 'completed'),
    ('Meera Iyer', NULL,     990.00, 'pending'),
    ('Sam Khan',   'Pune',    75.50, 'cancelled');

-- Get back what was actually stored, including generated values:
INSERT INTO orders (customer, total)
VALUES ('New Person', 200.00)
RETURNING id, placed_at;

-- ---------- SELECT: reading rows ----------

SELECT * FROM orders;                        -- every column, every row

SELECT customer, total FROM orders;          -- only the columns you need

-- WHERE filters rows
SELECT customer, total
FROM orders
WHERE status = 'completed' AND total > 100;

-- ORDER BY sorts; LIMIT caps the number of rows; OFFSET skips some
SELECT customer, total, placed_at
FROM orders
WHERE status = 'completed'
  AND placed_at >= date_trunc('year', now())
ORDER BY total DESC          -- DESC = largest first, ASC = smallest first (default)
LIMIT 10;                    -- the ten largest

-- OFFSET for simple paging (page 3, 10 per page):
SELECT customer FROM orders ORDER BY id LIMIT 10 OFFSET 20;

-- DISTINCT removes duplicate rows from the result
SELECT DISTINCT status FROM orders;

-- ---------- Useful WHERE conditions ----------

WHERE total BETWEEN 100 AND 500        -- inclusive on both ends
WHERE status IN ('pending', 'paid')    -- matches any in the list
WHERE customer LIKE 'A%'               -- starts with A  (case sensitive)
WHERE customer ILIKE 'a%'              -- same, case INsensitive (PostgreSQL extension)
WHERE city IS NULL                     -- unknown city  <- NOT  = NULL
WHERE city IS NOT NULL
WHERE placed_at >= now() - interval '7 days'

-- ---------- GROUP BY and HAVING: summarising ----------

-- One row per status, with a count and a sum
SELECT status,
       count(*)     AS order_count,
       sum(total)   AS revenue
FROM orders
GROUP BY status
ORDER BY revenue DESC;

-- WHERE filters rows BEFORE grouping.
-- HAVING filters groups AFTER grouping.
SELECT city, sum(total) AS revenue
FROM orders
WHERE status = 'completed'      -- drop non-completed rows first
GROUP BY city
HAVING sum(total) > 500         -- then keep only the big cities
ORDER BY revenue DESC;

-- ---------- NULL, COALESCE, CASE, CAST, aliases ----------

-- NULL means "unknown", so comparing with = never returns true:
SELECT NULL = NULL;        -- result: NULL, not true
SELECT city IS NULL FROM orders;   -- this is the correct test

-- COALESCE returns the first value that is not NULL:
SELECT customer,
       COALESCE(city, 'Unknown') AS city   -- "AS city" is an alias (a display name)
FROM orders;

-- CASE is an if/else inside a query:
SELECT customer,
       total,
       CASE
           WHEN total >= 500 THEN 'large'
           WHEN total >= 100 THEN 'medium'
           ELSE 'small'
       END AS size_band
FROM orders;

-- CAST converts a value from one type to another:
SELECT CAST('2026-01-31' AS date);
SELECT '42'::int;          -- PostgreSQL's shorter cast syntax

-- ---------- UPDATE and DELETE ----------
-- !! Both change data. Both take a WHERE clause.
-- !! WITHOUT a WHERE clause they affect EVERY ROW IN THE TABLE.

-- Step 1: ALWAYS run it as a SELECT first to see what you are about to touch.
SELECT id, customer, status FROM orders WHERE id = 3;

-- Step 2: then run the UPDATE with the identical WHERE clause.
UPDATE orders
SET status = 'refunded'
WHERE id = 3
RETURNING id, status;       -- RETURNING shows you what actually changed

-- DELETE follows exactly the same rule.
DELETE FROM orders WHERE id = 4;

-- DANGEROUS — deletes every row in the table:
-- DELETE FROM orders;

How it works #

PostgreSQL does not run a query in the order you write it. It filters with WHERE first, then groups, then applies HAVING, then sorts, then applies LIMIT. That ordering explains two things that otherwise look inconsistent.

First, why WHERE and HAVING both exist. WHERE runs before grouping, so it filters individual rows. HAVING runs after, so it filters the groups themselves. "Only completed orders" is a WHERE; "only cities with more than 500 in revenue" is a HAVING, because that total does not exist until the grouping has happened.

Second, why a column alias usually cannot be used in WHERE. The alias is assigned when the output is produced, which is after the filtering has already run.

GROUP BY status collapses all rows sharing a status into one output row. Anything else you select must therefore be either in the GROUP BY or wrapped in an aggregate such as count() or sum() — otherwise PostgreSQL has several possible values and no way to choose, and it will tell you so rather than guess.

NULL is the part to slow down on. It means unknown, so any comparison with it returns unknown rather than true or false. WHERE city = NULL matches nothing at all, even rows where the city really is empty — because "is this unknown value equal to this unknown value?" cannot be answered. IS NULL and IS NOT NULL are the only correct tests. Aggregates quietly skip NULLs too: count(city) counts rows where city is known, while count(*) counts all rows.

COALESCE(city, 'Unknown') returns the first argument that is not NULL, which is how you supply a fallback for display.

RETURNING is a genuinely useful PostgreSQL feature: it makes INSERT, UPDATE and DELETE report the rows they touched, so you can confirm you changed three rows and not three thousand.

Real-world use #

The habit that prevents most data accidents is simple and worth adopting permanently: write every UPDATE and DELETE as a SELECT first. Run the SELECT with the exact WHERE clause you intend to use, look at the rows, and only then change the verb. An UPDATE with a mistyped WHERE clause is indistinguishable from a correct one until it has already run.

On a production database, wrap risky changes in a transaction so you can undo them — that is the transactions lesson, and it pairs directly with this habit.

SELECT * is fine while exploring and a poor idea in application code. It fetches columns you do not need, and it silently changes meaning when someone adds a column later. Name the columns you actually use.

LIMIT with a large OFFSET gets slow, because the database still has to produce and discard all the skipped rows. Paging "page 5000" this way is a common cause of a mysteriously slow endpoint; keyset paging (WHERE id > :last_seen_id ORDER BY id LIMIT 20) stays fast.

ILIKE 'a%' is convenient but cannot use an ordinary index, so it degrades badly on large tables. The indexes lesson covers what to do instead.

Common mistakes #

• Running UPDATE or DELETE without a WHERE clause and changing every row in the table.
• Writing WHERE city = NULL, which matches nothing — the correct test is IS NULL.
• Confusing WHERE and HAVING: WHERE filters rows before grouping, HAVING filters groups after.
• Selecting a column that is neither grouped nor aggregated, then being confused by the error.
• Using SELECT * in application code, so adding a column silently changes what the code receives.

Practice #

Create the orders table above and insert at least eight rows, including two with a NULL city and a mix of statuses. Then write queries that answer: the five largest completed orders; revenue per city with unknown cities shown as "Unknown"; only those cities whose revenue exceeds some threshold; and a list of orders labelled small, medium or large. Finally, change one specific order's status — but write it as a SELECT first, and use RETURNING to confirm exactly one row changed.