For this lesson, we're going to use the library of the SQL Learners Academy as an example. Since the Academy is much more concerned about teaching SQL than proper cataloguing, its library catalogue is quite rudimentary. The catalogue is based on a few tables that are in relation to each other, enough to get the concept of relational databases across, and run some simple quries!

More importantly, the raw database is always available for download as an SQLite database file or as an SQL dump so that students can learn to tinker SQL.

A relational database is a way to store and manipulate information that is arranged as tables. Each table has columns (also known as fields) which describe the data, and rows (also known as records) which contain the data.

When we are using a spreadsheet, we put formulas into cells to calculate new values based on old ones. When we are using a database, we send commands (usually called queries) to a database manager: a program that manipulates the database for us. The database manager does whatever lookups and calculations the query specifies, returning the results in a tabular form that we can then use as a starting point for further queries.

Every database manager—Oracle, IBM DB2, PostgreSQL, MySQL, Microsoft Access, and SQLite—stores data in a different way, so a database created with one cannot be used directly by another. However, every database manager can import and export data in a variety of formats, so it is possible to move information from one to another.

Queries are written in a language called SQL, which stands for "Structured Query Language". SQL provides hundreds of different ways to analyze and recombine data; we will only look at a handful, but that handful accounts for most of what scientists do.

The tables below show the database we will use in our library example. Students using SQLite can download the database to try the queries along. Only the first 5 rows of each table are displayed here.

Works: simplistic bibliographical information for the works available in the library

Authors: the "authority file", containing information about the Authors of the Works

Works_Authors: The relationship between the Works and the Authors (more on that later)

Items: the actual copies of the Works owned by the library

Notice that some entries are shown in red because they don't contain any actual data: we'll return to these missing values later. For now, let's write an SQL query that displays the names of all authors present in the catalogue. We do this using the SQL command SELECT, giving it the names of the columns we want and the table we want them from. Our query and its output look like this:

The semi-colon at the end of the query tells the database manager that the query is complete and ready to run. We have written our commands in UPPER CASE, and the column and the table name in Title Case, but we don't have to: as the example below shows, SQL is case insensitive.

Whatever casing convention you choose, please be consistent: complex queries are hard enough to read without the extra cognitive load of random capitalization. An usual practice is to type SQL commands in UPPER CASE, but it's really up to you.

Note also the use of the LIMIT command in the above example. As the name implies, this limits output by only displaying the first 5 rows of data. This command can be notably useful when trying out queries to a large database, helping you make sure you're getting out what you're looking for without wasting time displaying the entire table on every attempt.

Going back to our query, it's important to understand that the rows and columns in a database table aren't actually stored in any particular order. They will always be displayed in some order, but we can control that in various ways. For example, we could swap the columns in the output by writing our query as:

or even repeat columns:

As a shortcut, we can select all of the columns in a table using *:

It may seem strange to use personal and family as field names instead of first and last, but it's a necessary first step toward handling cultural differences. For example, consider the following rules:

Clearly, even a two-part division into "personal" and "family" isn't enough...

Challenges

1. Write a query that selects only titles from the Works table.

2. Write a query that selects the first 10 barcodes and status from the Items table.

3. Many people format queries as:

   SELECT personal, family FROM authors;

   or as:

   select Personal, Family from AUTHORS;

   What style do you find easiest to read, and why?