The Five-Step Reading Method

Step 1: Read SELECT and identify whether the query returns fields, SUM or COUNT. Step 2: Read FROM and locate the correct table. Step 3: Apply WHERE to determine which records match. Step 4: calculate the aggregate or apply ORDER BY if present. Step 5: write the result using only the selected fields.

Following this order prevents common errors such as sorting records that should have been filtered out or displaying fields used only in the condition.

Underline field names in the question and check that the query uses exact spelling.

Recognising Missing Parts

Completing A Basic Query

A completion question may remove FROM, the text value A or the direction ASCENDING. Use grammar and the stated requirement to supply each part.

Check the data type of a missing literal. Category A is text or character and therefore quoted in this example; a numeric threshold would not be quoted.

SELECT ProductID, ProductName
FROM Products
WHERE Category = "A"
ORDER BY ProductName ASCENDING;

Tracing A Full Query

Trace Example

The WHERE condition matches P02 Monitor and P05 Adapter. P03 is category A but fails IsAvailable = TRUE.

Sort the two matches by ProductName ascending, giving Adapter before Monitor.

Display only ProductID and ProductName. The exact output order is P05 Adapter, then P02 Monitor.

SELECT ProductID, ProductName
FROM Products
WHERE Category = "A" AND IsAvailable = TRUE
ORDER BY ProductName ASCENDING;

Tracing Aggregate Queries

Evaluate the OR condition for each record. P01 is below 20, P03 is below 20 and unavailable, and P05 is below 20. These three records match.

COUNT(ProductID) returns 3. It does not output the matching ProductID values because the selected expression is an aggregate.

SELECT COUNT(ProductID)
FROM Products
WHERE UnitPrice < 20 OR IsAvailable = FALSE;

Checking SQL Against A Requirement

Compare each phrase in the requirement with a clause. The fields to display map to SELECT. The table maps to FROM. Filtering words map to WHERE and conditions. Ordering words map to ORDER BY. A total amount maps to SUM, and a number of records maps to COUNT.

If a proposed query returns too many rows, inspect WHERE. If it returns the wrong columns, inspect SELECT. If the rows are correct but the order is wrong, inspect ORDER BY.

This diagnostic approach is useful when asked to correct or complete a script.

Common Examination Forms

• State the number of fields or records in a displayed table.
• Identify a suitable primary key and give a reason.
• Suggest the most suitable data type for each named field.
• Complete a table definition with validation.
• Fill blanks in an SQL query.
• Write a query from a requirement.
• Identify the output of a query using given records.
• Calculate the result of SUM or COUNT.

Final Scope Reminder

The required database content is deliberately focused. Study single-table design, fields, records, validation, six basic data types, primary keys and the listed SQL features.

Do not spend examination time on multiple-table relationships, foreign keys, joins, normalisation, indexes, forms, reports, transactions or data-changing SQL because these are not included in Topic 9 for this syllabus.

Worked Examples

Examination Guidance

• Use the five-step reading method.
• Check every literal against its field data type.
• Write output in the requested order and with only selected fields.
• For aggregate queries, return one calculated value.
• Use the official command words in explanations: identify, state, suggest and explain.

Knowledge Check

1. What is the first clause to inspect?

2. When is ORDER BY applied in tracing?

3. What should be returned for SELECT COUNT(…)?

4. How do you find a missing literal format?

5. Name three out-of-scope topics.