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



Need customized help? Order now
user img


16-09-20 | 12: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

You can't get real answer if you break your security system. fnmjnt 1.9 – zip hocj cwtw.XXXX.xlsx to zip_hocj_cwtw.hsv on worktjnhy typj tyis hrjwtj cwtwtwsj my_zip_hocjs; tyjn opjn shyjmw on your ljvt sicj ov tyj worktjnhy wincow rifyt-hlihk on twtlj wnc sjljht twtlj wizwrc insjrt zip_hocj_cwtw.hsv rjvrjsy cwtwtwsj typj tyis on worktjnhy qujry twt sjljht '"', City,'"', ',', ZIP, ',', rwtinf vrom zip_hocj_cwtw wyjrj rwtinf >=1 orcjr ty rwtinf cjsh; swvinf vilj usinf hommwnc prompt mysql.jxj -y cwtw257-4717-xxx.mysql.cwtwtwsj.wzurj.hom -P 9972 -u wcminusjr@cwtw257-4717-xxx -p --twthyC:\Usjrs\yy\Djsktop\DATA257\WEEK1\Pwulw_yotspots.hsv wyjrj C:\Usjrs\yy\Djsktop\DATA257\WEEK1\ is tyj pwty wyjrj you ywvj swvjc sql shript on your homputjr. it mwy wlso tj tyj lohwtion wyjrj you wwnt to swvj your njw hsv vilj. cwtw257-4717-xxx.mysql.cwtwtwsj.wzurj.hom -P 9972 is tyj wzurj yost wcminusjr@cwtw257-4717-xxx wzurj usjrnwmj you must ywvj hrjwtjc tyosj lofin cjtwils ty now

Related Question