Question
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
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);
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started