Referential integrity with explicit exceptions · SQL
Task: the rules
Inputs
payments(id, account, amount_cents) — text. id is unique. amount_cents is always a valid whole number (refunds are negative).
accounts(account, name) — text. Accounts are trimmed and unique, never missing.
Rules
account: remove ASCII whitespace at both ends; an empty result is missing.
Quarantine each invalid payment with the FIRST failing reason:
1. missing_account account is missing
2. unknown_account account has no matching row in accounts
Valid payments get the account's name.
Outputs (row order does not matter)
clean(id text, account text, name text, amount_cents int64)
exceptions(id text, reason text)Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
| # | idstring | accountstring | amount_centsstring |
|---|---|---|---|
| 1 | pay59hn | 999 | 51974 |
| 2 | payij5o | 4 | 57923 |
| 3 | payfhun | 7 | 77641 |
| 4 | paym2n8 | missing | 72752 |
| 5 | paywa3o | 999 | -1746 |
| 6 | pay7u1w | ·7· (raw value " 7 ") | -1489 |
| 7 | pay77t1 | 999 | -3521 |
| 8 | payacbh | 48 | 80852 |
| 9 | payngmn | 7 | 87639 |
| 10 | payfkj9 | 999 | -461 |
Run preview · in your browser, not a grade
Loading the preview runtime…
Check answer · graded on an isolated server, including hidden datasets
Your code runs in a locked-down machine with no network, on the visible data and on hidden datasets that follow the same rules.
Learn from this exercise
A worked solution becomes available after your first check or once you've used every hint.