<jupyter_start><jupyter_text>ObjectivesAfter completing this lab you will be able to:-   Understand the dataset for Chicago Public School level performance-   Retrieve metadata about tables and columns and query data from mixed case columns-   Solve example problems to practice your SQL skills including using built-in database functions  Chicago Public Schools - Progress Report Cards (2011-2012)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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01)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?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkDB0201ENSkillsNetwork20127838-2021-01-01&download=true)**NOTE**: 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.Now review some of its contents.  Connect to the databaseLet us now load the ipython-sql  extension and establish a connection with the database<jupyter_code>!pip install sqlalchemy
!pip install mysqlclient
!pip install ipython-sql
%load_ext sql
%sql mysql+mysqldb://user:@localhost/learnbase<jupyter_output><empty_output><jupyter_text>Query the database system catalog to retrieve table metadata 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<jupyter_code># type in your query to retrieve list of all tables in the database for your db2 schema (username)<jupyter_output><empty_output><jupyter_text>Click here for the solution```pythonIn Db2 the system catalog table called SYSCAT.TABLES contains the table metadata%sql select TABSCHEMA, TABNAME, CREATE_TIME from SYSCAT.TABLES where TABSCHEMA='YOUR-DB2-USERNAME'or, you can retrieve list of all tables where the schema name is not one of the system created ones:%sql select TABSCHEMA, TABNAME, CREATE_TIME from SYSCAT.TABLES \      where TABSCHEMA not in ('SYSIBM', 'SYSCAT', 'SYSSTAT', 'SYSIBMADM', 'SYSTOOLS', 'SYSPUBLIC')      or, just query for a specifc table that you want to verify exists in the database%sql select * from SYSCAT.TABLES where TABNAME = 'SCHOOLS'```  Query the database system catalog to retrieve column metadata The SCHOOLS table contains a large number of columns. How many columns does this table have?<jupyter_code># type in your query to retrieve the number of columns in the SCHOOLS table<jupyter_output><empty_output><jupyter_text>Click here for the solution```pythonIn Db2 the system catalog table called SYSCAT.COLUMNS contains the column metadata%sql select count(*) from SYSCAT.COLUMNS where TABNAME = 'SCHOOLS'``` Now retrieve the the list of columns in SCHOOLS table and their column type (datatype) and length.<jupyter_code># type in your query to retrieve all column names in the SCHOOLS table along with their datatypes and length<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup