SQL

SQL (Structured Query Language) is how you talk to a relational database: you describe the data you want, or the change you want made, and the database works out how to do it. Learn a handful of keywords and you can read and change data in almost any relational database you will meet at work.

Data lives in tables

A relational database keeps its data in tables. A table is a grid with a fixed set of named columns, and each row is one record. In a shop's orders table, each row is one order and each column is one detail about it:

A real orders table would have a few more columns: an id that identifies each order, and an order_date. Every row has the same columns, and every column has a type. price holds numbers, customer holds text, order_date holds dates. The database checks those types when data goes in, which is part of why a table stays tidy over years of use.

Here is how that table might be created:

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  customer   TEXT NOT NULL,
  tuna       TEXT NOT NULL,
  price      NUMERIC(6, 2) NOT NULL,
  order_date DATE NOT NULL
);

PRIMARY KEY means no two rows can share an id, so each order can be found on its own. NOT NULL means the column must always have a value. These rules are called constraints, and the database enforces them on every write. A real shop would also keep its customers in a separate table and point to them from orders; how to split data up like that is what database normalisation is about.

Asking questions with SELECT

Reading data is a SELECT statement. The simplest one asks for everything in a table:

SELECT * FROM orders;

* means 'every column', and FROM orders names the table. The result is itself a small table: every row, every column.

In practice you usually name the columns you need:

SELECT customer, price FROM orders;

That returns only those two columns, so less data travels back to your code.

Filtering rows with WHERE

WHERE keeps only the rows that match a condition. To see only today's orders:

SELECT * FROM orders
WHERE order_date = CURRENT_DATE;

CURRENT_DATE is the database's own idea of today's date. A condition compares values with =, <> (not equal), < or >, and you can join conditions with AND and OR:

SELECT * FROM orders
WHERE tuna = 'Bluefin' AND price > 20;

Text values go in single quotes. In standard SQL, double quotes mark a name such as a column, not a value, so stick to 'Bluefin' for text.

Sorting with ORDER BY

Rows come back in no guaranteed order unless you ask for one. ORDER BY sorts them:

SELECT * FROM orders
WHERE order_date = CURRENT_DATE
ORDER BY price;

The sort is ascending by default, cheapest first. Add DESC to flip it, and LIMIT to keep only the first few rows:

SELECT customer, price FROM orders
ORDER BY price DESC
LIMIT 3;

That is the three most expensive orders. The clauses always appear in this order: SELECT, FROM, WHERE, ORDER BY, LIMIT. Put them in a different order and the database refuses the query.

Changing data

SQL does more than answer questions. Three more statements change what is stored.

INSERT adds a row:

INSERT INTO orders
  (id, customer, tuna, price, order_date)
VALUES
  (4, 'Paws', 'Albacore', 9.00, CURRENT_DATE);

UPDATE changes existing rows, and WHERE decides which ones:

UPDATE orders
SET price = 8.00
WHERE id = 4;

DELETE removes rows, again chosen by WHERE:

DELETE FROM orders
WHERE id = 4;

The WHERE clause matters most here. UPDATE orders SET price = 8.00 without one changes every order in the table, and DELETE FROM orders without one empties it. The database does exactly what you wrote, and does not ask whether you are sure.

When several changes must all happen or none of them should (take stock out, record the sale), wrap them in a transaction. BEGIN starts one, COMMIT saves every change in it together, and ROLLBACK undoes them all.

Why SQL feels strict

SQL is declarative: you say what result you want, not how to get it. WHERE order_date = CURRENT_DATE describes the rows you want, and the database's query planner picks the fastest way to find them, using an index if one exists. The same query keeps working when the table grows from ten rows to ten million; only the plan changes.

It is also strict. A misspelt column name or a clause in the wrong place stops the query with an error; the database never guesses what you meant. Types and constraints are checked on every write, so in PostgreSQL a price can't quietly become the word 'cheap'. Bad writes are refused, so the data you read back has already passed every rule.

One language, many databases

PostgreSQL, MySQL and Microsoft SQL Server all understand SQL, and so do SQLite, Oracle and many more. The core is standardised, so SELECT, WHERE, ORDER BY, INSERT, UPDATE and DELETE look the same everywhere.

Each database also adds its own extras and spellings, called its dialect. Limiting the number of rows is a good example. PostgreSQL, MySQL and SQLite use LIMIT 3 at the end of the query, while SQL Server writes SELECT TOP 3 at the start. Date functions and text functions vary too. Learn the shared core first; the dialect differences are quick to look up when you need them.

Not every database speaks SQL. Document and key-value databases, often grouped as NoSQL, use their own query styles. Relational databases are still the usual choice for data with a clear structure and links between records, like customers and their orders.

Common mistakes

• UPDATE or DELETE without WHERE changes or removes every row. Run the same WHERE in a SELECT first to see which rows it matches.
• Comparing with = NULL never matches, so WHERE price = NULL finds no rows. NULL means 'unknown', and nothing equals unknown, so write WHERE price IS NULL.
• Without ORDER BY, results can come back in a different order from one run to the next. Sort explicitly whenever the order matters.
• Gluing user input into a query string lets someone rewrite the query, which is called SQL injection. Pass input as parameters through your database library, so it is always treated as data.
• SELECT * is handy for exploring, but in application code, name the columns so the code keeps working when someone adds one to the table.

Key takeaways

• A relational database stores data in tables: each row is one record, each column one detail.
• SELECT ... FROM reads data, WHERE filters it and ORDER BY sorts it.
• INSERT, UPDATE and DELETE change data, and a missing WHERE affects every row.
• SQL is declarative and strict, which is what keeps the data reliable.
• PostgreSQL, MySQL and SQL Server share the same core SQL, with small dialect differences. Test yourself with the SQL quiz.