ASSIGNMENT: 1.       Start with the Zip Code Data

ASSIGNMENT:

1.       Start with the Zip Code Data Excel file (attached to this assignment as “Assignment 4.3 – zip code data.XXXX.xlsx”).  The XXXX is just a version control number and will probably be some series of numbers.  You can disregard the exact numbers.

2.       Insert a record for your favorite US city (make sure it’s not a duplicate of something already on there), and assign your favorite city a tourist rating of 6.

3.       Save it as a .csv file, and import it into your MySQL. Make a new database called my_zip_codes and name the table in it zip_code_data.

4.       Write an SQL script which will query your zip_code_data table as follows:

a.       Select just the city and zip code and rating (not the state)

b.       For which the tourist rating is a 4 or higher (this will include all ratings of 4)

c.       Sort it descending by tourist rating (so the highest rating is the first record)

d.       Put a comma between all fields

5.       Name your script ‘tourist.sql’ .

6.       Make sure it produces a results file with your personalized information in it. If my name is Carrie, my script should produce a resulting file named “carrie_hotspots.csv” . And Carrie’s favorite city should be featured at the top line among the results.

7.       Use the Azure Export example above to export the MySQL file into an outfile called “XXXX_hotspots.csv” where XXXX is your first name. (It’s OK if you want to use a suffix, like v01, v02, etc. when debugging and then just leave the suffix on when turning it in.)

a.       Hint from FA Bhakthi Liyanage – you are going to want some CLI that looks a little bit like the following, where you have replaced the “xxxxxx-spring” with the name of your database.

b.       mysql.exe -h data620-xxxxxx-spring.mysql.database.azure.com -P 3306 -u adminuser@data620-xxxxxx-spring -p < tourist.sql > c:\Documents\week 4\XXXX_hotspots.csv

c.       where tourist.sql is your sql script and XXXX_hotspots.csv is your output file.

8.       TURN IN #1: Attach your script (Note the requirement here is a full SQL script, with comments; see the SQL script rubric from Week 1 to see how to do this. It is OK if your script requires the user to have created, loaded, and activated the database before running the script, and simply issues the SQL commands. If this is the case, make sure your comments reflect that.)

9.       TURN IN #2: Attach your .csv file.

Do you have a Mac?  This one’s for you.  From FA Bhakthi Liyanage:

Here is a YouTube clip that explains how to execute mysql on a Mac.

Watch until 0:36 and after that, it is not relevant.

https://www.youtube.com/watch?v=ryvNDIX3gQA

Basically, what you have to do is;
$ cd /usr/local/mysql/bin
$ ./mysql -h <mysql server host name> -P 3306 -u <mysql user name> -p --batch < input\SQL Script\location\filename.sql > output\file\location\filename.csv

or you may execute:

/usr/local/mysql/bin/mysql -h <mysql server host name> -P 3306 -u <mysql user name> -p --batch < input\SQL Script\location\filename.sql > output\file\location\filename.csv