Advanced Analysis with SQL

Summary

A one-day class reinforcing the skills necessary to leverage a relational database to clean, process, and visualize NYC Parks data. The class will introduce key concepts and skills necessary to use the Structured Query Language (SQL) for data analysis, reinforcing the problem ideation and process mapping skills taught in Introduction to Data Analysis. Working collaboratively in small groups, participants will develop an analytical question they explore throughout the class, presenting their data story at the end of class for constructive feedback.

Target Audience

Employees of all levels of responsibility with a command of Microsoft Excel for sorting, filtering, aggregating, transforming, and visualizing data to tell a true and compelling story, ideally through Excel for Data Analysis I and II. No familiarity with SQL is necessary for this class as we will be introducing and practicing key concepts in class. Participants should complete Introduction to Data Analysis prior to attending this class for an introduction to problem ideation and process mapping.

Goals

• Introduce key concepts necessary for working with relational database management systems
• Demonstrate advanced techniques with SQL for data analysis
• Reinforce and practice best practices in exploratory data analysis
• Reinforce brainstorming, problem ideation, and process mapping skills introduced in previous classes
• Practice presenting true and compelling data stories to peers
• Cultivate an attitude of curiosity to foster a culture of data-driven inquiry

Participant Development Areas

Conceptualizing: Self-directed collaborative scoping of analytics problem and process

Skill Development: SQL for accessing, and extracting NYC Parks data for analysis

Integrating: Investigate context using NYC Parks resources

Communicating: Project pitch with content critique in order to foster an entrepreneurial mindset with respect to developing and communicating analytical work

Language Objective: Creatively apply the tools of data analysis to explain problems and develop potential solutions with data

Learning Objectives

• Participants will be familiar with the key concepts of a relational database management system
• Participants will be experienced in the key steps of exploratory data analysis using SQL
• Participants will be familiar with the techniques of advanced data analysis with SQL
• Participants will be practiced in using the techniques of brainstorming, problem ideation, and process mapping to scope and execute an analytical question
• Participants will be practiced presenting analytical findings and describing the key steps in their analysis to their peers
• Participants will have an increased curiosity about how data can be leveraged for operational awareness and program success

Outline

• Welcome, Introductions, and Housekeeping (Richard)
• Review of Previous Learning - Lecture/Discussion (Eric)
  • Data Analytics 101
  • 5 Data Analytics Tasks
• Importing from a Database - Guided Exercise (Eric)
• Overview of Databases and Introduction to SQL - Lecture/Discussion/Guided Exercise (Richard)
  • What is a database?
  • What is a table?
  • Using SELECT and FROM
  • Why use a database?
  • Why use SQL?
  • Your Turn - Construct a simple SQL query
• Filtering Queries with SQL - Lecture/Discussion/Guided Exercise (Eric)
  • Filtering with WHERE
  • Logical operators
  • Your Turn - Create a simple SQL query filtering results with WHERE
  • Combining filtering conditions
  • Your Turn - Create a SQL query with multiple filter conditions
• Wrap-up (Richard)
• Break
• Overview of Data Types - Lecture/Discussion/Guided Exercise (Richard)
  • Common data types
  • Importance of data types in databases
  • Your Turn - Test knowledge of data types by querying for specific value in the table
• Using Formulas and Functions in SQL - Lecture/Discussion/Guided Exercise (Eric)
  • Overview of formulas in SQL
  • Overview of functions in SQL
  • Your Turn - Write a simple SQL query that uses a built-in SQL Server function from the documentation provided
• Documenting Work - Lecture/Discussion (Richard)
• Sorting Data in SQL - Lecture/Discussion/Guided Exercise (Richard)
  • Overview of Sorting
  • Using ORDER BY
  • Specifing order with DESC
  • Your Turn - Write a SQL query that sorts the result by one or two columns of data
• Aggregating Data with SQL - Lecture/Discussion/Guided Exercise (Eric)
  • Overview of Aggregation
  • Aggregating in Excel (explain by reference to known example)
  • Aggregation in SQL with GROUP BY
  • Overview of Aggregation Operators (SUM, COUNT,AVG,MAX, MIN)
  • Your Turn - Write a SQL query that meaningfully aggregates the daily tasks data
• Wrap-Up - Lecture/Discussion (Richard)
• Lunch
• Welcome Back and Morning Recap - Lecture/Discussion (Richard)
• Joining Data - Lecture/Discussion/Guided Exercise (Richard/Eric)
  • Joins in Excel (Richard)
  • Joins in SQL (Eric)
  • Your Turn - Join daily tasks data to the PIP inspection data in SQL (Eric)
• Wrap-Up - Lecture/Discussion (Richard)
• Break
• Final Exercise - Small Group Exercise (Richard)
  • Overview of Task
  • Reminder of Previous Learning (Process Map)
  • Ideation of Question to Explore
  • Task Instruction
• Presentations (Eric/Richard)
• Wrap-Up - Lecture/Discussion (Eric)
  • What We've Covered
  • Optimizing Queries
  • SQL Server Management Studio (SSMS) Overview
  • Resources
  • Contact Information
  • Survey Link

Exercise Descriptions

Classroom Setup