1 / 8

Using Toad for Data Analysts

Using Toad for Data Analysts. A prettier SQL interface. Step 1: Get to know your data. Excel Workbook titled “CHSI Dataset” Community Health Status Indicators Downloaded from Data.gov DataElementDescription = Data Dictionary DefinedDataValue = Data Code sheet

carlow
Télécharger la présentation

Using Toad for Data Analysts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Toad for Data Analysts A prettier SQL interface

  2. Step 1: Get to know your data • Excel Workbook titled “CHSI Dataset” • Community Health Status Indicators • Downloaded from Data.gov • DataElementDescription = Data Dictionary • DefinedDataValue = Data Code sheet • Additional tables provide various health measures, often on the county level

  3. Import Data • Select all data in Workbook, save and close • Navigation Manager  Create new connection  Group: Excel Advanced Uncheck Read-Only & Automatically create ranges  Navigate to CHSI Data.xls  Name: CHSI

  4. Data Browser • Double-click on table DATAELEMENTDESCRIPTION • FilterWhere Clause: Page_Name = ‘Demographics’

  5. SQL Editor • Click Editor • Input: • Select * from DATAELEMENTDESCRIPTION descr where descr.PAGE_NAME = ‘Demographics’ • Don’t forget to name your table by typing in a nickname after full table name. This simplifies query writing

  6. Join tables • Let’s join Demographics to Summary Measures of Health • What are common fields? • State_FIPS_Code • County_FIPS_Code • select demo.CHSI_County_Name, demo.poverty, summ.ALE from DEMOGRAPHICS demo, SUMMARYMEASURESOFHEALTH summ where demo.CHSI_State_Abbr='CA' and summ.State_FIPS_Code=demo.State_FIPS_Code and summ.County_FIPS_Code=demo.County_FIPS_Code • This will tell us percent living below 10th Percentile average life expectancy per county

  7. Grouping Data • What if we want to know poverty rates and average life expectancy by state? • select demo.CHSI_State_Abbr, sum(demo.poverty), avg(summ.ALE) from DEMOGRAPHICS demo, SUMMARYMEASURESOFHEALTH summ where summ.State_FIPS_Code=demo.State_FIPS_Code and summ.County_FIPS_Code=demo.County_FIPS_Code group by demo.CHSI_State_Abbr

  8. Sorting Data • Now let’s sort the states by number in poverty, highest to lowest • You can simply click on output header and sort • Or use the following code: • select * from (select demo.CHSI_State_Abbr, sum(demo.poverty), avg(summ.ALE) from DEMOGRAPHICS demo, SUMMARYMEASURESOFHEALTH summ where summ.State_FIPS_Code=demo.State_FIPS_Code and summ.County_FIPS_Code=demo.County_FIPS_Code group by demo.CHSI_State_Abbr) order by Expr1001 asc

More Related