Summary and Schedule

This Library Carpentry lesson introduces librarians to relational database management system using SQLite. At the conclusion of the lesson you will: understand what SQLite does; use SQLite to summarise and link data.

	Setup Instructions	Download files required for the lesson
Duration: 00h 00m	1. Introduction to SQL	What is SQL? Why is it significant? What is the relationship between a relational database and SQL?
Duration: 00h 15m	2. Selecting and sorting data	What is a query? How do you query databases using SQL? How do you retrieve unique values in SQL? How do you sort results in SQL?
Duration: 00h 35m	3. Filtering	How can I filter data?
Duration: 01h 05m	4. Ordering and commenting	What is the order of execution in SQL queries? How can you organize and comment more complex SQL queries?
Duration: 01h 20m	5. Aggregating and calculating values	How can we aggregate values in SQL for reports? Can SQL be used to make calculations?
Duration: 01h 40m	6. Joins and aliases	How do I join two tables if they share a common point of information? How can I use aliases to improve my queries?
Duration: 02h 25m	7. Saving queries	How can I save a query for future use? How can I remove a saved query?
Duration: 02h 55m	8. Database design	What is database design?
Duration: 03h 40m	9. Creating tables and modifying data	How can I create, modify, and delete tables and data?
Duration: 04h 05m	10. Other database tools	Are there other database tools that I can use besides DB Browser and SqliteOnline?
Duration: 04h 10m	11. Extra challenges (optional)	Are there extra challenges to practice translating plain English queries to SQL queries?
Duration: 04h 45m	12. Good Style	What is good SQL Style, and how can I abide by SQL conventions?
Duration: 04h 55m	Finish

The actual schedule may vary slightly depending on the topics and exercises chosen by the instructor.

Software Requirements

---

DB Browser for SQLite

---

You will need to install DB Browser for SQLite to complete these lessons. DB Browser for SQLite provides a graphical user interface for connecting to and interacting with a SQLite database. This application bundles SQLite, so you won’t need to install SQLite separately.

Note: on Windows, the PortableApp download is recommended as the regular version may take a long time to install on certain systems.

Download the data

To import data, you’ll need to open DB Browser for SQLite and download a zip file containing the data files for this tutorial.

1. Download the data files doaj-article-sample.zip from Zenodo.
2. Open the zip file with the zip utility on your machine and save the folder and files to a location where you can easily find them. For example, your Desktop.
3. Contained in the zip file are two files, doaj-article-sample.db and doaj-article-sample.db.sql. You can either open the database file (less steps) or import the SQL file (more steps).

Open the database file

1. Open DB Browser for SQLite.
2. Choose “File” > “Open Database” from the menu bar at the top of your screen.
3. Navigate to where you saved the doaj-article-sample folder and/or files. For example, your Desktop.
4. Select “doaj-article-sample.db”.

Import the SQL file

1. Open DB Browser for SQLite.
2. Choose “File” > “Import” > “Database” from SQL file from the menu bar at the top of your screen.
3. Navigate to where you saved the doaj-article-sample folder and/or files. For example, your Desktop.
4. Select “doaj-article-sample.db.sql”.
5. You will be prompted to “Save As” (i.e. this is the name of the database).
6. Type “doaj-article-sample” in the “Save as” box.
7. Make sure that “SQLite database files” is selected in the drop down and that you save the database to a location where you can easily find it, again, like your Desktop.
8. Click “Save”.
9. You should see an “Executing SQL…” prompt and an “Import completed.” prompt when finished.
10. Click “OK”.
11. You will see one more prompt which says, “Do you want to save the changes made to the database file…”.
12. Click “Save”.

Alternatives: SQLite and SqliteOnline

---

SQLite

This step is optional. If you are completing the tutorial with DB Browser for SQLite, you won’t need to install SQLite separately. If you would like to run SQLite commands directly on the command line, you may need to install SQLite separately.

Linux and Mac OS x

SQLite command line tools come preinstalled on Linux and Mac OS x.

In order to check they are available type sqlite3 --version at the terminal command line. If this text displays, “Command ‘sqlite3’ not found, but can be installed with: sudo apt install sqlite3,” it means sqllite3 is not installed on the machine. To install sqlite3, type sudo apt install sqlite3. To exit type exit.

Windows

On Windows download the Windows Installer. Copy the file to a directory and open the directory using the windows command line. Type sqlite3.

For a more detailed explanation see this tutorial.

SqliteOnline

This step is optional. If you are completing the tutorial with DB Browser for SQLite, you won’t need to use SqliteOnline separately. If you are experiencing trouble with DB Browser for SQLite and/or SQLite or if you would like to run SQL commands online via a browser (nothing to install), then visit https://sqliteonline.com/.

Open the database file in SqliteOnline

1. Choose “File” > “Open DB” from the SqliteOnline menu bar.
2. Navigate to where you saved the doaj-article-sample folder and/or files. For example, your Desktop.
3. Select “doaj-article-sample.db”.

Open the SQL file in SqliteOnline

1. Choose “File >”Text-SQL” > “Open SQL” from the SqliteOnline menu bar.
2. Navigate to where you saved the doaj-article-sample folder and/or files. For example, your Desktop.
3. Select “doaj-article-sample.db.sql”.
4. You should see the SQL in a text box below the home icon.
5. Click the “Run” button in the SqliteOnline menu bar.