What a database is and how a query works

TL;DR:

• A relational database stores data in tables made of rows (records) and columns (fields).

• A primary key is a column whose value is unique for every row, so each row can be found exactly.

• A SELECT query names the columns you want, the table, and a WHERE condition that filters the rows.

A school keeps a list of every student, their grade and their club. If it lived in a notebook, finding all the 10th graders in the robotics club would mean reading every line. A database lets you ask that as a single question and get exactly the matching rows. The language for asking is SQL.

The idea in plain words

A database is an organised collection of data that a program can search and update. In a relational database, data lives in tables. A table is like a spreadsheet. Each row is one record, such as one student. Each column is one field, such as the student's name, and has a type: text, number, date.

A primary key is a column, often called id, whose value is different for every row. Two students can share a name, but never an id. Because the key is unique, other tables can point to a row without confusion.

Why queries beat scanning

With a query you say what you want, not how to find it. The database decides how to search and can use indexes to do it fast. That is why the same database can serve a tiny app and a huge site: your question stays the same while the engine handles the work.

Worked example: a SELECT with a WHERE clause

Here is a small students table with five rows.

Suppose we want the name and grade of every robotics student in grade 10 or above.

SELECT name, grade
FROM students
WHERE club = 'Robotics' AND grade >= 10
ORDER BY name;

1. FROM students chooses the table.

2. WHERE club = 'Robotics' AND grade >= 10 keeps only the rows where both conditions are true.

3. SELECT name, grade chooses which columns to show.

4. ORDER BY name sorts the result alphabetically.

Check each row against the WHERE clause. Ava is in Robotics but is in grade 9, so she is out. Ben is in grade 10 but in Chess, so he is out. Chloe is Robotics and grade 10: in. Dev is Robotics and grade 11: in. Eli is in Chess and grade 9: out. The result is:

Two rows came back out of five. If you change AND to OR, a row only needs one condition, and the result would include Ava, Ben, Chloe and Dev, since each is either in Robotics or in grade 10 or above, and only Eli, who fails both, would be left out.

A second table and a join

Suppose a second table, clubs, lists each club and the room where it meets: Robotics meets in B12 and Chess in A3. The students table points to it through the club column. A join combines rows from the two tables where the values match.

SELECT students.name, clubs.room
FROM students
JOIN clubs ON students.club = clubs.name
WHERE students.grade >= 10
ORDER BY students.name;

The students in grade 10 or above are Ben, Chloe and Dev. Matching each to their club gives Ben in A3, Chloe in B12 and Dev in B12. The room is stored once, in the clubs table. If the Robotics room changes, you update one row, and every query sees the new room.

Why a key matters

Imagine the table had no id column and two students were both named Chloe in grade 10. A query that tries to change 'Chloe' would update both rows, and you could not tell the two apart. With ids, each row has a name that is never shared. This is why almost every table gets a primary key, and why other tables store the key, not the name, when they refer to a row.

Choose keys that never change. A name can change, and a grade changes every year, but an id given to the row stays the same for as long as the row exists.

Other things you can do

• SELECT * FROM students; returns every column and every row.

• SELECT COUNT(*) FROM students WHERE club = 'Robotics'; returns 3.

• INSERT INTO students (id, name, grade, club) VALUES (6, 'Fay', 9, 'Chess'); adds a row.

• UPDATE students SET grade = 11 WHERE id = 2; changes Ben's grade.

Always use WHERE with UPDATE and DELETE: Without a WHERE clause, UPDATE changes every row and DELETE removes every row. Run a SELECT with the same WHERE first to see which rows would be affected.

How to check your answer

• Go through the table row by row and apply the WHERE condition yourself.

• Check that you have the right columns, in the right order, in the result.

• Count the rows returned and compare with your hand count.

• Look at text values: they need single quotes, and they are compared exactly as written.

Common mistakes

• Using == instead of = in SQL. SQL uses a single equals sign for comparison.

• Forgetting the quotes around text, as in club = Robotics.

• Leaving out the WHERE clause on UPDATE or DELETE.

• Mixing up AND and OR.

Where you will use it next

Real databases link tables together using keys, for example a clubs table that students point to. Data from a database is what a web server often turns into the page you see: see how a web page gets to your screen. Finding the problem when a query returns the wrong rows is a skill of its own, covered in debugging a program methodically.

Frequently asked questions

Is SQL a programming language?

It is a query language: you describe the data you want rather than the steps to get it.

Do I need to install a database to practise?

Many tools and sites run SQL in your browser. Your teacher may recommend one.