Sample and test data
Building the data set that tests a schema and its queries: nominal case, edge cases, rows to reject, expected result written in advance, population script and anonymisation of real data.
- 25 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Test data set: what is it?
Show the answer
A set of rows chosen to exercise a schema and its queries, with the expected result of each trial.
A good set is small but deliberate: every row is there for a precise reason, unlike a randomly picked extract.
This query must list all clients with their number of orders. Which test row reveals the error?
```sql
SELECT c.nom, COUNT(o.id) AS nb
FROM client c
INNER JOIN commande o ON o.id_client = c.id
GROUP BY c.id, c.nom;`- A client with no order at all
- A client with a single order
- A client with several orders
- Two clients who have the same name
Show the answer
A client with no order at all
The
INNER JOINdrops the client without orders: with Ada (2 orders) and Bo (none), the query returns only Ada, whereas aLEFT JOINalso returns Bo with 0. The other three cases pass with both joins.The expected result is written … running the query, from the business rules and the test data.
Show the answer
The expected result is written before running the query, from the business rules and the test data.
After running, one is tempted to call whatever comes out "correct". The result written beforehand is the only independent reference.
Nominal case: what is it?
Show the answer
An ordinary row that follows the business rules with nothing unusual: the expected operation.
It checks that the common case works, but proves very little: most errors hide elsewhere.