All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Choosing data types and formats
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Every field needs the right data type (specification 18.1.2) and a suitable common or customised format (specification 18.1.3). The first exam task often asks you to find and fix a wrong data type, and in June 2024 the structure question gave a mark for Date/Time and Currency types in the new tables.
The common data types
| Data | Type in database software such as Access | Typical SQL type | Example |
|---|---|---|---|
| Names, codes, postcodes, phone numbers | Short Text (up to 255 characters) | TEXT or VARCHAR(n) | 'B01', '07700 900123' |
| Long notes or descriptions | Long Text (called Memo in older versions of Access) | TEXT | Repair history |
| Whole numbers or decimals used in calculations | Number (field size Byte, Integer, Long Integer, Double or Decimal) | INTEGER, REAL or DECIMAL | Days = 3 |
| Money | Currency | DECIMAL(10,2) (SQLite stores REAL) | 18.00 |
| Dates and times | Date/Time | DATE, TIME or DATETIME (SQLite stores ISO text) | 2026-03-14 |
| Two-state (Boolean) values | Yes/No, displayed as Yes/No, True/False or On/Off | BOOLEAN (SQLite stores 0 or 1) | Paid? Yes |
| Automatic unique ID | AutoNumber | INTEGER PRIMARY KEY (auto-assigned in SQLite) | CustomerID 7 |
Names of types differ between products, but the ideas are the same.
Choosing well
- Use Text for anything not used in arithmetic, even if it contains digits: phone numbers, postcodes, ID codes like B01. As a number, a leading zero is lost:
SELECT CAST('07700900123' AS INTEGER) AS StoredAsNumber, '07700900123' AS StoredAsTextResult (1 row):
StoredAsNumber | StoredAsText
---------------+-------------
7700900123 | 07700900123- Use Currency for money so values display with a currency symbol and two decimal places and avoid rounding errors.
- Use Date/Time for dates so they sort and compare correctly and can be used in date criteria and calculations. Dates typed as text in day/month/year form sort as strings, which gives the wrong order:
SELECT d FROM (SELECT '9/3/2026' AS d UNION ALL SELECT '10/3/2026' UNION ALL SELECT '2/4/2026')
ORDER BY dResult (3 rows):
d
---------
10/3/2026
2/4/2026
9/3/2026ISO dates (year-month-day), which is how SQLite stores dates, sort correctly:
SELECT d FROM (SELECT '2026-03-09' AS d UNION ALL SELECT '2026-03-10' UNION ALL SELECT '2026-04-02')
ORDER BY dResult (3 rows):
d
----------
2026-03-09
2026-03-10
2026-04-02Field size and format
Properties refine a type:
- Field size: the maximum text length (Surname 50 characters) or the number size (Byte, Integer, Long Integer, Double).
- Format: how a value is displayed without changing it. Common (built-in) formats include Currency, Fixed and Percent with a set number of decimal places, Short Date, Long Date, and Yes/No, True/False or On/Off for a Yes/No field. Customised formats are typed into the Format property: dd/mm/yyyy or dd mmm yyyy for dates (14 Mar 2026), > to show text in upper case, < for lower case.
- Default value: filled in automatically, for example =Date() for today in Access, or DEFAULT in SQL.
- Required: whether the field must have a value.
Format changes the display; the data type decides what can be stored and how it behaves in sorting and calculations.
Worked example
Choose a data type for each field of a hotel booking table: BookingRef (e.g. HB0042), GuestPhone, ArrivalDate, Nights, RoomRate, BreakfastIncluded.
- BookingRef: Short Text, because it contains letters.
- GuestPhone: Short Text, to keep the leading zero and spaces; no arithmetic is done on it.
- ArrivalDate: Date/Time, so it sorts and can be used in date criteria.
- Nights: Number (Integer), used in calculations.
- RoomRate: Currency.
- BreakfastIncluded: Yes/No.
Answer: Text, Text, Date/Time, Number, Currency, Yes/No.
Check yourself
- Which data type should a mobile phone number field use?
- Number (Long Integer)
- Short Text
- Currency
- Date/Time
Show answer
Short Text. Phone numbers are not calculated and storing them as numbers drops the leading zero.
- A phone number 01632960001 is stored in a Number field. How many digits are stored? (A number cannot keep a leading zero.)
Show answer
10. As a number it becomes 1632960001, so 10 digits; the leading zero is lost.
- Which data type should DailyRate use in database software such as Access?
- Short Text
- Currency
- Yes/No
- AutoNumber
Show answer
Currency. Money should be Currency: correct display and no rounding errors in calculations.
- A query sorting hires by HireDate puts 10/03/2026 before 9/03/2026. What is the most likely cause?
- The query sorts in descending order
- HireDate is the primary key
- HireDate has a Text data type instead of Date/Time
- The table has too many records
Show answer
HireDate has a Text data type instead of Date/Time. Text sorts character by character, so '1' comes before '9'. A Date/Time type sorts by actual date.
- Which result does this query give on the Harbour Bike Hire database?
```sql
SELECT MIN(HireDate), MAX(HireDate) FROM tblHire
```
- 2026-03-02, 2026-05-01
- 2026-03-20, 2026-05-01
- 2026-03-02, 2026-04-18
- 2026-05-01, 2026-03-02
Show answer
2026-03-02, 2026-05-01. ISO dates compare correctly as text, so MIN and MAX give the earliest and latest hires: 2026-03-02, 2026-05-01.
- A designer sets the Format property of a Surname field to >. What happens?
- Surnames are converted and stored in capitals
- Surnames longer than 10 characters are rejected
- Surnames are displayed in capitals but stored as typed
- The field becomes required
Show answer
Surnames are displayed in capitals but stored as typed. Format changes display only. To change stored data you would need an update query or an input mask.
- Which field is best suited to the Yes/No data type?
- HelmetIncluded
- CustomerSurname
- HireDate
- DailyRate
Show answer
HelmetIncluded. A helmet is either included or not: two states.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: WIT14/01 question paper, June 2024
- Pearson: WIT14/01 mark scheme, June 2024
Last updated 8 November 2026. Spotted a mistake? Tell us.