SQL query results and joins for products and orders
2025-familiarisation-se-q12 · Multipart · 5 marks
Source: NESA 2025 HSC Software Engineering Familiarisation Q12
Question
An online business is planning to use a database to keep track of its products, customers and orders. The contents of the Products, Customers and Orders tables during testing are shown.
Products
| ProductID | ProductName | ProductPrice |
|---|---|---|
| P001 | The Plant (DVD) | 28.00 |
| P002 | Discovery 1 and 2 (DVD) | 26.98 |
| P003 | Travel 1 and 2 (Blu-Ray) | 22.00 |
| P004 | The Best Movie (DVD) | 19.98 |
| P005 | Celebration (DVD) | 12.00 |
Customers
| CustID | CustName | CustEmail |
|---|---|---|
| C001 | Nicky Singh | N.Singh@nesa.edu.au |
| C002 | Casey He | C.He@bos.edu.au |
| C003 | Ash Lee | A.Lee@bosnsw.edu |
| C004 | Kim Smith | K.Smith@nesa.edu.au |
| C005 | Pat Alvardo | P.Alvardo@bos.edu.au |
Orders
| OrderID | CustID | ProductID | OrderQuantity |
|---|---|---|---|
| T001 | C001 | P004 | 2 |
| T002 | C004 | P005 | 1 |
| T003 | C002 | P003 | 1 |
| T004 | C001 | P002 | 2 |
| T005 | C005 | P002 | 2 |
Part (a) 2 marks
The following SQL query is run to test the database.
SELECT ProductName, ProductPrice
FROM Products
WHERE ProductPrice > 22.00
ORDER BY ProductPrice ASC
Fill in the table with the result of running the SQL query.
| ProductName | ProductPrice |
|---|---|
Part (b) 3 marks
The contents of the Orders table need to be displayed as follows.
| Customer | Product | Quantity |
|---|---|---|
| Nicky Singh | The Best Movie (DVD) | 2 |
| Kim Smith | Celebration (DVD) | 1 |
| Casey He | Travel 1 and 2 (Blu-Ray) | 1 |
| Nicky Singh | Discovery 1 and 2 (DVD) | 2 |
| Pat Alvardo | Discovery 1 and 2 (DVD) | 2 |
Write a SQL query that can produce this result.
Reveal answer
Part (a)
| ProductName | ProductPrice |
|---|---|
| Discovery 1 and 2 (DVD) | 26.98 |
| The Plant (DVD) | 28.00 |
Part (b)
SELECT Customers.CustName AS Customer,
Products.ProductName AS Product,
Orders.OrderQuantity AS Quantity
FROM Orders
INNER JOIN Customers ON Orders.CustID = Customers.CustID
INNER JOIN Products ON Orders.ProductID = Products.ProductID;
Marking rubric
Part (a)
| Marks | Description |
|---|---|
| 2 | Correctly completes the query result table. |
| 1 | Correctly identifies some query results. |
Part (b)
| Marks | Description |
|---|---|
| 3 | Constructs a correct query joining Orders, Customers and Products. |
| 2 | Constructs a mostly correct join query. |
| 1 | Shows some relevant SQL knowledge. |
Explanation
Part (a) filters out products priced at 22.00 or below, then sorts the remaining products by price. Part (b) requires joins through the customer and product IDs.
Metadata
- Submitter
- Seed data
- Created
- 2026-05-02
- Status
- published
- Syllabus
- y12-web-sql-scripts
- Tags
- SQL joins databases query results