Selection with Conditions
This Tutorial's Database: swim.db
swim.db
Table Name: butterfly200; Showing only first 3 athletes
Lindsey Mcneil
Norway
133831
Giada Middleton
Georgia
144370
Natalie Patrick
North Macedonia
147728
There are 30 athletes included athletes with the following characteristics:
randomly generated name
randomly chosen country
randomly generated time measured in milliseconds
2 minutes is 120,000 milliseconds which is very close to Canada's Sarah McIntosh's Olympic Record time in 2024.
Selecting based on specific criteria
SELECT column_info
FROM table_name
WHERE condition
AND/OR more_conditions
The code snippet above is a template of how to choose records that include certain columns which fit the custom condition based criteria
Some Examples of Conditions
=, !=, < <=, >, >=
standard numeric comparisons
column_name != 6
BETWEEN x AND y
checking for inclusive range
column_name BETWEEN 1.5 AND 10.5
NOT BETWEEN x AND y
check for numbers outside of an inclusive range
column_name NOT BETWEEN 1 AND 10
IN (x, y, z)
value exists in a list
column_name IN ("RED", "GREEN" , "BLUE" )
NOT IN (x, y, z)
value does not exist in a list
column_name NOT IN (3, 1, 4)
SELECT name
FROM butterfly200
WHERE time < 130000
# Python w/ SQLite
import sqlite3
connection = sqlite3.connect("swim.db")
cursor = connection.cursor()
query = '''
SELECT name
FROM butterfly200
WHERE time < 130000
'''
result = cursor.execute(query)
print("Athletes that finished under 2.16 minutes:")
print(result.fetchall())
cursor.close()
connection.close()
This query generates:
Athletes that finished under 2.16 minutes:
[('Joslyn Rosario',), ('Saniya Palmer',), ('Jazmin Dixon',), ('Jazlyn Hensley',), ('Haven Dixon',), ('Kamryn Kane',), ('Sierra Mejia',), ('Yasmine Kline',), ('Nia Kane',)]
Finding Spanish, French or Belgian Athletes
Query:
SELECT *
FROM butterfly200
WHERE country IN ("Spain", "France", "Belgium")
import sqlite3
connection = sqlite3.connect("swim.db")
cursor = connection.cursor()
query = '''
SELECT *
FROM butterfly200
WHERE
country IN ("Spain", "France", "Belgium")
'''
result = cursor.execute(query)
print("Athletes that are from either Spain, France, and Belgium:")
print(result.fetchall())
cursor.close()
connection.close()
Output
Athletes that are from either Spain, France, and Belgium:
[('Chana Bernard', 'Spain', 143712), ('Jazlyn Hensley', 'Belgium', 128358), ('Erin Malone', 'France', 142178)]
Exercises
Did any randomized athlete beat Sarah McIntosh's Olympic Record of 2 minutes and 3 seconds (123300 milliseconds)?
Are there any Canadian athletes, are there American (United States) athletes, are there any from your background/home country?
Who was the fastest athlete?
Last updated