Exercise

Standardizing color names

In the previous exercise, the DIFFERENCE() function was used to identify colors that closely matched our desired representation of the color GRAY. However, this approach retained a number of records where the vehicle_color value may or may not be gray. Specifically, the string GR (green) has the same Soundex code as the string GRAY. Fortunately, records with these vehicle_color values can be excluded from the set of records that should be changed.

In this exercise, you will assign a consistent gray vehicle_color value by identifying similar strings that represent the same color. Again, the fuzzystrmatch module has already been installed for you.

Instructions

100 XP
  • Complete the SET clause to assign 'GRAY' as the vehicle_color for records with a vehicle_color value having a matching Soundex code to the Soundex code for 'GRAY'.
  • Update the WHERE clause of the subquery so that the summons_number values returned exclude summons_number values from records with 'GR' as the vehicle_color value.