Microsoft Auto Pilot Training Program

SQL Server Fundamentals

• SQL Server 2019 Installation

• Service Accounts & Use, Authentication Modes & Usage, Instance Congurations

• SQL Server Features & Purpose

• Using Management Studio (SSMS)

• Conguration Tools & SQLCMD

• Conventions & Collation

SQL Server 2019 Database Design

• SQL Database Architecture

• Database Creation using GUI

• Database Creation using T-SQL scripts

• DB Design using Files and File Groups

• File locations and Size parameters

• Database Structure modications

SQL Tables in MS SQL Server

• SQL Server Database Tables

• Table creation using T-SQL Scripts

• Naming Conventions for Columns

• Single Row and Multi-Row Inserts

• Table Aliases

• Column Aliases & Usage

• Table creation using Schemas

• Basic INSERT

• UPDATE

• DELETE

• SELECT queries and Schemas

• Use of WHERE, IN and BETWEEN

• Variants of SELECT statement

• ORDER BY

• GROUPING

• HAVING

• ROWCOUNT and CUBE Functions

Data Validation and Constraints

• Table creation using Constraints

• NULL and IDENTITY properties

• UNIQUE KEY Constraint and NOT NULL

• PRIMARY KEY Constraint & Usage

• CHECK and DEFAULT Constraints

• Naming Composite Primary Keys

• Disabling Constraints & Other Options

Views and Row Data Security

• Benets of Views in SQL Database

• Views on Tables and Views

• SCHEMA BINDING and ENCRYPTION

• Issues with Views and ALTER TABLE

• Common System Views and Metadata

• Common Dynamic Management views

• Working with JOINS inside views

Indexes and Query tuning

• Need for Indexes & Usage

• Indexing Table & View Columns

• Index SCAN and SEEK

• INCLUDED Indexes & Usage

• Materializing Views (storage level)

• Composite Indexed Columns & Keys

• Indexes and Table Constraints

• Primary Keys & Non-Clustered Indexes

Stored Procedures and Benets

• Why to use Stored Procedures

• Types of Stored Procedures

• Use of Variables and parameters

• SCHEMABINDING and ENCRYPTION

• INPUT and OUTPUT parameters

• System level Stored Procedures

• Dynamic SQL and parameterization

System functions and Usage

• Scalar Valued Functions

• Types of Table Valued Functions

• SCHEMABINDING and ENCRYPTION

• System Functions and usage

• Date Functions

• Time Functions

• String and Operational Functions

• ROW_COUNT

• GROUPING Functions

Triggers, cursors, memory limitations

• Why to use Triggers

• DML Triggers and Performance impact

• INSERTED and DELETED memory tables

• Data Audit operations & Sampling

• Database Triggers and Server Triggers

• Bulk Operations with Triggers

Cursors and Memory Limitations

• Cursor declaration and Life cycle

• STATIC

• DYNAMIC

• SCROLL Cursors

• FORWARD_ONLY and LOCAL Cursors

• KEYSET Cursors with Complex SPs

Transactions Management

• ACID Properties and Scope

• EXPLICIT Transaction types

• IMPLICIT Transactions and options

• AUTOCOMMIT Transaction and usage

Introduction to Power BI

• Overview of BI concepts

• Why we need BI

• Introduction to SSBI

• SSBI Tools

• Why Power BI

• What is Power BI

• Building Blocks of Power BI

• Getting started with Power BI Desktop

• Get Power BI Tools

• Introduction to Tools and Terminology

• Dashboard in Minutes

• Interacting with your Dashboards

• Sharing Dashboards and Reports

Power BI Desktop

• Power BI Desktop

• Extracting data from various sources

• Workspaces in Power BI

Power BI Data Transformation

• Data Transformation

• Query Editor

• Connecting Power BI Desktop to our Data Sources

• Editing Rows

• Understanding Append Queries

• Editing Columns

• Replacing Values

• Formatting Data

• Pivoting and Unpivoting Columns

• Splitting Columns

• Creating a New Group for our Queries

• Introducing the Star Schema

• Duplicating and Referencing Queries

• Creating the Dimension Tables

• Entering Data Manually

• Merging Queries

• Finishing the Dimension Table

• Introducing the another DimensionTable

• Creating an Index Column

• Duplicating Columns and Extracting Information

• Creating Conditional Columns

• Creating the FACT Table

• Performing Basic Mathematical Operations

• Improving Performance and Loading Data into the Data Model

Modelling with Power BI

• Introduction to Modelling

• Modelling Data

• Manage Data Relationship

• Optimize Data Models

• Cardinality and Cross Filtering

• Default Summarization & Sort by

• Creating Calculated Columns

• Creating Measures & Quick Measures

Data Analysis Expressions (DAX)

• What is DAX

• Data Types in DAX

• Calculation Types

• Syntax, Functions, Context Options

• DAX Functions
• Date and Time
• Time Intelligence
• Information
• Logical
• Mathematical
• Statistical
• Text and Aggregate

• Measures in DAX

• Measures and Calculated Columns

• ROW Context and Filter Context in DAX

• Operators in DAX - Real-time Usage

• Quick Measures in DAX - Auto validations

• In-Memory Processing DAX Performance

Capstone Project