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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

The common data types

DataType in database software such as AccessTypical SQL typeExample
Names, codes, postcodes, phone numbersShort Text (up to 255 characters)TEXT or VARCHAR(n)'B01', '07700 900123'
Long notes or descriptionsLong Text (called Memo in older versions of Access)TEXTRepair history
Whole numbers or decimals used in calculationsNumber (field size Byte, Integer, Long Integer, Double or Decimal)INTEGER, REAL or DECIMALDays = 3
MoneyCurrencyDECIMAL(10,2) (SQLite stores REAL)18.00
Dates and timesDate/TimeDATE, TIME or DATETIME (SQLite stores ISO text)2026-03-14
Two-state (Boolean) valuesYes/No, displayed as Yes/No, True/False or On/OffBOOLEAN (SQLite stores 0 or 1)Paid? Yes
Automatic unique IDAutoNumberINTEGER PRIMARY KEY (auto-assigned in SQLite)CustomerID 7

Names of types differ between products, but the ideas are the same.

Choosing well

SELECT CAST('07700900123' AS INTEGER) AS StoredAsNumber, '07700900123' AS StoredAsText

Result (1 row):

StoredAsNumber | StoredAsText
---------------+-------------
7700900123     | 07700900123
SELECT d FROM (SELECT '9/3/2026' AS d UNION ALL SELECT '10/3/2026' UNION ALL SELECT '2/4/2026')
ORDER BY d

Result (3 rows):

d
---------
10/3/2026
2/4/2026
9/3/2026

ISO 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 d

Result (3 rows):

d
----------
2026-03-09
2026-03-10
2026-04-02

Field size and format

Properties refine a type:

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.

  1. BookingRef: Short Text, because it contains letters.
  2. GuestPhone: Short Text, to keep the leading zero and spaces; no arithmetic is done on it.
  3. ArrivalDate: Date/Time, so it sorts and can be used in date criteria.
  4. Nights: Number (Integer), used in calculations.
  5. RoomRate: Currency.
  6. BreakfastIncluded: Yes/No.

Answer: Text, Text, Date/Time, Number, Currency, Yes/No.

Key idea: Text for anything not calculated (phones, postcodes, codes keep leading zeros); Number for quantities; Currency for money; Date/Time for dates (sort and compare correctly); Yes/No for two states; AutoNumber for generated IDs. Format changes display, not the stored value.

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.