Tue Nov. 28

SQLite3 & SELECT ... WHERE ...

9 – 12h Teaching

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

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

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).

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

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.

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

Report abuse