Tue Nov. 28

SQLite3 & SELECT ... WHERE ...

9 – 12h Teaching

Goals

  • I have installed SQLite3 and the Firefox GUI Plug-In on my computer
  • I know how to open SQLite databases on the Git Bash console
  • I have queried data from the Chinook database using the SELECT statement and WHERE clauses

Installing SQLite3

  • Install Git (Windows only)
    • We will be using Git Bash instead of the Command Line
  • Install SQLite3
    • Create a folder on your Desktop and name it SQLite
    • Download sqlite-shell and sqlite-dll archive files
    • Unpack the files from both archives into the SQLite folder
    • Launch Git Bash in the SQLite folder

GUI Tools

Many GUI tools exist for SQLite. Let's download SQLite Manager, an add-on for Firefox (first install Firefox, if you do not have it already).

Installing the Chinook Database

  • Download the Chinook Database files
  • Move the zipped file you downloaded to the SQLite folder
  • Unzip ChinookDatabase1.4_CompleteVersion.zip
  • Execute the following command to open the Chinook_Sqlite.sqlite database

From (Git-)Bash:

sqlite3 Chinook_Sqlite.sqlite

From SQLite directly:

sqlite> .open [path_to_your_database_folder]/Chinook_Sqlite.sqlite

12 – 13.30h Lunch

13.30 – 17h Coaching

Exercise

Spend a few minutes exploring the SQLite Manager

  • Pair up with your neighbor and discuss what you find.
  • View a few tables.
  • What do you find in the Structure tab? What are some different data types you can find?
  • How about the Browse and Search tab?
  • We can ignore Execute SQL and DB Settings for now. Just focus on what we see in the data.

Exercise

  • Spend the rest of class writing SELECT statements with WHERE clauses
  • Use SQLite Manager to get ideas of how the data is structured, and what you might query
  • Feel free to work with a neighbor!

Here are some challenges:

  1. Provide a query showing Customers (just their full names, customer ID and country) who are not in the US.
  2. Provide a query only showing the Customers from Brazil.
  3. Provide a query showing the Invoices of customers who are from Brazil. The resultant table should show the customer's full name, Invoice ID, Date of the invoice and billing country.
  4. Provide a query showing only the Employees who are Sales Agents.
  5. Provide a query showing a unique list of billing countries from the Invoice table.

Want more? Check out this link