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 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 questions 6 to 10)

6. List the information on crime charges for each charge that has had a fine above average
and a sum paid below average.

7. List the names of all criminals who have had any of the crime code charges involved in
crime ID 10089.

8. Use a correlated subquery to determine which criminals have had at least one probation
period assigned.

9. List the names of officers who have booked the highest number of crimes. Note that more
than one officer might be listed.

Note: Use a MERGE statement to satisfy the following request:

10. The criminal data warehouse contains a copy of the CRIMINALS table that needs to be
updated periodically from the production CRIMINALS table. The data warehouse table is
named CRIMINALS_DW. Use a single SQL statement to update the data warehouse

Answer 1