Database Analyst · UPDATE · INITCAP · TRIM
6. Standardize country names
Concept: UPDATE · INITCAP · TRIMshow
UPDATE table SET col = expression rewrites a column in place, for every row unless you add WHERE. To fix free-text values, TRIM the stray spaces and INITCAP the casing: INITCAP(' new york ') would be ' New York ' without the TRIM. In an interview, say you'd run the SELECT version first to preview the change.
Example (different data):
UPDATE stores SET city = INITCAP(TRIM(city)); SELECT city, COUNT(*) FROM stores GROUP BY city;
Your task
Country in the CRM is free text, so 'canada ', ' CANADA' and 'Canada' show up as three countries in every regional report.
UPDATE crm_contacts so every country is trimmed and title-cased (INITCAP), leaving NULLs as NULL. Then return country and contacts (the number of contacts) for each country, sorted by country (NULL last).
Starting Postgres in your browser…
Checking your access…