How would you write a query using JOINs, GROUP BY, and aggregate functions to verify database states?
Use the provided expected-state and actual-record tables. Identify whether each expected state is verified, mismatched, or missing, and also report actual states that were not expected.
verification_status column.object_type, object_key, and check_id, with missing check IDs last.| Column | Type | Description |
|---|---|---|
| check_idPK | INT | Unique expected-state check identifier |
| object_type | VARCHAR(50) | Category of the state being verified |
| object_key | VARCHAR(100) | Identifier for the state within its category |
| expected_count | INT | Expected number of actual records |
| expected_total | NUMERIC(12,2) | Expected sum of record amounts |
| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique actual record identifier |
| object_type | VARCHAR(50) | Category of the actual state |
| object_key | VARCHAR(100) | Identifier for the actual state |
| amount | NUMERIC(12,2) | Amount contributed by the actual record |