Intermediate SQL for Data Analytics Course Overview

Introduction

This is an intermediate SQL course aimed at those familiar with SQL basics and looking to advance their skills. The course consists of 10-minute lessons focusing on advanced SQL concepts, practical exercises, and real-world projects to build a portfolio.

Course Modules and Content

Module 1: Setting Up the Environment

• Introduction to Databases: Installing PostgreSQL, setting up databases with Google Collab, using PG Admin and DBaver.
• Course Structure: Divided into two halves focusing on practicing SQL queries and later building a portfolio project.

Module 2: Advanced SQL Concepts

• Data Manipulation and Aggregation: Working with case statements, pivoting data, aggregation functions, statistical functions, and using keywords for data filtration.
• Date and Time Functions: Utilizing functions for time series analysis, current date evaluations, and calculating intervals.
• Window Functions: Learning syntax, aggregation, ranking, lag/lead functions, and frame clauses for complex data manipulation.

Module 3: Data Cleaning

• Conditional Expressions: Handling null values using COALESCE and NULLIF.
• String Functions: Formatting text, combining fields, and cleaning data.

Module 4: Query Optimization

• Optimization Techniques: Basic to advanced query optimization practices; minimizing computation and improving execution performance.
• Explaining Execution Plans: Use of EXPLAIN and EXPLAIN ANALYZE to understand query execution.

Module 5: Building a Data Analytics Project

• Project Introduction: Creating views, SQL scripts, and documenting analysis.
• Project Questions and Analysis: Cohort analysis, customer segmentation, retention analysis.
• Using VS Code and GitHub: Setting up SQL projects, version control, and sharing on GitHub.

Final Project and Portfolio

• Building a Portfolio Project: Application of SQL skills in a real-world analytics project, documented and shared on GitHub.
• Sharing Results: Communicating findings and strategies, sharing on LinkedIn and other professional networks.

Tools and Skills

• Tools: PostgreSQL, PG Admin, DBaver, Google Collab, VS Code, Git, and GitHub.
• Skills Developed: Advanced SQL queries, data cleaning, optimization, project management, and professional documentation.

Conclusion

This comprehensive course equips learners with the skills needed to perform advanced data analytics using SQL, complete with a professional portfolio project for showcasing abilities to potential employers.