Case Study: City Jail Note: Make sure you have run the CityJail_8.sql script fro
ID: 3879010 • Letter: C
Question
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 city’s 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.
Explanation / Answer
SQL Queries-
1. Show the average number of crimes reported by an officer.
Aggregate function AVG() is used along with group by clause.
SELECT officer_ID, AVG(crime_id)
FROM crime_officers
GROUP BY officer_ID;
2. Show the total number of crimes by status.
Aggregate function COUNT() is used along with GROUP BY status.
SELECT status, COUNT(crime_ID)
FROM crimes
GROUP BY status;
3. List the highest number of crimes committed by a person.
Aggregate function COUNT() is used along with GROUP BY criminal_ID
SELECT criminal_ID, COUNT(crime_ID)
FROM crimes
GROUP BY criminal_ID;
4. Display the lowest fine amount assigned to a crime charge.
Function MIN() is used to get the lowest crime charge.
SELECT crime_code, MIN(fine_amount)
FROM crime_charges;
5. List criminals (ID and name) who have multiple sentences assigned.
Inner Join between table criminals and sentences is used along with group by and having clause to get the number of criminals who have multiple sentence assigned.
SELECT c.criminal_ID, last_col, first_col, COUNT(s.criminal_ID)
FROM criminals AS c
INNER JOIN sentences AS s
ON c.criminal_ID = s.criminal_ID
GROUP BY c.criminal_ID, last_col, first_col
HAVING COUNT(s.criminal_ID) > 2;
6. List the total number of crime charges successfully defended (guilty status assigned) by
precinct. Include only precincts with at least seven guilty charges.
SELECT COUNT(crime_ID)
FROM sentences AS s
INNER JOIN crimes AS c
ON c.criminal_ID = s.criminal_ID
WHERE c.status = 'guilty';
7. List the total amount of collections (fines and fees) and the total amount owed by crime
classification.
SELECT c.classification, SUM(fine_amount + court_fee)
FROM crimes AS c
INNER JOIN sentences AS s
ON c.crime_ID = s.crime_ID
GROUP BY c.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.
SELECT c.classification, c.status, COUNT(charge_ID)
FROM crimes AS c
INNER JOIN sentences AS s
ON c.crime_ID = s.crime_ID
GROUP BY c.classification, c.status;