Reference no: EM132084419

Note: Use the City Jail database created with the CityJail_8.sql script that you ran for the Chapter 8 case study.

The following list reflects the current data requests from city managers. Provide the SQL statements that satisfy the requests.

For each request, include one solution using the traditional method and one using an ANSI JOIN statement. Test the statements and show execution results

1. Create an alphabetical list of all criminals, including criminal ID, name, violent offender status, parole status, and any known aliases.

2. A table named Prob_Contact contains the required frequency of contact with a probation officer, based on the length of the probation period (the number of days assigned toprobation). Review the data in this table, which indicates ranges for the number of days and applicable contact frequencies.

Create a list containing the name of each criminal who has been assigned a probation period, which is indicated by the sentence type. The list should contain the criminal name, probation start date, probation end date, and required frequency of contact. Sort the list by criminal name and probation start date.

3A column named Mgr_ID has been added to the Prob_Officers table and contains the ID number of the probation supervisor for each officer. Produce a list showing each probation officer's name and his or her supervisor's name. Sort the list alphabetically by probation officer name.