Singapore H2 Computing Wiki

Paper 2 Databases Drills

3 min read

Paper 2 Databases Drills

These are original Paper 2-style practice questions. They use exact schemas, file contents, test calls, and output evidence.

For practice, the questions may be attempted independently. If a question refers to an existing table, assume it contains the sample data stated in that question or create the table and insert the data before running the required operation.

Detailed answers and model code are in Paper 2 Databases Answers.

Revise the topic hub first:

Questions

Question 1: Create Table

Write Python code using sqlite3 to create a database school.db with a table:

Student(student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER)

After creating the table, print the table names in the database. [6]

Question 2: Insert Records

Create or use the Student(student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER) table. Insert these records with parameterised SQL:

S01,Asha,74
S02,Ben,49
S03,Chen,82

Print all rows ordered by student_id. [6]

Question 3: Read CSV to DB

Assume students.csv contains:

student_id,name,mark
S01,Asha,74
S02,Ben,49
S03,Chen,82

Create or use the Student(student_id TEXT PRIMARY KEY, name TEXT, mark INTEGER) table, then read the CSV file and insert the records. Print the number of rows inserted. [10]

Question 4: Simple Query

Assume the Student(student_id, name, mark) table contains:

S01,Asha,74
S02,Ben,49
S03,Chen,82

Print the names of students with marks at least 50, ordered by name. [5]

Question 5: Join Query

Create these tables and records:

Class(class_code TEXT PRIMARY KEY, tutor TEXT)
Student(student_id TEXT PRIMARY KEY, name TEXT, class_code TEXT)
 
Class:
24C1,Ms Tan
24C2,Mr Lim
 
Student:
S01,Asha,24C1
S02,Ben,24C2
S03,Chen,24C1

Student.class_code should match Class.class_code.

Write Python code to run a join query and print each student’s name with their tutor. [8]

Question 6: Update Record

Assume the Student(student_id, name, mark) table contains:

S01,Asha,74
S02,Ben,49
S03,Chen,82

Update S02 from mark 49 to 55. Print S02’s row after the update. [5]

Question 7: Delete Record

Assume the Student(student_id, name, mark) table contains:

S01,Asha,74
S02,Ben,49
S03,Chen,82

Delete the record with student_id = "S02". Print the remaining student IDs ordered by student_id. [5]

Question 8: NoSQL JSON

Assume events.json contains:

[
  {"event_id": "E01", "title": "Robotics", "tags": ["AI", "hardware"]},
  {"event_id": "E02", "title": "Web Apps", "tags": ["web", "database"]},
  {"event_id": "E03", "title": "Data Night", "tags": ["database", "visualisation"]}
]

Write a function events_with_tag(filename, tag) that returns the titles of events containing the given tag. Test it with tag "database". [7]

Question 9: Integrity Check

Write a function safe_insert_student(conn, student_id, name, mark) that checks whether student_id already exists before inserting.

Use parameterised SQL for both the duplicate check and the insert.

Test it by inserting S01, then trying to insert another record with S01. Print the return values and final row count. [8]

Question 10: DB Path Debug

Write code that creates a SQLite connection to school.db, prints the absolute path of the database file, and prints whether the path ends with school.db. [4]

Review Checklist

After attempting these questions, check whether you can:

  • create tables with primary keys;
  • use parameterised SQL for inserts, updates, and deletes;
  • read CSV and JSON files with correct type conversion;
  • run SELECT, JOIN, UPDATE, and DELETE statements;
  • check integrity before inserting duplicate keys;
  • print visible evidence for database operations.

Graph View

Backlinks