Oracle 12c case study city jail solutions

Run the CityJail_8.sql file provided by your instructor to ensure that all necessary tables and constraints are available for this case study. This script isn’t included in student data files because case study assignments in previous chapters include table creation challenges. This script rebuilds the City Jail database. Don’t be concerned with errors from the DROP TABLE commands, which delete any existing tables of the same names. The following list reflects common data requests from city managers. Write the SQL statements to satisfy the requests. If the query can be accomplished by using different operators, supply alternative solutions so that the performance-tuning group can test them and identify the more efficient statements. Test the statements and show execution results.
1. List all criminal aliases beginning with the letter B.
2. List all crimes that occurred (were charged) during the month October 2008. List the crime ID, criminal ID, date charged, and classification.
3. List all crimes with a status of CA (can appeal) or IA (in appeal). List the crime ID, criminal ID, date charged, and status.
4. List all crimes classified as a felony. List the crime ID, criminal ID, date charged, and classification.
5. List all crimes with a hearing date more than 14 days after the date charged. List the crime ID, criminal ID, date charged, and hearing date.
6. List all criminals with the zip code 23510. List the criminal ID, last name, and zip code. Sort the list by criminal ID.
7. List all crimes that don’t have a hearing date scheduled. List the crime ID, criminal ID, date charged, and hearing date.
8. List all sentences with a probation officer assigned. List the sentence ID, criminal ID, and probation officer ID. Sort the list by probation officer ID and then criminal ID.

9. List all crimes that are classified as misdemeanors and are currently in appeal. List the crime ID, criminal ID, classification, and status.
10. List all crime charges with a balance owed. List the charge ID, crime ID, fine amount, court fee, amount paid, and amount owed.
11. List all police officers who are assigned to the precinct OCVW or GHNT and have a status of active. List the officer ID, last name, precinct, and status. Sort the list by precinct and then by officer last name.