Using a fill-in value
The sedan body type is the most frequently occurring vehicle_body_type in the sample parking violations. For this reason, you propose changing all NULL-valued vehicle_body_type records in the parking_violations table to SDN. Discussions with your team result in a decision to use a value other than SDN as a fill-in value. The body type can be determined by looking up the vehicle using its license plate number. A license plate number is present in most parking_violation records. Rather than using the most frequent value to replace NULL vehicle_body_type values, a placeholder value of Unknown will be used. The actual body type will be updated as license plate lookup data is gathered.
In this exercise, you will replace NULL vehicle_body_type values with the string Unknown.
This exercise is part of the course
Cleaning Data in PostgreSQL Databases
Exercise instructions
- Use
COALESCE()to replace anyvehicle_body_typethat isNULLwith the string valueUnknownin theparking_violationtable.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
UPDATE
parking_violation
SET
-- Replace NULL vehicle_body_type values with `Unknown`
___ = ___(___, ___);
SELECT COUNT(*) FROM parking_violation WHERE vehicle_body_type = 'Unknown';