ASSIGNMENT:



ASSIGNMENT: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.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.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.Write an SQL script which will query your zip_code_data table as follows:Select just the city and zip code and rating (not the state)For which the tourist rating is a 4 or higher (this will include all ratings of 4)Sort it descending by tourist rating (so the highest rating is the first record)Put a comma between all fieldsName your script ‘tourist.sql’ .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.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.)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.mysql.exe -h data620-xxxxxx-spring.mysql.database. -P 3306 -u adminuser@data620-xxxxxx-spring -p < tourist.sql?> c:\Documents\week 4\XXXX_hotspots.csvwhere tourist.sql is your sql script and XXXX_hotspots.csv is your output file.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.)TURN IN #2: Attach your .csv file. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download