Year 11 · ICT · Summer 1 · Final revision and the exams

Red and amber points, pupil by pupil

Exam practicePresent ▶

What this lesson covers

7.2 Design

Cambridge IGCSE ICT 0417 · Topic 7: The systems life cycle · Paper 1

18.2 Manipulate data: calculations, sorting and queries

Cambridge IGCSE ICT 0417 · Topic 18: Databases · Paper 2

20.1c Lookup functions

Cambridge IGCSE ICT 0417 · Topic 20: Spreadsheets · Paper 3

You will learn to

Identify your red and amber points from your tracker, in theory and practical skills.
Use the revision cycle on one point: key terms, quiz, then a past-paper question or task.
Evaluate your progress: change a point's colour only after full marks under time.

Success criteria

I can list my three reddest points before the lesson starts.
I can show a marked past-paper question or task for each point I worked on.
I can do one practical skill under time without notes.

The syllabus points for these sections

Design file and data structures: field name, field length, data type, coding of data.
Describe range, character, length, type, format, presence and check digit validation.
Calculate an ISBN-13 check digit.
Design input formats (data capture forms) and output formats (screen and report layouts).
Create calculated fields in queries and calculated controls in reports and forms.
Use sum, average, maximum, minimum and count.
Sort on one or more fields, ascending or descending.
Write query criteria with =, >, <, >=, <=, <> and AND, OR, NOT.
Use LIKE with the wildcards * and ?.
Give clear evidence of every query you build.
Use VLOOKUP with an exact match and with an approximate match.
Use HLOOKUP on a table laid out across rows.
Use LOOKUP on sorted data and know its limits.
Use XLOOKUP, including a message when nothing is found.

Key words

Say each word, then read what it means. Every one is in the glossary too.

System specification: A list of the hardware and software the new system needs.
Field: One column of a table: one piece of data stored for every record.
Coding of data: Storing short codes such as M or F instead of whole words.
Validation: An automatic computer check that data is sensible and allowed.
Check digit: An extra digit calculated from the other digits, used to detect input errors.
Data capture form: A paper or on-screen form used to collect data.
Test plan: A table listing each test, its data, the expected and actual outcomes and any remedial action.
Normal data: Sensible data that should be accepted.
Direct changeover: The old system stops and the new one starts immediately.
Database: An organised store of data that can be searched, sorted and reported on.
Table: A set of records about one kind of thing, such as members or bookings.
Record: One row of a table: all the data about one item.
Field type: The kind of data a field holds: text, numeric, date/time or Boolean.
Boolean: A field type with only two values, such as Yes/No or True/False.
Query: A search that shows only the records that meet given criteria.
Criteria: The condition COUNTIF uses to decide which cells to count.
Wildcard: A symbol standing for unknown characters: * for any number, ? for exactly one.
Calculated field: A field in a query that is worked out from other fields, not typed in.
Calculated control: A box on a report or form that shows a calculation, such as a total.
Cell: One box in the grid, where a column and a row meet.
Cell reference: The address of a cell: column letter then row number, such as C3.
Range: A block of cells written first:last, such as C3:C8.
Formula: A calculation in a cell that starts with =.
Function: A ready-made formula with a name, such as SUM or VLOOKUP.
Argument: One input to a function, written inside its brackets and separated by commas.
Absolute reference: A reference like $I$7 that stays fixed when the formula is copied.
Lookup: A function that finds a value in a table and returns a matching value.
Exact match: A lookup that only accepts the identical value (FALSE in VLOOKUP).

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

Start from your tracker

Your tracker lists every theory point and practical skill with a colour. Work on red first, then amber.

Green points only need a short quiz or task now and then.

?Why?

Why not revise what I already know?

It feels good, but it gains few marks. Marks come from closing gaps.

The revision cycle

For a theory point:

Write the key terms and their meanings from memory.
Check them and correct them in another colour.
Do the quiz for that point.
Do one past-paper question, timed, and mark it with the scheme.

For a practical skill:

Do the skill once with your notes open.
Close your notes. Do it again on a new past-paper task, timed.
Mark it with the scheme. Check your evidence too.

★Remember

A practical skill is green only when you can do it under time, without notes.

!Watch out

Copying out notes is not revision. If you are not recalling or doing, you are not revising.

7.2 Design

Designing the file structure

A file here means a table of records. A record is all the data about one thing. A field is one item of data in a record.

For each field the designer chooses a field name, a data type and a field length (the maximum number of characters).

Coding of data means storing short codes instead of long words, for example M or F, Y or N.

Field name	Data type	Field length	Example
StudentID	Text	6	S10432
Surname	Text	30	García
DateOfBirth	Date/Time	10	14/03/2011
Gender	Text (coded)	1	F
FinesOwed	Numeric (currency)	6	1.50
Overdue	Boolean	1	Y

?Why?

Codes save storage space, are quicker to type and are easier to validate. There are only a few allowed values, so mistakes are easy to spot.

!Watch out

Phone numbers and IDs with leading zeros are stored as text, not numbers. A numeric field would drop the leading zero (0034 would become 34).

Validation: what it is and why we need it

Validation is an automatic check, done by the computer, that data entered is sensible and allowed.

It stops impossible or unreasonable data getting into the system.

Validation cannot tell if data is correct. It can only tell if it is reasonable. An age of 15 passes a range check even if the student is really 14.

Check	What it checks	Example
Range check	The value is between a lower and an upper limit	Exam mark from 0 to 100
Length check	The number of characters is exact, or within limits	A Spanish mobile number has exactly 9 digits
Type check	The data is the right data type	Age must be a whole number, not 'fifteen'
Character check	Only allowed characters are used	A surname may contain letters, spaces, hyphens and apostrophes, but no digits
Format check	The data follows a set pattern	StudentID is a letter then 5 digits: S10432. A date is dd/mm/yyyy
Presence check	The field is not left empty	Surname must be filled in
Check digit	An extra digit worked out from the other digits matches	The last digit of an ISBN or a supermarket barcode

e.g.Example

Try the toy: type 19 in the Age box, which has a range check of 11 to 18. Then type 'abc' there for a type check. Leave the Surname box empty to trigger the presence check.

!Watch out

Do not mix up validation and verification. Verification checks data was copied correctly (double entry, visual check). Validation checks data is sensible.

There is something to try for this step in the IGCSE library.

The check digit, worked through

A check digit is an extra digit added to the end of a code. It is calculated from the other digits.

When the code is typed or scanned, the computer recalculates the check digit. If it does not match, there is an error.

It catches mistyped digits and transposition errors (two digits swapped, such as 45 typed as 54).

ISBN-13 (the number on books) uses weights of 1 and 3 and modulo 10. Modulo 10 means the remainder after dividing by 10.

e.g.Example

Find the check digit for the first 12 digits of an ISBN: 978030640615 Step 1: multiply digits by 1, 3, 1, 3 … in turn: 9×1=9, 7×3=21, 8×1=8, 0×3=0, 3×1=3, 0×3=0, 6×1=6, 4×3=12, 0×1=0, 6×3=18, 1×1=1, 5×3=15 Step 2: add them: 9+21+8+0+3+0+6+12+0+18+1+15 = 93 Step 3: remainder after dividing by 10: 93 MOD 10 = 3 Step 4: subtract from 10: 10 − 3 = 7 (If the remainder is 0, the check digit is 0.) Full ISBN: 978-0-306-40615-7 Spot an error: someone types 9780306406517 (1 and 5 swapped). The weighted total becomes 92 instead of 100 (counting the check digit 7 × 1). 92 is not a multiple of 10, so the computer rejects it.

?Why?

Multiplying by different weights means swapped digits give a different total. A plain sum would not catch a swap, because 1+5 and 5+1 are the same.

Input formats: data capture forms

A data capture form collects data from a person. It can be on paper or on screen.

A good form has a clear title, instructions, logical field order and enough space for each answer.

On-screen forms can use drop-down menus, radio buttons (choose one) and tick boxes (choose any). These limit what can be entered, so there are fewer errors.

Paper forms may use boxes with one character per box, so writing is easier to read or scan by OCR.

e.g.Example

Library sign-up screen: Title: 'New library member' StudentID [S_____] (format shown as a hint) Surname [__________] Year group: drop-down 7, 8, 9, 10, 11, 12, 13 Gender: radio buttons F / M Buttons: Save Clear Help

!Watch out

Exam answers about forms need specific features: 'use a drop-down for year group' earns a mark, 'make it look nice' does not.

Output formats: screen layouts and report layouts

Output formats decide how results are shown: on a screen or in a printed report.

A screen layout shows where each item appears, the fonts, the colours and any buttons.

A report layout shows the title, column headings, the order of fields, page numbers and totals.

The designer plans these early, so users can agree them before any building starts.

e.g.Example

Weekly overdue report layout: Title: Overdue books, week ending 13/11/2026 Columns: Tutor group | Surname | Book title | Days overdue Sorted by tutor group, then surname Footer: page 1 of 3, total overdue books: 47

?Why?

Showing users the layouts before building means changes are cheap. Changing a finished report later costs time and money.

18.2 Manipulate data: calculations, sorting and queries

Calculated fields and calculated controls

A calculated field is a new field in a query, worked out from other fields. It is not typed in and not stored in the table.

A calculated control is a box on a report or form that shows a calculation, such as a total.

In most database software you give the new field a name, a colon, then the formula. Field names go in square brackets.

e.g.Example

Booking 101: Hours 1.5, Rate €12.00 Cost = 1.5 × 12.00 = €18.00 Booking 103: Hours 2, Rate €11.00 Cost = 2 × 11.00 = €22.00 All eight costs: 18.00, 4.50, 22.00, 4.50, 8.00, 12.00, 24.00, 11.00

Calculated field in a query:
Cost: [Hours]*[Rate]

Calculated control in a report footer:
=Sum([Cost])

?Why?

Calculating, not storing, keeps the answer right. If someone corrects the hours, the cost updates by itself.

!Watch out

Spell the field names exactly as in the table. [Hour] instead of [Hours] makes the software ask for a value, and the answer is wrong.

Sum, average, maximum, minimum, count

These five functions summarise many records into one value. You use them in reports (calculated controls) or in queries (a totals row).

Sum adds up. Average adds up and divides by how many. Max finds the biggest. Min finds the smallest. Count counts the records.

Function	Formula typed	Result for all 8 bookings
Sum	=Sum([Cost])	€104.00
Average	=Avg([Cost])	€13.00
Maximum	=Max([Cost])	€24.00
Minimum	=Min([Cost])	€4.50
Count	=Count([BookingID])	8

e.g.Example

Sum: 18 + 4.50 + 22 + 4.50 + 8 + 12 + 24 + 11 = 104.00 Average: 104.00 ÷ 8 = 13.00 Max: 24.00 (booking 107) Min: 4.50 (bookings 102 and 104) Count: 8 records

!Watch out

Put a total in the report footer, not the page footer. A page footer total often shows an error or only that page's records.

Sort on one or more fields

Ascending means A to Z, smallest to largest, earliest date to latest. Descending is the reverse.

You can sort on two fields. The first sort field decides the main order. The second only matters when the first field has equal values.

In most query grids the software sorts from left to right. Put the main sort field to the left of the second one.

e.g.Example

Task: sort by Activity ascending, then by Cost descending. Padel 107 €24.00 Padel 101 €18.00 Padel 106 €12.00 Pilates 105 €8.00 Swimming 102 €4.50 Swimming 104 €4.50 Tennis 103 €22.00 Tennis 108 €11.00

?Why?

Within the three Padel bookings, the activity is the same, so the second sort (Cost, largest first) decides their order.

!Watch out

If Cost sits to the left of Activity in the grid, the software sorts by Cost first. The printout is then in the wrong order and loses the mark.

Read a query grid: a simple search

A query asks the database a question and shows only the matching records. Most software gives you a design grid.

Each column is one field. The rows are: Field, Table, Sort, Show, Criteria and Or.

Criteria is the condition a record must meet. Show decides if the field appears in the result.

Task: find all Padel bookings, by date. The software adds quote marks around text, so "Padel" is fine.

Field	BookingID	Activity	BookDate	Cost: [Hours]*[Rate]
Table	Bookings	Bookings	Bookings
Sort			Ascending
Show	✓	✓	✓	✓
Criteria		"Padel"
Or

e.g.Example

Result (3 records): 101 Padel 06/10/2026 €18.00 106 Padel 09/10/2026 €12.00 107 Padel 10/10/2026 €24.00

Comparison operators

Comparison operators compare a field with a value. They work on numbers, currency and dates.

= equal to. > greater than. < less than. >= greater than or equal to. <= less than or equal to. <> not equal to.

For dates, "greater than" means later. Some software puts # signs round a date for you. That is normal.

Criteria	Field	Matching bookings
>10	Cost	101, 103, 106, 107, 108
<=8	Rate	102, 104, 105
>=08/10/2026	BookDate	104, 105, 106, 107, 108
<>"Swimming"	Activity	101, 103, 105, 106, 107, 108
2	Hours	103, 107

!Watch out

Watch the difference between > and >=. ">08/10/2026" leaves out bookings 104 and 105, which are on 8 October. Read whether the task says "after" or "on or after".

AND: every condition must be true

AND means a record must meet all the conditions. AND makes the result smaller.

In a query grid, conditions on the same Criteria row are joined with AND.

Task: find Padel bookings that are not paid.

Field	BookingID	Activity	Paid
Table	Bookings	Bookings	Bookings
Sort
Show	✓	✓	✓
Criteria		"Padel"	No
Or

e.g.Example

Padel bookings: 101, 106, 107 Of these, Paid = No: only 106 Result: 1 record (106, Padel, No) A range on one field also uses AND: Cost criteria: >=10 And <=20 Result: 101 (€18.00), 106 (€12.00), 108 (€11.00)

?Why?

Cost cannot be both above 20 and below 10, so AND inside one cell gives a range. Some software also accepts Between 10 And 20.

OR: any condition can be true

OR means a record needs to meet at least one condition. OR makes the result bigger.

In a query grid, conditions on different rows (Criteria and Or) are joined with OR. You can also type "Padel" Or "Tennis" in one cell.

Each row is read as a complete condition on its own. This matters when you mix AND with OR.

Field	BookingID	Activity	Paid
Table	Bookings	Bookings	Bookings
Sort
Show	✓	✓	✓
Criteria		"Padel"	No
Or		"Tennis"	No

e.g.Example

Task: unpaid Padel or Tennis bookings. Row 1: Padel AND No → 106 Row 2: Tennis AND No → 103 Result: 103 and 106 Wrong grid: "No" typed only on row 1. Row 1: Padel AND No → 106 Row 2: Tennis (any Paid) → 103, 108 Result: 103, 106, 108. Booking 108 is paid, so this is wrong.

!Watch out

When you mix AND and OR, repeat the shared condition on every row. This is one of the most common lost marks in Paper 2.

NOT, LIKE and wildcards

NOT finds records that do not match. Not "Swimming" gives the same result as <>"Swimming".

LIKE searches for a pattern instead of an exact value. It uses wildcards: symbols that stand for unknown characters.

The `*` wildcard stands for any number of characters, including none. The `?` wildcard stands for exactly one character.

Try it below. The toy uses a different table: a sports centre's bookings. Press the Member LIKE S* example, then type Not in front of Like "S*". Watch the matching and non-matching records swap. Then try Like "?a*".

Criteria	Field	Matches	Bookings / members found
Like "P*"	Activity	Starts with P	Padel and Pilates: 101, 105, 106, 107
Not Like "P*"	Activity	Does not start with P	Swimming and Tennis: 102, 103, 104, 108
Like "*ing"	Activity	Ends with ing	Swimming: 102, 104
Like "?a*"	LastName	Second letter is a	García, Martín, Navarro (M01, M04, M05)

?Why?

In "?a*" the ? uses up exactly the first letter, so the a must be second. Ruiz and López fail because their second letters are u and ó.

!Watch out

Some database software uses % and _ instead of * and ?. Cambridge uses * and ?. If you type "P*" without LIKE, many programs search for a real star.

There is something to try for this step in the IGCSE library.

Queries across two tables, and evidence

Because the tables are related, one query can show fields from both. Add both tables to the query; the relationship joins them on MemberID.

Task: show the name, activity and cost of every unpaid booking, with a charge after discount.

Evidence: take a screenshot of the design grid showing all criteria in full, and a screenshot or printout of the result.

Field	FirstName	LastName	Activity	Paid	Charge: [Hours][Rate](1-[Discount])
Table	Members	Members	Bookings	Bookings
Sort		Ascending
Show	✓	✓	✓	✗	✓
Criteria				No
Or

e.g.Example

Unpaid bookings: 103, 105, 106, sorted by LastName Carmen López, Pilates: 1 × 8.00 × (1 − 0.3) = €5.60 Diego Martín, Tennis: 2 × 11.00 × (1 − 0) = €22.00 Sofía Navarro, Padel: 1 × 12.00 × (1 − 0.2) = €9.60

?Why?

Paid is used to search but is not shown, so Show is unticked. The task decides which fields appear.

!Watch out

Common lost marks: criteria cut off in a narrow column, a typing error such as "Paddel", showing fields the task did not ask for, or no screenshot of the criteria at all. The examiner can only mark what is visible.

20.1c Lookup functions

VLOOKUP: exact match

VLOOKUP (vertical lookup) searches down the first column of a table for a value. Then it returns a value from the same row.

It has four arguments. The table shows each one.

Copied down column E, it finds the right meal price for every pupil.

Argument	What it means	In =VLOOKUP(D3,$H$3:$I$5,2,FALSE)
lookup_value	what to look for	D3 (the code V)
table_array	the table; its first column holds the codes	$H$3:$I$5 (absolute, so it does not move)
col_index_num	which column of the table to return, counting from 1 at the table's left edge	2 (column I, Price €)
range_lookup	FALSE = exact match only. TRUE = approximate match	FALSE

e.g.Example

E3: =VLOOKUP(D3,$H$3:$I$5,2,FALSE) Step 1: D3 holds V. Step 2: search down H3:H5 (M, P, V). V is in row 5. Step 3: move across to column 2 of the table, which is column I: 6.50. Result: 6.50. Copied to E4: D4 holds M → 7.50.

?Why?

Exact match (FALSE) is right for codes, names and IDs. A missing code gives #N/A. That warns you, instead of quietly giving a wrong price.

!Watch out

The column number counts from the left edge of the table, not from column A. Column I is column 2 of H3:I5, not column 9.

VLOOKUP: approximate match

With TRUE, or with the 4th argument left out, VLOOKUP does an approximate match. It finds the largest value that is less than or equal to the lookup value.

The first column must be sorted in ascending order (smallest first). If not, the result can be wrong with no error shown.

Use it for bands: ticket prices by age, grades by mark, discounts by quantity.

	K	L
2	Age from	Ticket €
3	0	0.00
4	6	4.00
5	18	8.00

e.g.Example

Entry tickets for the Alcázar of Segovia by age (sample prices): =VLOOKUP(15,$K$3:$L$5,2,TRUE) 15 is not in K3:K5. The largest value that is 15 or less is 6 (row 4). Column 2 of row 4 → 4.00 =VLOOKUP(18,$K$3:$L$5,2,TRUE) → 18 is found exactly → 8.00 =VLOOKUP(4,$K$3:$L$5,2,TRUE) → the largest value that is 4 or less is 0 → 0.00

!Watch out

Leaving out the 4th argument means TRUE. For codes, always type FALSE. Otherwise a missing code can return a wrong row instead of #N/A.

HLOOKUP

HLOOKUP (horizontal lookup) is VLOOKUP turned on its side. It searches across the top row of a table, then goes down.

Argument 1, lookup_value: what to find (B4, the form 10B).

Argument 2, table_array: the table, with codes in its top row ($H$12:$J$13).

Argument 3, row_index_num: which row of the table to return, counting from 1 at the top (2). Argument 4, range_lookup: FALSE for an exact match.

	H	I	J
12	10A	10B	10C
13	Ms Ruiz	Mr Lee	Ms Ortega

e.g.Example

=HLOOKUP(B4,$H$12:$J$13,2,FALSE) Step 1: B4 holds 10B. Step 2: search across H12:J12 (10A, 10B, 10C). 10B is in column I. Step 3: go down to row 2 of the table, which is row 13: Mr Lee.

?Why?

Use HLOOKUP when the codes run across a row. Use VLOOKUP when they run down a column. Look at the table in the question before choosing.

LOOKUP

LOOKUP searches one column (or row) and returns the value in the same position from another column (or row).

Argument 1, lookup_value: what to find. Argument 2, lookup_vector: the single column to search. Argument 3, result_vector: the column of answers, the same size.

LOOKUP always does an approximate match. The lookup vector must be sorted in ascending order. With no exact match, it uses the largest value that is smaller.

e.g.Example

=LOOKUP(D4,$H$3:$H$5,$I$3:$I$5) D4 holds M. M is 1st in H3:H5 (M, P, V are in alphabetical order). The 1st value in I3:I5 is 7.50. Result: 7.50

!Watch out

LOOKUP has no exact-only mode. A missing code X returns the price for V (the largest code before X) instead of an error. Use it only on sorted data, and only when the question asks for it.

XLOOKUP

XLOOKUP is the newest lookup. It searches one column (or row) and returns from another. Unlike LOOKUP, it does an exact match by default.

Argument 1, lookup_value. Argument 2, lookup_array: where to search. Argument 3, return_array: where the answers are.

Argument 4, if_not_found, is optional: what to show when there is no match. Optional arguments 5 and 6 set the match mode and the search direction.

The answer column can be to the left of the search column. VLOOKUP cannot do that.

e.g.Example

=XLOOKUP(D8,$H$3:$H$5,$I$3:$I$5,"No meal") D8 holds P → 2nd in H3:H5 → 2nd in I3:I5 → 6.00 If D8 held X → no match → shows No meal Looking left: =XLOOKUP(46.5,F3:F8,A3:A8) → 46.50 is in F6 → A6 → David

!Watch out

XLOOKUP is only in recent spreadsheet software. Older software shows #NAME?. Check before the exam which functions your software has.

Study it

Open the study page and go one step at a time.

ICT topic 1

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.

Red and amber points, pupil by pupil

You will learn to

Success criteria

Start from your tracker

The revision cycle

Designing the file structure

Validation: what it is and why we need it

The check digit, worked through

Input formats: data capture forms

Output formats: screen layouts and report layouts

Calculated fields and calculated controls

Sum, average, maximum, minimum, count

Sort on one or more fields

Read a query grid: a simple search

Comparison operators

AND: every condition must be true

OR: any condition can be true

NOT, LIKE and wildcards

Queries across two tables, and evidence

VLOOKUP: exact match

VLOOKUP: approximate match

HLOOKUP

LOOKUP

XLOOKUP

Types and components of computer systems

Input and output devices

Storage devices and media

Networks and the effects of using them

The effects of using IT

ICT applications

The systems life cycle

Safety and security

Audience

Communication

File management

Images

Layout

Styles

Proofing

Graphs and charts

Document production

Databases

Presentations

Spreadsheets

Website authoring