Tue Nov. 28
SQLite3 & SELECT ... WHERE ...
9 – 12h Teaching
Goals
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 andWHERE
clauses
Installing SQLite3
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
- Create a folder on your Desktop and name it
GUI Tools
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
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
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
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:
- Provide a query showing Customers (just their full names, customer ID and country) who are not in the US.
- Provide a query only showing the Customers from Brazil.
- 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.
- Provide a query showing only the Employees who are Sales Agents.
- Provide a query showing a unique list of billing countries from the Invoice table.
Want more? Check out this link