Assessment Section $1$

$1$

Join Queries Assessment

This database contains data about crimes reported in the city of Chicago during $2018.$

$1\mathrm{.}1$

Make a community$-$area neighborhood list.

Show the side, community area name, and the name of the neighborhood.

Sort the results by side then by community area name then by neighborhood

$10$

$1\mathrm{.}2$

List the crimes reported to have occurred at schools in ward $48$ with a IUCR primary description of "BATTERY".

Show the secondary description, district number, arrest, and date.

Crimes occurring at schools have a location that starts with "school".

Sort the results by secondary description then by district number then by case number.

$10$

$1\mathrm{.}3$

List the crimes reported with a primary description of "OTHER OFFENSE" that occurred in ward #$39$ where an arrest has been made.

Show the primary description, ward number, district number, case$\_$number, location description, and beat.

Sort the results by district number then by ward number then by primary description then by case$\_$number.

$10$

$1\mathrm{.}4$

List the crimes reported between latitudes $41\mathrm{.}908$ and $41\mathrm{.}909($inclusive$)$ that have a location description of "DEPARTMENT STORE".

Show the title from the FBI$\_$code table, ward number, district number, report$\_$number, case number, and block.

Sort the results by ward number then by district number then by title then by report number.

$10$

$1\mathrm{.}5$

Build a detailed list of all crimes reported to have occurred in locations under management of the Chicago Housing Authority in ward number $10$ where an arrest has been made.

Show the case number, location description, IUCR primary description, community area number, and date.

Order the result by primary description then secondary description then by case number.

Note: locations under the management of the Chicago Housing Authority begin with "CHA".

$10$

$1\mathrm{.}6$

Build a detailed list of crimes against Property reported to have occurred in district #$12$ at locations with a description of "BARBERSHOP".

Show the case number, IUCR primary description, IUCR secondary description, community area number, and date.

Order the result by secondary description then primary description then by case number.

Note: Only include crimes where the "crime$\_$against" column equals "Property".

$10$

$1\mathrm{.}7$

Build a community$-$area crime report showing each incident of arson.$$

Include crimes reported in Chicago's side named "South" with a primary description of "ARSON".

Show the community area name, secondary description, arrest, report$\_$number, case$\_$number, and ward number columns.

Sort the results by secondary description then by arrest then by community area name then by report number.

$10$

$1\mathrm{.}8$

List the crimes reported to have occurred at exactly $11$:$00$ am on December $5,2018.$

Show the title from the FBI$\_$code table, ward number, district number, report number, community area number, and case number.

Sort the results by ward number then by district number then by title then by report number.

$10$

$1\mathrm{.}9$

Suppose you work for alderman Emma Mitts in the office of ward #$37.$ The alderman would like to discuss progress on the cases of a particular category of crime with commanders of the districts where the crimes occurred. Generate a list to facilitate this discussion.

List all crimes reported in ward #$37$ with an IUCR primary description of "HOMICIDE".

Show the primary$\_$description, secondary$\_$description, district number, arrest, case$\_$number, commander, district phone number, FBI code number, and location description.

Sort the results by secondary description then by district number then by case$\_$number then by arrest.

$10$

$1\mathrm{.}10$

Suppose you live in the Cabrini$-$Green neighborhood and are concerned about the rise in a particular category of crime. You have a meeting scheduled with a member of your alderman's staff to discuss the issue. To prepare for the meeting, you need to prepare a list of the crimes.

List all crimes reported in the community area associated with your neighborhood $($Cabrini$-$Green$)$ with an IUCR primary description of "INTERFERENCE WITH PUBLIC OFFICER".

Show the primary$\_$description, secondary$\_$description, arrest, report number, date, block, and location description.

Sort the results by arrest then by secondary description then by report number.

FBI$\_$Code

The standard crime classification scheme used by the United States Federal Bureau of Investigation.

IUCR

Illinois Uniform Crime Reporting $($IUCR$)$ codes are four digit codes that law enforcement agencies use to classify criminal incidents when taking reports. These codes are also used to aggregate types of cases for statistical purposes.

Neighborhood

A list of unofficial neighborhoods in the city of Chicago. Community areas generally have several neighborhoods although there is not universal agreement about neighborhood boundaries

Ward

The City of Chicago is geographically divided into fifty legislative districts or wards. Each ward is represented by an alderman who is elected by his or her constituency to serve