Skip to content

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Data Analysis with SQL and Python

Course Summary

The Data Analysis with SQL and Python training course is designed to demonstrate a deep exploration into how to use SQL for data analysis and its usefulness within the Python development environment.

The course begins with a foundation of regular expressions, volatile tables, and cross joins which make SQL a powerful platform for data analytics. Next, it describes CTEs and subqueries as well as windowing functions. Lastly, the course utilizes the common limitations of SQL and when they may need to use a more powerful language like Python for certain problems.

Purpose
 Provide students with the advanced SQL skills needed for effective data analysis.
Audience
 Data Analysts, Data Engineers and Data Scientists with at least some exposure to basic SQL queries and use of databases
Role
Data Engineer - Data Scientist
Skill Level
Intermediate
Style
Workshops
Duration
3 Days
Related Technologies
SQL | Python

 

Productivity Objectives
  • Write window functions, cross joins and regular expressions in SQL
  • Build complex queries that are more readable with CTE's and subqueries
  • Understand when to use Python over SQL for certain problems

What You'll Learn:

In the Data Analysis with SQL and Python training course, you'll learn:
  • Introduction to SQL
    • What is SQL?
    • Functionality supported by SQL
  • Database Concepts
    • Core database terminologies
    • Database design process
    • Overview of Database Normalization
  • Simple Data Querying
    • Review of SELECT concepts
    • SELECT rows of data
    • SELECT columns of data
    • Filter SELECT using WHERE
    • Order results using ORDER BY
    • Group results using GROUP BY
  • Complex Data Querying
    • Understand a join
    • Query related rows using INNER JOIN
    • Include non-matched rows with OUTER JOIN
    • Unions Handle
    • JOIN results
  • SQL Functions
    • Math functions: count, avg, sum, round, etc.
    • Character functions: ucase, lcase, etc.
    • Time functions: now, date, etc. Other functions
  • Advanced subqueries and query compositions
    • Correlation predicates
    • Expression subqueries and derived tables
    • Value list subqueries
    • The benefits of EXISTS processing Unleashing the power of set operators
    • Advanced use of UNION, EXCEPT, and INTERSECT
  • Recursions, window functions, and imperative processing
    • Recursive common table expressions
    • The use case for imperative constructs
  • Window Functions
    • What are Window Functions?
    • Partition a simple aggregation
    • Partition a rolling aggregation
    • Partition a rolling aggregation by quarter
    • Apply a ranking
    • Filter on a partitioned field
    • Use the WINDOW keyword to reuse partitions
  • Using SQL with Python
    • Intro to SQLAlchemy
    • Use Pandas with SQLAlchemy
    • What are Pandas Dataframes?
    • End to end extract, transform and load using Pandas
real world content

Real-World Content

Project-focused demos and labs using your tool stack and environment, not some canned "training room" lab.
expert practitioners

Expert Practitioners

Industry experts that bring their battle scars into the classroom.
experiential learning

Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
tailored outlines

Tailored Outlines

One-size-fits-all doesn't apply to training teams. That's where we come in!
“I appreciated the instructor's technique of writing live code examples rather than using fixed slide decks to present the material.”

VMware

Dive in and learn more

When transforming your workforce, it's important to have expert advice and tailored solutions. We can help. Tell us your unique needs and we'll explore ways to address them.

Let's chat

By filling out this form and clicking submit, you acknowledge our privacy policy.