SQL Basics: Ask Questions of Your Data

A database stores data in tables, a lot like spreadsheet tabs. Each column holds one kind of fact (a name, an age), and each row is one record (one pet, one order, one customer).

SQL (Structured Query Language, said “S-Q-L” or “sequel”) is the language you use to ask a database questions. The same basics work in MySQL, PostgreSQL, SQLite, Microsoft SQL Server and most others, with small differences in the advanced stuff.

All the examples here use this small table called pets, from an imaginary animal shelter:

A query is a question. You don't tell the database how to find the answer, only what you want. “Give me the names of all cats in Austin” becomes one line of SQL.

Every query starts with SELECT (which columns) and FROM (which table).

SELECT name, species FROM pets;

• List columns with commas between them. The result shows them in that order.
• SELECT * means “every column.” Handy for a quick look, but in real work, name the columns you need.
• Keywords like SELECT aren't case-sensitive. Writing them in capitals is a common habit that makes queries easier to read.
• End with a semicolon ;. Many tools need it when you run several queries at once.

You can rename a column in the result with AS: SELECT name AS pet_name FROM pets;

WHERE keeps only the rows that match a condition.

SELECT name, age FROM pets WHERE species = 'cat';

SELECT name, species, age FROM pets
WHERE city = 'Austin' AND age < 5;

Text goes in single quotes: 'cat'. Without quotes, SQL thinks cat is a column name. Numbers don't need quotes. Also, in many databases 'Cat' and 'cat' aren't equal, so match the capitals in your data.

Mixing AND with OR? AND is checked first, like multiplication before addition. Use parentheses to be clear: WHERE (species = 'cat' OR species = 'dog') AND age > 8.

Rows come back in no guaranteed order unless you ask. ORDER BY sorts them: ASC is smallest first (the default), DESC is largest first. LIMIT keeps only the first few rows.

-- the 3 heaviest pets
SELECT name, weight FROM pets
ORDER BY weight DESC
LIMIT 3;

• Sort by more than one column with commas: ORDER BY city, age DESC sorts by city, then by age within each city.
• The clauses must go in this order: SELECT … FROM … WHERE … GROUP BY … ORDER BY … LIMIT.
• Lines starting with -- are comments.
• SQL Server uses SELECT TOP 3 … instead of LIMIT 3.

Aggregate functions squash many rows into one number:

SELECT COUNT(*) FROM pets WHERE species = 'dog';

GROUP BY splits the rows into groups first, then runs the function once per group. It's how you answer “how many per…” or “average for each…” questions.

SELECT species, COUNT(*) AS how_many, AVG(weight) AS avg_weight
FROM pets
GROUP BY species
ORDER BY how_many DESC;

The GROUP BY rule: every column in your SELECT must either be in the GROUP BY or be inside a function like COUNT. Otherwise the database doesn't know which row's value to show for the group. To filter groups after counting, real databases use HAVING, for example HAVING COUNT(*) > 2.

Type a query about the pets table and press Run (or Ctrl+Enter). Then try the challenges. This playground understands SELECT, FROM, WHERE (with AND, OR, NOT, LIKE and parentheses), GROUP BY, ORDER BY, LIMIT, AS and COUNT/SUM/AVG/MIN/MAX.

Real databases split data into several tables so nothing is stored twice. Say the shelter also has an adopters table, and each pet row stores an adopter_id:

A JOIN lines up rows from two tables wherever a column matches, here pets.adopter_id = adopters.id:

SELECT pets.name, adopters.adopter
FROM pets
JOIN adopters ON pets.adopter_id = adopters.id;

• A plain JOIN (also called INNER JOIN) keeps only rows with a match on both sides. Milo has no adopter, and Sam hasn't adopted anyone, so neither appears.
• LEFT JOIN keeps every row from the first table, with empty values where there's no match. Use it for “all pets, and their adopter if they have one.”
• Write table.column when both tables could have a column with the same name.

Why split tables? If Jordan changes their phone number, you update one row in adopters, not every pet they've adopted. This idea is called normalization.