Unit 11
Source Code
import sqlite3
import os
def main():
# Connect to the SQLite database
db = 'unit11.db'
conn = sqlite3.connect(db)
done = False
print("Welcome to the Main Function")
while not done:
print("Menu")
print("E1 - Example 1")
print("Q - Quit")
choice = input("Choice: ").upper()
match choice:
case "E1":
create(conn)
case "E2":
insert(conn)
case "E3":
select(conn)
case "E4":
select2(conn)
# v2
case "E5":
create_v2(conn)
case "E6":
insert_v2(conn)
case "E7":
select_v2(conn)
case "E8":
select2_v2(conn)
case "E9":
select3_v2(conn)
case "E10":
select4_v2(conn)
# v3
case "E11":
create_v3(conn)
case "E12":
insert_v3(conn)
case "E13":
select_v3(conn)
case "E14":
example1_v3(conn)
case "E15":
example2_v3(conn)
case "E16":
example3_v3(conn)
case "E17":
example4_v3(conn)
# v4
case "E18":
create_v4(conn)
case "E19":
insert_v4(conn)
case "E20":
select_v4(conn)
case "E21":
example1_v4(conn)
case "E22":
example2_v4(conn)
case "E23":
example3_v4(conn)
case "E24":
example4_v4(conn)
# v5
case "E25":
create_v5(conn)
case "E26":
insert_v5(conn)
case "E27":
select_v5(conn)
case "E28":
example1_v5(conn)
case "E29":
example2_v5(conn)
case "E30":
example3_v5(conn)
case "E31":
example4_v5(conn)
# v6
case "E32":
create_v6(conn)
case "E33":
insert_v6(conn)
case "E34":
select_v6(conn)
case "E35":
example1_v6(conn)
case "E36":
example2_v6(conn)
case "E37":
example3_v6(conn)
case "E38":
example4_v6(conn)
# Q
case "Q":
print("Quitting!")
done = True
# default case
case _:
print("Invalid, try again!")
# close the connection
conn.close()
def create(conn):
print("Create Table")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Create table query
sql = '''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL,
grade INTEGER NOT NULL,
gpa REAL NOT NULL
)
'''
# Execute query
cursor.execute(sql)
print("Table created successfully!")
def insert(conn):
print("Insert Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# student data
students_data = [
("Alice", 18, 12, 3.8),
("Bob", 17, 11, 3.5),
("Charlie", 16, 10, 3.2),
("Diana", 18, 12, 3.9),
("Ethan", 17, 11, 3.6)
]
# Insert data into table query
sql = """
INSERT INTO students (name, age, grade, gpa) VALUES (?, ?, ?, ?)
"""
# Insert data into table query
cursor.executemany(sql, students_data)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
def select(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT all students
sql = "SELECT * FROM students"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def select2(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT certain columns from students
sql = "SELECT name, age FROM students"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
# v2
def create_v2(conn):
print("Create Table")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Create table query
sql = '''
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
department TEXT NOT NULL,
salary REAL,
hire_date TEXT,
is_manager BOOLEAN
)
'''
# Execute query
cursor.execute(sql)
print("Table created successfully!")
def insert_v2(conn):
print("Insert Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# data
data = [
('John Smith', 'Engineering', 75000, '2020-05-15', 0),
('Jane Doe', 'Marketing', 65000, '2019-03-20', 0),
('Michael Johnson', 'Engineering', 85000, '2018-11-10', 1),
('Emily Williams', 'Human Resources', 60000, '2021-01-08', 0),
('Robert Brown', 'Finance', 90000, '2017-07-22', 1),
('Sarah Miller', 'Marketing', 72000, '2020-09-30', 1),
('David Garcia', 'Engineering', 78000, '2019-06-12', 0),
('Jennifer Martinez', 'Human Resources', 62000, '2021-04-18', 0),
('William Thompson', 'Finance', 95000, '2016-10-05', 1),
('Lisa Rodriguez', 'Marketing', 68000, '2020-02-28', 0)
]
# Insert data into table query
sql = """
INSERT INTO employees (name, department, salary, hire_date, is_manager)
VALUES (?, ?, ?, ?, ?)
"""
# Insert data into table query
cursor.executemany(sql, data)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
def select_v2(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT all
sql = "SELECT * FROM employees"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def select2_v2(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT certain columns
sql = "SELECT name, salary FROM employees WHERE department = 'Engineering'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def select3_v2(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT certain columns
sql = "SELECT name, salary, hire_date FROM employees WHERE hire_date <= '2020-01-01'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def select4_v2(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT certain columns
sql = "SELECT name FROM employees WHERE name <> 'John Smith'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
# v3
def create_v3(conn):
print("Create Table")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Create table query
sql = '''
CREATE TABLE IF NOT EXISTS games (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
genre TEXT NOT NULL,
price REAL NOT NULL,
release_date TEXT NOT NULL,
is_multiplayer BOOLEAN NOT NULL
)
'''
# Execute query
cursor.execute(sql)
print("Table created successfully!")
def insert_v3(conn):
print("Insert Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# data
data = [
('Minecraft', 'Sandbox', 29.99, '2011-11-18', 1),
('Fortnite', 'Battle Royale', 0.00, '2017-07-25', 1),
('The Legend of Zelda', 'Adventure', 59.99, '2023-05-12', 0),
('Among Us', 'Party', 4.99, '2018-06-15', 1),
('FIFA 24', 'Sports', 69.99, '2023-09-29', 1),
('Call of Duty', 'FPS', 59.99, '2022-10-28', 1),
('Roblox', 'Platform', 0.00, '2006-09-01', 1),
('Spider-Man 2', 'Action', 69.99, '2023-10-20', 0),
('Skyrim', 'RPG', 19.99, '2011-11-11', 0),
('Rocket League', 'Sports', 19.99, '2015-07-07', 1)
]
# Insert data into table query
sql = """
INSERT INTO games (title, genre, price, release_date, is_multiplayer)
VALUES (?, ?, ?, ?, ?)
"""
# Insert data into table query
cursor.executemany(sql, data)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
def select_v3(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT all
sql = "SELECT * FROM games"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example1_v3(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Basic WHERE clause to find games in the Adventure genre
sql = "SELECT * FROM games WHERE genre = 'Adventure'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example2_v3(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find games in the FPS genre with price over $50
sql = "SELECT * FROM games WHERE genre = 'FPS' AND price > 50"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example3_v3(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find games that are either free or in the Sports genre
sql = "SELECT * FROM games WHERE price = 0 OR genre = 'Sports'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example4_v3(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find games that don't support multiplayer
sql = "SELECT * FROM games WHERE NOT is_multiplayer"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
# v4
def create_v4(conn):
print("Create Table")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Create table query
sql = '''
CREATE TABLE IF NOT EXISTS games2 (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
genre TEXT NOT NULL,
release_year INTEGER,
publisher TEXT,
rating REAL,
platform TEXT
)
'''
# Execute query
cursor.execute(sql)
print("Table created successfully!")
def insert_v4(conn):
print("Insert Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# data
data = [
('Minecraft', 'Sandbox', 2011, 'Mojang Studios', 4.8, 'Multi-platform'),
('Fortnite', 'Battle Royale', 2017, 'Epic Games', 4.5, 'Multi-platform'),
('Among Us', 'Party', 2018, 'InnerSloth', 4.7, 'Multi-platform'),
('Roblox', 'Game Creation', 2006, 'Roblox Corporation', 4.4, 'Multi-platform'),
('Call of Duty: Modern Warfare', 'First-person shooter', 2019, 'Activision', 4.3, 'Multi-platform'),
('FIFA 23', 'Sports', 2022, 'EA Sports', 4.1, 'Multi-platform'),
('Grand Theft Auto V', 'Action-Adventure', 2013, 'Rockstar Games', 4.9, 'Multi-platform'),
('The Legend of Zelda: Breath of the Wild', 'Action-Adventure', 2017, 'Nintendo', 4.9, 'Switch'),
('Spider-Man: Miles Morales', 'Action-Adventure', 2020, 'Sony Interactive Entertainment', 4.7, 'PlayStation'),
('Halo Infinite', 'First-person shooter', 2021, 'Xbox Game Studios', 4.5, 'Xbox'),
('Valorant', 'First-person shooter', 2020, 'Riot Games', 4.6, 'PC'),
('Apex Legends', 'Battle Royale', 2019, 'Electronic Arts', 4.4, 'Multi-platform'),
('Animal Crossing: New Horizons', 'Life simulation', 2020, 'Nintendo', 4.7, 'Switch'),
('Rocket League', 'Sports', 2015, 'Psyonix', 4.6, 'Multi-platform'),
('Genshin Impact', 'Action RPG', 2020, 'miHoYo', 4.5, 'Multi-platform')
]
# Insert data into table query
sql = """
INSERT INTO games2 (title, genre, release_year, publisher, rating, platform)
VALUES (?, ?, ?, ?, ?, ?)
"""
# Insert data into table query
cursor.executemany(sql, data)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
def select_v4(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT all
sql = "SELECT * FROM games2"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example1_v4(conn):
print("Example 1")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find all games with titles starting with 'M'
sql = "SELECT title, genre, release_year FROM games2 WHERE title LIKE 'M%'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example2_v4(conn):
print("Example 2")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Games published by companies ending with 'Games'
sql = "SELECT title, publisher, release_year FROM games2 WHERE publisher LIKE '%Games'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example3_v4(conn):
print("Example 3")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find all games with exactly 7 characters in the title
sql = "SELECT title, genre, release_year FROM games2 WHERE title LIKE '_______'"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example4_v4(conn):
print("Example 4")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Games with 'of' in title and released after 2015
sql = "SELECT title, genre, release_year FROM games2 WHERE title LIKE '%of%' AND release_year > 2015"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
# v5
def create_v5(conn):
print("Create Table")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Create table query
sql = '''
CREATE TABLE IF NOT EXISTS game_scores (
id INTEGER PRIMARY KEY AUTOINCREMENT,
player_name TEXT NOT NULL,
game_title TEXT NOT NULL,
score INTEGER NOT NULL,
playing_time_minutes INTEGER NOT NULL,
date_played TEXT NOT NULL,
grade_level TEXT NOT NULL
)
'''
# Execute query
cursor.execute(sql)
print("Table created successfully!")
def insert_v5(conn):
print("Insert Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# data
data = [
('Alex', 'Fortnite', 2801, 45, '2023-09-15', '9th'),
('Jamie', 'Minecraft', 1501, 120, '2023-09-20', '10th'),
('Taylor', 'Rocket League', 851, 30, '2023-10-10', '11th'),
('Jordan', 'Among Us', 401, 25, '2023-10-15', '9th'),
('Casey', 'Fortnite', 3101, 50, '2023-11-05', '12th'),
('Riley', 'Minecraft', 2201, 90, '2023-11-10', '10th'),
('Morgan', 'Rocket League', 951, 35, '2023-11-15', '11th'),
('Drew', 'Among Us', 601, 30, '2023-12-05', '9th'),
('Parker', 'Fortnite', 3501, 60, '2023-12-10', '12th'),
('Quinn', 'Minecraft', 1801, 100, '2023-12-20', '10th'),
('Sam', 'Valorant', 2501, 40, '2023-09-18', '11th'),
('Avery', 'Valorant', 2801, 45, '2023-10-12', '12th'),
('Cameron', 'Apex Legends', 3201, 55, '2023-10-22', '9th'),
('Harper', 'Apex Legends', 2901, 50, '2023-11-08', '10th'),
('Logan', 'Valorant', 3101, 60, '2023-11-25', '11th'),
('Reese', 'League of Legends', 1801, 70, '2023-12-02', '12th'),
('Dakota', 'League of Legends', 2201, 75, '2023-12-15', '9th'),
('Blake', 'Genshin Impact', 1651, 120, '2023-09-25', '10th'),
('Emerson', 'Genshin Impact', 1951, 140, '2023-10-18', '11th'),
('Hayden', 'Fortnite', 2951, 40, '2023-11-20', '10th'),
('Jordan', 'Minecraft', 1701, 110, '2023-11-28', '9th'),
('Kai', 'Rocket League', 1101, 40, '2023-12-08', '12th'),
('Kennedy', 'Among Us', 551, 30, '2023-09-30', '11th'),
('Elliott', 'Apex Legends', 3401, 65, '2023-10-25', '10th'),
('Charlie', 'League of Legends', 2401, 80, '2023-11-12', '9th')
]
# Insert data into table query
sql = """
INSERT INTO game_scores (player_name, game_title, score, playing_time_minutes, date_played, grade_level)
VALUES (?, ?, ?, ?, ?, ?)
"""
# Insert data into table query
cursor.executemany(sql, data)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
def select_v6(conn):
print("Select Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT all
sql = "SELECT * FROM game_scores"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example2_v5(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Count total number of game entries
sql = "SELECT COUNT(*) FROM game_scores"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the row
count = cursor.fetchone()[1]
print(count)
def example3_v5(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Count how many students played each game
sql = "SELECT game_title, COUNT(*) FROM game_scores GROUP BY game_title"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example4_v5(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Calculate total points scored across all games
sql = "SELECT SUM(score) FROM game_scores"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
total = cursor.fetchone()[1]
print(total)
def example5_v5(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Calculate total playing time for each game
sql = "SELECT game_title, SUM(playing_time_minutes) FROM game_scores GROUP BY game_title"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
#v7
def create_v7(conn):
print("Create Table")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Create table query
sql = '''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_name TEXT NOT NULL,
course TEXT NOT NULL,
grade INTEGER NOT NULL,
school_year TEXT NOT NULL
)
'''
# Execute query
cursor.execute(sql)
print("Table created successfully!")
def insert_v7(conn):
print("Insert Data")
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# data
data = [
('Alex', 'Algebra 2', 92, '2024-2025'),
('Alex', 'English 2', 88, '2024-2025'),
('Alex', 'Computer Science 2', 95, '2024-2025'),
('Alex', 'US History', 79, '2024-2025'),
('Alex', 'Chinese 2', 99, '2024-2025'),
('Taylor', 'Algebra 2', 75, '2024-2025'),
('Taylor', 'English 2', 90, '2024-2025'),
('Taylor', 'Computer Science 2', 82, '2024-2025'),
('Taylor', 'US History', 86, '2024-2025'),
('Taylor', 'Arabic', 96, '2024-2025'),
('Jordan', 'Algebra 2', 88, '2024-2025'),
('Jordan', 'English 2', 79, '2024-2025'),
('Jordan', 'Computer Science 2', 94, '2024-2025'),
('Jordan', 'US History', 82, '2024-2025'),
('Jordan', 'Spanish 2', 100, '2024-2025'),
('Morgan', 'Algebra 2', 95, '2024-2025'),
('Morgan', 'English 2', 87, '2024-2025'),
('Morgan', 'Computer Science 2', 91, '2024-2025'),
('Morgan', 'US History', 84, '2024-2025'),
('Morgan', 'French 2', 79, '2024-2025'),
('Casey', 'Algebra 2', 79, '2024-2025'),
('Casey', 'English 2', 93, '2024-2025'),
('Casey', 'Computer Science 2', 84, '2024-2025'),
('Casey', 'US History', 90, '2024-2025'),
('Casey', 'Japanese 2', 87, '2024-2025')
]
# Insert data into table query
sql = """
INSERT INTO students (student_name, course, grade, school_year)
VALUES (?, ?, ?, ?)
"""
# Insert data into table query
cursor.executemany(sql, data)
print("SQL query executed")
# Commit changes
conn.commit()
print("changed committed")
def select_v7(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# SQL Query to SELECT all
sql = "SELECT * FROM students"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example2_v6(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find the average score for each student
sql = "SELECT student_name, AVG(grade) FROM students GROUP BY student_name"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the row
rows = cursor.fetchall()
for row in rows:
print(row)
def example3_v6(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find the max grade for each student
sql = "SELECT student_name, course, MAX(grade) FROM students GROUP BY student_name"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example4_v6(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Find the overall class average, minimum and maximum grades
sql = "SELECT AVG(grade), MIN(grade), MAX(grade) FROM students"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
def example5_v6(conn):
# Create a cursor object using the cursor() method
cursor = conn.cursor()
# Print each students average grade from greatest to least
sql = "SELECT student_name, AVG(grade) FROM students GROUP BY student_name ORDER BY AVG(grade) DESC"
# Execute query
cursor.execute(sql)
print("SQL query executed")
# fetch the rows
rows = cursor.fetchall()
for row in rows:
print(row)
main()Last updated