Scribd
Upload
1K views2 pages

6 SQL WK5 Final

The document contains 9 SQL queries to analyze data from 3 databases: CENSUS_DATA, CHICAGO_PUBLIC_SCHOOLS, and CHICAGO_CRIME_DATA. The queries find crime statistics, community area details, school safety scores, and poverty rates. Problems 8-10 use subqueries to identify the most crime-prone community area and the area with the highest hardship index and most crimes.

Uploaded by

kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
1K views2 pages

6 SQL WK5 Final

The document contains 9 SQL queries to analyze data from 3 databases: CENSUS_DATA, CHICAGO_PUBLIC_SCHOOLS, and CHICAGO_CRIME_DATA. The queries find crime statistics, community area details, school safety scores, and poverty rates. Problems 8-10 use subqueries to identify the most crime-prone community area and the area with the highest hardship index and most crimes.

Uploaded by

kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Problem 1: Find the total number of crimes recorded in the CRIME table.

%sql select count(*) from CHICAGO_CRIME_DATA;


Problem 2: List community areas with per capita income less than 11000.

%%sqlSELECT community_area_name, per_capita_income FROM CENSUS_DATA


Problem 3: List all case numbers for crimes involving minors?

%sql SELECT case_number FROM CHICAGO_CRIME_DATA WHERE DESCRIPTION LIKE


'%MINOR%';

Problem 4: List all kidnapping crimes involving a child?(children are not considered
minors for the purposes of crime analysis)

%sql SELECT CASE_NUMBER, BLOCK, PRIMARY_TYPE, DESCRIPTION FROM


CHICAGO_CRIME_DATA WHERE PRIMARY_TYPE = 'KIDNAPPING';

Problem 5: What kind of crimes were recorded at schools?

%sql SELECT DISTINCT(PRIMARY_TYPE) FROM CHICAGO_CRIME_DATA WHERE


LOCATION_DESCRIPTION LIKE '%SCHOOL%';

Problem 6: List the average safety score for all types of schools.

%sql SELECT "Elementary, Middle, or High School",AVG(SAFETY_SCORE) AS


AVERAGE_SAFETY_SCORE

FROM CHICAGO_PUBLIC_SCHOOLS GROUP BY "Elementary, Middle, or High School";

Problem 7: List 5 community areas with highest % of households below poverty line.

%sql SELECT COMMUNITY_AREA_NAME, PERCENT_HOUSEHOLDS_BELOW_POVERTY


FROM CENSUS_DATA ORDER BY PERCENT_HOUSEHOLDS_BELOW_POVERTY DESC
LIMIT 5 ;

Problem 8: Which community area(number) is most crime prone?

%sql SELECT
CCD.COMMUNITY_AREA_NUMBER ,COUNT(CCD.COMMUNITY_AREA_NUMBER) AS
FREQUENCY FROM CHICAGO_CRIME_DATA AS CCD GROUP BY
CCD.COMMUNITY_AREA_NUMBER ORDER BY
COUNT(CCD.COMMUNITY_AREA_NUMBER) DESC LIMIT 1

Problem 9: Use a sub-query to find the name of the community area with highest
hardship index.

%sql SELECT COMMUNITY_AREA_NAME FROM CENSUS_DATA WHERE


HARDSHIP_INDEX = (SELECT MAX(HARDSHIP_INDEX) FROM CENSUS_DATA);

Problem 10: Use a sub-query to determine the Community Area Name with most
number of crimes?

%sql SELECT community_area_name FROM CENSUS_DATA WHERE


COMMUNITY_AREA_NUMBER = ( SELECT CCD.COMMUNITY_AREA_NUMBER FROM
CHICAGO_CRIME_DATA AS CCD GROUP BY CCD.COMMUNITY_AREA_NUMBER ORDER
BY COUNT(CCD.COMMUNITY_AREA_NUMBER) DESC LIMIT 1) LIMIT 1;

1. CENSUS_DATA

2. CHICAGO_PUBLIC_SCHOOLS

3. CHICAGO_CRIME_DATA

SQL MAGIC LOGIN

4. !pip install --force-reinstall ibm_db ibm_db_sa


5. !pip install sqlalchemy==1.3.9
6. !pip install ibm_db_sa
7. %Load_ext sql
8. %sql ibm_db_sa://spd12110:NKNIfXCB5DI6XLSa@ba99a9e6-d59e-4883-8fc0-
d6a8c9f7a08f.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud:31321/bludb?security=SSL
9.

You might also like