Source Code
unit13.py
import sqlite3
def main():
# Connect to the SQLite database
db = 'unit13.db'
conn = sqlite3.connect(db)
print("Welcome to Restaurant Database Manager")
done = False
while not done:
print()
print("Menu")
print("=" * 50)
print("SETUP")
print("=" * 50)
print("CREATE - Create menu table")
print("INSERT - Insert sample menu items")
print("SELECT - View all menu items")
print()
print("=" * 50)
print("BASIC QUERIES")
print("=" * 50)
print("E1 - Select all columns")
print("E2 - Select specific columns")
print("E3 - WHERE clause (category)")
print("E4 - WHERE clause (price comparison)")
print()
print("=" * 50)
print("WHERE CLAUSES")
print("=" * 50)
print("E5 - WHERE with equality")
print("E6 - WHERE with less than or equal")
print("E7 - WHERE with not equal")
print("E8 - WHERE with greater than")
print()
print("=" * 50)
print("LOGICAL OPERATORS")
print("=" * 50)
print("E9 - WHERE with AND")
print("E10 - WHERE with OR")
print("E11 - WHERE with NOT")
print("E12 - WHERE with complex logic")
print()
print("=" * 50)
print("PATTERN MATCHING")
print("=" * 50)
print("E13 - LIKE with starts with")
print("E14 - LIKE with ends with")
print("E15 - LIKE with contains")
print("E16 - LIKE with exact length")
print()
print("=" * 50)
print("AGGREGATE FUNCTIONS")
print("=" * 50)
print("E17 - COUNT all rows")
print("E18 - COUNT with GROUP BY")
print("E19 - SUM function")
print("E20 - SUM with GROUP BY")
print("E21 - AVG function")
print("E22 - AVG with GROUP BY")
print()
print("=" * 50)
print("MIN/MAX & SORTING")
print("=" * 50)
print("E23 - MIN and MAX functions")
print("E24 - MIN and MAX with GROUP BY")
print("E25 - ORDER BY ascending")
print("E26 - ORDER BY descending")
print("E27 - ORDER BY multiple columns")
print()
print("=" * 50)
print("ADVANCED QUERIES")
print("=" * 50)
print("E28 - GROUP BY with HAVING")
print("E29 - COUNT DISTINCT")
print("E30 - BETWEEN operator")
print("E31 - IN operator")
print("E32 - Multiple aggregates")
print()
print("=" * 50)
print("UPDATE OPERATIONS")
print("=" * 50)
print("E33 - UPDATE single record")
print("E34 - UPDATE multiple records")
print("E35 - UPDATE with calculation")
print()
print("=" * 50)
print("DELETE OPERATIONS")
print("=" * 50)
print("E36 - DELETE single record")
print("E37 - DELETE with WHERE")
print("E38 - DELETE with complex condition")
print()
print("=" * 50)
print("ALTER & DROP")
print("=" * 50)
print("E39 - ALTER TABLE add column")
print("E40 - DROP TABLE")
print()
print("Q - Quit")
print("=" * 50)
choice = input("Choice: ").upper()
match choice:
case "CREATE":
create_table(conn)
case "INSERT":
insert_data(conn)
case "SELECT":
select_all(conn)
case "E1":
e1(conn)
case "E2":
e2(conn)
case "E3":
e3(conn)
case "E4":
e4(conn)
case "E5":
e5(conn)
case "E6":
e6(conn)
case "E7":
e7(conn)
case "E8":
e8(conn)
case "E9":
e9(conn)
case "E10":
e10(conn)
case "E11":
e11(conn)
case "E12":
e12(conn)
case "E13":
e13(conn)
case "E14":
e14(conn)
case "E15":
e15(conn)
case "E16":
e16(conn)
case "E17":
e17(conn)
case "E18":
e18(conn)
case "E19":
e19(conn)
case "E20":
e20(conn)
case "E21":
e21(conn)
case "E22":
e22(conn)
case "E23":
e23(conn)
case "E24":
e24(conn)
case "E25":
e25(conn)
case "E26":
e26(conn)
case "E27":
e27(conn)
case "E28":
e28(conn)
case "E29":
e29(conn)
case "E30":
e30(conn)
case "E31":
e31(conn)
case "E32":
e32(conn)
case "E33":
e33(conn)
case "E34":
e34(conn)
case "E35":
e35(conn)
case "E36":
e36(conn)
case "E37":
e37(conn)
case "E38":
e38(conn)
case "E39":
e39(conn)
case "E40":
e40(conn)
case "Q":
print("Quitting!")
done = True
case _:
print("Invalid choice!")
# close the connection
conn.close()
# ============================================================================
# SETUP FUNCTIONS
# ============================================================================
def create_table(conn):
print("Creating menu table...")
cursor = conn.cursor()
sql = '''
CREATE TABLE IF NOT EXISTS menu (
id INTEGER PRIMARY KEY AUTOINCREMENT,
dish_name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL,
calories INTEGER NOT NULL,
rating REAL NOT NULL,
is_vegetarian BOOLEAN NOT NULL,
prep_time INTEGER NOT NULL
)
'''
cursor.execute(sql)
print("Table created successfully!")
def insert_data(conn):
print("Inserting menu items...")
cursor = conn.cursor()
data = [
('Caesar Salad', 'Appetizer', 8.99, 350, 4.5, 1, 10),
('Chicken Wings', 'Appetizer', 12.99, 520, 4.7, 0, 15),
('Mozzarella Sticks', 'Appetizer', 9.99, 480, 4.3, 1, 12),
('Tomato Soup', 'Appetizer', 6.99, 220, 4.6, 1, 8),
('Grilled Chicken', 'Entree', 16.99, 580, 4.8, 0, 25),
('Beef Burger', 'Entree', 14.99, 750, 4.6, 0, 20),
('Veggie Burger', 'Entree', 13.99, 520, 4.4, 1, 18),
('Salmon Fillet', 'Entree', 22.99, 650, 4.9, 0, 30),
('Pasta Primavera', 'Entree', 15.99, 680, 4.5, 1, 22),
('Steak', 'Entree', 28.99, 820, 4.9, 0, 35),
('Pizza Margherita', 'Entree', 12.99, 720, 4.7, 1, 18),
('Fish Tacos', 'Entree', 13.99, 540, 4.6, 0, 20),
('Chocolate Cake', 'Dessert', 7.99, 450, 4.8, 1, 5),
('Ice Cream', 'Dessert', 5.99, 320, 4.7, 1, 3),
('Cheesecake', 'Dessert', 8.99, 510, 4.9, 1, 5),
('Apple Pie', 'Dessert', 6.99, 380, 4.6, 1, 10),
('Coffee', 'Beverage', 2.99, 5, 4.5, 1, 5),
('Iced Tea', 'Beverage', 2.49, 80, 4.3, 1, 3),
('Lemonade', 'Beverage', 3.49, 150, 4.4, 1, 3),
('Smoothie', 'Beverage', 5.99, 280, 4.6, 1, 5)
]
sql = """
INSERT INTO menu (dish_name, category, price, calories, rating, is_vegetarian, prep_time)
VALUES (?, ?, ?, ?, ?, ?, ?)
"""
cursor.executemany(sql, data)
print("SQL query executed")
conn.commit()
print("Data committed - 20 menu items added!")
def select_all(conn):
print("All Menu Items:")
cursor = conn.cursor()
sql = "SELECT * FROM menu"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
# ============================================================================
# BASIC QUERIES (E1-E4)
# ============================================================================
def e1(conn):
print("E1 - Select all columns")
cursor = conn.cursor()
sql = "SELECT * FROM menu"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
def e2(conn):
print("E2 - Select specific columns")
cursor = conn.cursor()
sql = "SELECT dish_name, category, price FROM menu"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(f"{row[0]} ({row[1]}): ${row[2]}")
def e3(conn):
print("E3 - WHERE clause (category)")
cursor = conn.cursor()
sql = "SELECT dish_name, price FROM menu WHERE category = 'Entree'"
cursor.execute(sql)
rows = cursor.fetchall()
print("Entrees:")
for row in rows:
print(f"{row[0]}: ${row[1]}")
# ============================================================================
# WHERE CLAUSES (E4-E8)
# ============================================================================
def e4(conn):
print("E4 - WHERE clause (price comparison)")
cursor = conn.cursor()
sql = "SELECT dish_name, price FROM menu WHERE price > 15"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items over $15:")
for row in rows:
print(f"{row[0]}: ${row[1]}")
def e5(conn):
print("E5 - WHERE with equality")
cursor = conn.cursor()
sql = "SELECT dish_name, calories FROM menu WHERE is_vegetarian = 1"
cursor.execute(sql)
rows = cursor.fetchall()
print("Vegetarian items:")
for row in rows:
print(f"{row[0]}: {row[1]} calories")
def e6(conn):
print("E6 - WHERE with less than or equal")
cursor = conn.cursor()
sql = "SELECT dish_name, calories FROM menu WHERE calories <= 300"
cursor.execute(sql)
rows = cursor.fetchall()
print("Low calorie items (≤300):")
for row in rows:
print(f"{row[0]}: {row[1]} calories")
def e7(conn):
print("E7 - WHERE with not equal")
cursor = conn.cursor()
sql = "SELECT dish_name, category FROM menu WHERE category <> 'Beverage'"
cursor.execute(sql)
rows = cursor.fetchall()
print("All food items (no beverages):")
for row in rows:
print(f"{row[0]} ({row[1]})")
def e8(conn):
print("E8 - WHERE with greater than")
cursor = conn.cursor()
sql = "SELECT dish_name, rating FROM menu WHERE rating > 4.7"
cursor.execute(sql)
rows = cursor.fetchall()
print("Highly rated items (>4.7):")
for row in rows:
print(f"{row[0]}: {row[1]} stars")
# ============================================================================
# LOGICAL OPERATORS (E9-E12)
# ============================================================================
def e9(conn):
print("E9 - WHERE with AND")
cursor = conn.cursor()
sql = "SELECT dish_name, price, calories FROM menu WHERE category = 'Entree' AND calories < 600"
cursor.execute(sql)
rows = cursor.fetchall()
print("Lower-calorie entrees:")
for row in rows:
print(f"{row[0]}: ${row[1]}, {row[2]} calories")
def e10(conn):
print("E10 - WHERE with OR")
cursor = conn.cursor()
sql = "SELECT dish_name, category, price FROM menu WHERE category = 'Appetizer' OR category = 'Dessert'"
cursor.execute(sql)
rows = cursor.fetchall()
print("Appetizers and Desserts:")
for row in rows:
print(f"{row[0]} ({row[1]}): ${row[2]}")
def e11(conn):
print("E11 - WHERE with NOT")
cursor = conn.cursor()
sql = "SELECT dish_name, is_vegetarian FROM menu WHERE NOT is_vegetarian"
cursor.execute(sql)
rows = cursor.fetchall()
print("Non-vegetarian items:")
for row in rows:
print(row[0])
def e12(conn):
print("E12 - WHERE with complex logic")
cursor = conn.cursor()
sql = "SELECT dish_name, price FROM menu WHERE (category = 'Entree' OR category = 'Appetizer') AND price < 15"
cursor.execute(sql)
rows = cursor.fetchall()
print("Affordable entrees and appetizers:")
for row in rows:
print(f"{row[0]}: ${row[1]}")
# ============================================================================
# PATTERN MATCHING (E13-E16)
# ============================================================================
def e13(conn):
print("E13 - LIKE with starts with")
cursor = conn.cursor()
sql = "SELECT dish_name, category FROM menu WHERE dish_name LIKE 'C%'"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items starting with 'C':")
for row in rows:
print(f"{row[0]} ({row[1]})")
def e14(conn):
print("E14 - LIKE with ends with")
cursor = conn.cursor()
sql = "SELECT dish_name, category FROM menu WHERE dish_name LIKE '%Cake'"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items ending with 'Cake':")
for row in rows:
print(f"{row[0]} ({row[1]})")
def e15(conn):
print("E15 - LIKE with contains")
cursor = conn.cursor()
sql = "SELECT dish_name, price FROM menu WHERE dish_name LIKE '%Burger%'"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items containing 'Burger':")
for row in rows:
print(f"{row[0]}: ${row[1]}")
def e16(conn):
print("E16 - LIKE with exact length (5 characters)")
cursor = conn.cursor()
sql = "SELECT dish_name, category FROM menu WHERE dish_name LIKE '_____'"
cursor.execute(sql)
rows = cursor.fetchall()
print("5-letter items:")
for row in rows:
print(f"{row[0]} ({row[1]})")
# ============================================================================
# AGGREGATE FUNCTIONS (E17-E22)
# ============================================================================
def e17(conn):
print("E17 - COUNT all rows")
cursor = conn.cursor()
sql = "SELECT COUNT(*) FROM menu"
cursor.execute(sql)
count = cursor.fetchone()[0]
print(f"Total menu items: {count}")
def e18(conn):
print("E18 - COUNT with GROUP BY")
cursor = conn.cursor()
sql = "SELECT category, COUNT(*) FROM menu GROUP BY category"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items per category:")
for row in rows:
print(f"{row[0]}: {row[1]} items")
def e19(conn):
print("E19 - SUM function")
cursor = conn.cursor()
sql = "SELECT SUM(calories) FROM menu WHERE category = 'Entree'"
cursor.execute(sql)
total = cursor.fetchone()[0]
print(f"Total calories in all entrees: {total:,}")
def e20(conn):
print("E20 - SUM with GROUP BY")
cursor = conn.cursor()
sql = "SELECT category, SUM(calories) FROM menu GROUP BY category"
cursor.execute(sql)
rows = cursor.fetchall()
print("Total calories by category:")
for row in rows:
print(f"{row[0]}: {row[1]:,} calories")
def e21(conn):
print("E21 - AVG function")
cursor = conn.cursor()
sql = "SELECT AVG(price) FROM menu"
cursor.execute(sql)
avg = cursor.fetchone()[0]
print(f"Average menu price: ${avg:.2f}")
def e22(conn):
print("E22 - AVG with GROUP BY")
cursor = conn.cursor()
sql = "SELECT category, AVG(price) FROM menu GROUP BY category"
cursor.execute(sql)
rows = cursor.fetchall()
print("Average price by category:")
for row in rows:
print(f"{row[0]}: ${row[1]:.2f}")
# ============================================================================
# MIN/MAX & SORTING (E23-E27)
# ============================================================================
def e23(conn):
print("E23 - MIN and MAX functions")
cursor = conn.cursor()
sql = "SELECT MIN(price), MAX(price), MIN(calories), MAX(calories) FROM menu"
cursor.execute(sql)
row = cursor.fetchone()
print(f"Price range: ${row[0]} - ${row[1]}")
print(f"Calorie range: {row[2]} - {row[3]}")
def e24(conn):
print("E24 - MIN and MAX with GROUP BY")
cursor = conn.cursor()
sql = "SELECT category, MIN(price), MAX(price) FROM menu GROUP BY category"
cursor.execute(sql)
rows = cursor.fetchall()
print("Price range by category:")
for row in rows:
print(f"{row[0]}: ${row[1]} - ${row[2]}")
def e25(conn):
print("E25 - ORDER BY ascending")
cursor = conn.cursor()
sql = "SELECT dish_name, price FROM menu ORDER BY price ASC"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items by price (low to high):")
for row in rows:
print(f"{row[0]}: ${row[1]}")
def e26(conn):
print("E26 - ORDER BY descending")
cursor = conn.cursor()
sql = "SELECT dish_name, rating FROM menu ORDER BY rating DESC"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items by rating (high to low):")
for row in rows:
print(f"{row[0]}: {row[1]} stars")
def e27(conn):
print("E27 - ORDER BY multiple columns")
cursor = conn.cursor()
sql = "SELECT dish_name, category, rating FROM menu ORDER BY category ASC, rating DESC"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items by category, then rating:")
for row in rows:
print(f"{row[1]}: {row[0]} ({row[2]} stars)")
# ============================================================================
# ADVANCED QUERIES (E28-E32)
# ============================================================================
def e28(conn):
print("E28 - GROUP BY with HAVING")
cursor = conn.cursor()
sql = "SELECT category, AVG(rating) FROM menu GROUP BY category HAVING AVG(rating) > 4.5"
cursor.execute(sql)
rows = cursor.fetchall()
print("Categories with average rating > 4.5:")
for row in rows:
print(f"{row[0]}: {row[1]:.2f} stars")
def e29(conn):
print("E29 - COUNT DISTINCT")
cursor = conn.cursor()
sql = "SELECT COUNT(DISTINCT category) FROM menu"
cursor.execute(sql)
count = cursor.fetchone()[0]
print(f"Number of unique categories: {count}")
def e30(conn):
print("E30 - BETWEEN operator")
cursor = conn.cursor()
sql = "SELECT dish_name, price FROM menu WHERE price BETWEEN 10 AND 15"
cursor.execute(sql)
rows = cursor.fetchall()
print("Items priced $10-$15:")
for row in rows:
print(f"{row[0]}: ${row[1]}")
def e31(conn):
print("E31 - IN operator")
cursor = conn.cursor()
sql = "SELECT dish_name, category FROM menu WHERE category IN ('Appetizer', 'Dessert', 'Beverage')"
cursor.execute(sql)
rows = cursor.fetchall()
print("Starters, desserts, and drinks:")
for row in rows:
print(f"{row[0]} ({row[1]})")
def e32(conn):
print("E32 - Multiple aggregates")
cursor = conn.cursor()
sql = "SELECT category, COUNT(*), AVG(price), AVG(calories), AVG(rating) FROM menu GROUP BY category"
cursor.execute(sql)
rows = cursor.fetchall()
print("Category statistics:")
for row in rows:
print(f"{row[0]}: {row[1]} items, ${row[2]:.2f} avg, {row[3]:.0f} cal, {row[4]:.2f} stars")
# ============================================================================
# UPDATE OPERATIONS (E33-E35)
# ============================================================================
def e33(conn):
print("E33 - UPDATE single record")
cursor = conn.cursor()
sql = "UPDATE menu SET price = 13.99 WHERE dish_name = 'Caesar Salad'"
cursor.execute(sql)
conn.commit()
print(f"Rows updated: {cursor.rowcount}")
cursor.execute("SELECT dish_name, price FROM menu WHERE dish_name = 'Caesar Salad'")
print(cursor.fetchone())
def e34(conn):
print("E34 - UPDATE multiple records")
cursor = conn.cursor()
sql = "UPDATE menu SET price = price + 1 WHERE category = 'Beverage'"
cursor.execute(sql)
conn.commit()
print(f"Rows updated: {cursor.rowcount}")
cursor.execute("SELECT dish_name, price FROM menu WHERE category = 'Beverage'")
print("Updated beverage prices:")
for row in cursor.fetchall():
print(f"{row[0]}: ${row[1]}")
def e35(conn):
print("E35 - UPDATE with calculation (10% discount)")
cursor = conn.cursor()
sql = "UPDATE menu SET price = price * 0.9 WHERE price > 20"
cursor.execute(sql)
conn.commit()
print(f"Rows updated: {cursor.rowcount}")
cursor.execute("SELECT dish_name, price FROM menu WHERE price > 18 ORDER BY price DESC")
print("Discounted items:")
for row in cursor.fetchall():
print(f"{row[0]}: ${row[1]:.2f}")
# ============================================================================
# DELETE OPERATIONS (E36-E38)
# ============================================================================
def e36(conn):
print("E36 - DELETE single record")
cursor = conn.cursor()
sql = "DELETE FROM menu WHERE dish_name = 'Lemonade'"
cursor.execute(sql)
conn.commit()
print(f"Rows deleted: {cursor.rowcount}")
cursor.execute("SELECT COUNT(*) FROM menu")
print(f"Remaining items: {cursor.fetchone()[0]}")
def e37(conn):
print("E37 - DELETE with WHERE")
cursor = conn.cursor()
sql = "DELETE FROM menu WHERE calories > 800"
cursor.execute(sql)
conn.commit()
print(f"Rows deleted: {cursor.rowcount}")
cursor.execute("SELECT dish_name, calories FROM menu ORDER BY calories DESC")
print("Remaining items:")
for row in cursor.fetchall():
print(f"{row[0]}: {row[1]} calories")
def e38(conn):
print("E38 - DELETE with complex condition")
cursor = conn.cursor()
sql = "DELETE FROM menu WHERE rating < 4.5 AND price > 10"
cursor.execute(sql)
conn.commit()
print(f"Rows deleted: {cursor.rowcount}")
print("Removed: expensive items with lower ratings")
# ============================================================================
# ALTER & DROP (E39-E40)
# ============================================================================
def e39(conn):
print("E39 - ALTER TABLE add column")
cursor = conn.cursor()
sql = "ALTER TABLE menu ADD COLUMN is_spicy BOOLEAN DEFAULT 0"
cursor.execute(sql)
print("Column 'is_spicy' added!")
# Update a few items
cursor.execute("UPDATE menu SET is_spicy = 1 WHERE dish_name IN ('Chicken Wings', 'Fish Tacos')")
conn.commit()
print("Sample spicy items marked")
def e40(conn):
print("E40 - DROP TABLE")
print("WARNING: This will delete the entire menu table!")
confirm = input("Type 'YES' to confirm: ")
if confirm == "YES":
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS menu"
cursor.execute(sql)
print("Table 'menu' has been dropped!")
else:
print("DROP cancelled.")
# call to main function
main()
Last updated