Learn SQL for Analytics by Vlad

Introduction

• Goal: Enable users to analyze and manipulate data using SQL, specifically for solving hard SQL problems often encountered in data interviews.
• Tools: Google BigQuery (all work is done through a browser).
• No prior experience required, although familiarity with spreadsheets like Excel or Google Sheets can be helpful.
• The course consists of two parts: Theory and Practice.

Course Structure

Theory

• Short explainers about fundamental SQL concepts using BigQuery.
• Google BigQuery: A service allowing users to upload data and run SQL queries. The course includes instructions on using BigQuery for free.
• SQL Concepts: Topics include data organization, SQL statements, and the order of SQL operations, which is a crucial beginner concept.
• Basic SQL Components: SELECT, FROM, column transformation, WHERE, DISTINCT, UNION, ORDER BY, LIMIT, and simple aggregations.
• Complex SQL Concepts:
  • Subqueries and Common Table Expressions (CTEs).
  • Joins: Understanding how to connect SQL tables.
  • Aggregations: Group by, having, window functions, etc.

Practice

• SQL Exercises: Recommended to use the "PostgreSQL Exercises" website (free and open-source).
• Guided Solutions: The course includes 42 filmed explainer exercises where Vlad solves and explains the most important SQL problems.

Suggested Learning Paths

1. For Beginners: Watch theory lectures first, then attempt exercises on your own. If stuck, refer to the video solutions.
2. Aggressive Learning: Dive directly into exercises and use video solutions whenever stuck to fill gaps.

Course Syllabus Overview

1. Getting Started: Introduction to SQL and setting up BigQuery.
2. Writing Your First Query: Understanding BigQuery and running the first SQL query.
3. Essential SQL Concepts: Data organization, SQL statements, and order of operations.
4. Basic Components of SQL: SELECT, FROM, transformations, WHERE, DISTINCT, UNION, ORDER BY, LIMIT, simple aggregations.
5. Complex Queries: Subqueries, CTEs, joins, advanced aggregations, window functions.

Learning Resources

• Main webpage for the course (provided in video description) which will be updated with links and resources over time.
• Files with example data, link to spreadsheets, exercises, and course-related drawings.
• Stack Overflow Developer Survey: Shows SQL as a highly in-demand skill.

Behind the Scenes (BigQuery)

• Interface: Explanation of how the BigQuery interface works, opening tables, schema, table details, and running queries.
• Creating and Managing Projects: Instructions on setting up a free Google Cloud BigQuery account.
• Operations in SQL: Detailed walkthrough of SELECT, FROM, WHERE, CASE, aggregations, and window functions using practical examples.

Practical SQL Techniques

Query Examples

• SELECT * FROM table: Retrieve all columns from the specified table.
• SELECT column1, column2 FROM table: Retrieve specific columns.
• WHERE Clause: Filter rows based on conditions.
• CASE Statement: Conditional logic within queries.
• Aggregation Functions: Summarize data using functions like SUM, COUNT, AVG, MIN, MAX.
• Joining Tables: Combine data from multiple tables using different types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).
• Window Functions: Perform complex calculations across sets of table rows related to the current row.*

Advanced Exercises and Examples

Handling More Complex Queries

• Subqueries within WHERE and SELECT clauses for dynamic filtering and data retrieval.
• CTEs: Define temporary result sets used within the main query to organize and simplify complex queries.
• Ordering and Limiting Results: Use ORDER BY and LIMIT clauses to sort and restrict the number of rows returned.
• Set Operations: Use of UNION, INTERSECT, and EXCEPT for combining results from multiple queries.

Real-Life Scenarios and Examples

• Business Questions: Example scenarios like calculating monthly revenues, summarizing data by different dimensions, and other typical data analysis tasks.
• Exercises: From basic retrievals to complex transformations and aggregations, aimed at reinforcing understanding and practical applications.

Conclusion

• Practice Makes Perfect: Encouragement to continuously practice through exercises and real-life data scenarios.
• Additional Resources: Links provided throughout the course for deeper dives into specific topics.
• End Goal: Equip learners with a strong SQL foundation to confidently tackle data challenges and make data-driven decisions.