download the Chinook sample database
Sign up for Pythonanywhere.com
Normally when you use Python you use in locally on your computer, but we are going to cheat are going to use a website calledpythonanywhere.com.
We are going to use this because:
- It’s free!
- It’s easy!
- It’s web based so you code from home, school or even on the beach
So if you haven’t done already, head over to Pythonanywhere.com and sign up for a free account!
Create an /sql/chinook folder
A. Create a folder called sql
B. Create a folder inside that folder called chinook
Upload the chinook db
Load up SQLite
Ready to rock!
.table
This command finds out what tables the database contains
PRAGMA table_info(table_name);
If you don’t know what columns are contained within a table, you can use the PRAGMA table_info statement to list all available columns.
Selecting all rows and columns in a table
If you just want to display the entire contents of a database table, the you can use the SELECT * FROM statement. This will get the entire contents of the table.
SELECT * FROM table_name;
Selecting specific column names in a table
If you just want to select certain columns, then you can specify the column names you want, separated by a comma.
SELECT column_name, another_columnn_name FROM table_name;
Select Where Equal To Value
SELECT * FROM table_name WHERE column_name = “search_criteria“;
If you want to only select rows from the table where data in a column matches a certain value, you need to use WHERE.
Examples:
SELECT Firstname, Lastname from Students WHERE Year = “7”;
SELECT FirstName from Animals WHERE location = “Europe” AND Type = “Mammal”;
WARNING! This command looks for an exact match – including capital letters – Be careful with your search criteria!
If you don’t want to match the capital letters for an exact match use LIKE
SELECT Firstname, Age from Students WHERE Name LIKE “john”;
Select Where greater or less than a value
SELECT * FROM table_name WHERE column_name > search_criteria;
If you want to filter data from a table where values are greater or less than an amount, then you should
Challenge 1
Find out what tables the database contains.
Challenge 2
Find out what column names and types the Album table contains
Challenge 3
Find out what artists are in the Artist table.
Challenge 4
Create a query that lists the first name and last name of all customers in the Customer table.
Challenge 5
Create a query that selects all rows in the Employee table where the Title is ‘Sales Support Agent’
Challenge 6
Create a query that lists all rows in the PlaylistTrack table where the TrackId is greater than 3000.