Materials for January 2021 SCF workshop on databases and SQL
- Install either the RSQLite R package or the sqlite3 Python package on your laptop.
- Download this data file onto your laptop.
Alternatively, if you have an SCF account you can plan to use one of the SCF machines via ssh or the SCF Jupyterhub.
If you'd like to be able to replicate the demo I do of creating a PostgreSQL database, you'll need to install PostgreSQL on your computer. Probably the easiest way to enable this is to install Docker, which will allow you to start up a Linux virtual machine on your laptop. See the Mac instructions or the Windows instructions for how to install Docker. We'll set up PostgreSQL within Docker during the workshop.
For replicating the demo of creating a database (either in SQLite or PostgreSQL) please download and unzip this file.
You can view the content by opening the databases.html file in a browser after downloading to your laptop, or by going here
Thursday:
- 9 am - 9:30 am: (optional) review/introduction to databases and database schema and normalization
- 9:30 am - 11 am: more advanced SQL: joins, set operations, string operations, subqueries, with time to work on questions
- 11:30 am - noon: discussion of efficiency, query plans
Friday:
- 9 am - 10 am: review and compare SQL problem answers from day 1 (plus time for discussing efficiency if not enough time on Thursday)
- 10 am - 11 am: window functions and additional breakout time to work on questions
- 11 am - noon: demo of database administration and getting data into databases
Zoom, to be announced via email.