Area 04 · Database manipulation
Contact book in SQLite
Store contacts in an embedded SQLite database and support add, list, update and delete commands.
- Spec no.
- HO-07
- Level
- Warm-up
- Time box
- 1 h
- Reference
- Java · C#
- Grading
- Self-assessed · rubric is ours, not DICT's
01 Coverage
| Area | Sub-skills practised | Role |
|---|---|---|
| 01 Data manipulation | Validation | Also |
| 04 Database manipulation | Create, Read, Update, Delete | Primary |
Sign up to start coding practice
Read the task freely. An account is required before editing and running a solution. Code runs also need an internet connection.
Sign up or sign in02 Requirements
- Create a fresh contacts.db with table contacts(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, mobile TEXT NOT NULL UNIQUE, city TEXT).
- add name|mobile|city: mobile must be 11 digits starting with 09. Print Added #id, Invalid mobile: x or Duplicate mobile: x.
- list prints id name mobile city ordered by id, or (no contacts).
- update id city=value or update id mobile=value. Only those two columns may change: choose the column name from a fixed list, never from raw input.
- delete id prints Deleted #id or No contact #id.
- Use parameterised queries for every value.
03 Input and output
- One command per line
- One result line per command; list prints one line per row
add Ana Reyes|09171234567|Quezon City
add Ben Cruz|09181112222|Cebu City
add Bad Number|12345|Iloilo
add Copy|09171234567|Pasig
list
update 2 city=Davao City
delete 1
delete 9
listAdded #1
Added #2
Invalid mobile: 12345
Duplicate mobile: 09171234567
1 Ana Reyes 09171234567 Quezon City
2 Ben Cruz 09181112222 Cebu City
Updated #2
Deleted #1
No contact #9
2 Ben Cruz 09181112222 Davao CityMatch the sample exactly first, then write at least two test inputs of your own.
04 Edge cases to test
- A mobile that already exists
- update with an unknown column such as name=X
- delete of an id that was never used
- A non-numeric id
05 Database
Engine: SQLite (Java: org.xerial sqlite-jdbc; C#: Microsoft.Data.Sqlite). An embedded database needs no server, which suits a timed sitting.
- The reference solution deletes contacts.db on start so ids begin at 1 for the sample.
The table definitions are in the requirements above and in the reference solution.
06 Starter outline and hints
open db; CREATE TABLEloop: split command → add / list / update / deleteeach uses a prepared statement with ? or $p parameters
Hint 1
SQLite reports a UNIQUE constraint failure as an exception; catch it and print the duplicate message.
07 Practice rubric
| Criterion | Sub-skills | Look for | Pts |
|---|---|---|---|
| Schema | Create | Primary key, NOT NULL and UNIQUE where specified. | 15 |
| Create and read | CreateRead | Inserts return the new id; list is ordered and handles an empty table. | 30 |
| Update and delete | UpdateDelete | Affected-row counts drive the messages; column whitelist used. | 30 |
| Validation and safety | Validation | Mobile format checked; duplicates reported; no SQL built from input. | 25 |
| Total | 100 | ||
Our own weighting for practice. It is not the DICT scoring scheme.
08 Self-check
Tick only what you have verified by running your own program.
Loading saved checks…
09 Reference solution
Both versions compiled and reproduced the sample output on Java 21 and .NET 8 when this site was built. Reference solutions are in Java and C#. The 2023 FAQ copy also lists C, C++ and Visual Basic 6.0 / .NET; the same structure carries over, but you will need your language's own file and database APIs.
Try the whole brief first. The reference is one correct approach, not the only one, and reading it early removes most of the practice value.
10 Exam-day habits
- Work without internet search to simulate exam conditions.
- Type the sample input yourself and compare your output line by line before you look at the reference.