ASSIGNMENT: 1.       Start with the Zip Code Data



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 -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.

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




16-09-20 | 17:52:05

change the name and file format of Assignment 4.3 – zip code data.XXXX.xlsx to zip_code_data.csv
on workbench type this
create database my_zip_codes;
then open schema on your left side of the workbench window
right-click on table and select table wizard
insert zip_code_data.csv
refresh database

type this on workbench query tab
select '"', City,'"', ',', ZIP, ',', rating from zip_code_data
where rating >=4
order by rating desc;

saving file using command prompt
mysql.exe -h -P 3306 -u adminuser@data620-9040-xxx -p --batchC:\Users\yy\Desktop\DATA620\WEEK4\Paula_hotspots.csv

where C:\Users\yy\Desktop\DATA620\WEEK4\ is the path where you have saved sql script on your computer. it may also be the location where you want to save your new csv file. -P 3306 is the azure host
adminuser@data620-9040-xxx azure username
you must have created those login details by now

Login to answer this question!