Testing SQL (SQA National 5 Computing Science): Revision Note
Exam code: X816 75
Testing SQL
What is SQL testing?
SQL testing checks whether an SQL statement works correctly by comparing expected output with actual output
You predict the output by looking at table data, search criteria, joins, and sort order
If the actual results match the expected results, the SQL is correct
How to determine expected output
Look at the fields selected, the tables involved, and any conditions in the WHERE clause
Identify whether an equi-join should limit the results
Identify the exact records that should appear and the order they should appear in
How to evaluate SQL output
Compare expected vs actual
If they match, state that the SQL works correctly
If they do not match, state the SQL is incorrect and explain why
Common SQL errors
SQL error | What goes wrong | Why it causes incorrect output |
|---|---|---|
Wrong field name | SQL returns no results or fails | Field does not exist in the table |
Wrong table name | SQL fails or produces empty output | FROM clause references a table that is not in the database |
Missing equi-join | SQL returns far too many records | Produces a Cartesian product because tables are not linked |
Incorrect join condition | SQL returns wrong records | Join links fields that are not primary key to foreign key |
Incorrect search criteria | Wrong records appear | Wrong operator or wrong value used in WHERE |
Restricted choice mismatch | No results appear | Value does not match validation rules, for example "smallest" instead of "small" |
Wrong sort order | Rows appear in the wrong order | ORDER BY incorrectly uses ascending or descending |
Over-broad DELETE | Deletes too many records | WHERE clause does not uniquely identify one record |
Over-broad UPDATE | Updates multiple rows | Missing criteria means more records are changed than intended |
Testing each SQL operation
SQL operation | What you test | What correct output should show |
|---|---|---|
SELECT | Check the right records appear, check the right fields appear, check sort order, check join works | Only the records that meet the criteria appear, fields match the SELECT list, sort order is correct, and no extra rows from missing joins |
INSERT | Check required fields are included, check values follow validation rules, check the new record exists | The new row is added to the table with correct data and no missing required values |
UPDATE | Check only intended rows changed, check updated values are correct and valid | The correct record(s) show new values, all other records remain unchanged |
DELETE | Check only the intended rows are removed, check WHERE clause is specific | Only the correct row(s) disappear from the table, no accidental deletions |
Example
SQL:
SELECT surname, forename FROM Customer WHERE town = "Dundee";
Expected output:
Only customers with town = Dundee
Actual result analysis:
If only Dundee customers appear, SQL is correct
If extra customers appear, criteria is wrong or join missing
If none appear, the field or value may be wrong
Expected vs Actual comparison
Expected output | Actual output | What this means |
|---|---|---|
Matches exactly the predicted records, fields, and sort order | Actual output is identical | SQL works correctly |
Expected 3 records but actual output shows more | Extra rows appear | Search criteria is wrong or equi-join is missing |
Expected 3 records but actual output shows fewer | Some rows missing | Wrong field, wrong value, or restricted choice mismatch |
Expected specific fields but actual output includes different fields | Wrong headings or extra columns | SELECT list is incorrect |
Expected sorted list but actual output is unsorted or reversed | Rows in wrong order | ORDER BY is incorrect |
Expected 1 record updated, actual shows multiple updated | Too many rows changed | UPDATE uses broad criteria |
Expected 1 record deleted, actual shows multiple deleted | Too many rows removed | DELETE criteria not unique |
Worked Example
A large sports association uses a database to track registered players and their teams. The database contains two linked tables: Player (fields include playerName, clubID) and Club (fields include clubID, clubTown, teamDivision). The tables are linked by the shared primary key (clubID) and foreign key (clubID).
The association requires a list displaying the players' names and the town where their club is located, for all players in Division 1 teams.
The following SQL statement was written to retrieve this list:
SELECT playerName, town
FROM Player, Club
WHERE teamDivision = 1
ORDER BY playerName ASC;State two reasons why this SQL statement will not produce the expected output
[2]
Answer
Any two of the following:
An equi-join is missing (or a join condition is missing) in the
WHEREclause to link thePlayerandClubtables [1 mark]The field named
towndoes not exist (or is missing) in theClubtable (the correct field isclubTown) [1 mark]Because the tables are not joined, the output will contain incorrect results by showing every player with every club (a Cartesian product) [1 mark]
Unlock more, it's free!
Did this page help you?
