ExpertHard6 marksStructured
AQA GCSE · Question 18.5 · Relational databases and structured query language (SQL)
The youth club needs to produce a report listing the members who have been given the Leader award. The report must include both names of each member and the date the award was presented. The results must be in order of the date the awards were presented, starting with the earliest.
Write an SQL query that could be used to find this information.
The youth club needs to produce a report listing the members who have been given the Leader award. The report must include both names of each member and the date the award was presented. The results must be in order of the date the awards were presented, starting with the earliest.
Write an SQL query that could be used to find this information.
How to approach this question
1. **SELECT:** Identify the columns you need. The question asks for "both names of each member" (`FirstName`, `LastName`) and "the date the award was presented" (`DatePresented`).
2. **FROM:** You need data from both the `Member` and `Award` tables.
3. **JOIN:** You need to link the two tables. The common field is `MemberID`. So, you need to `JOIN Award ON Member.MemberID = Award.MemberID`.
4. **WHERE:** You need to filter the results to only include the "Leader" award. So, `WHERE Award.AwardName = 'Leader'`.
5. **ORDER BY:** The results must be sorted by the date, "starting with the earliest". This means ordering by `DatePresented` in ascending order (`ASC`).
6. **Combine** these clauses in the correct SQL syntax.
Full Answer
SELECT Member.FirstName, Member.LastName, Award.DatePresented
FROM Member
INNER JOIN Award ON Member.MemberID = Award.MemberID
WHERE Award.AwardName = 'Leader'
ORDER BY Award.DatePresented ASC;
To construct this SQL query, we need to combine data from two tables and then filter and sort it.
SELECT Member.FirstName, Member.LastName, Award.DatePresented
FROM Member
INNER JOIN Award ON Member.MemberID = Award.MemberID
WHERE Award.AwardName = 'Leader'
ORDER BY Award.DatePresented ASC;
**Explanation of each clause:**
- `SELECT Member.FirstName, Member.LastName, Award.DatePresented`: This specifies the columns we want to see in our final report. We need the first and last names from the `Member` table and the presentation date from the `Award` table.
- `FROM Member`: This indicates that we are starting our query with the `Member` table.
- `INNER JOIN Award ON Member.MemberID = Award.MemberID`: This is the crucial step that links the two tables. It tells the database to match rows from the `Member` table with rows from the `Award` table where the `MemberID` is the same in both.
- `WHERE Award.AwardName = 'Leader'`: This is a filter. It instructs the database to only include the records where the name of the award is 'Leader'.
- `ORDER BY Award.DatePresented ASC`: This sorts the final result set. It orders the rows based on the `DatePresented` column in ascending (`ASC`) order, from the earliest date to the latest.
Common mistakes
✗ Forgetting to join the tables.
✗ Selecting the wrong fields.
✗ Using the wrong condition in the WHERE clause.
✗ Forgetting to order the results or ordering in the wrong direction (descending).
✗ Syntax errors, such as missing quotes around 'Leader' or incorrect join syntax.
Practice the full AQA GCSE Computer Science Paper 2
46 questions · hints · full answers · grading
More questions from this exam
Q01.1Convert the binary number 11010100 into decimal.EasyQ01.2Convert the binary number 10111001 into hexadecimal. You should show your working.MediumQ01.3State the largest decimal number that can be represented using 6 bits.EasyQ02.1Add together the following three binary numbers and give your answer in binary.
00110110
1001...MediumQ02.2Apply a binary shift three places to the right on the bit pattern 10101000. Give the result using...Easy