KNOWLEDGE / 07
API, SQL and data
API contracts, database work, data integrity, and integration testing.
Questions and practice
Open a question to see the answer, examples, and exercises.
What should be checked beyond the HTTP status?Junior
Answer
Response schema and semantics, state changes, authorization, side effects, and repeated-request behaviour.
Examples
- A 201 does not prove that the resource was stored correctly or that an event was published exactly once.
Practice exercises
- For a create endpoint, design checks for contract, state, access, side effects, and retries.
What does a resource-oriented REST API mean in practice?Junior
Answer
A resource has a stable identity, representations, and operations with consistent HTTP semantics. A URI usually names the resource, the method expresses the action, and status plus headers explain the result. REST is more than JSON over HTTP: statelessness, cacheability, and consistent error rules also matter.
Examples
- PATCH /users/42 changes part of a representation, while GET on the same URI returns the current state.
Practice exercises
- Redesign RPC-like createOrder and cancelOrder endpoints as resources and explain the trade-offs.
How do JOINs and cardinality affect SQL result correctness?Junior
Answer
An INNER JOIN keeps matching rows, a LEFT JOIN preserves all rows on the left, and a many-to-many relationship can multiply results. Understand each table’s grain and expected cardinality before aggregating, or COUNT and SUM may look plausible while being inflated.
Examples
- Joining orders to order_items returns one order multiple times; SUM(order.total) after that join duplicates the total.
Practice exercises
- Create data with no match, one match, and multiple matches, then compare INNER and LEFT JOIN results.
Why use a transaction?Middle
Answer
So related operations complete as one logical unit with either commit or rollback.
Examples
- Creating an order and reserving stock should either both complete or both roll back.
Practice exercises
- Simulate a failure between two SQL operations and verify the rollback.
Why does pagination require stable ordering?Middle
Answer
Without deterministic order, records may be duplicated or disappear between pages. Offset pagination is simple but sensitive to inserts and slow at large offsets; cursor pagination works better with sequential keys but has a more complex contract. Filters and sort fields also require validation.
Examples
- Sorting only by created_at is ambiguous; adding id provides a stable tie-breaker.
Practice exercises
- Design pagination tests during concurrent inserts and deletes, comparing offset and cursor behaviour.
Which anomalies can a transaction isolation level permit?Middle
Answer
Weaker levels may allow non-repeatable reads, phantoms, or lost updates depending on the database and its implementation. The isolation-level name alone is not a sufficient oracle. A concurrency test should control transaction order and verify the final invariant instead of relying on accidental timing.
Examples
- Two clients simultaneously buy the final stock item; without correct synchronization, inventory becomes negative.
Practice exercises
- Build a deterministic lost-update test with two connections and define expectations for your chosen database.
Why does an index not always make a system faster?Senior
Answer
It may speed up reads but costs storage and work on writes; value depends on the query plan and data distribution.
Examples
- An index on a low-selectivity field may be ignored while still slowing inserts.
Practice exercises
- Compare EXPLAIN before and after an index, and measure its effect on reads and writes.
How can a database migration be tested without risking rollout?Senior
Answer
Test the schema change, data backfill, compatibility with old and new application versions, locking, duration, and rollback or roll-forward strategy. Zero-downtime changes often require expand–migrate–contract stages. Production-like data volume matters because a fast migration on an empty database proves little.
Examples
- Add a nullable column first, backfill it, move readers, and only later introduce the constraint.
Practice exercises
- Plan a safe column rename with mixed-version deployment and monitoring signals.
How do you test idempotency with retries and eventual consistency?Senior
Answer
Repeating one logical request with the same key should produce a consistent result without repeating the side effect. Test concurrent duplicates, timeout after commit, key expiry, and a different payload with the same key. Under eventual consistency, assertions should allow bounded convergence without hiding an endless delay.
Examples
- A client misses the payment-creation response and repeats POST; the system returns the same payment instead of creating another.
Practice exercises
- Design an idempotency matrix for success, timeout, parallel retry, changed payload, and expired key.