Question

Note I did not post the city jail database because it was too long and it couldnt let me post, however, there was another question answered with the same database right here, so please use that. It is exactly the same. If not let me know how i can post the database subqueries)

Your files must be in sql script format so that they can be run in 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 the maximum number of crimes officers have reported.
2. List the names of all criminals who have committed more than average number of crimes and aren’t listed as violent offenders.
3. List appeal information for each appeal that has a less than the average number of days between the filing and hearing dates.
4. List the names of probation officers who have had a greater than average number of criminals assigned.
5. List each crime that has had the least number of appeals recorded.

   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 to working with your company to create a much-needed crime-tracking system. As you requested, our project group has outlined the crime-tracking data needs we anticipate. Our goal is to simplify the process of tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions.

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

   Crimes: classification (felony, misdemeanor, other), date charged, appeal status (closed, can appeal, in appeal), hearing date, 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, and charge status (pending, guilty, not guilty).

   Sentencing: start date, end date, number of violations (such as not reporting to probation officer), and type of sentence ( jail period, 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 involve multiple crime charges, such as burglary and assault. Criminals can be assigned multiple sentences. For example, a criminal might be required to serve a jail sentence followed by a period of probation. Answer each or the questions with an sql statement that contains 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 courses he ever taught, and so on.

Answer 1

Some Collumn, Table names are missing from above table Question. The correct answer with respect to to correct Questions is posted below.

/*List the name of each officer who has reported less than the maximum number of crimes officers have reported.*/

SELECT officer_name FROM crime_officers JOIN crime_chargers USING(crime_id) where crime_charges < All (SELECT AVG(COUNT(*)) FROM crime_charges);

/*List the names of all criminals who have committed more than average number of crimes and aren’t listed as violent offenders.*/

SELECT cls.first, cls.last from criminals cls JOIN crime cr ON cls.criminal_id = cr.criminal_id WHERE crime_id > ALL (SELECT AVG(COUNT(*)) FROM crimes) AND cls.violent offender status = "NO";

/*List appeal information for each appeal that has a less than the average number of days between the filing and hearing dates.*/

SELECT * FROM appeals WHERE AVG((filing date - hearing date)) < ALL (SELECT AVG((filling date - hearing date)));

/*List the names of probation officers who have had a greater than average number of criminals assigned.*/

SELECT p,first, p.last from prob_officer p JOIN sentences s ON p.prob_id = s.prob_id WHERE crime_id > ALL (SELECT AVG(COUNT(*)) FROM sentences;

/*List each crime that has had the least number of appeals recorded.*/

SELECT * FROM crime_charges c JOIN appeals a ON c.crime_id = a.crime_id WHERE a.filling date < ALL(SELECT MAX(filing date) FROM appeals);