Home About Courses Schedule Services Webinars Contact Search

Data Analytics using SQL

SEE SCHEDULE

Duration: 2.0 days

Method: Instructor-led with hands-on exercises.

Price: $1300.00

Course Code: DA1010


Audience

Programmers, Analysts, Database Administrators, Data Scientists, IT Managers and anyone that performs analytics on data in a relational database using SQL. This is specific to data stored in a relational database such as Db2, Oracle, SQL Server or PostgreSQL.

Description

This class covers the functions and facilities available in SQL to analyze data stored in relational database. The basic tools of statistical analysis are presented, such as median, mode, variance, standard deviation, normal distribution, covariance, and coefficient of correlation. The related SQL functions are then reviewed along with how to interpret the results. This will be followed by a discussion of the SQL OLAP functions. The related concepts of partitioning, framing, and ranges are introduced and their use to focus on the specific data to be analyzed is explained. Additional techniques for grouping and aggregating data for analysis are then presented, including advanced GROUP BY clause syntax, and how to create pivot tables. Within each topic students will learn how to write the SQL query and then how to analyze the outputs. Each topic is reinforced with hands-on workshops.

Prerequisites

A basic understanding of Structured Query Language (SQL).

Topics

  • I. Aggregate and Statistical Functions
    • Aggregate Functions
    • Statistics Terminology
    • Calculating the Mean / Median / Mode
    • Variance vs. Standard Deviation
    • Variance and Standard Deviation Functions
    • Normal Distribution
    • Covariance Functions
    • Coefficient of Correlation Functions
  • II. OLAP Functions
    • What are OLAP Functions?
    • Families of OLAP Functions
    • Ranking Functions
    • Partitioning
    • OLAP Aggregate Functions
    • Window and Framing
    • Row-Based Framing
    • Range-Based Framing
    • Lag / Lead Functions
    • First / Last Functions
    • Inverse Distribution Functions
    • Hypothetical Set Functions
  • III. Advanced Grouping Facilities
    • GROUP BY ROLLUP
    • GROUP BY CUBE
    • GROUP BY GROUPING SETS
  • IV. Pivoting and Unpivoting Data
    • What are Pivot Tables?
    • Creating Pivot Tables
    • Unpivoting Data