Assignment 2 - Import and Analyze Data
Objectives
Demonstrate the ability to import data into SQL Server and analyze that data with basic queries
Assignment
In SQL Server you will create a new database called CrimeDatabase. You are going to import some crime statistics into this database. These statistics are available from data.gov -2008 Crime Statistics http://catalog.data.gov/dataset/2008-crime-in-the-united-states
You will then import the Florida Statistics from the file 08tbl08fl.xls which will be one of the files in this download (the download is a zip file with multiple spreadsheets representing various Uniform Crime Reporting (UCR) statistics.
Now from this data you are going to determine the most dangerous cities based on the field "Violent Crime". The query should show the City, Population, Number of violent crimes, and the number of violent crimes per 1000 residents. It should be ordered so the worst cities based on the number of violent crimes per 1000 persons are at the top of the list.
Your report will show the SQL Query you used and the show the worst 10 cities with the fields above. You can easily do this by screen capturing the results of the query from Management Studio and pasting it into your submission. This should be a doc or pdf file turned into the drop box prior to the assignment due date.
Your submission will be a one page report that shows the SQL Query and the results.
Information
The data is available at https://explore.data.gov/Law-Enforcement-Courts-and-Prisons/2007-Crime-in-the-United-States/x2gu-2drm
Estimated Completion Time
This took me about 20 minutes. To go through the lecture and try different methods of import you should allot a few hours.
Supporting Lectures
The data import necessary for this is completely covered in the week 2 lectures COP4709 Course Lectures By Week
Questions and Answers
Q: What will I need to know to do this assignment (besides the lectures)
A:
|
This is pretty easily done with a single SQL query. If you are not comfortable with this level of SQL you will want to do some practice with SQL. You can start with the SQL at W3Schools -
http://www.w3schools.com/sql/sql_tryit.asp
but this SQL is very basic and this class will go into a LOT more depth. Here is a piece of the schema from my working this sample if you want to play with the data with SQL
http://sqlfiddle.com/#!2/231fb
Finally - you will need to delve into SQL pretty heavily. As a starting point for this class you need to be familiar with all the SQL commands at this tutorial site
http://www.sqlcourse2.com/intro2.html
SQL is NOT HARD - it is extremely powerful and learning the basics should only take a few hours.
|
External Resources
Grading Criteria
5 points - demonstrating you can successfully download and import the data
5 points - structuring the correct query and reporting the results.
Comments (0)
You don't have permission to comment on this page.