9 programs
Choose the best training program according to your career.
Improve the performance of your business with our innovative Industrial Training Program.
Read More →
Our instructor-led training classes are designed to make you an expert.
Read More →
Real-life case studies are a great way to demonstrate your practical knowledge.
Read More →
Explore blogs, latest technologies and industry updates with practical examples.
Read More →
Browse all available training programs and choose the right one for your career.
Read More →Know more about who we are and what we stand for.
Discover our mission to equip individuals with essential IT skills and industry expertise.
Read More →Learn, practice and plan your career — bilkul free.
Book a free video counselling session and pick the right course for you.
Read More →Download the SQL data cleaning guide PDF from Uncodemy: NULLs, duplicates, trimming, casting and standardising text with tested queries.
This page offers Uncodemy's SQL data cleaning guide as a PDF download. When data already lives in a database, cleaning it with SQL is fast and repeatable. The guide shows queries to profile data, find and remove duplicates, handle NULLs, standardise text and dates, and validate the result, using examples that run in SQLite.
Download the guide below. Each download is its own file, and everything in it is also explained on this page.
Profiling, finding duplicates, standardising text, handling NULLs, dates and numbers, building a cleaned view and validating it, with a checklist and a cleaning log, using a messy raw_customers table.
A short form asks for your details before the download starts. Your details are used only to share study material and course information.
If data is already stored in a database, SQL is often the quickest way to inspect and fix it, because you avoid moving large files around. A saved SQL cleaning query is also repeatable and auditable: anyone can read exactly what was changed.
The best practice is not to modify the raw table but to create a cleaned view or a new table derived from it, so the original stays intact. This guide uses a messy example table called raw_customers with columns customer_id, name, email, city, signup_date and monthly_spend, which contains duplicates, inconsistent text, NULLs and mixed date styles.
Counts rows, missing values and distinct cities in one query.
SELECT COUNT(*) AS total_rows,
COUNT(DISTINCT customer_id) AS distinct_ids,
SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS missing_email,
SUM(CASE WHEN monthly_spend IS NULL THEN 1 ELSE 0 END) AS missing_spend,
COUNT(DISTINCT city) AS distinct_city_values
FROM raw_customers;Shows customer ids that appear more than once, and then ranks rows within each id so that you can keep one.
-- which ids are duplicated?
SELECT customer_id, COUNT(*) AS copies
FROM raw_customers
GROUP BY customer_id
HAVING COUNT(*) > 1;
-- keep the row with the latest signup_date for each id
SELECT *
FROM (
SELECT r.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY signup_date DESC) AS rn
FROM raw_customers r
)
WHERE rn = 1;Trim spaces, fix case, standardise a misspelling and give missing values a default or a clear label.
SELECT customer_id,
TRIM(name) AS name_clean,
LOWER(TRIM(email)) AS email_clean,
CASE UPPER(TRIM(city))
WHEN 'NEW DELHI' THEN 'Delhi'
WHEN 'DELHI' THEN 'Delhi'
ELSE SUBSTR(UPPER(TRIM(city)), 1, 1) || LOWER(SUBSTR(TRIM(city), 2))
END AS city_clean,
COALESCE(monthly_spend, 0) AS spend_filled,
COALESCE(NULLIF(TRIM(email), ''), 'unknown') AS email_or_unknown
FROM raw_customers;Names vary slightly by database.
| Task | Function idea |
|---|---|
| Remove spaces | TRIM, LTRIM, RTRIM |
| Change case | UPPER, LOWER |
| Replace text | REPLACE |
| Part of a string | SUBSTR or SUBSTRING, LEFT, RIGHT |
| Default for NULL | COALESCE |
| Turn empty text into NULL | NULLIF(column, '') |
| Conditional fixes | CASE WHEN ... THEN ... END |
| Change type | CAST(column AS type) |
| Detect pattern | LIKE; regular expressions where supported |
Dates are a frequent source of trouble: text values in different styles, impossible dates and time zones. Where possible store dates in a proper date type and write them in ISO form, year-month-day, which sorts correctly as text too. When converting, test the conversion on a sample and check for rows that fail.
For numbers, watch for values stored as text, currency symbols and commas. CAST a cleaned string to a number, but first remove characters that do not belong, and report rows that could not be converted instead of hiding them.
A view keeps the raw data intact. The check afterwards compares counts with the raw table.
CREATE VIEW clean_customers AS
SELECT customer_id,
TRIM(name) AS name,
LOWER(TRIM(email)) AS email,
TRIM(city) AS city,
COALESCE(monthly_spend, 0) AS monthly_spend
FROM (
SELECT r.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY signup_date DESC) AS rn
FROM raw_customers r
)
WHERE rn = 1;
SELECT (SELECT COUNT(*) FROM raw_customers) AS raw_rows,
(SELECT COUNT(*) FROM clean_customers) AS clean_rows,
(SELECT COUNT(DISTINCT customer_id) FROM raw_customers) AS raw_distinct_ids;Write a short note or comments in the SQL for every rule: what the problem was, how you fixed it and how many rows it touched. The count is easy to get with a query before and after. These notes turn a one-off fix into reusable knowledge, and they make it easy for a colleague to review your work.
| Check | Query idea |
|---|---|
| Count rows and distinct keys | SELECT COUNT(*), COUNT(DISTINCT id) FROM table |
| Find NULLs per column | SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) |
| Find duplicates | GROUP BY key HAVING COUNT(*) > 1 |
| Find leading and trailing spaces | WHERE col <> TRIM(col) |
| Check value ranges | MIN, MAX and ordered samples |
| Check category spellings | SELECT DISTINCT col ORDER BY col |
Want to clean real data the way analysts do at work? Explore Uncodemy's Data Analytics course and learn from industry trainers.