Assignment, Computer sciences and Information technology
Assignment

Project description

To Do
Answer all questions and perform all tasks. Use the Access File ISMG2050 SUM14 Homework 6 Main File” located in the Files Section of Canvas. Perform database work using Access. Save each query independently; each query should have its own tab in the final Access file you turn in. Write a 1 page max summary using Word describing how you accomplished each query. Upload your Access and Word files and submit via Canvas. Show all work. Work must be completed via the design and run of a query.
Task
The goal of this assignment is to practice advanced queries. The queries must be designed in the most efficient manner for full credit. This will require advanced query solutions. Make sure to show all variables (fields)
1. Question 1: Design a query that returns all crime information for any city in California with a population of greater than five hundred thousand but less than one million. Also provide full city and state name.

2. Question 2: Design a query that returns a comparison of statistics for Robberies in the cities of California and Ohio. Statistics are Sum, Average, Standard Deviation, Min, and Max.

3. Question 3: Design a query that returns the property crime data for all cities that start with the letter R or S, and where Motor Vehicle crime is greater than 1,000. Also provide full city name and state abbreviation.

4. Question 4: Design a query that calculates the total of all violent and property crime (new variables) and grand total crime (new variable, total of both types), by city. Also provide the full city and state names, and sort the data by grand total crime, from high to low.

5. Provide the SQL Code for a query that queries and finds cities where crime data where Murder is greater than 100 or where Burglary is greater than 8000. Also provide full city and state names.