{"cells":[{"cell_type":"markdown","id":"973d9302-d9b0-4d78-afc2-fb4a3184ea2b","metadata":{},"outputs":[],"source":["
\n"," \"cognitiveclass.ai\n","
\n","\n","# Working with a real world data-set using SQL and Python\n","\n","Estaimted time needed: **30** minutes\n","\n","## Objectives\n","\n","After complting this lab you will be able to:\n","\n","- Understand the dataset for Chicago Public School level performance\n","- Store the dataset in an Db2 database on IBM Cloud instance\n","- Retrieve metadata about tables and columns and query data from mixed case columns\n","- Solve example problems to practice your SQL skills including using built-in database functions\n"]},{"cell_type":"markdown","id":"5682e886-1cbb-43ef-85a4-08c2621765f9","metadata":{},"outputs":[],"source":["## Chicago Public Schools - Progress Report Cards (2011-2012)\n","\n","The city of Chicago released a dataset showing all school level performance data used to create School Report Cards for the 2011-2012 school year. The dataset is available from the Chicago Data Portal: [https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t](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&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)\n","\n","This dataset includes a large number of metrics. Start by familiarizing yourself with the types of metrics in the database: [https://data.cityofchicago.org/api/assets/AAD41A13-BE8A-4E67-B1F5-86E711E09D5F?download=true](https://data.cityofchicago.org/api/assets/AAD41A13-BE8A-4E67-B1F5-86E711E09D5F?download=true&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)\n","\n","**NOTE**: \n","\n","Do not download the dataset directly from City of Chicago portal. Instead download a static copy which is a more database friendly version from this link.\n","\n","**NOTE**:\n","\n","For the learners who are encountering issues with loading from .csv in DB2 on Firefox, you can download the .txt files and load the data with those: link.\n","\n","Now review some of its contents.\n"]},{"cell_type":"markdown","id":"0206d2fa-7181-4b33-8100-1b3bcb478807","metadata":{},"outputs":[],"source":["### Store the dataset in a Table\n","\n","In many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. To analyze the data using SQL, it first needs to be stored in the database.\n","\n","While it is easier to read the dataset into a Pandas dataframe and then PERSIST it into the database as we saw in the previous lab, it results in mapping to default datatypes which may not be optimal for SQL querying. For example a long textual field may map to a CLOB instead of a VARCHAR. \n","\n","Therefore, **it is highly recommended to manually load the table using the database console LOAD tool, as indicated in Week 2 Lab 1 Part II**. The only difference with that lab is that in Step 5 of the instructions you will need to click on create \"(+) New Table\" and specify the name of the table you want to create and then click \"Next\". \n","\n","##### Now open the Db2 console, open the LOAD tool, Select / Drag the .CSV file for the CHICAGO PUBLIC SCHOOLS dataset and load the dataset into a new table called **SCHOOLS**.\n","\n","\n"]},{"cell_type":"markdown","id":"83329fff-7a40-4c10-97e9-59fce152ae4c","metadata":{},"outputs":[],"source":["### Connect to the database\n","\n","Let us now load the ipython-sql extension and establish a connection with the database\n","\n","The following modules are pre-installed in the Skills Network Labs environment. However if you run this notebook commands in a different Jupyter environment (e.g. Watson Studio or Ananconda) you may need to install these libraries by removing the `#` sign before `!pip` in the code cell below.\n"]},{"cell_type":"code","id":"238bbab4-7d76-4cf8-ac85-b2c305efc542","metadata":{},"outputs":[],"source":["# These libraries are pre-installed in SN Labs. If running in another environment please uncomment lines below to install them:\n# !pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n# Ensure we don't load_ext with sqlalchemy>=1.4 (incompadible)\n# !pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24\n# !pip install ipython-sql"]},{"cell_type":"code","id":"5a6f39d6-2fc6-4060-bcac-28bc7abe0eee","metadata":{},"outputs":[],"source":["!pip install ipython-sql\n!pip install ibm_db ibm_db_sa\n%load_ext sql"]},{"cell_type":"code","id":"0716a447-c9ee-4a3f-87b6-17027fa4ce58","metadata":{},"outputs":[],"source":["!pip install ipython-sql prettytable\nimport prettytable\nprettytable.DEFAULT = 'DEFAULT'"]},{"cell_type":"code","id":"095856a4-5285-459b-bc35-2b1e81eacd0b","metadata":{},"outputs":[],"source":["# Enter the connection string for your Db2 on Cloud database instance below\n# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name?security=SSL\n%sql ibm_db_sa://"]},{"cell_type":"markdown","id":"d9e9b26a-955d-4025-aea8-aa590eb05d22","metadata":{},"outputs":[],"source":["### Query the database system catalog to retrieve table metadata\n","\n","##### You can verify that the table creation was successful by retrieving the list of all tables in your schema and checking whether the SCHOOLS table was created\n"]},{"cell_type":"code","id":"c01ec3ea-3631-4d71-a75a-e015f3793796","metadata":{},"outputs":[],"source":["# type in your query to retrieve list of all tables in the database for your db2 schema (username)\n"]},{"cell_type":"markdown","id":"e7dad39b-dac0-4d52-bc51-55e64b9c37ab","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"4b35b537-8e9b-4a66-b354-221fbe1c92ae","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"6686ad40-4423-461e-9120-6feb1193ada0","metadata":{},"outputs":[],"source":["### Query the database system catalog to retrieve column metadata\n","\n","##### The SCHOOLS table contains a large number of columns. How many columns does this table have?\n"]},{"cell_type":"code","id":"c665af8b-3b1a-493d-ba03-b18934799f17","metadata":{},"outputs":[],"source":["# type in your query to retrieve the number of columns in the SCHOOLS table\n"]},{"cell_type":"markdown","id":"64da6936-460b-4600-a32c-1f99b2ac8de2","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"14086ff1-926f-41f3-8c80-5c825eccbe79","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"88206072-6806-44b6-bd74-7c023f522532","metadata":{},"outputs":[],"source":["Now retrieve the the list of columns in SCHOOLS table and their column type (datatype) and length.\n"]},{"cell_type":"code","id":"cb14a658-f1a1-43ac-907e-a076a4e1a58b","metadata":{},"outputs":[],"source":["# type in your query to retrieve all column names in the SCHOOLS table along with their datatypes and length\n"]},{"cell_type":"markdown","id":"98fc5c4e-5025-4583-b05e-abd3ece0fc86","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"6f882903-df7e-49cb-8cd9-5eefdd67e044","metadata":{},"outputs":[],"source":["### Questions\n","\n","1. Is the column name for the \"SCHOOL ID\" attribute in upper or mixed case?\n","2. What is the name of \"Community Area Name\" column in your table? Does it have spaces?\n","3. Are there any columns in whose names the spaces and paranthesis (round brackets) have been replaced by the underscore character \"_\"?\n"]},{"cell_type":"markdown","id":"07839baf-7018-4832-947b-28435e009afb","metadata":{},"outputs":[],"source":["## Problems\n","\n","### Problem 1\n","\n","##### How many Elementary Schools are in the dataset?\n"]},{"cell_type":"code","id":"1fb573a6-03ce-4097-836b-f240a266832f","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"422084c3-bae3-41f6-90b3-1c10c16a29e3","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"f26e4663-ac97-45f4-8834-1688be3a9943","metadata":{},"outputs":[],"source":["Double-click **here** for another hint\n","\n","\n"]},{"cell_type":"markdown","id":"d8b1844a-dccb-45b1-abbe-4b61718b1d6f","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"507a7fa6-48dd-46fb-9476-0de818b16495","metadata":{},"outputs":[],"source":["### Problem 2\n","\n","##### What is the highest Safety Score?\n"]},{"cell_type":"code","id":"d898a469-e595-4eb6-84e1-e192d9087a8e","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"53740662-b330-4d4d-a1a5-9647763c88bd","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"85125200-0511-46a5-95d9-a4b27af5d9e1","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"abf20b5e-7244-4499-8990-44356bbdf2ba","metadata":{},"outputs":[],"source":["### Problem 3\n","\n","##### Which schools have highest Safety Score?\n"]},{"cell_type":"code","id":"d4fb23df-8f02-4da8-a7e6-c6476a571d16","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"a904a4ea-8a4f-46e3-8e1a-0cae032f750b","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"0566accc-b93a-46ca-ba9b-fa71c23c9f40","metadata":{},"outputs":[],"source":["### Problem 4\n","\n","##### What are the top 10 schools with the highest \"Average Student Attendance\"?\n"]},{"cell_type":"code","id":"07c9d0fb-c7af-4c59-b6ad-edbac9b14059","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"ae68b332-6d66-4091-9080-aaad6cbd1736","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"8edaa29a-0717-4de6-be32-c1a461ebdef1","metadata":{},"outputs":[],"source":["### Problem 5\n","\n","##### Retrieve the list of 5 Schools with the lowest Average Student Attendance sorted in ascending order based on attendance\n"]},{"cell_type":"code","id":"a466cfe4-0caa-426b-90af-a492b9e0fa15","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"8f1fb84d-02fe-4bd7-abb8-86e8f3dc3358","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"db968e49-1381-450a-ada9-40816c011ef2","metadata":{},"outputs":[],"source":["### Problem 6\n","\n","##### Now remove the '%' sign from the above result set for Average Student Attendance column\n"]},{"cell_type":"code","id":"a931ecc9-0e64-48f4-ae40-7511c178a7fd","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"a4612515-5a2e-46de-9f03-c34666f62fef","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"22b1268e-588b-4d59-8227-4d7b2107cc8f","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"6ab709d3-54d0-4830-a0a2-1d9ec36e3079","metadata":{},"outputs":[],"source":["### Problem 7\n","\n","##### Which Schools have Average Student Attendance lower than 70%?\n"]},{"cell_type":"code","id":"f0429e25-0c9a-4e8e-9374-1ebfb4ac3887","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"290ac45d-60ce-4d70-875d-ae33ca679a5c","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"eed90a52-73ee-4878-93c6-50a4897e1d6d","metadata":{},"outputs":[],"source":["Double-click **here** for another hint\n","\n","\n"]},{"cell_type":"markdown","id":"3ed69e73-809c-451b-942d-001ee7d85eea","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"ddd0acf5-937d-4cd4-9d53-ffc8cd0601e5","metadata":{},"outputs":[],"source":["### Problem 8\n","\n","##### Get the total College Enrollment for each Community Area\n"]},{"cell_type":"code","id":"1d13377c-1cad-4388-abff-36460db77601","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"4af9cd33-7f64-494b-af2c-d8c10ac8b7f9","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"73250dba-8411-4aa0-8aaf-ff6e9e05ab1d","metadata":{},"outputs":[],"source":["Double-click **here** for another hint\n","\n","\n"]},{"cell_type":"markdown","id":"dee113d4-1492-454f-b6ab-9c566792dcd3","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"b8d1391e-8cc3-471c-a0ab-92b29afd0d00","metadata":{},"outputs":[],"source":["### Problem 9\n","\n","##### Get the 5 Community Areas with the least total College Enrollment sorted in ascending order\n"]},{"cell_type":"code","id":"2c9eb80e-7b0d-4873-8aab-6d9c7c5cb69e","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"5af21b5e-2987-4626-8af2-8302b38740c9","metadata":{},"outputs":[],"source":["Double-click **here** for a hint\n","\n","\n"]},{"cell_type":"markdown","id":"ba6563a2-0a13-4150-bb00-eff3c05fd2a7","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"d7aaceaf-afcf-4d9c-906e-2e380a190036","metadata":{},"outputs":[],"source":["### Problem 10\n","\n","##### List 5 schools with lowest safety score.\n"]},{"cell_type":"code","id":"493f4f16-7204-465d-bc28-090238de9fcd","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"e28865ef-f2f0-41d7-bb57-22d814e96c5a","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"2e606280-c0e9-45b6-8498-1e56ebb650ef","metadata":{},"outputs":[],"source":["### Problem 11\n","\n","##### Get the hardship index for the community area which has College Enrollment of 4368\n"]},{"cell_type":"code","id":"12098add-fef2-45f9-a9d7-1b23d13b8cc2","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"73132625-b72a-4f42-8cb6-d3328c38d43d","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"8405b14b-770d-4313-b794-9051f9f5ff95","metadata":{},"outputs":[],"source":["### Problem 12\n","\n","##### Get the hardship index for the community area which has the school with the highest enrollment.\n"]},{"cell_type":"code","id":"6f0d170c-bcea-4667-818f-0f0280a04330","metadata":{},"outputs":[],"source":[""]},{"cell_type":"markdown","id":"98586432-cd97-42ee-8697-775f98a9f626","metadata":{},"outputs":[],"source":["Double-click **here** for the solution.\n","\n","\n"]},{"cell_type":"markdown","id":"e65e404d-bcfd-4916-a770-6159fc772f71","metadata":{},"outputs":[],"source":["## Summary\n","\n","##### In this lab you learned how to work with a real word dataset using SQL and Python. You learned how to query columns with spaces or special characters in their names and with mixed case names. You also used built in database functions and practiced how to sort, limit, and order result sets, as well as used sub-queries and worked with multiple tables.\n"]},{"cell_type":"markdown","id":"9c3af0e4-4e7e-41f3-affb-3d8687867baa","metadata":{},"outputs":[],"source":["## Author\n","\n","Rav Ahuja\n","\n","
\n","\n","##

© IBM Corporation 2020. All rights reserved.

\n"]}],"metadata":{"kernelspec":{"name":"python3","display_name":"Python 3 (ipykernel)","language":"python"},"language_info":{"name":"python","version":"3.12.8","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"prev_pub_hash":"40ee53ce9f1bb11130f77daf5d75209ec852e01b02b71529fb42544206aaf8ee"},"nbformat":4,"nbformat_minor":4}