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!

pa-signup1 pa-signup2

 

Create an /sql/chinook folder

pa-files

A. Create a folder called sql

B. Create a folder inside that folder called chinook

pa-challenges

 

Upload the chinook db

pa-upload

Load up SQLite

pa-bash

pa-chinook

Ready to rock!
pa-sqlreadytorock


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