Programming requirements: SQL and algorithms

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

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

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

Definitions

programming requirements

Fill in the Blanks

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

-- Req 1: list all Red house students SELECT firstName FROM students _____ house = 'Red';

-- Req 2: count students per house SELECT house, COUNT(*) FROM students ________ house;

-- Req 3: algorithm totals all points total ← _ for i from 1 to 10: total ← total + p[i]

-- Req 4: rank students by points SELECT firstName, points FROM students ORDER BY points ____;

-- Req 5: algorithm counts marks over 80 count ← 0 if mark > 80 ____ count ← count + 1

Spot the Error

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

Question 1

A -- R1: Red house students

B SELECT firstName, lastName

C FROM students

D WHERE house = Red;

Question 2

A -- R2: average mark by subject

B SELECT subjectId, SUM(mark)

C FROM results

D GROUP BY subjectId;

Question 3

A found <- false

B for i from 1 to n + 1

C if ids[i] = target then

D found <- true

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;

Question 5

A total <- 0

B for each mark in marks

C total <- total + 1

D average <- total / n

Answer Key

Programming requirements: SQL and algorithms

Section A: Quick Questions

A — A SELECT query with WHERE stock = 0

A — An SQL statement or algorithm the solution must include to carry out a function

B — A binary search algorithm

B — SELECT house, SUM(points) FROM students GROUP BY house

A — So you can check every function has the SQL or algorithm it needs

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

Section C: Definitions

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.

Section D: Fill in the Blanks

-- Req 1: list all Red house students
SELECT firstName FROM students
WHERE house = 'Red';

-- Req 2: count students per house
SELECT house, COUNT(*) FROM students
GROUP BY house;

-- Req 3: algorithm totals all points
total ← 0
for i from 1 to 10: total ← total + p[i]

-- Req 4: rank students by points
SELECT firstName, points FROM students
ORDER BY points DESC;

-- Req 5: algorithm counts marks over 80
count ← 0
if mark > 80 then count ← count + 1

Section E: Spot the Error

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';

B — The requirement asks for an average, but SUM adds the marks together. The query needs AVG(mark) to meet the requirement.

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.

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

C — An accumulator that finds a total must add each mark. Adding 1 only counts the items. It should be total <- total + mark.