SQL: Searching a Database

A program that holds a few dozen values can search them with a loop. A program that holds millions — every borrower in a library, every pupil in a school, every product in a shop — needs something faster to run and shorter to write. That is the job of SQL. One short instruction asks the database a question, and the database hands back only the data that answers it.

Records, fields and tables

Data in a database is held in a table. Picture the same grid you would build with a two-dimensional array. Each column is a field — one named category of data, such as title or year. Each row is a record — all the data about one item.

Every example below uses one small table called Books, taken from a school library system. It has six fields and five records.

Books
book_id | title               | author   | genre     | year | copies
1       | Noughts and Crosses | Blackman | Drama     | 2001 | 4
2       | The Hunger Games    | Collins  | Sci-Fi    | 2008 | 6
3       | Holes               | Sachar   | Adventure | 1998 | 3
4       | Wonder              | Palacio  | Drama     | 2012 | 5
5       | Skellig             | Almond   | Fantasy   | 1998 | 2

SELECT and FROM

Every search begins with two keywords. SELECT lists the fields you want to see, separated by commas. FROM names the table those fields come from. A semicolon marks the end of the query.

SELECT title, author
FROM Books;

That returns two columns but all five rows: it has chosen the fields and left every record in. To show every field instead of listing them, use a star. The star is a wildcard, and here it means ‘all fields’.

SELECT *
FROM Books;

WHERE: choosing the records

Most searches want some rows, not all of them. WHERE adds a condition. The database tests every record in turn and keeps only those for which the condition is true. The three keywords are always written in this order: SELECT, then FROM, then WHERE.

A condition compares a field with a value using one of six operators: equal to =, not equal to <>, less than <, greater than >, less than or equal to <=, and greater than or equal to >=. Text values sit inside single quotes; numbers do not.

SELECT title
FROM Books
WHERE genre = 'Drama';

SELECT title, year
FROM Books
WHERE year < 2000;

SELECT *
FROM Books
WHERE copies >= 5;

SELECT title
FROM Books
WHERE genre <> 'Drama';

Two conditions at once

A single WHERE clause can hold more than one condition, joined by AND or OR. AND keeps a record only when both conditions are true. OR keeps it when at least one is true, so OR always returns the same number of records or more.

SELECT title
FROM Books
WHERE genre = 'Drama' AND year > 2005;

Record 1 is a drama but was published in 2001, so it fails the second test and is dropped. Only Wonder is returned. Change AND to OR and the same query returns three titles instead: both dramas, plus The Hunger Games from 2008.

SELECT title
FROM Books
WHERE author LIKE 'S%';

Common mistakes

• Forgetting the single quotes around a text value, so WHERE genre = Drama is read as a comparison between two fields.
• Putting quotes around a number, which can stop a comparison such as year < 2000 working as intended.
• Writing the clauses out of order, for example putting WHERE before FROM.
• Naming a field that does not exist in the table given in the question, or misspelling one. Always copy field names straight from the table you are shown.
• Using > when the question says ‘at least’ or ‘five or more’. Those phrases need >=.