The following list reflects current data requests from city managers. Provide the SQL statement to satisfy each request. Test the statements and show execution results.1. List the following information for all crimes that have a period greater than 14 days between the date charged and the hearing date: crime ID, classification, date charged, hearing date, and number of days between

The following list reflects current data requests from city managers. Provide the SQL statement to satisfy each request. Test the statements and show execution results.
1. List the following information for all crimes that have a period greater than 14 days between the date charged and the hearing date: crime ID, classification, date charged, hearing date, and number of days between the date charged and the hearing date.
2. Produce a list showing each active police officer and his or her community assignment, indicated by the second letter of the precinct code. Display the community description listed in the following chart, based on the second letter of the precinct code.

Second Letter of Precinct Code Description Shady Grove Center City B Bay Landing

3. Produce a list of sentencing information to include criminal ID, name (displayed in all uppercase letters), sentence ID, sentence start date, and length in months of the sentence. The number of months should be shown as a whole number. The start date should be displayed in the format €œDecember 17, 2009.€
4. A list of all amounts owed is needed. Create a list showing each criminal name, charge ID, total amount owed (fine amount plus court fee), amount paid, amount owed, and payment due date. If nothing has been paid to date, the amount paid is NULL. Include only criminals who owe some amount of money. Display the dollar amounts with a dollar sign and two decimals.
5. Display the criminal name and probation start date for all criminals who have a probation period greater than two months. Also, display the date that€™s two months from the beginning of the probation period, which will serve as a review date.
6. An INSERT statement is needed to support users adding a new appeal. Create an INSERT statement using substitution variables. Note that users will be entering dates in the format of a two-digit month, a two-digit day, and a four-digit year, such as €œ12 17 2009.€ In addition, a sequence named APPEALS_ID_SEQ exists to supply values for the Appeal_ID column, and the default setting for the Status column should take effect (that is, the DEFAULT option on the column should be used). Test the statement by adding the following appeal: crime_ID = 25344031, filing date = 02 13 2009, and hearing date = 02 27 2009.

This problem has been solved!


Do you need an answer to a question different from the above? Ask your question!
Related Book For answer-question

Oracle 12c SQL

3rd edition

Authors: Joan Casteel

ISBN: 978-1305251038

Hands-On Assignments: