Ask for a row, then change it

We have a table called products. Its columns include product_id, name, price, and stock. The application needs the name and price of product 27.

SQL is a language for expressing requests like that. A statement is one instruction written in the language. Read this statement from the table and condition toward the result:

SELECT name, price
FROM products
WHERE product_id = 27;

FROM products chooses the table. WHERE product_id = 27 keeps rows whose product number equals 27. SELECT name, price chooses which columns to return. The semicolon ends the statement.

With our example data, the result is one row:

Reading the row does not change it. SQL describes the result we want; the engine chooses how to find it. That choice will become the subject of our indexing and query-execution lessons.

Change one fact

The seller raises the price to 25 credits:

UPDATE products
SET price = 25
WHERE product_id = 27;

UPDATE identifies the table to change. SET supplies the new value. The condition selects which rows to change. If the condition were omitted, this statement would set the price of every product to 25.

An INSERT adds a row. A DELETE removes selected rows. Those operations must also respect the schema’s rules. We will examine their implementation after we can follow a read.

A condition is a test, not a position

WHERE stock = 0 means “keep rows whose stock equals zero.” It does not mean “go to storage location zero.” Without a structure that guides the search, the engine can inspect each row and test the condition. This is called a scan.

SQL also lets us request an order explicitly. Without an ORDER BY clause, we must not rely on a particular result order, even if repeated runs happen to return the same sequence. Storage and execution choices may change it.

Predict the result

After the price change, consider:

SELECT product_id
FROM products
WHERE price < 30
ORDER BY product_id;

Notebook 12 costs 8, tote 27 costs 25, and lamp 46 costs 35. What comes back?

Next we will connect a product to an order stored in another table. That connection motivates a join.

Further reading

PostgreSQL’s query tutorial explains selection and explicit result ordering. The examples here use a deliberately small SQL subset.