← VCE Data Analysis

Designing a query from a requirement

The three parts of a query design: which fields to show, which records match the criteria (using AND/OR), and how results are sorted, so the output meets the requirement.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 79, VCE Data Analytics - U3O1 - Data Analytics — Module 34, VCE Data Analytics - U3O2 - Analysis and Design — Module 153

Understanding Designing a query from a requirement in VCE Data Analysis

What it does: OR matches a record if at least one condition is true, which fits 'either' in the requirement. Sorting is the third part of the design. Descending puts the largest value first. Criteria decide which records match. Fields only decide which columns are shown in the results. Cheapest first means the lowest value comes first, which is ascending order. Show only the fields the requirement asks for. Extra fields make the output clutter and don't meet the requirement exactly. The year must always match (AND), while the subject can be either one (OR). Using OR for everything would also return students from other year levels.

Common mistakes: The requirement asks for Year 9 students, so the criteria must be Year = 9. Year = 10 would return the wrong records. A student cannot be in Year 7 and Year 8 at once, so AND returns no records. Use OR to match either year.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Designing a query from a requirement to a class →

This module's key terms are also in a printable crossword (Modules 68–80) and word search (Modules 56–80).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A requirement says: 'Show only the names of students in Year 10.' Which part of the query design handles 'Year 10'?
Fields: Year = 10Criteria: Year = 10
Matches
Criteria: Year = 11 AND Year = 12
↓
A record can't be in both years at once, so no records match. It should use OR: Year = 11 OR Year = 12.
Applying
Meanings
Term
Fields, Criteria, Sort
Meaning
The three parts of a query design: which fields to show, which records match the criteria (using AND/OR), and how results are sorted, so the output meets the requirement.
Practice
Requirement: Year 10 or 11 students owing over 5 dollars
Testing
Gaps
Year = 10
Check Answer
Spots
2Fields: Name, Year
3Criteria: Year = 10

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Star Fox' badge — Frozen Master, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A requirement says: 'Show only the names of students in Year 10.' Which part of the query design handles 'Year 10'?
A. Criteria: Year = 10 ✓
B. Fields: Year = 10
Criteria decide which records match. Fields only decide which columns are shown in the results.
2. A requirement says: 'List customers from Geelong AND who spent over $100.' Which criteria design meets it?
A. City = "Geelong" AND Spend > 100 ✓
B. City = "Geelong" OR Spend > 100
AND means both conditions must be true for a record to match. OR would also return customers who only meet one condition.
3. A requirement says: 'Show books that are either Fiction or Fantasy.' Which criteria is correct?
A. Genre = "Fiction" AND Genre = "Fantasy"
B. Genre = "Fiction" OR Genre = "Fantasy" ✓
A single record has one genre, so it can't be both. OR matches records that meet either condition.
4. A requirement says: 'Show the name and price of each product, cheapest first.' How should the sort be designed?
A. Sort Price in ascending order ✓
B. Sort Price in descending order
Cheapest first means the lowest value comes first, which is ascending order.
5. A requirement asks for 'only the email addresses of members'. Which fields should the query design show?
A. Every field in the table
B. Email only ✓
Show only the fields the requirement asks for. Extra fields make the output clutter and don't meet the requirement exactly.

Match the Pairs

Field: Name, Year | Criteria: Year = 10 | Sort: Name A-Z → Shows only the Name and Year fields, keeps only Year 10 records, and lists them alphabetically by name
Criteria: Suburb = "Geelong" AND Age >= 18 → A record must meet both conditions to appear: it is from Geelong and the person is 18 or older
A requirement says to show students from either Year 11 or Year 12 → Criteria: Year = 11 OR Year = 12
A requirement says to list products by price from highest to lowest → Sort: Price Descending
Criteria: Year = 11 AND Year = 12 → A record can't be in both years at once, so no records match. It should use OR: Year = 11 OR Year = 12.
Fields: Name | Criteria: Score > 80 | Requirement: show Name and Score → The Score field is missing from the fields to show, so the output doesn't meet the requirement. Add Score to the fields.

Key Term

Fields, Criteria, Sort
The three parts of a query design: which fields to show, which records match the criteria (using AND/OR), and how results are sorted, so the output meets the requirement.

Fill in the Blanks

1. SELECT FirstName, Surname FROM Students WHERE Year = 10
Hint: Type the keyword that starts the criteria, which decides which records match.
2. SELECT Surname FROM Students WHERE Year = 10 AND House = "Red"
Hint: Type the operator that requires both criteria to be true for a record to match.
3. SELECT Surname, Year FROM Students WHERE Year = 11 OR Year = 12
Hint: Type the operator that matches a record when either criterion is true.
4. SELECT Surname, Score FROM Results ORDER BY Score DESC
Hint: Type the sort keyword that puts the highest scores first.
5. -- Scores above 80 SELECT Surname, Score FROM Results WHERE Score > 80
Hint: Type the comparison operator that turns the requirement 'above' into a criterion.

Spot the Error

Question 1
A Need: Year 9 names, A to Z
B Fields: Name, Year
C Criteria: Year = 10
D Sort: Name Ascending
Line C — The requirement asks for Year 9 students, so the criteria must be Year = 9. Year = 10 would return the wrong records.
Question 2
A Need: Year 7 or Year 8 only
B Fields: Name, Year
C Criteria: Year=7 AND Year=8
D Sort: Name Ascending
Line C — A student cannot be in Year 7 and Year 8 at once, so AND returns no records. Use OR to match either year.
Question 3
A Need: Cheap books, A-Z title
B Fields: Title, Price
C Criteria: Price < 20
D Sort: Price Ascending
Line D — The requirement says A-Z by title, so the sort must be on Title Ascending, not Price.
Question 4
A Need: Name+mark, 50+, best 1st
B Fields: Name, Year
C Criteria: Mark >= 50
D Sort: Mark Descending
Line B — The requirement says to show the name and the mark. The Fields line shows Year instead of Mark, so it should be Fields: Name, Mark.
Question 5
A Need: Year 10 with mark < 50
B Fields: Name, Year, Mark
C Criteria: Year=10 AND Mark>50
D Sort: Name Ascending
Line C — The requirement is marks below 50, so the criteria must be Mark<50. Mark>50 returns the opposite group of students.

Student with a class code? Join your class →