CompTIA Data+ (DA0-002)Data MiningHard
A data quality specialist is auditing a customer database and finds that the 'Email' column sometimes contains 'NULL' values, empty strings (''), or even incorrect formats like 'not_available' or 'customer@domain'. They need to identify all records where the email address is effectively missing or invalid for a new marketing campaign. Which SQL query construct would be most suitable to identify these records?
- AWHERE Email IN ('NULL', '', 'not_available')
- BWHERE Email IS NULL OR Email = '' OR Email = 'not_available' OR Email NOT LIKE '%@%.%'
- CSELECT COUNT(*) FROM Customers GROUP BY Email HAVING Email IS NULL
- DWHERE Email IS NOT NULL AND Email != ''
Show answer & explanationAnswer & explanation
Correct answer: B. WHERE Email IS NULL OR Email = '' OR Email = 'not_available' OR Email NOT LIKE '%@%.%'
To identify all effectively missing or invalid emails, the query must check for `NULL` values, empty strings (`''`), specific placeholder strings (`'not_available'`), AND patterns that don't resemble a valid email address (e.g., `NOT LIKE '%@%.%'`). Option B combines all these conditions using `OR` to catch any of the invalid states.
Why the other options are wrong
- A. This only checks for exact string matches; it misses `NULL` values and any other invalid formats not explicitly listed.
- C. This groups by email and counts nulls, but doesn't identify all types of invalid emails or the specific records themselves.
- D. This only identifies valid, non-empty emails, the opposite of what's needed.
SQL Data Validation
SQL data validation involves using SQL queries to check data against predefined rules, constraints, or patterns to ensure its accuracy, consistency, and integrity.
- Uses WHERE clauses, LIKE/REGEXP patterns, IS NULL, and comparison operators.
- Essential for data quality and reliability.
- Can be used to identify records needing cleansing or correction.
Memory trick: Null, Empty, Specific Bad, or Bad Pattern!