These twelve questions mix the five skills in database structure and validation: choosing field types, setting keys and validation, importing, spotting numeric-text mix-ups and explaining duplicates. All data is invented for this page. Questions go from easier to harder.
Try each one on paper first, then open the answer. Write explanations in full sentences, because that is how an exam rewards them.
Questions 1 to 4: types and keys
Q1. A table of school canteen items has the columns ItemCode (such as CN-012), Price (such as 3.50), StockCount (such as 48) and Vegetarian (Yes or No). Give a field type for each column.
Show answer
- ItemCode: text, because it holds letters and a hyphen and is never calculated.
- Price: currency or decimal, because it has two decimal places and is multiplied or added.
- StockCount: integer, because it is a whole count.
- Vegetarian: Boolean (yes/no), because only two values appear.
Q2. Explain why a membership number such as 007245 should be stored as text and not as a number.
Show answer
It is a label, not a quantity, since nobody adds or averages membership numbers. In a number field the zeros at the start would be dropped, giving 7245, which no longer matches the real number. Text keeps all six characters.
Q3. A table of library loans has the fields LoanID, BookTitle, BorrowerName and DateBorrowed. Which is the most suitable primary key, and why are the other three unsuitable?
Show answer
LoanID. It is unique for each loan, always present and never changes. BookTitle repeats when a book is borrowed more than once, BorrowerName repeats when one person borrows twice, and DateBorrowed repeats when two loans happen on one day.
Q4. A new table needs an ID of exactly 6 characters. Which type of check is this? Say whether ST0045 and ST045 are accepted.
Show answer
A length check. ST0045 has 6 characters, so it is accepted. ST045 has 5 characters, so it is rejected.
Questions 5 to 8: validation and numeric-text
Q5. A Mark field must accept whole marks from 0 to 100 inclusive. Write a validation condition and test it with 100, 101, 0 and -1.
Show answer
Condition: >=0 AND <=100.
- 100: 100 >= 0 true and 100 <= 100 true, accepted.
- 101: 101 <= 100 false, rejected.
- 0: 0 >= 0 true and 0 <= 100 true, accepted.
- -1: -1 >= 0 false, rejected.
The boundaries 0 and 100 are accepted because the question says inclusive.
Q6. A teacher enters 54 for a student whose real mark is 45. The Mark field has the rule from Q5. Does validation catch this? Explain.
Show answer
No. 54 is inside the range 0 to 100, so it passes. Validation only tests whether a value is reasonable and allowed. Catching a transposed value needs verification, such as comparing the entry with the original mark sheet.
Q7. A column stored as text contains 5, 12, 120 and 9. Write the order it shows when sorted ascending, and the order after converting to integer.
Show answer
As text: compare character by character. The first characters are 5, 1, 1 and 9, so 12 and 120 come first (12 is shorter), then 5, then 9: 12, 120, 5, 9.
As integer: 5, 9, 12, 120.
Q8. A postcode field shows 8000 for a source value of 08000. What went wrong, and how do you fix it?
Show answer
The field was stored as a number, which dropped the zero at the start. Change the field to text and import the data again from the source file, so that 08000 is kept in full.
Questions 9 to 12: import and duplicates
Q9. A CSV file has 1 header line and 8 data lines. After import, the table shows 9 records and the first record has the field names as its values. Name the likely cause and the fix.
Show answer
The option first row contains field names was not set, so the header was imported as a record. The correct count is 8. Delete the bad record or re-import with the header option turned on, then check the field types again.
Q10. A CSV line reads: P014,Lee, Anna,45.50 (the name was meant to be one value, Lee, Anna). The table has 3 fields: ID, Name and Fee. What happens on import, and how should the source be written?
Show answer
The comma inside the name is read as a delimiter, so the line splits into 4 values: P014, Lee, Anna and 45.50. They do not fit 3 fields, and Fee may receive the wrong value. The name should be in text qualifiers: P014,“Lee, Anna”,45.50.
Q11. A table of 50 records has every fee set to 20.00. It contains 3 duplicate records. State the fee total shown, the true total and the amount overstated.
Show answer
Shown: 50 × 20.00 = 1000.00. Real records: 50 - 3 = 47, so the true total is 47 × 20.00 = 940.00. Overstated by 1000.00 - 940.00 = 60.00, which also equals 3 × 20.00.
Q12. A table has the primary key MemberID. A member appears as M021 and again as M058 with all other fields identical. Why did the key not stop this, and what should happen next?
Show answer
The key only blocks identical key values. M021 and M058 differ, so both were accepted. Because all other fields match, they are almost certainly one person. Keep the original record (M021, the earlier one), delete M058 and check the record count and totals again.
If you got these wrong
| Type of error | Questions | Go back to |
|---|---|---|
| Wrong field type, or text/number confusion at set-up | Q1, Q2 | choosing field types from sample values |
| Key choice, validation rule or boundary error | Q3, Q4, Q5, Q6 | defining a key and a validation condition |
| Import settings, header or comma problems | Q9, Q10 | importing a small fictional dataset |
| Wrong sort order, zero lost, totals not working | Q7, Q8 | detecting an incorrect numeric-text interpretation |
| Duplicate effects and key limits | Q11, Q12 | explaining why a duplicate record matters |
Record each miss in the mistake log and retest queue so that you retest it later. The read-only SQL practice lab and the ICT practical task and evidence checker give more invented data to examine.
A teacher in online one-to-one ICT tuition can mark your written explanations with you and set new tables for the types of error you made.