Problems caused by inconsistent data formats when inserting data

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Two sources give dates as 03/04/2026 and 2026-04-03. What is the problem when inserting both into one date field?

A There is no problem because both are dates, so a database always understands them

B The formats are inconsistent, so one may be rejected or misread (03/04 could be 3 April or 4 March)

2. A CSV file stores a price as the text "$12.50" but the database field is a decimal number. What should happen before insertion?

A Remove the $ symbol and convert the text to a number like 12.50

B Insert the text as it is, because the database will fix the type itself

3. Which is a result of inserting the text value "twenty" into a numeric Quantity field?

A The database converts it to 20 automatically and stores it

B The insert fails or the value is wrong, because text is not a number

4. One file separates fields with commas and another uses semicolons. What happens if both are read with a comma delimiter?

A The semicolon file is read as one long field, so the data lands in the wrong fields

B Both files are split correctly because delimiters do not affect reading

5. Source A lists fields as Name,Age,Year and Source B lists them as Year,Name,Age. What must be done before inserting both into one table?

A Reorder the fields so every record matches the table's field order

B Nothing, because the data goes in by position and will match up

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1INSERT INTO orders VALUES (11, 1, 2, 3, '03/02/2026');		AThe date is written in a different style from the YYYY-MM-DD format used by the table, so it is inconsistent and must be converted.
2INSERT INTO products VALUES (11, 'Ruler', 'Stationery', '$2.50', 10);		BThe yearLevel is written as the word 'Seven' (text) instead of the number 7, so it is the wrong format for a numeric field.
3Two files use different delimiters, so one must be split differently before its fields can be inserted into the same table		CNguyen,Ava,7 must become Ava,Nguyen,7
4Sources can list fields in a different order, so values must be reordered to match the table before insertion		DAva\|Nguyen\|7 versus Ava,Nguyen,7
5INSERT INTO students VALUES (11, 'Kai', 'Lee', 'Seven', 'Red', 60);		EFails to match the stored dates because it uses DD/MM/YYYY instead of the YYYY-MM-DD format the orderDate column uses
6INSERT INTO orders VALUES (11, 2, 3, 1, 'Feb 5 2026');		FInserts a price as text with a dollar sign instead of a plain number, so the price column holds inconsistent data

Definitions

inconsistent data format

Fill in the Blanks

Complete each line of code by filling in the missing parts.

markNum ← _________(markText) insert markNum into results

fields ← _____(line, ";")

fields ← split(line, ";") // File B order: mark;name. Index from 1 name ← fields[_]

parts ← split(date, "/") // DD/MM/YYYY iso ← parts[3]+"-"+parts[2]+"-"+parts[_]

if format _ "YYYY-MM-DD" then date ← convertDate(raw) end if

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A parts <- split(csvRow, ';')

B name <- parts[0]

C mark <- integer(parts[1])

D insert(students, name, mark)

Question 2

A dateText <- '15/03/2025'

B p <- split(dateText, '/')

C iso <- p[0]+'-'+p[1]+'-'+p[2]

D insert(events, iso)

Question 3

A priceTxt <- '$4.50'

B clean <- remove(priceTxt, '$')

C price <- string(clean)

D insert(items, price)

Question 4

A row <- '7,Ava'

B p <- split(row, ',')

C name <- p[0]

D year <- integer(p[1])

Question 5

A mark <- '88'

B total <- mark + 5

C insert(results, total)

Answer Key

Problems caused by inconsistent data formats when inserting data

Section A: Quick Questions

B — The formats are inconsistent, so one may be rejected or misread (03/04 could be 3 April or 4 March)

A — Remove the $ symbol and convert the text to a number like 12.50

B — The insert fails or the value is wrong, because text is not a number

A — The semicolon file is read as one long field, so the data lands in the wrong fields

A — Reorder the fields so every record matches the table's field order

Section B: Match the Pairs

INSERT INTO orders VALUES (11, 1, 2, 3, '03/02/2026'); → Fails to match the stored dates because it uses DD/MM/YYYY instead of the YYYY-MM-DD format the orderDate column uses

INSERT INTO products VALUES (11, 'Ruler', 'Stationery', '$2.50', 10); → Inserts a price as text with a dollar sign instead of a plain number, so the price column holds inconsistent data

Two files use different delimiters, so one must be split differently before its fields can be inserted into the same table → Ava|Nguyen|7 versus Ava,Nguyen,7

Sources can list fields in a different order, so values must be reordered to match the table before insertion → Nguyen,Ava,7 must become Ava,Nguyen,7

INSERT INTO students VALUES (11, 'Kai', 'Lee', 'Seven', 'Red', 60); → The yearLevel is written as the word 'Seven' (text) instead of the number 7, so it is the wrong format for a numeric field.

INSERT INTO orders VALUES (11, 2, 3, 1, 'Feb 5 2026'); → The date is written in a different style from the YYYY-MM-DD format used by the table, so it is inconsistent and must be converted.

Section C: Definitions

inconsistent data format — Data from different sources written in different ways (dates, text vs numbers, delimiters). It must be converted to one consistent format before it can be inserted into a data store.

Section D: Fill in the Blanks

markNum ← toInteger(markText)
insert markNum into results

fields ← split(line, ";")

fields ← split(line, ";")
// File B order: mark;name. Index from 1
name ← fields[2]

parts ← split(date, "/") // DD/MM/YYYY
iso ← parts[3]+"-"+parts[2]+"-"+parts[1]

if format ≠ "YYYY-MM-DD" then
    date ← convertDate(raw)
end if

Section E: Spot the Error

A — A CSV row uses commas as its delimiter. Splitting on ';' leaves the whole row in one piece, so the fields cannot be inserted. It should be split(csvRow, ',').

C — The source date is DD/MM/YYYY, so p[0] is the day and p[2] is the year. The line keeps the day-first order, so the store gets an inconsistent date. It should build year-month-day: p[2]+'-'+p[1]+'-'+p[0].

C — The price column stores numbers, but string() leaves the value as text. The value must be converted to a real number before insertion: price <- real(clean).

A — The fields are in a different order from the one the code expects. The code reads name first and year second, so this row would put 7 in name and fail to convert 'Ava' to an integer. The row should be 'Ava,7'.

B — mark is text, so adding 5 to it does not give a numeric result. The text must be converted first: total <- integer(mark) + 5.