Case Study: City Jail Note: Make sure you have run the CityJail_8.sql script from Chapter 8. This script makes all database objects available to complete this case study. The citys Crimes Analysis unit has submitted the following data requests. Provide the SQL statements to satisfy these requests. Test the statements and show the query results. 1. Show the average number of crimes reported by an officer. 2. Show the total number of crimes by status. 3. List the highest number of crimes committed by a person. 4. Display the lowest fine amount assigned to a crime charge. 5. List criminals (ID and name) who have multiple sentences assigned. 6. List the total number of crime charges successfully defended (guilty status assigned) by precinct. Include only precincts with at least seven guilty charges. 7. List the total amount of collections (fines and fees) and the total amount owed by crime classification.

8. Use single queries to address the following requests: 9. List the total number of charges by crime classification and charge status. Include a grand total in the results. 10. Perform the same task as in Question #8 and add the following: a) a subtotal by each crime classification and b) a subtotal for each charge status. Provide two different queries to accomplish this task. 11. Perform the same task as in Question #8 and add a subtotal by each crime classification. Provide two different queries to accomplish this task.