Tutorial: SQL

This tutorial will teach you the basics of SQL for querying relational databases.

Tutorial

This tutorial is based on the Library Carpentry SQL tutorial, licensed under CC-BY 4.0.

You can run this Jupyter notebook in Google Colab or on your local installation.

Note: In Colab, if the notebook is View Only, remember to make a copy, so that you can modify it:

• File –> Save a copy in Drive.

Attribution

Jordan Perdersen (Ed.), Kristin Lee (Ed.), Christopher Erdmann (Ed.), Lise Doucette (Ed.), Elaine Wong (Ed.), Janice Chan (Ed.), James Baker, Fernando Rios, Tim Dennis, Belinda Weaver, … orobecca. (2019, July). LibraryCarpentry/lc-sql: Library Carpentry: Introduction to SQL, June 2019 (Version v2019.06.1). Zenodo. http://doi.org/10.5281/zenodo.3266102

Modifications

Extracts modified to Jupyter notebook use by Stian Soiland-Reyes https://orcid.org/0000-0001-9842-9718.

References

• https://pythonspot.com/sqlite-database-with-pandas/
• https://pythoncourses.gumroad.com/l/KmxqY
• https://www.sqlitetutorial.net/
• https://wesmckinney.com/book/pandas-basics

import sqlite3

Data ingestion

In this tutorial we assume data has already been ingested and cleaned. We’ll retrieve the already prepared SQLite database.

SQLite is a miniature relational database that can be used without a server installation, for instance by directly importing the SQLite library in Python and “connecting” it to a local file. It is worth noting that this means only a single process can access the file at a time (in this case our Jupyter notebook).

Download SQLite database

The Zenodo deposit https://doi.org/10.5281/zenodo.8360812 contains a ZIP file with the pre-populated database in SQLite format. As this tutorial is not showing “proper” data ingestion, we’ll just download and unzip using Linux shell commands:

!wget https://zenodo.org/records/8360812/files/doaj-article-sample-db.zip && unzip doaj-article-sample-db.zip

--2025-07-08 15:12:03--  https://zenodo.org/records/8360812/files/doaj-article-sample-db.zip
Resolving zenodo.org (zenodo.org)... 188.185.45.92, 188.185.48.194, 188.185.43.25, ...
Connecting to zenodo.org (zenodo.org)|188.185.45.92|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 947007 (925K) [application/octet-stream]
Saving to: ‘doaj-article-sample-db.zip’

doaj-article-sample 100%[===================>] 924.81K   499KB/s    in 1.9s    

2025-07-08 15:12:06 (499 KB/s) - ‘doaj-article-sample-db.zip’ saved [947007/947007]

Archive:  doaj-article-sample-db.zip
  inflating: doaj-article-sample.db  
  inflating: doaj-article-sample.db.sql  

Now let’s connect to this database from within Python using the sqlite3 library, and see which tables are included. In SQLite this can be done by inspecting the sqlite_master table, using the SELECT * query . We’ll use pandas as a helper to better view the SQL results in Jupyter Notebook.

import sqlite3
import pandas as pd

db = sqlite3.connect("doaj-article-sample.db")
pd.read_sql_query("SELECT * FROM sqlite_master", db)

Introduction to SQL

In this tutorial we’ll follow the Library Carpentry guide, but run the queries from within Python instead of the DB Browser.

• Read through Section 1: Introduction to SQL (ignoring the DB Browser subsection).

Selecting and querying data

Next, follow the Section 2: Selecting and sorting data. Use Python’s """ strings to support multiple lines.

Note that you don’t need to use print(), as Jupyter Notebook will output the result of the last line, which with Panda becomes a HTML table:

pd.read_sql_query("""
SELECT title
FROM articles
""", db)

1001 rows × 1 columns

SQL results in Python

Above, Pandas’ DataFrame is rendered in Juyter Notebook, skipping most of the 1001 rows. Alternatively we can inspect the table returned in Python. This is beneficial in case you need to do further processing in code.

Note: You do not have to use Panda dataframes for accessing SQL databases from Python, see also the SQLite Python tutorial for accessing the database directly.

Below, the method .head() is used to only select the first couple lines of the dataframe, while .itertuples() iterates over each row in the SQL result. See the Getting started with pandas for further guidance on dataframes.

Warning: While SQL is generally case-insensitive, the database’s spelling of the column name will be returned, which in this case is Title (instead of title as in the query) – this matters below as Python code is case-sensitive.

articles = pd.read_sql_query("""
SELECT Title
FROM articles
""", db)
for article in articles.head().itertuples():
  print(article.Title)

The Fisher Thermodynamics of Quasi-Probabilities
Aflatoxin Contamination of the Milk Supply: A Pakistan Perspective
Metagenomic Analysis of Upwelling-Affected Brazilian Coastal Seawater Reveals Sequence Domains of Type I PKS and Modular NRPS
Synthesis and Reactivity of a Cerium(III) Scorpionate Complex Containing a Redox Non-Innocent 2,2′-Bipyridine Ligand
Performance and Uncertainty Evaluation of Snow Models on Snowmelt Flow Simulations over a Nordic Catchment (Mistassibi, Canada)

Tip: You can modify the code of Jupyter notebook, and click each cell’s ▶️ button to run them. Use the [+ Code] button in Colab to add further code blocks. Use one code block per output.

Errors

Before we continue, let’s look quickly at different errors in the SQL, which will cause a Python exception.

Syntax error

The Python code is valid, but the SQL inside has a typo (SELET instead of SELECT):

pd.read_sql_query("""
SELET title
FROM articles
""", db)

---------------------------------------------------------------------------

OperationalError                          Traceback (most recent call last)

/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in execute(self, sql, params)
   2673         try:
-> 2674             cur.execute(sql, *args)
   2675             return cur


OperationalError: near "SELET": syntax error


The above exception was the direct cause of the following exception:


DatabaseError                             Traceback (most recent call last)

/tmp/ipython-input-22-3013307109.py in <cell line: 0>()
----> 1 pd.read_sql_query("""
      2 SELET title
      3 FROM articles
      4 """, db)


/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
    524 
    525     with pandasSQL_builder(con) as pandas_sql:
--> 526         return pandas_sql.read_query(
    527             sql,
    528             index_col=index_col,


/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)
   2736         dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
   2737     ) -> DataFrame | Iterator[DataFrame]:
-> 2738         cursor = self.execute(sql, params)
   2739         columns = [col_desc[0] for col_desc in cursor.description]
   2740 


/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in execute(self, sql, params)
   2684 
   2685             ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
-> 2686             raise ex from exc
   2687 
   2688     @staticmethod


DatabaseError: Execution failed on sql '
SELET title
FROM articles
': near "SELET": syntax error

Semantic error

Here the query is correct syntactically, but it’s referencing a column that does not exist:

pd.read_sql_query("""
SELECT wrongColumn
FROM articles
""", db)

---------------------------------------------------------------------------

OperationalError                          Traceback (most recent call last)

/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in execute(self, sql, params)
   2673         try:
-> 2674             cur.execute(sql, *args)
   2675             return cur


OperationalError: no such column: wrongColumn


The above exception was the direct cause of the following exception:


DatabaseError                             Traceback (most recent call last)

/tmp/ipython-input-23-818033597.py in <cell line: 0>()
----> 1 pd.read_sql_query("""
      2 SELECT wrongColumn
      3 FROM articles
      4 """, db)


/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
    524 
    525     with pandasSQL_builder(con) as pandas_sql:
--> 526         return pandas_sql.read_query(
    527             sql,
    528             index_col=index_col,


/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)
   2736         dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
   2737     ) -> DataFrame | Iterator[DataFrame]:
-> 2738         cursor = self.execute(sql, params)
   2739         columns = [col_desc[0] for col_desc in cursor.description]
   2740 


/usr/local/lib/python3.11/dist-packages/pandas/io/sql.py in execute(self, sql, params)
   2684 
   2685             ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
-> 2686             raise ex from exc
   2687 
   2688     @staticmethod


DatabaseError: Execution failed on sql '
SELECT wrongColumn
FROM articles
': no such column: wrongColumn

• First, try to correct the errors above, then re-execute the cells.

Next, follow the rest of section 2 onwards to the remaining SQL selection exercises.

Capitalization and good style

Follow exercise on good style below:

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

Unique values

Follow exercise on unique values

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

Sorting

Follow exercise on sorting

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

Filtering

Follow exercies on filtering

pd.read_sql_query("""
SELECT *
FROM articles
WHERE ISSNs = '2056-9890'
""", db).head()

Note that in SQL, value comparisons is done with a single equal sign =, unlike Python and other programing languagues typical use of == or .equals

pd.read_sql_query("""
SELECT ...
""", db).head()

pd.read_sql_query("""
SELECT ...
""", db).head()

pd.read_sql_query("""
SELECT ...
""", db).head()

pd.read_sql_query("""
SELECT ...
""", db).head()

Ordering and commenting

Follow exercises on Ordering and commenting

pd.read_sql_query("""
SELECT Title, Authors
FROM articles
WHERE ISSNs = '2067-2764|2247-6202'
ORDER BY First_Author ASC;
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

Aggregating values

Follow exercise on Aggregating and calculating values

pd.read_sql_query("""
SELECT ISSNs, AVG(Citation_Count)
FROM articles
GROUP BY ISSNs;
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

pd.read_sql_query("""
SELECT ...
""", db)

Joins and aliases

Follow exercises on Joins and aliases

pd.read_sql_query("""
SELECT *
FROM articles
JOIN journals
ON articles.ISSNs = journals.ISSNs;
""", db).head()

5 rows × 21 columns

pd.read_sql_query("""
SELECT ...
""", db).head()

pd.read_sql_query("""
SELECT ...
""", db).head()

pd.read_sql_query("""
SELECT ...
""", db).head()

pd.read_sql_query("""
SELECT ...
""", db).head()

Aliases

Follow exercises on aliases

pd.read_sql_query("""
SELECT ...
""", db).head()

Saving queries

Follow exercise of Saving queries

Note: As DROP VIEW and CREATE VIEW do not return any result, below we have to use db.execute directly rather than pd.read_sql_query.

db.execute("DROP VIEW IF EXISTS journal_counts")

db.execute("""
CREATE VIEW ...

""")

pd.read_sql_query("""
SELECT ...
""", db).head()

Remember that a view is not stored as a separate table, but is a pre-canned query that can then itself be queries. That means updates to the tables will modify the views! SQL Views typically cannot themselves be modified with INSERT.

Nevertheless, if a view was temporary, we may want to delete it so it does clutter our database:

db.execute("DROP VIEW journal_counts")

Additional reading

The remaining sections of Library Carpentry SQL relates to creating databases before they can be queried.

• Database design
• Creating tables
• Extra challenges
• Good SQL style

Tip: Sometimes knowing what kind of queries are needed will influence the design at earlier data engineering phases, e.g. reducing the need for complex JOINs.