Subqueries Assignment

The city$$s Crime Analysis unit has submitted the following data requests. Provide the SQL

statements using subqueries to satisfy the requests. Test the statements and show execution

results.

$1.$ List the name of each officer who has reported more than the average number of crimes

officers have reported.

SQL$/$MySQL Code:

Screenshot:

$2.$ List the names of all criminals who have committed less than average number of crimes

and aren$$t listed as violent offenders.

SQL$/$MySQL Code:

Screenshot:

$3.$ List appeal information for each appeal that has a less than average number of days

between the filing and hearing dates.

SQL$/$MySQL Code:

Screenshot:

$4.$ List the names of probation officers who have had a less than average number of

criminals assigned.

SQL$/$MySQL Code:

Screenshot:

$5.$ List each crime that has had the highest number of appeals recorded.

SQL$/$MySQL Code:

Screenshot:

$6.$ List the information on crime charges for each charge that has had a fine above average

and a sum paid below average.

SQL$/$MySQL Code:

Screenshot:

$7.$ List the names of all criminals who have had any of the crime code charges involved in

crime ID $10089.$

SQL$/$MySQL Code:

Screenshot:

$8.$ Use a correlated subquery to determine which criminals have had at least one probation

period assigned.

SQL$/$MySQL Code:

Screenshot:

$9.$ List the names of officers who have booked the highest number of crimes. Note that more

than one officer might be listed.

SQL$/$MySQL Code:

Screenshot:

$10.$ The criminal data warehouse contains a copy of the CRIMINALS table that needs to be

updated periodically from the production CRIMINALS table. The data warehouse table is

named CRIMINALS$\_$DW$.$ Use a single SQL statement to update the data warehouse

table to reflect any data changes for existing criminals and to add new criminals. Note:

Use a MERGE statement to satisfy this request.

SQL$/$MySQL Code:

Screenshot: