Coursera | Databases and SQL for Data Science(IBM) | Assignment

其他资源：
Coursera课程|数据库与SQL数据分析（IBM）|练习题解答
无

Coursera | Databases and SQL for Data Science | Assignment

• 课程作业
  • 理解数据集
    • 芝加哥的社会经济指标
      • 芝加哥公立学校
      • 芝加哥犯罪数据

以下是对输入文本的同义改写版本

以下是对输入文本的同义改写版本

以上为改写后的文本内容

这个assignment也不算太难。除了最初的数据导入步骤外,其余的部分与quiz类似

!!!:
这里是在向您介绍如何导入本地数据库。具体来说，请按照教程设置好所需表结构及凭证信息，并确保所有设置均为本地环境参数！无需过多说明的是，默认情况下直接运行该操作应该就能顺利完成。请注意，在此过程中请确保所有设置均为本地环境参数！后续SQL语句的操作逻辑保持不变！😊

Course Assignment

Using this Python notebook you will:

1. Grasp the details of three datasets from Chicago
2. Import three datasets into three tables across a Db2 database
3. Run SQL queries to address assignment-related queries

Understand the datasets

To finish the assignment issues in this notebook, you will access three data sources located on the City of Chicago's Data Portal platform.

1. 数据集：社会经济指标在芝加哥
2. 数据集：芝加哥公立学校进展报告卡（2011年）
3. 数据集：芝加哥犯罪数据（从2001年至今）

Socioeconomic Indicators in Chicago

This dataset includes a collection of six socioeconomic metrics of public health importance in each Chicago community area from 2008 to 2012.

For this exercise, you are to be using a snapshot of this dataset, which can be accessed via:

A detailed description of this dataset and its original data can be retrieved from the Chicago Data Portal located at: https://data.cityofchicago.org/Health-Human-Services/Census-Data-Selected-socioeconomic-indicators-in-C/kn9c-c2s2

Chicago Public Schools

This dataset includes all school-level performance data that were utilized to generate CPS School Report Cards for the 2011-2012 academic year. The dataset is made available by the Chicago Data Portal.

In this assignment, you will utilize a sample of this dataset, which is available for download from: Chicago Public School

The primary and foundational datasets, along with their comprehensive overview, are accessible through the Chicago Data Portal at https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t?cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork-20127838&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ.

Chicago Crime Data

该数据集反映了自2001年以来在芝加哥发生的犯罪事件报告（除谋杀案件外，在每一起受害者的资料中存在的情况下）），不包括最近七天的数据。

This dataset is comprehensively extensive, exceeding 1.5 gigabytes in size and containing over 6.5 million entries. For the purposes of this assignment, we will utilize a significantly reduced sample from this dataset, which can be downloaded from: Chicago Crime Data.

A comprehensive overview of the specific data collection and its original source can be accessed via the Chicago Data Portal at: https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2

Download the datasets

In numerous instances, where data analysis is required, datasets are frequently accessible in .CSV format, particularly online. By clicking on the links provided below, you can conveniently access and store these .CSV files for further processing.

CENSUS_DATA: Census Dataset

CHICAGO_PUBLIC SCHOOL SYSTEM High-Performing Public Schools in Chicago

The Chicago Crime Data Repository provides access to the comprehensive dataset detailing crime statistics and incidents across the city of Chicago. (https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/FinalModule_Coursera/data/Chicago_Crime_Data-v2.csv)

NOTE: 请注意，请确保您使用上方提供的链接下载数据集而非直接从芝加哥数据港下载。这些链接中的版本是原始数据集的子集，并且对某些列名进行了修改以使其更适合数据库环境，这将使完成此任务更容易。

Store the datasets in database tables

To analyze the data using SQL, it first needs to be stored in the database.

While importing the dataset into a Pandas DataFrame and subsequently writing back to the database, as demonstrated in our Week 3 Lab exercise, this approach leads to mapping data types that might not be ideal for efficient SQL operations. For instance, a lengthy textual field could end up being stored as a CLOB rather than a VARCHAR.

Therefore, it is strongly advised to perform a manual load of the table using the database console interface, as detailed in Week 2 Lab 1 Part II. The primary variation from this lab compared to Week 2 Lab 1 Part II lies in Step 5 of the instructions, where you are required to click on create "(+) New Table" and specify the desired table name before proceeding.

启动Db2控制台，并导入LOAD工具，随后拖拽包含第一组数据的CSV文件。然后创建一个新的数据表，请按照屏幕上的操作步骤完成数据加载。请为新创建的表命名为以下名称：

1. CENSUS_DATA
2. CHICAGO_PUBLIC_SCHOOLS
3. CHICAGO_CRIME_DATA

Connect to the database

First, we need to install or enable the SQL extension. Then, we should create a session with the database.

    %load_ext sql

    The sql extension is already loaded. To reload it, use:
      %reload_ext sql

Next, input your DB2 connection string into the following cell. Remember that you previously set up Service Credentials for your DB2 instance during Week 3’s first lab. Refer to these credentials’ username field and enter everything after db2:// (excluding any trailing double quotes) into the cell below, following ibm_db_sa:// format.

    # Remember the connection string is of the format:
    # %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name
    # Enter the connection string for your Db2 on Cloud database instance below
    %sql ibm_db_sa://wls55462:2ls9wcg2s5l7n%4008@dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net:50000/BLUDB

    'Connected: wls55462@BLUDB'

Problems

Now write and execute SQL queries to solve assignment problems

Problem 1

Find the total number of crimes recorded in the CRIME table

    %sql select COUNT(*) as TOTAL_CRIMES from CHICAGO_CRIME_DATA

Problem 2

Retrieve first 10 rows from the CRIME table

    %sql SELECT * FROM CHICAGO_CRIME_DATA LIMIT 10

Problem 3

How many crimes involve an arrest?

    %sql SELECT COUNT(*) AS NUMBER_OF_ARRESTS \
    FROM CHICAGO_CRIME_DATA \
    WHERE ARREST = "TRUE"

Problem 4

What are the distinct kinds of crimes that have been uniquely recorded at the GAS STATION sites?

    %sql SELECT DISTINCT PRIMARY_TYPE \
    FROM CHICAGO_CRIME_DATA \
    WHERE LOCATION_DESCRIPTION = 'GAS STATION'

Hint: Which column lists types of crimes e.g. THEFT?

Problem 5

Enumerate all Community Areas in the CENUS_DATA table where their names begin with the letter ‘B’.

    %sql SELECT COMMUNITY_AREA_NAME \
    FROM CENSUS_DATA \
    WHERE COMMUNITY_AREA_NAME LIKE 'B%'

Problem 6

Which schools in Community Areas 10 to 15 are healthy school certified?

    %sql SELECT NAME_OF_SCHOOL \
    FROM CHICAGO_PUBLIC_SCHOOLS \
    WHERE COMMUNITY_AREA_NUMBER BETWEEN 10 AND 15 \
    AND HEALTHY_SCHOOL_CERTIFIED = 'Yes'

Problem 7

What is the average school Safety Score?

    %sql SELECT AVG(SAFETY_SCORE) AS AVERAGE \
    FROM CHICAGO_PUBLIC_SCHOOLS

Problem 8

The top five Community Areas are ranked by their average College Enrollment numbers.

    %sql SELECT COMMUNITY_AREA_NAME, AVG(COLLEGE_ENROLLMENT) as Number_of_Students\
    FROM CHICAGO_PUBLIC_SCHOOLS \
    GROUP BY COMMUNITY_AREA_NAME \
    ORDER BY Number_of_Students DESC LIMIT 5

Problem 9

Apply a sub-query to be determined by the Community Area with the lowest value for school Safety Score.

    %sql SELECT COMMUNITY_AREA_NAME, SAFETY_SCORE \
    FROM CHICAGO_PUBLIC_SCHOOLS \
    WHERE SAFETY_SCORE = \
    (SELECT MIN(SAFETY_SCORE) FROM CHICAGO_PUBLIC_SCHOOLS)

Problem 10

Calculate the Per Capita Income for a Community Area that has a School Safety Score rated at 1.

    %sql SELECT CD.COMMUNITY_AREA_NAME, CD.PER_CAPITA_INCOME \
    FROM CENSUS_DATA CD, CHICAGO_PUBLIC_SCHOOLS CPS \
    WHERE CD.COMMUNITY_AREA_NUMBER = CPS.COMMUNITY_AREA_NUMBER \
    AND CPS.SAFETY_SCORE = 1