Drill 6 of 10 in Database Analyst
0

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…