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. (Oracle 12c: SQL   Joan Casteel ISBN: 978-1-305-25103-8 Chapter 12 Case Study: City Jail)

Use a sql sub-query statement to answer the following: 

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 an 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 <ALL (SELECT AVG(COUNT(*)) FROM crimes )

AND cls.v_status = 'N';

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

WRONG Answer:

SELECT *

FROM appeals

WHERE AVG((filing_date - hearing_date)) <ALL (SELECT AVG((filing_date - hearing_date));

4. List the names of probation officers who have had a less than an 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 <ALL (SELECT AVG (COUNT(*)) FROM sentences;

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);