Assignment \#1 - Single-row functions \& Subquery Due Date: Midnight of Feb 3 (Friday) Purpose: The purpose of this assignment is to help you: - Become familiar with the data source: City Jail database - Understand single-row functions, subquery and merge Instructions: Be sure to read the following general instructions carefully: This assignment should be completed individually by all the students. Submit your solution through the dropbox, Your submission should include sql command and the screenshot of command execution result, the submission must be named according to the following rule: studentID(yourlastname)_Assignment\#number.doc. e.g., 300123456(smith)_Assignment\#1.doc 1. Produce a list of sentencing information to include criminal ID, full name, sentence ID, sentence start date, and the length in months of the sentence. The number of months should be shown as a whole number. That start date should be displayed in the format "January 1, 2023" [2 marks] Assignment \#1 Page 1 of 3 Advanced Database COMP214 2. List the name of each officer who has reported longer than the average number of crimes officers have reported [ 2 marks] 3. List the names of all criminals who have committed less than average number of crimes and aren't listed as violent offenders [2 marks] 4. List appeal information for each appeal that has longer than the average number of days between the filing and hearing dates [2 marks] 5. List the names of probation officers who have had less than average number of criminals assigned [2 marks] 6. List each crime that has had the least number of appeals recorded [2 marks] 7. List the following information for all crimes that have a period longer than 10 days between the data charged and the hearing date: crime ID, classification, date charged, hearing date, and the number of days between the date charged and the hearing date [ 2 marks] HINT: The following memo was used to create an initial database design (E-R model) for the City Jail that indicates entities, attributes (columns), primary keys, and relationships. MEMO To: Database Consultant From: City Jail Information Director Subject: Establishing a Crime-Tracking Database System It was a pleasure meeting with you last week. I look forward to working with your company to create a much-needed crime-tracking system. As you requested, our project group has outlined the crime-tracking data needs we anticipate. Our goal is to simplify the process of tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions. Criminals: name, address, phone number, violent offender status (yeso), probation status (yeso), and aliases Crimes: classification (felony, misdemeanor, other), date charged, appeal status (closed, can appeal, in appeal), hearing date, appeal cutoff date (always 60 days after the hearing date), arresting Assignment \#1 Page 2 of 3 Advanced Database COMP214 officers (can be more than one officer), crime codes (such as burglary, forgery, assault; hundreds of codes exist), amount of fine, court fee, amount paid, payment due date, and charge status (pending, guilty, not guilty) Sentencing: start date, end date, number of violations (such as not reporting to probation officer), and type of sentence ( jail period, house arrest, probation) Appeals: appeal filing date, appeal hearing date, status (pending, approved, and disapproved) Note: Each crime case can be appealed up to three times. Police officers: name, precinct, badge number, phone contact, status (active/inactive) Additional notes: - A single crime can involve multiple crime charges, such as burglary and assault. - Criminals can be assigned multiple sentences. For example, a criminal might be required to serve a jail sentence followed by a period of probation