Overview

Structured Query Language (SQL) is a programming language for interacting with relational databases. This workshop covers basic SQL keywords to view, filter, aggregate, and combine tables in a database. SQL is supported by many different database management systems. We’ll focus on querying data to get to know a database and answer questions, and joining data from separate tables.

The examples in this workshop use a SQLite database, but most of the keywords are applicable to other database systems as well. The workshop also covers how to use SQLiteStudio, an integrated development environment for SQL code.

Learning Objectives

After this workshop learners should be able to:

  • Describe the advantages and disadvantages of using SQL with your own data.
  • Use SQL queries to view, filter, aggregate, and combine data.
  • Combine SQL keywords to develop sophisticated queries.
  • Use SQL queries to solve problems with and answer questions about data.
  • Identify additional resources for learning more about SQL, such as how to use SQL with the R programming language.

Prerequisites

No prior programming experience is necessary. We recommend learners either attend or review the written materials for DataLab’s Overview of Databases & Data Storage Technologies workshop.

Before the workshop, learners should:

  • Install SQLiteStudio using the install guide and verify that it runs.
  • Download the file 2024-10-24_library-data.sqlite from this link.

NOTE:
You must have a UC Davis account to access the data; contact Nick Ulle () if you have difficulty accessing the data.

NOTE:
This reader was developed with a slightly different version of the data set, so if you run the example code in the reader, you may get slightly different results.

NOTE:
If you have a Mac (OSX), you will need to right-click on the SQLiteStudio installer and select open. If you open the installer regularly, the Mac operating system will block the installer from running.

Please see these recommendations for making SQLiteStudio easier to read, particularly for those with low vision and those who use a screen reader.

Data Disclosure and Appropriate Use

The database in this lesson is based on a subset of data provisioned courtesy of the UC Davis Shields Library in 2024. All unique patron identifiers have been removed. Checkouts have been assigned a deidentified patron ID value. Use of these data is restricted to educational and operational purposes and is not intended for research. Patron groupings with fewer than 5 unique patron IDs have been lumped into broader categories to maintain privacy. If you have questions regarding the dataset and use, please contact us at datalab-training@ucdavis.edu or the Library’s Scholarly Communications Officer at mladisch@ucdavis.edu.

Microcredential Assessment

If you’re taking this workshop to complete a microcredential, you can download the assessment here.