Paper 2 Databases Answers

These answers correspond to Paper 2 Databases Drills.

Verification note: each model snippet was tested independently in a clean working directory.

The model answers below recreate and seed tables where needed so that each snippet can be tested independently.

Answer 1: Create Table

import sqlite3
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("""
CREATE TABLE Student (
    student_id TEXT PRIMARY KEY,
    name TEXT,
    mark INTEGER
)
""")
conn.commit()
 
rows = conn.execute(
    "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name"
).fetchall()
print(rows)
conn.close()

Expected output:

[('Student',)]

Mark points:

• imports sqlite3;
• connects to school.db;
• creates table Student;
• defines student_id as TEXT PRIMARY KEY;
• defines name and mark fields with suitable types;
• commits and shows table creation evidence.

Common weak answer:

• creating student_id without a primary key constraint.

Answer 2: Insert Records

import sqlite3
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("CREATE TABLE Student (student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER)")
records = [
    ("S01", "Asha", 74),
    ("S02", "Ben", 49),
    ("S03", "Chen", 82),
]
conn.executemany(
    "INSERT INTO Student (student_id, name, mark) VALUES (?, ?, ?)",
    records
)
conn.commit()
 
rows = conn.execute(
    "SELECT student_id, name, mark FROM Student ORDER BY student_id"
).fetchall()
print(rows)
conn.close()

Expected output:

[('S01', 'Asha', 74), ('S02', 'Ben', 49), ('S03', 'Chen', 82)]

Mark points:

• creates or uses the correct table;
• stores records as tuples or equivalent;
• uses parameterised SQL with placeholders;
• inserts all three records;
• commits the changes;
• prints rows in the required order.

Common weak answer:

• building SQL by concatenating values into the statement string.

Answer 3: Read CSV to DB

import csv
import sqlite3
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("CREATE TABLE Student (student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER)")
 
inserted = 0
with open("students.csv", "r", newline="") as file:
    reader = csv.DictReader(file)
    for row in reader:
        conn.execute(
            "INSERT INTO Student (student_id, name, mark) VALUES (?, ?, ?)",
            (row["student_id"], row["name"], int(row["mark"]))
        )
        inserted = inserted + 1
 
conn.commit()
print(inserted)
conn.close()

Expected output:

3

Mark points:

• imports and uses csv;
• reads the header row correctly;
• creates or uses Student;
• converts mark to integer;
• uses parameterised insert;
• inserts all CSV rows;
• commits the inserts;
• prints the correct inserted count.

Common weak answer:

• treating the header row as a student record.

Answer 4: Simple Query

import sqlite3
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("CREATE TABLE Student (student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER)")
conn.executemany(
    "INSERT INTO Student VALUES (?, ?, ?)",
    [("S01", "Asha", 74), ("S02", "Ben", 49), ("S03", "Chen", 82)]
)
conn.commit()
 
rows = conn.execute(
    "SELECT name FROM Student WHERE mark >= 50 ORDER BY name"
).fetchall()
for row in rows:
    print(row[0])
conn.close()

Expected output:

Asha
Chen

Mark points:

• uses SELECT name;
• uses table Student;
• filters with mark >= 50;
• orders by name;
• fetches and prints the matching names.

Common weak answer:

• using mark > 50, which would wrongly exclude a mark of exactly 50.

Answer 5: Join Query

import sqlite3
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("DROP TABLE IF EXISTS Class")
conn.execute("CREATE TABLE Class (class_code TEXT PRIMARY KEY, tutor TEXT)")
conn.execute("CREATE TABLE Student (student_id TEXT PRIMARY KEY, name TEXT, class_code TEXT)")
conn.executemany("INSERT INTO Class VALUES (?, ?)", [("24C1", "Ms Tan"), ("24C2", "Mr Lim")])
conn.executemany(
    "INSERT INTO Student VALUES (?, ?, ?)",
    [("S01", "Asha", "24C1"), ("S02", "Ben", "24C2"), ("S03", "Chen", "24C1")]
)
conn.commit()
 
rows = conn.execute("""
SELECT Student.name, Class.tutor
FROM Student
INNER JOIN Class
ON Student.class_code = Class.class_code
ORDER BY Student.name
""").fetchall()
 
for name, tutor in rows:
    print(name + " - " + tutor)
conn.close()

Expected output:

Asha - Ms Tan
Ben - Mr Lim
Chen - Ms Tan

Mark points:

• creates or uses both tables;
• inserts the given class and student data;
• joins Student to Class;
• uses matching class_code fields;
• selects student name and tutor;
• orders or prints results clearly;
• produces the required output.

Common weak answer:

• joining without an ON condition.

Answer 6: Update Record

import sqlite3
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("CREATE TABLE Student (student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER)")
conn.executemany(
    "INSERT INTO Student VALUES (?, ?, ?)",
    [("S01", "Asha", 74), ("S02", "Ben", 49), ("S03", "Chen", 82)]
)
conn.execute("UPDATE Student SET mark = ? WHERE student_id = ?", (55, "S02"))
conn.commit()
 
row = conn.execute(
    "SELECT student_id, name, mark FROM Student WHERE student_id = ?",
    ("S02",)
).fetchone()
print(row)
conn.close()

Expected output:

('S02', 'Ben', 55)

Mark points:

• uses UPDATE;
• sets mark to 55;
• uses WHERE student_id = ?;
• uses parameterised values;
• commits the update;
• prints the updated row.

Common weak answer:

• omitting the WHERE clause, which updates every student’s mark.

Answer 7: Delete Record

import sqlite3
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("CREATE TABLE Student (student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER)")
conn.executemany(
    "INSERT INTO Student VALUES (?, ?, ?)",
    [("S01", "Asha", 74), ("S02", "Ben", 49), ("S03", "Chen", 82)]
)
conn.execute("DELETE FROM Student WHERE student_id = ?", ("S02",))
conn.commit()
 
rows = conn.execute("SELECT student_id FROM Student ORDER BY student_id").fetchall()
print(rows)
conn.close()

Expected output:

[('S01',), ('S03',)]

Mark points:

• uses DELETE FROM Student;
• uses a WHERE clause;
• uses parameterised key value;
• commits the deletion;
• prints only the remaining IDs.

Common weak answer:

• omitting the WHERE clause and deleting all records.

Answer 8: NoSQL JSON

import json
 
 
def events_with_tag(filename, tag):
    with open(filename, "r", encoding="utf-8") as file:
        events = json.load(file)
 
    titles = []
    for event in events:
        if tag in event["tags"]:
            titles.append(event["title"])
    return titles
 
 
print(events_with_tag("events.json", "database"))

Expected output:

['Web Apps', 'Data Night']

Mark points:

• imports and uses json;
• loads the file into Python structures;
• treats the outer data as a list of dictionaries;
• accesses the nested tags list;
• filters events containing the requested tag;
• returns matching titles;
• shows the required output.

Common weak answer:

• checking event["tag"] when the field is named tags and stores a list.

Answer 9: Integrity Check

import sqlite3
 
 
def safe_insert_student(conn, student_id, name, mark):
    existing = conn.execute(
        "SELECT student_id FROM Student WHERE student_id = ?",
        (student_id,)
    ).fetchone()
    if existing is not None:
        return False
 
    conn.execute(
        "INSERT INTO Student (student_id, name, mark) VALUES (?, ?, ?)",
        (student_id, name, mark)
    )
    conn.commit()
    return True
 
 
conn = sqlite3.connect("school.db")
conn.execute("DROP TABLE IF EXISTS Student")
conn.execute("CREATE TABLE Student (student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER)")
 
print(safe_insert_student(conn, "S01", "Asha", 74))
print(safe_insert_student(conn, "S01", "Anya", 88))
row_count = conn.execute("SELECT COUNT(*) FROM Student").fetchone()[0]
print(row_count)
conn.close()

Expected output:

True
False
1

Mark points:

• queries for an existing ID first;
• uses parameterised SQL;
• returns False for duplicate ID;
• inserts only when ID is absent;
• commits successful insert;
• returns True for successful insert;
• prints final row count showing no duplicate was inserted.

Common weak answer:

• checking duplicate names instead of duplicate primary keys.

Answer 10: DB Path Debug

import os
import sqlite3
 
 
db_name = "school.db"
conn = sqlite3.connect(db_name)
 
path = os.path.abspath(db_name)
print(path)
print(path.endswith("school.db"))
 
conn.close()

Expected output:

/.../school.db
True

The exact absolute path depends on the working directory.

Mark points:

• connects to school.db;
• obtains an absolute path;
• prints the absolute path and whether it ends with school.db;
• closes the connection.

Common weak answer:

• printing only "school.db", which does not reveal the working directory being used.