Summary and Schedule
Databases are useful for both storing and using data effectively. Using a relational database serves several purposes.
- It keeps your data separate from your analysis. This means there’s no risk of accidentally changing data when you analyze it.
- If we get new data we can rerun a query to find all the data that meets certain criteria.
- It’s fast, even for large amounts of data.
- It improves quality control of data entry (type constraints and use of forms in Access, Filemaker, etc.)
- The concepts of relational database querying are core to understanding how to do similar things using programming languages such as R or Python.
This lesson will teach you what relational databases are, how you can load data into them and how you can query databases to extract just the information that you need.
Getting Started
Data Carpentry’s teaching is hands-on, so participants are encouraged
to use their own computers to insure the proper setup of tools for an
efficient workflow.
These lessons assume no prior knowledge
of the skills or tools.
Prerequisites
This lesson requires:
- Working copy of DB Browser for SQLite for SQL
- A dataset
Follow the directions in the Setup section to download both the DB Browser for SQLite and the data to your computer and follow any installation instructions.
Please make sure to install everything before working through this lesson.
For Instructors
If you are teaching this lesson in a workshop, please see the Instructor notes.
| Setup Instructions | Download files required for the lesson | |
| Duration: 00h 00m | 1. Introducing Databases and SQL |
What is a relational database and why should I use it? What is SQL? |
| Duration: 01h 05m | 2. Accessing Data With Queries | How do I write a basic query in SQL? |
| Duration: 01h 40m | 3. Aggregating and Grouping Data |
How can I summarize my data by aggregating, filtering, or ordering query
results? How can I make sure column names from my queries make sense and aren’t too long? |
| Duration: 02h 40m | 4. Combining Data With Joins | How do I bring data together from separate tables? |
| Duration: 03h 05m | Finish |
The actual schedule may vary slightly depending on the topics and exercises chosen by the instructor.
Setup
Data
This lesson uses specific data files from the Portal Project Teaching Database. To get the data, go to the data location on figshare and click on the Download all button to download the zip file.
If you are working on this lesson on your own, you can move the zipped data file to anywhere on your computer where it is easy to access, and unzip the files. If you are working on this lesson as part of a workshop, your instructor may advise you to unzip the data files in a specific location.
See the Ecology Workshop Overview Page for more details about the data set.
Software
Download DB Browser for SQLite for your operating system from https://sqlitebrowser.org/dl/. DB Browser for SQLite is a visual tool to create, edit, and query SQLite databases. SQLite is included with DB Browser for SQLite, so it does not have to be installed separately.
- There are a few options for Windows, but most modern computers can
use the
Standard installer for 64-bit Windowsversion- The
.zip (no installer)version can be run directly from the folder, after extracting the contents of the zip file. It will not show up in theStartmenu.
- The
Launch DB Browser for SQLite to confirm that the installation was successful.
Am I ready?
The first lesson episode has instructions on loading the data in DB Browser. To test your setup, you can follow the instructions for importing your data into DB Browser under the sections Relational Databases or Import.