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!
A. Create a folder called sql
B. Create a folder inside that folder called chinook
.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.