Reference no: EM132174966
We will be using a second database for many of our Assignments called CityJail. The SQL scripts to load the CityJail database can be found in Lesson 1. For this assignment, you will need to use the CityJail_8.sql file.
Once you sign in to SQLNavigator, use the File->Open menu option to bring in the script and execute it. A data dictionary for the database can be found at the end of Chapter 3 in the Casteel book.
At the end of the data dictionary is a coding key for selected columns that you will need to complete this assignment.
The following list reflects data requests from city managers. Write the SQL statements to satisfy the requests. Test the statements and show execution results.
1- List all Aliases beginning with the letter C.
2- List all crimes that occurred (were charged) during the month of September 2008. List the crime ID, criminal ID, date charged, and classification.
3- List all crimes with the status of CL (closed) or IA (in appeal). List the crime ID, criminal ID, date charged, and status
4- List all crimes classified as a misdemeanor. List the crime ID, criminal ID, date charged, and classification.
5 - List all crimes with a hearing date more than 10 days after the date charged. List the crime ID, criminal ID, date charged, and hearing date.
6- List all criminals with a zip code of 26503. List the criminal ID, last name, and zip code. Sort the list by criminal ID.
7- List all crimes that do have a hearing date scheduled. List the crime ID, criminal ID, date charged, and hearing date.
8- List all crimes that are classified as a felony and that can be appealed. List the crime ID, criminal ID, classification, and status.
9- List all crime charges with the balance owed. List the charge ID, crime ID, fine amount, court fee, amount paid, and amount owed.
10- List all police officers were assigned to either the OCVW or WAVE precinct and have a status of active. (Just to be clear, the officers can be in either precinct but must 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.