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

Introduction To Python

• Installation and Working with Python

• Understanding Python variables

• Python basic Operators

• Understanding the Python blocks.

Python Keyword and Identiers

• Python Comments, Multiline Comments.

• Python Indentation

• Understating the concepts of Operators
• Arithmetic
• Relational
• Logical
• Assignment
• Membership
• Identity

Data Analysis and Visualization using Pandas.

• Statistics
• Categorical Data
• Numerical Data
• Mean
• Median
• Mode
• Outliers
• Range
• Interquartile range
• Correlation
• Standard Deviation
• Variance
• Box plot

• Pandas
• Read data from Excel File using Pandas More Plotting, Date Time Indexing and writing to les
• How to get record specic records Using Pandas Adding & Resetting Columns, Mapping with function
• Using the Excel File class to read multiple sheets More Mapping, Filling Nonvalue’s
• Exploring the Data Plotting, Correlations, and Histograms
• Getting statistical information about the data Analysis Concepts, Handle the None Values
• Reading les with no header and skipping records Cumulative Sums and Value Counts, Ranking etc
• Reading a subset of columns Data Maintenance, Adding/Removing Cols and Rows
• Applying formulas on the columns Basic Grouping, Concepts of Aggre gate Function
• Complete Understanding of Pivot Table Data Slicing using iLoc and Loc property (Setting Indices)
• Under sting the Properties of Pivot Table in Pandas Advanced Reading CSVs/HTML, Binning, Categorical Data
• Exporting the results to Excel Joins
• Python | Pandas Data Frame Inner Join
• Under sting the properties of Data Frame Left Join (Left Outer Join)
• Indexing and Selecting Data with Pandas Right Join (Right Outer Join)
• Pandas | Merging, Joining and Concatenating Full Join (Full Outer Join)
• Pandas | Find Missing Data and Fill and Drop NA Appending Data Frame and Data
• Pandas | How to Group Data How to apply Lambda / Function on Data Frame
• Other Very Useful concepts of Pandas in Python Data Time Property in Pandas (More and More)