SQL for Data Analysis at Work

Complete syllabus

Every chapter and lesson

1. 01

   Chapter 1 · 3 lessons

   Your First Real Queries

   1. 1.1

      How relational data is laid out

      8 min · Quick check

      Lesson goal: By the end you can explain how relational data is structured in tables.

      • Understand the concept of tables in relational databases.
      • Identify rows and columns as fundamental components of tables.
      • Explain primary keys and foreign keys and their roles in data relationships.
   2. 1.2

      SELECT, FROM & column aliases

      9 min · Quick check

      Lesson goal: By the end you can construct basic SQL queries using SELECT and FROM statements with column aliases.

      • Learn the purpose of the SELECT statement in SQL.
      • Understand the role of the FROM clause in specifying data sources.
      • Define and use column aliases to improve query readability.
   3. 1.3

      Computed columns & expressions

      9 min · Quick check

      Lesson goal: By the end you can create computed columns and use expressions in SQL queries.

      • Define computed columns and their purpose in SQL queries.
      • Learn how to use mathematical and string expressions in SELECT statements.
      • Understand the importance of data types when creating computed columns.
2. 02

   Chapter 2 · 3 lessons

   Filtering & Sorting

   1. 2.1

      WHERE, AND/OR & comparison

      9 min · Quick check

      Lesson goal: By the end you can effectively use the WHERE clause along with AND/OR operators and comparison operators to filter data in SQL queries.

      • Define the WHERE clause and its purpose in SQL.
      • Explain the use of AND and OR operators for combining multiple conditions.
      • Describe comparison operators such as =, <, >, <=, and >=.
      • Demonstrate how to filter records based on specific criteria using the WHERE clause.
   2. 2.2

      IN, BETWEEN, LIKE & NULLs

      9 min · Quick check

      Lesson goal: By the end you can utilize the IN, BETWEEN, LIKE operators and handle NULL values to refine your SQL queries.

      • Define the IN operator and its use for matching multiple values.
      • Explain the BETWEEN operator for filtering ranges of values.
      • Describe the LIKE operator for pattern matching in string data.
      • Discuss how to handle NULL values in SQL and their implications for data filtering.
   3. 2.3

      ORDER BY & LIMIT

      8 min · Quick check

      Lesson goal: By the end you can apply the ORDER BY clause and the LIMIT clause to sort and restrict the results of your SQL queries.

      • Define the ORDER BY clause and its role in sorting query results.
      • Explain how to sort data in ascending and descending order.
      • Describe the LIMIT clause and its use for restricting the number of returned records.
      • Demonstrate combining ORDER BY and LIMIT to control the output of SQL queries.
3. 03

   Chapter 3 · 3 lessons

   Aggregation & Grouping

   1. 3.1

      COUNT, SUM, AVG & friends

      9 min · Quick check

      Lesson goal: By the end you can understand and apply aggregate functions such as COUNT, SUM, and AVG in SQL queries.

      • Define aggregate functions and their purpose in SQL.
      • Explain how COUNT, SUM, and AVG functions work.
      • Demonstrate the syntax for using these functions in SQL queries.
      • Discuss the importance of aggregate functions in data analysis.
   2. 3.2

      GROUP BY for breakdowns

      10 min · Quick check

      Lesson goal: By the end you can effectively use the GROUP BY clause to organize data into meaningful categories.

      • Define the GROUP BY clause and its role in SQL.
      • Explain how to group data based on one or more columns.
      • Demonstrate the use of GROUP BY with aggregate functions.
      • Discuss the significance of grouping data for analysis.
   3. 3.3

      HAVING & filtering groups

      8 min · Quick check

      Lesson goal: By the end you can apply the HAVING clause to filter groups based on aggregate values.

      • Define the HAVING clause and its purpose in SQL.
      • Explain the difference between WHERE and HAVING clauses.
      • Demonstrate how to use HAVING to filter grouped data.
      • Discuss scenarios where filtering groups is necessary for analysis.
4. 04

   Chapter 4 · 3 lessons

   Joining Across Tables

   1. 4.1

      Keys & how joins work

      9 min · Quick check

      Lesson goal: By the end you can explain the concept of keys and how joins function in SQL.

      • Define primary keys and foreign keys.
      • Explain the purpose of joins in relational databases.
      • Describe how keys facilitate the joining of tables.
   2. 4.2

      INNER vs. LEFT joins

      10 min · Quick check

      Lesson goal: By the end you can differentiate between INNER and LEFT joins in SQL.

      • Define INNER join and LEFT join.
      • Explain the differences in results produced by INNER and LEFT joins.
      • Provide examples of when to use INNER join versus LEFT join.
   3. 4.3

      Joining several tables

      9 min · Quick check

      Lesson goal: By the end you can demonstrate how to join multiple tables in SQL.

      • Explain the concept of joining more than two tables.
      • Describe the syntax for joining multiple tables.
      • Provide an example of a query that joins three or more tables.
5. 05

   Chapter 5 · 3 lessons

   Analyst-Grade Queries

   1. 5.1

      Subqueries & CTEs

      10 min · Quick check

      Lesson goal: By the end you can explain and utilize Subqueries and Common Table Expressions (CTEs) in SQL for data analysis.

      • Define Subqueries and Common Table Expressions (CTEs).
      • Explain the differences between Subqueries and CTEs.
      • Demonstrate how to write and execute Subqueries.
      • Demonstrate how to write and execute CTEs.
      • Discuss the advantages of using CTEs over Subqueries.
   2. 5.2

      Window functions

      11 min · Quick check

      Lesson goal: By the end you can apply Window functions to perform advanced data analysis in SQL.

      • Define Window functions and their purpose in SQL.
      • Explain the syntax and components of Window functions.
      • Demonstrate how to use Window functions for ranking and aggregation.
      • Discuss the differences between Window functions and regular aggregate functions.
      • Provide examples of practical applications of Window functions in data analysis.
   3. 5.3

      A real analysis, end to end

      10 min · Quick check

      Lesson goal: By the end you can conduct a complete data analysis project using SQL, from data extraction to reporting.

      • Define the steps involved in a complete data analysis project.
      • Demonstrate how to extract data from a database using SQL queries.
      • Explain how to clean and prepare data for analysis.
      • Show how to apply various SQL techniques learned in previous lessons.
      • Present the findings of the analysis in a clear and concise manner.