Lesson overview
Use conditions, sorting and simple aggregate functions to answer database questions.
Focus: SQL filtering, sorting and aggregates.
What you will learn
- Define the key GCSE Computer Science terms used in sql filtering, sorting and aggregates.
- Explain how order by works in a practical scenario.
- Compare count with an alternative or related method.
- Apply sql filtering, sorting and aggregates accurately in exam-style questions.
Before you start
- SQL SELECT basics.
- Comparison operators.
- Database fields and records.
SQL Filtering scenarios
Use sales, marks and booking data below to filter, sort and summarise records.
SQL filtering, sorting and aggregates infographic
Explanation
Filtering uses WHERE conditions to keep only matching records. Conditions can combine comparisons where the board or task expects it.
ORDER BY sorts results by a field, often ascending by default or descending when DESC is used.
Aggregate functions such as COUNT, SUM and AVG summarise several records into one result, such as the number of bookings or average score.
Worked examples
Applying sql filtering
A Marks table stores PupilName and Score.
We need the highest scores first.
Only scores of 50 or more should be shown.
Answer: SELECT PupilName, Score FROM Marks WHERE Score >= 50 ORDER BY Score DESC;
Quick checks
1. What does ORDER BY usually control?
- a. The computer's power cable
- b. The screen resolution
- c. The order of the query results
Reveal answer
Answer: c. Correct. ORDER BY sorts records in the result set.
Not quite. Focus on order by in the scenario.
2. Which idea is most closely linked to sql filtering, sorting and aggregates?
- a. Aggregate functions
- b. A monitor brightness setting
- c. A random file extension
Reveal answer
Answer: a. Correct. Aggregate functions is part of this topic.
Not quite. Choose the option that belongs to the Computer Science concept.
Practice
1. What does COUNT(*) return?
Reveal answer
Answer: The number of matching records.
Marking: Credit count of rows.
2. What does AVG(Score) calculate?
Reveal answer
Answer: The mean average of the Score values.
Marking: Credit average/mean.
3. How would you sort oldest dates first?
Reveal answer
Answer: ORDER BY Date ASC, or ORDER BY Date if ascending is the default taught.
Marking: Credit ascending sort.
4. Why combine WHERE and ORDER BY?
Reveal answer
Answer: To filter the records first and then sort the matching results.
Marking: Credit both operations.
Exam practice ladder
- Fluency: recall the key term and use it accurately.
- Application: apply the idea to the scenario rather than giving a generic definition.
- Algorithmic reasoning: show the steps, condition or variable change clearly.
- Evaluation: explain why one method, structure or control is suitable.
Answers and marking guidance
Exact answers and marking guidance are hidden under each question. For this lesson, earn marks by naming the correct Computer Science idea, applying it to the scenario and showing the logic or value change clearly.
Common mistakes
- Using ORDER BY to filter records.
- Forgetting DESC when highest first is required.
- Using AVG on a text field.
- Counting all records when the question asks for matching records only.
Extension
Write a query that counts how many orders over GBP 20 were placed by one customer.
Next lesson
Next, continue with Data privacy, law and ethics.
Exam-board guidance
Aplailasain is an independent learning resource and is not endorsed by any exam board.
AQA GCSE Computer Science
AQA GCSE Computer Science: this skill supports problem solving, clear algorithms and accurate programming explanations.
OCR GCSE Computer Science
OCR GCSE Computer Science: expect precise algorithm reasoning, trace work and careful use of programming vocabulary.
Pearson Edexcel GCSE Computer Science
Pearson Edexcel GCSE Computer Science: practise explaining the method as well as giving the final answer or code.
Eduqas GCSE Computer Science
Eduqas GCSE Computer Science: secure the transferable idea first, then check how your class applies it in tasks.
WJEC Wales
WJEC Wales: use accurate terminology and show enough working for the examiner to follow your logic.
CCEA
CCEA: connect the idea to your class route and use the same algorithm, programming or systems vocabulary your teacher uses.