Note I did not post the city jail databasebecause it was too long and it couldnt let me post, however, therewas another question answered with the same database right here, soplease use that. It is exactly the same. If not let me know how ican post the database subqueries)

Your files must be in sql script format so that they can be runin SQL Developer.

Using the City Jail database to answer the questions.

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

1. List the name of each officer who has reported less than themaximum number of crimes officers have reported.
2. List the names of all criminals who have committed more thanaverage number of crimes and aren’t listed as violentoffenders.
3. List appeal information for each appeal that has a less thanthe average number of days between the filing and hearingdates.
4. List the names of probation officers who have had a greaterthan average number of criminals assigned.
5. List each crime that has had the least number of appealsrecorded.

   HINT:

   The following memo was used to create an initial database design(E-R model) for the City Jail that indicates entities, attributes(columns), primary keys, and relationships.

   MEMO

   To: Database Consultant

   From: City Jail Information Director

   Subject: Establishing a Crime-Tracking Database System

   It was a pleasure meeting with you last week. I look forward toworking with your company to create a much-needed crime-trackingsystem. As you requested, our project group has outlined thecrime-tracking data needs we anticipate. Our goal is to simplifythe process of tracking criminal activity and provide a moreefficient mechanism for data analysis and reporting. Please reviewthe data needs outlined below and contact me with anyquestions.

   Criminals: name, address, phone number, violent offender status(yes/no), probation status (yes/no), and aliases

   Crimes: classification (felony, misdemeanor, other), datecharged, appeal status (closed, can appeal, in appeal), hearingdate, appeal cutoff date (always 60 days after the hearing date),arresting officers (can be more than one officer), crime codes(such as burglary, forgery, assault; hundreds of codes exist),amount of fine, court fee, amount paid, payment due date, andcharge status (pending, guilty, not guilty).

   Sentencing: start date, end date, number of violations (such asnot reporting to probation officer), and type of sentence ( jailperiod, house arrest, probation).

   Appeals: appeal filing date, appeal hearing date, status(pending, approved, and disapproved).

   Note: Each crime case can be appealed up to three times.

   Police officers: name, precinct, badge number, phone contact,status (active/inactive)

   Additional notes: A single crime can involvemultiple crime charges, such as burglary and assault. Criminals canbe assigned multiple sentences. For example, a criminal might berequired to serve a jail sentence followed by a period ofprobation. Answer each or the questions with an sql statement thatcontains at least one subquery.

   Hint: Use subqueries, and work “inside out”toward the result; that is, retrieve the

   employee number of N. Smith, search for the codes of all courseshe ever taught, and so on.