OverviewTeaching: 10 min
Exercises: 5 minQuestions
How can I get data from a database?Objectives
Explain the difference between a table, a record, and a field.
Explain the difference between a database and a database manager.
Write a query to select all values for specific fields from a single table.
A relational database is a way to store and manipulate information. Databases are arranged as tables. Each table has columns (also known as fields) that 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.
Changing Database Managers
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, like .csv, 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 of queries, but that handful accounts for most of what scientists do.
Getting Into and Out Of SQLite
In order to use the SQLite commands interactively, we need to enter into the SQLite console. So, open up a terminal, and run
$ cd /path/to/survey/data/ $ sqlite3 survey.db
The SQLite command is
sqlite3and you are telling SQLite to open up the
survey.db. You need to specify the
.dbfile otherwise, SQLite will open up a temporary, empty database.
To get out of SQLite, type out
.quit. For some terminals,
Ctrl-Dcan also work. If you forget any SQLite
.(dot) command, type
Before we get into the data and using SQLite to select the data,
The tables below show the database we will use in our examples:
Person: people who took readings.
Site: locations where readings were taken.
Visited: when readings were taken at specific sites.
Survey: the actual readings.
Notice that three entries — one in the
and two in the
Survey table — don’t contain any actual
data, but instead have a special
we’ll return to these missing values later.
Checking If Data is Available
On the shell command line, change the working directory to the one where you saved
survey.db. If you saved it at your Desktop you should use
$ cd Desktop $ ls | grep survey.db
If you get the same output, you can run
$ sqlite3 survey.db
SQLite version 3.8.8 2015-01-16 12:08:06 Enter ".help" for usage hints. sqlite>
that instructs SQLite to load the database in the
For a list of useful system commands, enter
All SQLite-specific commands are prefixed with a
.to distinguish them from SQL commands. Type
.tablesto list the tables in the database.
Person Site Survey Visited
You can change some SQLite settings to make the output easier to read. First, set the output mode to display left-aligned columns. Then turn on the display of column headers.
.mode column .header on
To exit SQLite and return to the shell command line, you can use either
let’s write an SQL query that displays scientists’ names.
We do this using the SQL command
giving it the names of the columns we want and the table we want them from.
Our query and its output look like this:
SELECT family, personal FROM Person;
The semicolon 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 names for the table and columns in lower case, but we don’t have to: as the example below shows, SQL is case insensitive.
SeLeCt FaMiLy, PeRsOnAl FrOm PeRsOn;
You can use SQL’s case insensitivity to your advantage. For instance,
some people choose to write SQL keywords (such as
in capital letters and field and table names in lower
case. This can make it easier to locate parts of an SQL statement. For
instance, you can scan the statement, quickly locate the prominent
FROM keyword and know the table name follows. Whatever casing
convention you choose, please be consistent: complex queries are hard
enough to read without the extra cognitive load of random
capitalization. One convention is to use UPPER CASE for SQL
statements, to distinguish them from tables and column names. This is
the convention that we will use for this lesson.
While we are on the topic of SQL’s syntax, one aspect of SQL’s syntax
that can frustrate novices and experts alike is forgetting to finish a
; (semicolon). When you press enter for a command
without adding the
; to the end, it can look something like this:
SELECT * FROM Person ...> ...>
This is SQL’s prompt, where it is waiting for additional commands or
; to let SQL know to finish. This is easy to fix! Just type
; and press enter!
Now, 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:
SELECT personal, family FROM Person;
or even repeat columns:
SELECT id, id, id FROM Person;
As a shortcut,
we can select all of the columns in a table using
SELECT * FROM Person;
Selecting Site Names
Write a query that selects only site names from the
Many people format queries as:
SELECT personal, family FROM person;
select Personal, Family from PERSON;
What style do you find easiest to read, and why?
A relational database stores information in tables, each of which has a fixed set of columns and a variable number of records.
A database manager is a program that manipulates information stored in a database.
We write queries in a specialized language called SQL to extract information from databases.
Use SELECT… FROM… to get values from a database table.
SQL is case-insensitive (but data is case-sensitive).