Practice SQL using SQLITE3 in python

Learn SQL via sqlite 3 in python worksheet

For Alevel computer science you will need to know these commands:

In the exam, you are expected to be familiar with the following commands in SQL:

• SELECT
• FROM
• WHERE
• UNION
• LIKE
• AND
• OR
• DELETE
• INSERT
• DROP
• JOIN
• TRUNCATE

Part 1: CREATE and INSERT

We start by building the database and adding data.

import sqlite3
db = sqlite3.connect('lesson.db')
cur = db.cursor()
# DEMONSTRATION: CREATE & INSERT
cur.execute("DROP TABLE IF EXISTS countries") # Resetting for the lesson
cur.execute("CREATE TABLE countries (id INTEGER PRIMARY KEY, name TEXT, continent TEXT, population INTEGER)")
# Adding countries
cur.execute("INSERT INTO countries (name, continent, population) VALUES ('Algeria', 'Africa', 44000000)")
db.commit()

For these bits of data, insert them into the database:

('Brazil', 'South America', 214000000)
('Belgium', 'Europe', 11000000) 
('Canada', 'North America', 38000000)

Part 2: SELECT and WHERE

Retrieving specific data.

# DEMONSTRATION: Simply SELECT *
cur.execute("SELECT * FROM countries")
print("All data:", cur.fetchall())

PRACTICE: Write a query to SELECT names where population is less than 50,000,000

Part 3: UPDATE and DELETE

Changing or removing records.

# DEMONSTRATION: UPDATE a table SET fields WHERE condition
cur.execute("UPDATE countries SET population = 45000000 WHERE name = 'Algeria'")
# DEMONSTRATION: DELETE FROM table WHERE condition
cur.execute("DELETE FROM countries WHERE population > 200000000") 
db.commit()

PRACTICE: UPDATE ‘Canada’ to have a population of 39000000

Part 4: LIKE, OR, and ORDER BY

Advanced filtering and sorting.

# DEMONSTRATION: Using LIKE with % wildcard
# TASK: Find names starting with 'B'
cur.execute("SELECT * FROM countries WHERE name LIKE 'B%'")
print("Starts with B:", cur.fetchall())

# DEMONSTRATION: Combining with OR
cur.execute("SELECT * FROM countries WHERE name LIKE 'B%' OR continent = 'North America'")
# DEMONSTRATION: ORDER BY ASC/DESC
cur.execute("SELECT * FROM countries ORDER BY population DESC")
print("Sorted (Highest to Lowest):", cur.fetchall())

# PRACTICE: Try to find countries ending in ‘a’ (Hint: ‘%a’)Part 5: Nested SELECT and DROP

Get Icodewithben’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Queries within queries and cleaning up.

# DEMONSTRATION: Nested SELECT (Find countries bigger than Belgium)
cur.execute('''
    SELECT name FROM countries 
    WHERE population > (SELECT population FROM countries WHERE name = 'Belgium')
''')
print("Larger than Belgium:", cur.fetchall())

# DEMONSTRATION: DROP TABLE
# WARNING: This deletes everything!
cur.execute("DROP TABLE countries")
db.commit()
db.close()
print("Step 5 Complete: Table dropped and connection closed.")

Practice Tasks

Setup a database so it has this information. You will need to have this data to make this work, so insert this data too:

INSERT INTO Country (name, continent, population) VALUES
('France', 'Europe', 65000000),
('Germany', 'Europe', 83000000),
('Nigeria', 'Africa', 206000000),
('Kenya', 'Africa', 54000000),
('Egypt', 'Africa', 102000000),
('Eswatini', 'Eswatini', 1160000),
('Eritrea', 'East Africa', 3500000),
('China', 'Asia', 1400000000),
('Japan', 'Asia', 126000000),
('India', 'Asia', 1380000000),
('Nepal', 'Asia', 29000000),
('Bhutan', 'Asia', 770000),
('Brazil', 'South America', 212000000),
('Venezuela', 'South America', 28000000),
('New Zealand', 'Oceania', 5000000),
('Belize', 'North America', 420000),
('Canada', 'North America', 38000000),
('Mexico', 'North America', 128000000),
('Estonia', 'Europe', 1300000),
('Switzerland', 'Europe', 8700000);

• List all country names and their populations.
• Show the names of all countries in the continent ‘Asia’.
• Retrieve the names of countries with a population greater than 40,000,000.
• Find the names of countries whose continent starts with the letter ‘E’.
• Show the names and populations of countries with a population less than 1,000,000.
• List all countries in Africa, ordered by population from largest to smallest.
• Display the names of countries whose name contains the letter ‘z’.
• Find all countries with populations between 10,000,000 and 50,000,000.
• List the first 5 countries (by alphabetical order) in the database.
• Show the countries not in Europe.