SQL Workshop

Prerequisite

1. Please complete the following LinkedIn Learning online course before the SQL workshop. (Note that you will need to install DB Browser for SQLite for this online course.)

   • Learning SQL Programming by Scott Simpson (1h 27m).

   LinkedIn Learning is not free anymore for UofT students. However, one of the following options can get you free access to this course.

   • Register a free 1-month trial account and take the course.
   • Apply for a free Toronto Public Library card, and login LinkedIn Learning via the library site. (Note that you can get Toronto Public Library’s Digital Access Card without visiting a library branch.)
   • If you are a UofT graduate, you could register here at UofT alumni site to get free access to LinkedIn Learning from 8/20/2021 to 12/15/2021.

2. During the workshop, we will practise SQL together using Google Colab, so please make sure you have a Google account.

Slides

• Intro to SQL (sql_bootcamp.pdf; updated on Sept. 7)

  Please print out page 8 of the slides (the “Northwind DB” page) so you can refer to it easily when we start hands-on exercises.

Notebook

• SQL Intro Notebook

• The full Notebook (all questions solved) in HTML format has been posted on Quercus. It’s not in notebook format so you cannot simply load it to Google Colab and hit run all. Use it as a reference and build your SQL Bootcamp Notebook on your own.

Backup Setup

If you don’t have access to Google service, below is a backup setup for the workshop.

1. Install DB Browser for SQLite. You should already have it installed if you took the online course by Scott Simpson (see Prerequisite above).

2. Download the dataset (northwind.sqlite3).

3. Load the dataset in DB Browser for SQLite, and follow along using the html version of the notebook.

---

SQL Setup for RSM8411 (MMA, Fall 2021)

You will learn more SQL in RSM8411 this fall. Instead of SQLite as we use in this workshop, RSM8411 will use PostgreSQL, a powerful open-source DBMS. Please make sure you have it installed and tested before the end of this workshop. If you encounter any problem setting it up, please let me know.

• Download and install PostgreSQL. You can follow the installation guide from this PostgreSQL Tutorial site: Windows installation or MacOS installation. Note that

  • PostgreSQL management client pgAdmin is included in the installation.
  • You do not need to install the component “Stack Builder”.
  • After the installation, you should verify that everything is installed correctly. Follow the “Verify the Installation” step in the Windows installation guide or the “Load the sample database” step in the MacOS installation guide.

• Getting-started resources: PostgreSQL Essential Training. (To access this LinkedIn course for free, refer to information in the Prerequisite section.)

---

Back to TDMDAL Computing Page