CompTIA Data+ (DA0-002)Data MiningMedium
A data engineer is developing an ETL pipeline for a marketing analytics platform. The source data contains customer demographics, and the 'Gender' column has inconsistent entries such as 'Male', 'male', 'M', 'Female', 'female', 'F', and some nulls. To ensure data consistency for reporting, all gender entries must be transformed into either 'Male', 'Female', or 'Unknown'. Which SQL function or statement is most suitable for this type of conditional data transformation?
- ASUBSTRING
- BCOALESCE
- CCASE
- DCONCAT
Show answer & explanationAnswer & explanation
Correct answer: C. CASE
The CASE statement is ideal for conditional logic in SQL, allowing you to specify different outcomes based on various conditions. It can handle multiple inconsistent input values for 'Gender' and map them to a standardized set of 'Male', 'Female', or 'Unknown'.
Why the other options are wrong
- A. SUBSTRING extracts a portion of a string, which is insufficient for the complex conditional mapping required.
- B. COALESCE returns the first non-NULL expression, useful for handling missing values but not for complex conditional mapping.
- D. CONCAT concatenates strings, which is not applicable for conditional transformation of values.
SQL CASE Statement
A conditional expression in SQL that allows you to define different results based on various conditions, similar to 'if-then-else' logic in programming.
- Used for conditional logic in SELECT, WHERE, ORDER BY, and GROUP BY clauses.
- Can map multiple input values to a single output value.
- Essential for data transformation, categorization, and handling complex business rules.
Memory trick: SQL conditions are like a CASE: when this, then that, else something else.