← QCE Digital Solutions Unit 2

Programming requirements: SQL and algorithms

The list of SQL statements and algorithms a data-driven solution must include so it can carry out each of its functions, with each requirement linked to a task.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 7

Understanding Programming requirements: SQL and algorithms in QCE Digital Solutions Unit 2

What it does: The requirement links the function to one query that groups students by house and totals their points. The task of finding the highest mark is met by a looping algorithm that compares each value, so it is listed as a requirement. Programming requirements list the SQL and algorithms needed. Appearance relates to user interface design, not to programming requirements. Searching a sorted list is an algorithm task, and binary search suits sorted data. A primary key is a database design feature, not a search algorithm. Linking requirements to tasks shows nothing is missing and nothing is included without a reason.

Common mistakes: Text values in a WHERE condition must be in quotes. Without them SQL reads Red as a column name. It should be WHERE house = 'Red';. The requirement asks for an average, but SUM adds the marks together. The query needs AVG(mark) to meet the requirement.

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 Programming requirements: SQL and algorithms to a class →

This module's key terms are also in a printable crossword (Modules 144–185) and word search (Modules 144–228).

Take this worksheet further — free with Cloodit

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

Learning
Duo
A school canteen app must show all products with stock of 0. Which programming requirement does this function create?
A SELECT query with WHERE stock = 0A CREATE TABLE statement for the canteen
Matches
Requirement: Show a student's marks (no SQL statement or task listed)
↓
The requirement is not linked to a task and names no SQL statement, so it does not say what the solution must contain
Applying
Meanings
Term
programming requirements
Meaning
The list of SQL statements and algorithms a data-driven solution must include so it can carry out each of its functions, with each requirement linked to a task.
Practice
R1 Show sold-out items: SQL SELECT name FROM products WHERE stock = 0;
Testing
Gaps
house = 'Red';
Check Answer
Spots
3FROM students
4WHERE house = Red;

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

The 'Polar Bear' badge — Data Heavyweight, 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 school canteen app must show all products with stock of 0. Which programming requirement does this function create?
A. A SELECT query with WHERE stock = 0 ✓
B. A CREATE TABLE statement for the canteen
Each function needs a matching requirement. Listing rows that meet a condition needs SELECT with a WHERE clause.
2. Which statement best describes a programming requirement?
A. An SQL statement or algorithm the solution must include to carry out a function ✓
B. A colour or layout choice that makes the screen look good
Programming requirements list the SQL and algorithms needed. Appearance relates to user interface design, not to programming requirements.
3. A library solution must let a student find a book by its ID in a sorted list. Which algorithm requirement fits best?
A. A primary key constraint
B. A binary search algorithm ✓
Searching a sorted list is an algorithm task, and binary search suits sorted data. A primary key is a database design feature, not a search algorithm.
4. A function must show each house's total points. Which query requirement is needed?
A. SELECT house FROM students WHERE SUM(points)
B. SELECT house, SUM(points) FROM students GROUP BY house ✓
Totals for each group need an aggregate function with GROUP BY. SUM cannot be used inside WHERE like that.
5. Why should each programming requirement be linked to a task or function?
A. So you can check every function has the SQL or algorithm it needs ✓
B. So the requirements list can be made as long as possible
Linking requirements to tasks shows nothing is missing and nothing is included without a reason.

Match the Pairs

SELECT name, price FROM products WHERE stock = 0; → A query requirement: the solution needs this SQL to list the products that are out of stock
Linear search on studentId → An algorithm requirement: the solution needs this to find one student record in an unsorted array
Query needed: the function 'show total points for each house' needs this SQL statement → SELECT house, SUM(points) FROM students GROUP BY house;
Linking a requirement to a task: 'Find the highest mark' needs an algorithm in the programming requirements → max <- marks[1] for i from 2 to 20 if marks[i] > max then max <- marks[i]
Requirement: Show a student's marks (no SQL statement or task listed) → The requirement is not linked to a task and names no SQL statement, so it does not say what the solution must contain
Requirements list: SQL queries only (no algorithms for validating or sorting data) → The list leaves out the algorithms for processing data, so some functions have nothing to carry them out

Key Term

programming requirements
The list of SQL statements and algorithms a data-driven solution must include so it can carry out each of its functions, with each requirement linked to a task.

Fill in the Blanks

1. -- Req 1: list all Red house students SELECT firstName FROM students WHERE house = 'Red';
Hint: Type the SQL keyword that filters rows by a condition.
2. -- Req 2: count students per house SELECT house, COUNT(*) FROM students GROUP BY house;
Hint: Type the two-word clause that sorts rows into sets before counting.
3. -- Req 3: algorithm totals all points total ← 0 for i from 1 to 10: total ← total + p[i]
Hint: Type the starting value an accumulator needs before the loop adds to it.
4. -- Req 4: rank students by points SELECT firstName, points FROM students ORDER BY points DESC;
Hint: Type the keyword that puts the highest values first.
5. -- Req 5: algorithm counts marks over 80 count ← 0 if mark > 80 then count ← count + 1
Hint: Type the keyword that follows the condition in a pseudocode selection.

Spot the Error

Question 1
A -- R1: Red house students
B SELECT firstName, lastName
C FROM students
D WHERE house = Red;
Line D — Text values in a WHERE condition must be in quotes. Without them SQL reads Red as a column name. It should be WHERE house = 'Red';
Question 2
A -- R2: average mark by subject
B SELECT subjectId, SUM(mark)
C FROM results
D GROUP BY subjectId;
Line B — The requirement asks for an average, but SUM adds the marks together. The query needs AVG(mark) to meet the requirement.
Question 3
A found <- false
B for i from 1 to n + 1
C if ids[i] = target then
D found <- true
Line B — A linear search must check each of the n items and no more. Looping to n + 1 goes past the end of the array. The loop should be for i from 1 to n.
Question 4
A -- R4: student names and marks
B SELECT s.firstName, r.mark
C FROM students s JOIN results
D ON s.studentId = r.studentId;
Line C — The query uses the alias r in r.mark and in the ON condition, but the alias is never given to results. It should be FROM students s JOIN results r
Question 5
A total <- 0
B for each mark in marks
C total <- total + 1
D average <- total / n
Line C — An accumulator that finds a total must add each mark. Adding 1 only counts the items. It should be total <- total + mark.

Student with a class code? Join your class →