Unlock the power of SQL in the world of Data Analytics with our SQL for Data Analytics, third edition course. Whether you’re a beginner or looking to enhance your data analysis skills, this course is designed to provide you with practical knowledge and hands-on experience in leveraging SQL for effective data analysis. By the end of this course, you’ll be equipped with the skills to leverage SQL for efficient data analysis.
Here’s what you will learn
Lessons 1: Preface
- About the Course
- Audience
- About the Lessons
- Conventions
- Setting up Your Environment
- Installing Git
- Loading the Sample Datasets – Windows
- Loading the Sample Datasets – Linux
- Loading the Sample Datasets – macOS
- Running SQL files
- Accessing the Code Files
Lessons 2: Understanding and Describing Data
- Introduction
- Data Analytics and Statistics
- Types of Statistics
- Working with Missing Data
- Statistical Significance Testing
- SQL and Analytics
- Summary
Lessons 3: The Basics of SQL for Analytics
- Introduction
- The World of Data
- Relational Databases and SQL
- PostgreSQL Relational Database Management System (RDBMS)
- Creating Tables
- Basic Data Types of SQL
- Data Structures: JSON and Arrays
- Column Constraints
- Updating Tables
- SQL and Analytics
- Summary
Lessons 4: SQL for Data Preparation
- Introduction
- Assembling Data
- Cleaning Data
- Transforming Data
- Summary
Lessons 5: Aggregate Functions for Data Analysis
- Introduction
- Aggregate Functions
- Aggregate Functions with the GROUP BY Clause
- Aggregate Functions with the HAVING Clause
- Using Aggregates to Clean Data and Examine Data Quality
- Summary
Lessons 6: Window Functions for Data Analysis
- Introduction
- Window Functions
- Statistics with Window Functions
- Window Frame
- Summary
Lessons 7: Importing and Exporting Data
- Introduction
- The COPY Command
- Using Python with your Database
- Going Passwordless
- Summary
Lessons 8: Analytics Using Complex Data Types
- Introduction
- Date and Time Data types for Analysis
- Performing Geospatial Analysis in PostgreSQL
- Using Array Data types in PostgreSQL
- Using JSON Data types in PostgreSQL
- Text Analytics Using PostgreSQL
- Summary
Lessons 9: Performant SQL
- Introduction
- The Importance of Highly Efficient SQL
- Database Scanning Methods
- Killing Queries
- Functions and Triggers
- Summary
Lessons 10: Using SQL to Uncover the Truth: A Case Study
- Introduction
- Case Study
- Summary
Hands-on LAB Activities
Understanding and Describing Data
- Creating a Histogram in Excel
- Exploring Dealership Sales Data
The Basics of SQL for Analytics
- Running the SELECT Query
- Creating and Modifying Tables
SQL for Data Preparation
- Generating a List Using the UNION Query
- Building a Sales Model
Aggregate Functions for Data Analysis
- Analyzing Sales Data Using Aggregate Functions
Window Functions for Data Analysis
- Analyzing Sales Using Window Frames and Window Functions
Importing and Exporting Data
- Reading, Visualizing, and Saving Data in Python
Analytics Using Complex Data Types
- Performing Text Analytics
- Searching and Analyzing Sales
Performant SQL
- Implementing Hash Indexes
- Creating Functions with Arguments
- Creating a Trigger to Track Average Purchases
Using SQL to Uncover the Truth: A Case Study
- Using SQL Techniques to Collect Preliminary Data
- Analyzing the Difference in the Sales Price Hypothesis
- Analyzing the Performance of the Email Marketing Campaign
