Designing a query from a requirement

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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"

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

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

Match the Pairs

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

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

Definitions

Fields, Criteria, Sort

Fill in the Blanks

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

SELECT FirstName, Surname FROM Students _____ Year = 10

SELECT Surname FROM Students WHERE Year = 10 ___ House = "Red"

SELECT Surname, Year FROM Students WHERE Year = 11 __ Year = 12

SELECT Surname, Score FROM Results ORDER BY Score ____

-- Scores above 80 SELECT Surname, Score FROM Results WHERE Score _ 80

Spot the Error

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

Question 1

A Need: Year 9 names, A to Z

B Fields: Name, Year

C Criteria: Year = 10

D Sort: Name Ascending

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

Question 3

A Need: Cheap books, A-Z title

B Fields: Title, Price

C Criteria: Price < 20

D Sort: Price Ascending

Question 4

A Need: Name+mark, 50+, best 1st

B Fields: Name, Year

C Criteria: Mark >= 50

D Sort: Mark Descending

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

Answer Key

Designing a query from a requirement

Section A: Quick Questions

A — Criteria: Year = 10

A — City = "Geelong" AND Spend > 100

B — Genre = "Fiction" OR Genre = "Fantasy"

A — Sort Price in ascending order

B — Email only

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

SELECT FirstName, Surname
FROM Students
WHERE Year = 10

SELECT Surname
FROM Students
WHERE Year = 10 AND House = "Red"

SELECT Surname, Year
FROM Students
WHERE Year = 11 OR Year = 12

SELECT Surname, Score
FROM Results
ORDER BY Score DESC

-- Scores above 80
SELECT Surname, Score FROM Results
WHERE Score > 80

Section E: Spot the Error

C — The requirement asks for Year 9 students, so the criteria must be Year = 9. Year = 10 would return the wrong records.

C — A student cannot be in Year 7 and Year 8 at once, so AND returns no records. Use OR to match either year.

D — The requirement says A-Z by title, so the sort must be on Title Ascending, not Price.

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.

C — The requirement is marks below 50, so the criteria must be Mark<50. Mark>50 returns the opposite group of students.