Opening and Navigating Databases

download the Chinook sample database

Sign up for

Normally when you use Python you use in locally on your computer, but we are going to cheat are going to use a website

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 and sign up for a free account!

pa-signup1 pa-signup2


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!



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.



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.