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.

• 1494216342-Bold Choice Database Term Paper Final.doc

ComputerScienceExpert

(11)

Status NEW Posted 08 May 2017 04:05 AM My Price 9.00

-----------

Attachments

file 1494216344-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer