SQL FOR BUSINESS

Syllabus

1. 07 JULY WEDNESDAY 5 PM PST / 8 PM EST

   Introduction to SQL

   SQL is one of the most important languages in today's data-driven world. You'll understand why that is and learn the basics of how SQL works: what different types of databases are out there, the database management systems that are used, and how the SQL language can retrieve specific information from these systems.
   • What is SQL and what it can be used for (and by whom)?
   • What are databases?: Relational vs. non-relational
   • Getting to know Database Management Systems (DMBS)

   Assignment 01

   Send Adam a short self introduction, relating your existing knowledge of data and/or programming, if any, and what you hope to gain from the course.

2. 12 JULY MONDAY 5 PM PST / 8 PM EST

   Basic Querying

   Today, you'll learn the basic structure of a database and its tables. By the end of the class, you'll understand the components of a SQL query and how to construct queries to pull up specific subsets and sections from a database table.
   • Database structures: Schemas, tables
   • SELECT FROM functions
   • WHERE clauses (=, <>, >, <, IS/IS NOT NULL)

   Assignment 02

   Connect to the course database and begin constructing your own queries for specific data requests. Query individual tables, subsetting by column and row using SQL syntax.

3. 14 JULY WEDNESDAY 5 PM PST / 8 PM EST

   Joins

   Logic and syntax of join statements is the focus of today's class. Learn how to use a variety of join functions across multiple tables and how to avoid common issues with joining, such as duplicate creation.
   • Inner/outer join
   • Multiple joins, aliases
   • Understanding joins and avoiding duplicates

   Assignment 03

   Write queries that join data across multiple tables. Practice with the different types of joins, adding conditional statements to joins and performing joins without creating unwanted duplicates.

4. 19 JULY MONDAY 5 PM PST / 8 PM EST

   Querying for Answers

   This class builds on the last to begin constructing queries that return answers to specific questions. You'll learn how to calculate aggregation and to order operations across one or multiple tables.
   • Aggregations (GROUP BY): SUM, COUNT, MIN, MAX, AVG
   • ORDER BY
   • Subselects

   Assignment 04

   Construct queries using aggregation functions to return specific numeric answers. Practice with ranking results and creating nested queries to answer more complex data requests.

5. 21 JULY WEDNESDAY 5 PM PST / 8 PM EST

   Workshop: Database Creation and Solving Common Data Questions

   Today, you'll apply the techniques you've learned so far to real-life use cases. You'll be presented with several common data questions in the business arena and practice developing a SQL solution for each of them.
   Assignment 05

   Continue practicing the techniques covered today to solve for a variety of practical business use cases.

6. 26 JULY MONDAY 5 PM PST / 8 PM EST

   Modifying and Manipulating Data

   Now that you've nailed querying, it's time for data modification and manipulation. You'll learn how to do that by renaming variables, changing data types, and creating new variables.
   • Renaming variables with AS
   • Changing data types with CAST
   • Creating new variables with CASE WHEN

   Assignment 06

   Continue writing queries on your own which modify existing data by renaming variables and changing data types. Perform within-query calculations to create new variables.

7. 28 JULY WEDNESDAY 5 PM PST / 8 PM EST

   Time Functions and Tables

   Date and time data poses specific challenges for databases. In this class, you'll learn how to write queries using date / time functions and be able to perform specific date time operations.
   • Data types for which date / time functions are applicable
   • DATE, DATETIME, TIMESTAMP, YEAR queries
   • Time data operations: DATE_TRUNC, DATE_DIFF

   Assignment 07

   Practice working with date time data and the complications involved, such as data types, time zone adjustments, and operations with date time variables.

8. 02 AUGUST MONDAY 5 PM PST / 8 PM EST

   Time Series Data

   Today, we're learning time series analysis techniques for working with date time data. This class will teach you how to calculate answers to common business questions, such as aggregations by time period, cohort analysis, and growth analysis.
   • Aggregations by time period
   • Cohort analysis
   • Running total, calculating growth

   Assignment 08

   Build queries to solve common business use cases involving date time data. Practice performing time period operations and aggregations, cohort analysis, and growth analysis.

9. 04 AUGUST WEDNESDAY 5 PM PST / 8 PM EST

   Advanced SQL

   SQL is a fairly simple language, but there are some more involved SQL techniques, which we will go over today. Students will be able to employ methods for reusing created data, joining data on itself, and mitigating duplicates.
   • WITH statements
   • Self join, join on inequalities
   • Managing duplicates: SELECT DISTINCT, ROW_NUMBER

   Assignment 09

   Answer more complicated assigned data problems, specifically focusing on using WITH statements to reference the same created data multiple times, joining data on itself, and managing duplicates with SELECT DISTINCT and ROW_NUMBER functions.

10. 09 AUGUST MONDAY 5 PM PST / 8 PM EST

    Workshop: Complex Business Use Cases

    So far, we've learned how to write queries, structure and manipulate data, and solve for common business questions using SQL. In this workshop, you'll bring together the techniques you have learned in the course so far to solve more complex business use cases encountered by data-based organizations.
    Assignment 10

    Solve a number of specific data-centered business use cases, which will require the techniques they have learned in the previous lessons.

11. 11 AUGUST WEDNESDAY 5 PM PST / 8 PM EST

    Working with Database Administrators (DBAs)

    Database Administrators (DBAs) oversee access control and database development, and knowing how to communicate and work with them is no less essential than knowing how to work with databases themselves. In this class, you'll learn how to construct specific asks to DBAs for data tasks that are typically only performed by DBAs.
    • DBA concerns: Permissions (SELECT, WRITE), PII, database performance, refresh rates, data security
    • Table structures: Unique keys, foreign keys, enums
    • CREATE TABLE, CREATE VIEW, COPY, TRUNCATE, DROP

    Assignment 11

    Construct queries using aggregation functions to return specific numeric answers. Practice with ranking results and creating nested queries to answer more complex data requests.

12. 16 AUGUST MONDAY 5 PM PST / 8 PM EST

    SQL and Beyond

    SQL is just one piece of a fast-moving industry that has already become indispensable to nearly every sphere of the modern world. Thus, we'll wrap up the course with a discussion of the philosophies of data storage and usage underpinning database construction. You'll learn about wider trends in data and where we can expect the industry to go in the near future. We'll conclude with a discussion of the skills beyond SQL you might also find useful.
    • Database philosophies: Fact/dimension tables, event data
    • Views vs. tables
    • Beyond SQL: Non-relational DBS, data lakes, what’s next for data storage

    Assignment 12

    Reflect on the course, what you have learned, and what you would like to continue learning. How will you employ the skills you have learned in your current or desired role?