Data Analysis with SQL and Python Course

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

Download

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

Learn About Virtual Training

Request Information

Get your team upskilled or reskilled today. Chat with one of our experts to create a custom training proposal. Fully customized at no additional cost.

If you are not completely satisfied with your training class, we'll give you your money back.

about our training

Real-World Content

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

Industry experts with 15+ years of industry experience that bring their battle scars into the classroom.
Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
Fully Customized

One-size-fits-all doesn't apply to training teams. That's where we come in!

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