Page Page numberof $3$ Introduction to Database and SQL $($ITC $5104)$ Assignment $04$ City Jail Assignment $04$ Submission: Rename this document by replacing $$StdNumber$$ by your student number and $$Name$$ by your name. This is your work, do it yourself. SQL Format $$ Keywords must be in lower case $$ Table names must be in upper case $$ Column names must be in upper case $$ Functions are in lower case $$ Column alias are mixed case $$ MAKE THE CODE READABLE!!! Note: 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$.$ SQL: Screen shot: $2.$ List all crimes that occurred $($were charged$)$ during the month October $2008.$ List the crime ID$,$ criminal ID$,$ date charged, and classification. SQL: Screen shot: Assignment $041$ of $3$ Introduction to Database and SQL $($ITC $5104)$ Assignment $04$ City Jail $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. SQL: Screen shot: $4.$ List all crimes classified as a felony. List the crime ID$,$ criminal ID$,$ date charged, and classification. SQL: Screen shot: $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. SQL: Screen shot: $6.$ List all criminals with the zip code $23510.$ List the criminal ID$,$ last name, and zip code. Sort the list by criminal ID$.$ SQL: Screen shot: $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$.$ SQL: Screen shot: Assignment $042$ of $3$ Introduction to Database and SQL $($ITC $5104)$ Assignment $04$ City Jail Assignment $043$ of $3$