SQL for Data Analysts: Advanced (Two Day Hands-On)

Course Description

This hands-on course will help data analysts and report/dashboard developers to understand complex SQL concepts. Attendees will learn how to use complex calculations and functions, and how to use subqueries.

Intended Audience:

Data analysts and report/dashboard developers who want to expand their SQL skills.

Prerequisites:

At least one year of experience with SQL or completion of the SQL for Data Analysts Beginner/Intermediate course.

Major Topics:

SQL queries

Complex calculations

Aggregations

Subqueries

July 20th, Day 1:

  • Brief review of SQL queries
    • SQL concepts and terminology
    • SQL query clauses and structures
  • Complex calculations in SQL
    • Using SQL functions
    • Numeric functions and formulas
    • Date functions and formulas
    • String (text) functions and formulas
    • CASE statements and conditional logic
    • Handling null values
    • Changing data types with CAST and CONVERT
  • Aggregate calculations in SQL
    • Average and standard deviation functions
    • Calculating a median
    • Performing conditional aggregation
    • Using percentiles
  • Using subqueries
    • How, when, and where to use subqueries
    • Using the EXISTS and IN statements with subqueries
    • Nested subqueries

July 21st, Day 2:

  • Complex functions
    • Using the WITH statement
    • Window functions
    • Ranking data
    • Point in time functions
    • Using the APPLY function
  • Deploying SQL queries in your tool
    • Web Intelligence
    • Crystal Reports
    • InfoBurst
    • Tableau
    • Power BI
  • Other database systems
    • Compare/contrast RDBMS’s
    • Syntactical differences