55232 Writing Analytical Queries for Business Intelligence

sql_server_logo

55232 Writing Analytical Queries for Business Intelligence

The 3-day instructor-led course is about writing TSQL queries for the purpose of database reporting, analysis, and business intelligence.

Share on facebook
Share on twitter
Share on linkedin

Overview

The 3-day instructor-led course is about writing TSQL queries for the purpose of database reporting, analysis, and business intelligence. Specifically, this course presents TSQL within the context of data analysis – in other words, making meaning from the data rather than transaction-oriented data-tier application development.

The course starts with a brief discussion of levels of measurement and quantitative research methodology, and integrates these concepts into each TSQL topic presented. The goal is to provide a consistent, direct, and purposeful learning path for RDBMS data retrieval for use in analytical tools such as SQL Server Reporting Services, Power BI, R and Excel.

Prerequisites

Those who wish to take up this course should meet the following prerequisites:

  • 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 Windows operating system and its core functionality, including file system navigation.
  • Basic understanding of the purpose of relational database management systems such as SQL Server.

Who Should Attend?

The course is highly recommended for information workers and data science professionals who seek to use database reporting and analysis tools such as Microsoft SQL Server Reporting Services, Excel, Power BI, R, SAS and other business intelligence tools, and wish to use TSQL queries to efficiently retrieve data sets from Microsoft SQL Server relational databases for use with these tools.

Course Outline

  • Two Approaches to SQL Programming
  • TSQL Data Retrieval in an Analytics Environment
  • The Database Engine
  • SQL Server Management Studio and the CarDeal Sample Database
  • Identifying Variables in Tables
  • SQL is a Declarative Language
  • Introduction to SELECT Query

Lab: Lab 1

  • Turning Columns into Variables for Analysis
  • Column Expressions, Data Types, and Built-in Functions
  • Column aliases
  • Data type conversions
  • Built-in Scalar Functions
  • Table Aliases
  • The WHERE clause
  • ORDER BY
  • Lab: Lab 2 – Write queries using:
    • Column and table aliases
    • DISTINCT
    • WHERE
    • ORDER BY
    • Built-in functions
    • Explicit and implicit data type conversion
  • Primary Keys, Foreign Keys, and Joins
  • Understanding Joins, Part 1: CROSS JOIN and the Full Cartersian Product
  • Understanding Joins, Part 2: The INNER JOIN
  • Understanding Joins, Part 3: The OUTER JOINS
  • Understanding Joins, Part 4: Joining more than two tables
  • Understanding Joins, Part 5: Combining INNER and OUTER JOINs
  • Combining JOIN Operations with WHERE and ORDER BY
  • Lab: Lab 3 – Write SELECT queries using:
    • Inner Join
    • Left, right, and full join
    • Joins of more than two tables
    • Join operators, in addition to WHERE and ORDER BY
  • Identifying required aggregation level and granularity
  • Aggregate functions
  • GROUP BY
  • HAVING
  • Order of operations in SELECT queries
  • Lab: Lab 4 – Write queries using:
    • Aggregate functions
    • Aggregate function with HAVING
    • Aggregate function with GROUP BY and HAVING
    • Aggregate function with GROUP BY, HAVING, WHERE, and ORDER BY
  • Non-correlated and correlated subqueries
  • Derived tables
  • Common table expressions
  • Lab: Lab 5 – Write queries using:
    • Non-correlated subqueries
    • Correlated subqueries
    • Derived tables
    • Common table expressions
    • Subqueries, derived tables, common table expressions and other topics
  • Views
  • Table-valued functions
  • Stored procedures
  • Creating objects for read-access users
  • Creating database accounts for analytical client tools

Lab: Lab 6

  • Connecting to SQL Server and Submitting Queries from Client Tools
  • Connecting and running SELECT queries from:
    • Excel
    • PowerBI
    • RStudio
    • Exporting datasets to files using:
      • Results pane from SSMS
      • The bcp utility
      • The Import/Export Wizard

Lab: Lab 7

Get Brochure and Pricing

More Like This

Get the course Brochure & Pricing

Our course consultant will contact you within 1 working day

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Stay-Home-Series-main-header-v2

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Get in touch with our consultant