Levels Tought:
Elementary,Middle School,High School,College,University,PHD
Teaching Since: | Jul 2017 |
Last Sign in: | 273 Weeks Ago, 6 Days Ago |
Questions Answered: | 1850 |
Tutorials Posted: | 1850 |
Graduate in Biology and Nutrition, MBA Finance
Florida State University
Aug-2000 - Jul-2007
Ass. Relationship Manager
Penn-Florida
Mar-2009 - Feb-2016
oracle 12c SQL ISBN: 9781305251038 chapter 12
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.
Â
1. List the name of each officer who has reported more than the average number of crimes officers
have reported.
Â
2. List the criminal names for all criminals who have a less than average number of crimes and
aren't listed as violent offenders.
Â
3. List appeal information for each appeal that has a less than average number of days
between the filing and hearing dates.
Â
4. List the names of probation officers who have had a less than average number of criminals
assigned.
Â
5. List each crime that has had the highest number of appeals recorded.
Â
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 all 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 table
to reflect any data changes for existing criminals and to add new criminals.
Hel-----------lo -----------Sir-----------/Ma-----------dam----------- Â----------- -----------Tha-----------nk -----------you----------- fo-----------r u-----------sin-----------g o-----------ur -----------web-----------sit-----------e a-----------nd -----------acq-----------uis-----------iti-----------on -----------of -----------my -----------pos-----------ted----------- so-----------lut-----------ion-----------.Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m Â----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I