Instructor Notes

Tips and Tricks

Making a handout

To make a handout for this lesson, adapt/print from https://librarycarpentry.org/lc-sql/reference.

General notes of SQL

database (dā’tə-bās’) noun: “A collection of data arranged for ease and speed of search and retrieval by a computer”

— The American Heritage® Science Dictionary {: .quotation}

  • Three common options for storing data
  • Text
    • Easy to create, work well with version control
    • But then we have to build search and analysis tools ourselves
  • Spreadsheets
    • Good for simple analyses
    • But don’t handle large or complex data sets well
  • Databases
    • Include powerful tools for search and analysis
    • Can handle large, complex data sets.

Overall

  • Libraries often start off with spreadsheet-based projects and there are a number of examples provided in the Introduction to SQL where they might move to a database and use SQL. The What are some of the uses of SQL in libraries section can be possibly turned into a group exercise where workshop participants can share some projects that can benefit from moving to a database.

  • If you are short on time, consider pointing workshop participants to the Ordering and commenting and Saving queries episodes to refer to later.

  • The Extra challenges episode is optional if workshop participants want to try additional challenge exercises later. Depending on time it can be done as homework or at the end of a workshop.

  • Database design episode can be positioned at the start, during, at the end of the lesson. It adds more time and can be a more complex episode to teach but it also helps with providing further background on how databases can be helpful with structured data.

  • Some advanced learners may have heard that NoSQL databases (i.e., ones that don’t use the relational model) are the next big thing, and ask why we’re not teaching those. The answers are:

    1. Relational databases are far more widely used than NoSQL databases.
    2. We have far more experience with relational databases than with any other kind, so we have a better idea of what to teach and how to teach it.
    3. NoSQL databases are as different from each other as they are from relational databases. Until a leader emerges, it isn’t clear which NoSQL database we should teach.

Import CSV in SQLite

For instructors demonstrating the use of SQLite, Aaron Culich recommends using a Directory of Open Access Journals (DOAJ) example, importing a dataset CSV file from the CERN repository Zenodo. The example is below:

BASH 

$ sqlite3 output.db
sqlite> .mode csv
sqlite> .import dataset-final-20160825-zenodo.csv
sqlite> .schema

Resources

Introduction to SQL

Selecting and sorting data

Filtering

Ordering and commenting

Aggregating and calculating values

Joins and aliases

Saving queries

Database design

Creating tables and modifying data

Other database tools

Extra challenges (optional)

Good Style