Oracle 12c: SQL Joan Casteel ISBN: 978-1-305-25103-8 Chapter 12 Case Study: City Jail

Make sure you have run the CityJail_8.sql script from Chapter 8. This script makes all database objects available for completing this case study. The city's Crime Analysis unit has submitted the following data requests. Provide the SQL statements using subqueries to satisfy the requests. Test the statements and show execution results.

Use a sql sub-query statement to answer the following: (Please provide correct answer for question 2 to 5)

1. List the name of each officer who has reported more than the average number of crimes officers have reported. WRONG Answer:

SELECT officer_id

FROM crime_officers JOIN crime_charges USING(crime_id)

WHERE crime_charges >ALL (SELECT AVG(COUNT(*))

FROM crime_charges) ;

CORRECT ANSWER:

SELECT co.officer_id, o.last, o.first

FROM crime_officers co JOIN officers o

ON co.officer_id = o.officer_id

GROUP BY co.officer_id, o.last, o.first

HAVING COUNT(*) > (SELECT COUNT(*) / COUNT(DISTINCT officer_id) FROM crime_officers);

2. List the criminal names for all criminals who have a less than average number of crimes and aren't listed as violent offenders.

WRONG Answer:

SELECT cls.criminal_id, cls.first, cls.last, cr.crime_id

FROM criminals cls

JOIN crimes cr

ON cls.criminal_id = cr.criminal_id

WHERE crime_id

AND cls.v_status = 'N';

3. List appeal information for each appeal that has a less than average number of days between the filing and hearing dates.

WRONG Answer:

SELECT *

FROM appeals

WHERE AVG((filing_date - hearing_date))

4. List the names of probation officers who have had a less than average number of criminals assigned.

WRONG Answer:

SELECT p.prob_id, p.last, p.first

FROM prob_officers p

JOIN sentences s

ON p.prob_id = s.prob_id

WHERE crime_id

5. List each crime that has had the highest number of appeals recorded.

WRONG Answer:

SELECT c.crime_id, c.crime_code, c.crime_charges, a.filing_date

FROM crime_charges c

JOIN appeals a

ON c.crime_id = a.crime_id

WHERE a.filing_date >ALL (SELECT MAX(filing_date) FROM appeals);