Join Queries Assessment

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

(1.1) Make a community-area neighborhood list. a. Show the side, community area name, and the name of the neighborhood. b. Sort the results by side then by community area name then by neighborhood (1.2) List the crimes reported to have occurred at residences in ward 9 with a IUCR primary description of "ROBBERY". - Show the secondary description, district, beat, and arrest. - Crimes occurring at residences have a location that starts with "residen". - Sort the results by secondary description then by district. (1.3 List the crimes reported with a primary description of "ROBBERY" that occurred in ward \#7 where an arrest has been made. - Show the primary description, ward, district, date, and FBI code. - Sort the results by primary description then by district then by ward. (1.4 List the crimes reported between latitudes 41.892 and 41.893 (inclusive) that have a location description of "GROCERY FOOD STORE". - Show the title from the FBI_code table, ward, district, arrest, and beat. - Sort the results by title then by district then by ward. (1.5) Build a detailed list of all crimes reported to have occurred in locations under management of the Chicago Transit Authority in ward number 47 where no arrest has been made. - Show the case number, IUCR primary description, IUCR secondary description, community area number, and district number. - Order the result by primary description then secondary description. - Note: locations under the management of the Chicago Transit Authority begin with "CTA". (1.6) Build a detailed list of crimes against Persons reported to have occurred in district 22 at locations with a description of "BARBERSHOP". Show the case number, IUCR primary description, IUCR secondary description, beat, and date. - Order the result by primary description then secondary description. - Note: Only include crimes where the "crime_against" column equals "Persons". (1.7) Build a community-area crime report showing each incident of obscenity. Include crimes reported in Chicago's "West" side with a primary description of "OBSCENITY". - Show the community area name, secondary description, arrest, block, and date columns. - Sort the results by community area name then by arrest then by secondary description. (1.8) List the crimes reported to have occurred at exactly 9:00 am on September 2, 2018. - Show the title from the FBI_code table, ward, district, arrest, and location description. - Sort the results by ward then by title then by district. (1.9 Suppose you work for alderman Edward Burke in the office of ward \#14. 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 \#14 with an IUCR primary description of "OFFENSE INVOLVING CHILDREN". - Show the primary_description, secondary_description, district, arrest, case_number, commander, district phone number, FBI code, and location description. - Sort the results by district then by secondary description then by arrest then by case_number. (1.10) Suppose you live in the Magnificent Mile 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 (Magnificent Mile) with an IUCR primary description of "OFFENSE INVOLVING CHILDREN". - Show the primary_description, secondary_description, date, location description, and beat. - Sort the results by secondary description then by arrest then by crime_id. 20012002200326 1620172018201