SQL for Data Analytics

SQL for Data Analytics (55232)

Course Length: 3 days
Delivery Methods: Available as private class only
Course Overview

This course is designed as an introductory course for data scientists and data analysts who need to master Microsoft’s data analytics tools and platform.  At the end of the course, you’ll be prepared to efficiently and competently implement TSQL for data retrieval and analysis, and be well-prepared to:

  1. Take on a professional project role responsible for retrieving relational database data and preparing it for analysis and loading into an analytical data store, such as a Power BI or Power Pivot data model;
  2. Take additional coursework for which knowledge of TSQL is required or desirable, such as courses for Power BI and Microsoft Power Platform, Azure Synapse Analytics, Data Engineering, Python, R, etc.

With a focus on those parts of the TSQL language most useful for data retrieval and analysis, students learn the database fundamentals necessary to both efficiently create TSQL queries and create a data warehouse implemented as a star schema. By the end of the course, students are able to analyze a real-word data retrieval problem, and write queries to address it using advanced features within the SELECT statement.

Our goal is to help you efficiently achieve your learning and skill goals. We hope you’ll find this Cuban Sandwich Press course to be the best technical training course you’ve ever taken!

This is an updated version of Microsoft Community Course 55232 Writing Analytical Queries for Business Intelligence.
Course Benefits
  1. Presents database queries within an analytical/reporting application framework
  2. Covers database concepts critical for data analysts
  3. Implements popular query development tools such as SQL Server Management Studio
  4. Provides a deep dive into the SELECT query, including (but not limited to) joins, subqueries, derived tables, common table expressions, and views
  5. Students implement a data model as a star schema using views within a SQL Server database
  6. Students create a data model in Power BI using their star schema
Course Outline
  1. Introduction to T-SQL for Business Intelligence
    1. Two approaches to SQL programming
    2. T-SQL data retrieval in an analytics / business intelligence environment
    3. The database engine
    4. SQL Server Management Studio and the CarDeal sample database
    5. Identifying variables in tables
    6. SQL is a declarative language
    7. Introduction to the SELECT query
  2. Turning Table Columns into Variables for Analysis: SELECT List Expressions, WHERE, and ORDER BY
    1. Turning columns into variables for analysis
    2. Column expressions, data types, and built-in functions
    3. Column aliases
    4. Data type conversions
    5. Built-in scalar functions
    6. Table aliases
    7. The WHERE clause
    8. ORDER BY
  3. Combining Columns from Multiple Tables into a Single Dataset: The JOIN Operators
    1. Primary keys, foreign keys, and joins
    2. Understanding joins, part 1: CROSS JOIN and the full Cartesian product
    3. Understanding joins, part 2: The INNER JOIN
    4. Understanding joins, part 3: The OUTER JOINS
    5. Understanding joins, part 4: Joining more than two tables
    6. Understanding joins, part 5: Combining INNER and OUTER JOINs
    7. Combining JOIN operations with WHERE and ORDER BY
  4. Creating an Appropriate Aggregation Level Using GROUP BY
    1. Identifying required aggregation level and granularity
    2. Aggregate functions
    3. GROUP BY
    4. HAVING
    5. Order of operations in SELECT queries
  5. Subqueries, Derived Tables, and Common Table Expressions
    1. Non-correlated and correlated subqueries
    2. Derived tables
    3. Common table expressions
  6. Encapsulating Data Retrieval Logic
    1. Views
    2. Table-valued functions
    3. Stored procedures
    4. Creating objects for read-access users
    5. Creating database accounts for analytical client tools
  7. Getting Your Dataset to the Client
    1. Connecting to SQL Server and submitting queries from client tools
    2. Connecting and running SELECT queries from:
      1. Excel
      2. PowerBI
      3. RStudio
    3. Exporting datasets to files using:
      1. Results pane from SSMS
      2. The bcp utility
      3. The Import/Export Wizard
Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this Microsoft SQL Server class:

  • Context knowledge of data analysis and business intelligence scenarios. For example, an understanding of a work-related business intelligence project or need.
  • Basic knowledge of the Windows operating system and its core functionality, including file system navigation.
Live Private Class
  • Private Class for your Team
  • Live training
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Proposal