SQL Overview

This chapter provides an overview of basic syntax and operations in SQLite, a version of Structured Query Language (SQL). It also provides an overview of connecting relational database workflows and SQL syntax in Python.

Acknowledgements

The author consulted the following resources when building out this chapter:

• W3 Schools “SQL Syntax”

• Library Carpentry “Database Design”

• Library Carpentry “SQL”

• Chapter 6, “Data Loading, Storate, and File Formats” section 6.4 “Interacting With Databases” (pp. 191-193) from Wes McKinney’s Python for Data Analysis: Data Wrangling With pandas, Numpy, and IPython (O’Reilly, 2017).

• David Muller, “How To Use the sqlite3 Module in Python 3” Digital Ocean (2 June 2020)

• Data Carpentries tutorial, “Data Analysis and Visualization in Python for Ecologists”

• Chapter 15 “Using Databases and SQL” (pp. 185-208) from Charles Severance’s Python for Everybody: Exploring Data in Python 3 (2016).

• Nik Piepenbreier, “Python SQLite Tutorial - The Ultimate Guide”, Towards Data Science (1 April 2020).

Peer review and editing was provided by Spring 2021 graduate teaching assistants Aidan Draper, Eric Tsai Sahoo & Subhadyuti Sahoo.

Chapter Table of Contents

• Background & Setup
• Basic Syntax
  • Selecting & Sorting
  • Filtering & Conditions
  • Subqueries & Wildcards
  • Aggregating & Calculating
• Joins & Views
• From SQL to Python
• Other Database Workflows
• Application

Data

We’ll be working with a relational database file that includes the players, teams, locations, and transactions tables. Download link options are included below.

• data.db

• data.sql

Software

Navigate to https://sqlitebrowser.org/ in a web browser. Select the download version for your operating system:

• Windows: OOP Coders, “Install DB Browser SQLite and create database” YouTube (2 September 2020)

• Mac: Cool IT Help, “Sqlite Browser Installation on Mac OS” YouTube (3 October 2019)

• Google Chromebook: Follow the instructions for the Debian varation of Linux.

  • See also: Dave McKay, “How to Use DB Browser for SQLite on Linux”, How-To Geek (16 September 2020)

Follow the installation instructions. Once the installation is complete, launch the program.

Application

Click here for a Jupyter Notebook template for this chapter’s application problems.

For questions that involve SQL queries, include your query in a code cell.