SQL for Data Analytics, Third Edition

(SQL-DA.AJ2)/ISBN:978-1-64459-485-8

This course includes
Lessons
TestPrep
Hand-on Lab

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.

Lessons

10+ Lessons | 150+ Exercises | 80+ Quizzes | 34+ Flashcards | 34+ Glossary of terms

TestPrep

67+ Pre Assessment Questions | 67+ Post Assessment Questions |

Hand on lab

17+ LiveLab | 00+ Minutes

Here's what you will learn

Download Course Outline

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