COALESCE: Handle Missing Composers
Many tracks have NULL composers. Use COALESCE to provide a default value when the Composer is missing. Write a query...
COALESCENULL Handling
Beginner
4m15 pts
16100%
SQL Practice Hub
SQL Data Cleaning Practice Questions
Fix missing values, duplicates and messy formatting before you analyse.
6 questionsFreeRuns in your browser
- NULL
- Not 0 and not an empty string: use IS NULL or COALESCE.
- Duplicates
- Rank with ROW_NUMBER and keep the first row per key.
- Formatting
- Normalise text and types before grouping or joining.
What you will practice
- Test for missing values with IS NULL and supply defaults with COALESCE or NULLIF
- Deduplicate with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at) and keep row number 1
- Understand why NULL breaks equality comparisons, NOT IN and some aggregate counts
- Normalise inconsistent text with TRIM, LOWER and CASE mappings before grouping on it
- Validate a cleanup by comparing row counts and distinct key counts before and after
Read the full overview
Real tables arrive with missing values, duplicate rows and inconsistent formatting, and cleaning them is usually the first half of any analysis. NULL is not zero and not an empty string, so it needs IS NULL and COALESCE rather than an equality test, and duplicates are removed most reliably by ranking rows with ROW_NUMBER and keeping the first of each key. These questions give you dirty seeded data and ask for the clean result set.
6 questions
Handle NULL Company Names
Some customers don't have a company. Use COALESCE to show 'Individual' for customers without a company. Write a query...
COALESCENULL HandlingBeginner4m15 pts16100%Self-Join: Customers in Same City
Find pairs of customers who live in the same city. This can help with organizing local meetups or referral programs....
Self-JoinJOINDeduplicationIntermediate9m25 pts1594%LEFT JOIN: All Artists Including Those Without Albums
An INNER JOIN only returns matching rows. Use LEFT JOIN to include ALL artists, even those without any albums. Write a...
LEFT JOINAggregationNULL HandlingIntermediate6m25 pts1493%North America Customer Profile
The customer success team wants a quick contact list of customers from North America, showing each as a single readable...
WHEREINConcatenationCOALESCE+3Intermediate8m20 pts267%Customer Email Domain Report
The analytics team wants a quick view of customer email providers, alongside each customer's full name and an...
String FunctionsSUBSTRSTRPOSConcatenation+5Intermediate10m25 pts267%
FAQ
SQL Data Cleaning practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
Why does = NULL not work?
NULL means unknown, so NULL = NULL is not true. Test with IS NULL instead.
- COALESCE(col, default) replaces NULL with a fallback value.
- NULLIF(col, '') turns an empty string into NULL.
- Most aggregates ignore NULL; COUNT(*) does not.
SQL
SELECT COALESCE(NULLIF(TRIM(phone), ''), 'unknown') AS phone
FROM customers;How do I remove duplicate rows?
Rank the rows inside each duplicate group with ROW_NUMBER and keep number 1.
- PARTITION BY the columns that define a duplicate, such as email.
- ORDER BY decides which row survives, for example the newest updated_at.
- SELECT DISTINCT only removes rows that are identical in every column.
SQL
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY email ORDER BY updated_at DESC
) AS rn
FROM users
)
SELECT * FROM ranked WHERE rn = 1;How do I fix inconsistent text values?
Normalise text before you group, join or compare it.
- TRIM removes stray spaces; LOWER or UPPER fixes case.
- REPLACE swaps fixed characters; CAST fixes types.
- Group by the cleaned expression, or "Pune", "pune " and "PUNE" count as three cities.
SQL
SELECT LOWER(TRIM(city)) AS city, COUNT(*) AS customers
FROM customers
GROUP BY LOWER(TRIM(city));Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.