Sql - Capstone: Part 2
Code Life
By Pierre Marsaa
The Clean-up
Second final challenge: the field researchers sent their sighting reports in a hurry — spaces around names, random capitals, levels stored as text, and one Codémon that is not in the official list. This time the table itself has to be cleaned, in a single script.
This challenge combines string functions, CAST and CASE, UPDATE, ALTER TABLE, DELETE and a final GROUP BY report. Every run starts again from the original data and displays the result of the last statement, so while you work you can end the script with SELECT * FROM sightings; and replace it with the report once the cleaning is right.
Loading data...
Loading data...
Step 1: UPDATE sightings so that reporter and codemon lose their surrounding spaces and get a capital first letter followed by lowercase letters — ' alex' becomes 'Alex', 'PLANTACHU' becomes 'Plantachu' (TRIM, UPPER, LOWER, SUBSTR and ||).
Step 2: add an INTEGER column level with ALTER TABLE, then fill it with UPDATE: level_text converted with CAST, or NULL when level_text is '' or 'unknown'.
Step 3: DELETE the sightings whose codemon is not a name of the codemon table — the names are clean now, so a plain NOT IN subquery does the job.
Step 4: the report, one row per Codémon: codemon, its type (join the codemon table), sightings (how many), avg_weight (average weight_kg rounded to 2 decimals) and best_level (the highest level, NULL when every level is unknown), most sighted first, then by codemon — 8 rows.
Expected result (the last SELECT):
Loading editor...
↗️
Loading editor...
↶
↷
💡 Show Solution
Finish