Challenge: In the process of preparing your dataset for analysis, you will often need to define PK and FK$.$ One such situation occurs when working with state and county data. One table may have Census info and a second table may have area $($size$)$ info. Fortunately the government has realized that they need to have unique identifiers for all kinds of economic analyses. This is called "Federal Information Processing Series Links to an external site." or "FIPS"

TASK $1$:

Get the $2-$character state code from the reference data set and combine it with CSV$\_$FIPS data and write the $4$ columns to SQL Server

Transforms to use: Sort, Merge Join, derived column Result: $3188$ rows, $4$ columns $($see disclaimer at the top$)$

You are given two data sets $-$ both CSV $-$ CSV$\_$states$-$in$-$us Download CSV$\_$states$-$in$-$us $("$reference data set"$)$ and CSV$\_$FIPS Download CSV$\_$FIPS. These have a $1$:M relationship. You are to create a new column called StateCode and write this combined data to a new table called mygateID$\_$FIPS$\_$M$6$ in YOUR database with all the columns from CSV$\_$FIPS. The state code comes from $"$CSV$\_$states$-$in$-$us$".$ The state NAME is in both files $-$ the basis of your join.

TASK $2$: $($Lookup$?$ Merge? Decide after watching both videos$)$

You are given two CSV data sets: CSV$\_$census Download CSV$\_$census and CSV$\_$FIPS $($downloaded in TASK $1).$ Your job is to append the FIPS code column to the data in CSV$\_$census and write the resulting $4$ columns $-$ County, state, MHI and FIPS$\_$Code $-$ to a new table in your database called mygateID$\_$CensusFIPS. Since the county names may not be unique across the US$,$ you will have to combine the county name and state name to locate the appropriate FIPS code $-$ the basis of your join. $($Hint: Comparison is case sensitive$)$

You are bound to miss some. Don't worry about that for this assignment. HOWEVER, you are required to analyze the result and explain what problems you ran into

did it get all the codes correctly

Which counties got skipped $?$ Why?

How would you fix these $?$

Tip: Remove ALL spaces from county name and state name before JOINing $($TRIM$)$