This workshop uses PostgreSQL. Much of the material applies generically to SQL and other relational database systems, but some of it is specific to PostgreSQL.
This workshop uses the database discussed in, and follows much of the content of, the PostgreSQL Tutorial.
The workshop starts with the presentation below, then SQL Part 1.
These materials are created for an in-person workshop. Participants will be connecting to a database server that will only be active for the duration of the workshop. In-person workshop participants do not need to install PostgreSQL.
Those wishing to work through the materials on their own will need to install PostgreSQL on their own systems or run a database instance/server via a cloud computing provider.
See Section 1. Getting started with PostgreSQL, from the PostgreSQL Tutorial for details on how to set up your own database server.
While in-person workshop participants do not need to install PostgreSQL, you will need a terminal program capable of creating an SSH connection to a remote server. On a Mac, the built-in Terminal program will work. On Windows, we suggest PuTTY if you don't already have another program installed.
Typing long commands in a terminal can be tedious. We also recommend you install DataGrip for working with databases. It has a free 30 day trial or you can apply for a JetBrains academic license for free.
This repository also includes materials for connecting to a database using Python or R. For Python, you will need to install the psycopg2 package. For R, you will need the package RPostgreSQL.
Basic Explanation of Relational Databases: from the BBC, a quick explanation of relational databases
DataGrip Tutorial: video on how to use the DataGrip program; it even uses the same database we use in this workshop.
These resources use PostgreSQL or SQL generally.
PostgreSQL Exercises: interactive, online exercises to practice SQL skills in a PostgreSQL environment.
SQL Tutorial: from SQL Zoo. Not specific to PostgreSQL. Also has interactive exercises.
Try SQL: from Code School; the basic course is free. Interactive, online tutorial.
Intermediate SQL Tutorial: intermediate level SQL tutorial from Dataquest; uses PostgreSQL and Python (pandas, psycopg2) and includes exercises. For when you're ready for more practice beyond the basics.
The workshop uses the PostgreSQL database system, but if you're working on your own projects, SQLite may be a good option. SQLite doesn't require running a server and it creates a database in a single, portable file locally on your computer.
Software Carpentry Databases and SQL: introductory workshop using SQLite
Databases with Python and Pandas: from Data Quest. Examples of using a SQLite database with Python and pandas too.
Programming for Biologists: includes a section on databases; it uses MS Access instead of PostgreSQL or SQLite, but many of the concepts should be the same.