What is Database?; Purpose of Database & Management; Advantages of Database for Data Storage; Introduction to DBMS; Database Design; Most Popular RDBMS Products; History, Real time Database Examples (LIVE); Microsoft SQL, Oracle & MySQL Differences; When to use which?

MySQL Installation Process (step by step); Common Installation Errors & Solutions; MySQL Workbench Installation Concepts & MySQL Command prompt; MySQL Versions and Editions Comparisons; Connect Workbench Developer Tool; Creating a new User; Grant permission; Lock and Unlock User Account;

Structured Query Language (SQL); Data Definition Language (DDL); Data Retrieval Language (DRL); Data Manipulation Language (DML); Transaction Control Language (TCL); Database Security (DCL); Rules of SQL Queries and Statements; Real-world applicative uses of SQL; Creating Users and Tables;

Table Data Inserts; MySQL Data Types; DDL Commands with Examples; CREATE, ALTER, TRUNCATE, DROP, RENAME; DESCRIBE command; DML, DRL Commands Operators; INSERT, UPDATE, DELETE Statements; Truncate & Delete commands; SELECT; SQL Comments; Single Line & Multi Line Comments;

SET, AND, OR, NOT, IN; BETWEEN (NOT BETWEEN); Arithmetic, Logical Operators; Operator Precedence; UNION, UNION ALL, INTERSECT, MINUS; LIKE (NOT LIKE), IS NULL (IS NOT NULL); DCL and TCL;

Identify Distinct Values in Tables; Group & Aggregate function; Group By Operations in Queries; Having Clause; Aggregate Functions with Group By; Order By; Query Execution Order with Group By; Arithmetic Functions, Character Function; Date & Time Functions, String Function; Conversion Functions;

Data Integrity constraints; Domain Integrity Constraints; Entity Integrity Constraints; Referential Integrity Constraints; Check Constraints; NOT NULL, UNIQUE Constraint; PRIMARY KEY and Usage;

FOREIGN KEY Constraints & Relations; Column & Table Constraints; Adding Constraints to Tables, User Constraints ; Enabling – Dropping Constraints, Self Referential Integrity; Disabling Constraints on Tables;

Need for Joins & Table Comparisons; SET Operations; Join Types : Equi Join, Simple Join; Inner Join and Query Conditions; Cross Join (Cartesian Join); Non-Equi Join & Self Join; Outer Joins – Types, Advantages; Cross Joins – Advantages * Limitations; Self Joins, Merge Joins, Sub Queries; Inner Self Joins & HAVING;

VIEWS IN MYSQL, Understanding Views & Use; Relational Views and Standard Views; SIMPLE VIEWS and COMPLEX VIEWS in MYSQL; Column Definitions in VIEWS; Using VIEWS for DML Operations; Forced Views, CHECK Constraints in Views

Sub Queries in Real-world; Dynamic Conditions with Sub Queries; Sub Queries and Nested Sub Queries; How does oracle Execute Nested Sub Queries?; Inner Select and Outer Select Queries; Usage of Sub Queries with WERE, HAVING; Impact of Having Clause in Sub Queries; Select Nth Highest salary; Select Duplicate Records; Delete Duplicate Records; Advantages of MySQL Sub Query

Subquery in the Select Clause; Subquery in the From Clause; Execution of Correlated Sub Queries in SQL; IN, ANY SOME, ALL Operators in Sub Queries; PAIR WISE and NON PAIR WISE in Sub Queries; Single Row Subquery & Multiple Row Subquery; Multiple Column Subquery; Uncorrelated Subquery;

Indexes – Definition & Architecture; B Tree Concept; Clustered and Non clustered Indexes; Primary Key and Clustered Index; Unique Key and Non-Clustered Indexes; Simple Index, Rebuild Index; Materialized Views – Indexed Views; Composite Index, Function Based Index; Range and Hash Partition; List Partition and Composite Partition; Parallel Query Process; Performance Tuning Advantages?;

Simple If, If. Else. Nested If. Else Statements; Ladder, Selection, Simple Case Statements; GOTO Label and EXIT Statements; Iterations, Simple LOOP, WHILE; FOR LOOP and NESTED LOOPS in MYSQL; MYSQL Select statements; Composite Data Types;

Cursor Variables and Management in MYSQL; Implicit & Explicit Cursors and Attributes; Cursor with Parameters and Nested LOOPs; Cursors with Sub Queries, Reference Cursors; Implicit Cursors, Explicit Cursor; Parameterized Cursors, Ref Cursors; MySQL Cursors Usage

Transaction Concepts in Databases with SQL; Transaction Types and Uses; Commit and Rollback Operations; Nested Transactions with Save points; Database Read Consistency with SQL; Creation of READ ONLY VIEWS – Realtime Use; Sequences, with Synonyms; View Vs Synonym in MySQL

Procedures in MYSQL: STORED PROCEDURE; Parameters (IN, OUT, IN OUT); POSITIONAL and NAMED Notation; Procedure with Cursors and Sub Queries; ALTER and DROP of Stored Procedures; Using SPs for Dynamic SQL Statements; Loops and Table Variables in SQL Programs

Functions in MYSQL: Real-time Usage; User Defined Functions, Nested Functions; Using Functions in SQL Statements; Comparing Stored Procedures and Functions; Using SPs with Table Value Functions; Analytical Function; Rank(), Dense_Rank(),Row_Number(); IFNULL() FUNCTION;

Pragma_Autonomous_Transaction() with SPs; Returning into clause, Bulk Collect; For All, Definer/Invoker Rights & Usage; About Flash Back Queries, Dynamic SQL; Flash Back Command, Purge Command; Regular Expressions in MYSQL; Recycle Bin; Delete Recycle Bin Table;

MySQL Triggers: Row Level & Statement Level Triggers; DML Triggers; DDL Triggers and Schema Level Triggers; OLD & NEW References, Trigger Auditing; Enabling / Disabling Triggers, Dropping Triggers; Triggers and Data Manipulations; Memory Tables in Triggers; DML & DDL Events with Triggers; Compound Triggers; Working with LARGE Tables;

Ch 1: Data Analytics Intro & Python

• Data and Databases : Introductions
• Data Analytics Job Role
• Python : Introduction & Advantages
• Python : Career Options, Scope
• Python for Big Data Analytics
• Why Python? Usage Options
• Python Installation : Multi OS
• Anaconda Software Installation
• Jupyter Interface for Python
• Python Activities with Jupyter
• Notebooks : Python Web Interface
• Notebooks and Cells: Introduction

Ch 2: Basic Operations with Python

• Python Interface : Creating Notebook
• Adding Cells, Saving Notebook
• Executing Basic Cells; Result Window
• Single Line & Multi Line Comments
• Save, Open / Clone Notebooks
• Indentation Options with Python
• Python : Internal Architecture
• Code Editor, Source Files
• Compiler, Byte Code, Virtual Machine
• Program Execution : Py, PYC and PVM
• Python Libraries / Modules
• Compiler Versus Interpreter

Ch 3: Data Types & Variables

• Integer / Int Data Types
• Float & String Data Types
• Boolean Data Types, Binary Types
• Sequence Types: List, Tuple
• Range, Complex & memoryview
• Retrieving Data Type: type()
• Python Variables: Naming
• Camel / Pascal / Snake Case
• Multi Assignments & Casting
• Multi Word Variables, PRINT
• Multiple Variables and Vales
• Unpack Collection, Outputs

Ch 4: Python Operators, Conditions

• Python Operators : Arithmetic
• Assignment, Compare Operators
• Logical, Identity Operators
• Member & Bitwise Operators
• Operator Precedence Options
• Python Operator Expressions
• Python If … Else Statement
• Short Hand If Statement, Use
• OR, AND and NOT Statements
• Pass Statement with Python
• ELIF and ELSE IF Statements
• Ternary Operators in Python

Ch 5: Python Loops, Iterations

• Python Loop & Realtime Use
• Python While Loop Statement
• Break and Continue Statement
• Using Print with While()
• Iterations & Conditions
• Exit Conditions : Cautions
• Python For Loop Statement
• Break, Continue & Range
• Python Iterators : Creation
• __iter__() and __next__()
• Iterator vs Iterable
• iter() and Looping Options

Ch 6: Python Collections

• Python Collections (Arrays)
• Python Collection Data Types
• List, Tuple, Set, Dictionary
• List Items, Ordered & Length
• list() Constructor, print()
• Python Tuples, Tuple Items
• tuple() Constructor, Usage
• Python Sets : Syntax Rules
• Duplicates, Types, Ordered
• Python Dictionaries: Usage
• Changeable, Ordered Data
• Dictionary Construct, type()

Ch 7: Python Functions

• Python Functions : Realtime Use
• Function : Creation, Execution Call
• Function Parameters, Arguments
• Arguments Number, Arg keyword
• Arbitrary Keyword & **kwargss
• Default & List Value Parameters
• Python Lambda Functions
• Using Lamdba Options in Python
• Anonymous Functions in Python
• Arguments, Expressions
• Recursive Functions, Usage
• Return & Print with Lamdba

Ch 8: Python Classes & Arrays

• Python Classes & Objects
• Python Classes : Usage
• __init__() Function
• __str__() Function
• Self Parameters & Usage
• Object Properties Options
• Python Inheritance & Classes
• Adding Parent & Child Classes
• Add __init__() Function
• Using super() Function
• Add Properties, Methods
• Polymorphism in Python

Ch 9: Python Modules

• Python Modules : Creation
• Import Python Modules
• Using Variables in Modules
• Naming, Renaming Module
• Built In Modules & dir
• Using Modules, Properties
• datetime module in Python
• Date Objections, strftime Method
• import datetime, datetime.now()
• Using Python Constructors
• Conditional Columns, Expressions
• Disable / Enable Data Loads

Ch 10: Python JSON & RegEx

• Python JSON Concepts, Usage
• Python Dictionary & import json
• Convert from Python to JSON
• Python Objects into JSON strings
• Result Formatting & Ordering
• json.dumps, print options
• Python Regular Expressions
• RegEx Module in Python
• RegEx Functions : findall
• search() Function & split
• span() function & Usage
• Using RegEx with JSON

Ch 11: Python User Inputs & TRY

• Python Try Except
• Python Exception Handling
• NameError Resolution
• Python Finally Block, Usage
• Raise an exception method
• TypeError, Scripting in Python
• Python User Inputs
• Python String Formatting
• Multiple Values & String
• Python Index Numbers
• Named Indexes, Usage
• input() & raw_input()

Ch 12: Python File Handling

• File Handling
• r, a, w, x modes
• t, b Operations
• File Activities
• Read Only Parts
• Loop, Close Files
• Python File Write
• Appending, Overwriting
• Create a New File
• import os, path.exists
• f.open, f.write
• f.read, f.close

Ch 13: Data Analytics – Pandas

• Python Modules & Pandas
• Why Use Pandas?
• Pandas Codebase & Usage
• Installation of Pandas
• import & pandas.DataFrame
• Checking Pandas Version
• Pandas Series
• one-dimensional array
• Labels : Creation, Use
• series(), print()
• Pandas DataFrames
• Dataframes & Series

Ch 14: Data Analytics – DataFrames

• Pandas DataFrames in Python
• DataFrame() & Realtime Usage
• Indexes & Named Options
• Locate Row and Load Rows
• Row Index & Index Lists
• Load Files Into a DataFrame
• Pandas Read CSV
• pd.read_csv() Function
• pd.options.display.max_rows
• df.to_string() Function
• Dictionary as JSON
• tail() & null() Function

Ch 15: Data Analytics – Pandas

• Pandas – Cleaning Data
• Removing Rows, Data Cleansing
• Replace, Transform Columns
• Data Discovery & Column Fill
• Identify & Remove Duplicates
• dropna(), fillna() Functions
• Pandas – Data Correlations
• Data Relations and Validations
• Good & Bad Correlation
• Perfect Correlation Scenarios
• Python Data Plotting Options
• matlib Module & Plotting

Ch 16: SQL Server with Python – 1

• Installing SQL Server DB Engine
• Install Machine Learning Services
• SQL Server Management Studio
• Install Azure Data Studio Tool
• sp_execute_external_script
• Input Data & Result Sets
• DDL & DML with Python
• SQL_out, SQL_in with Python
• Variables & Parameters in Python
• Python Version, Package List
• Script Parameters & Usage
• WITH RESULT SETS Options

Ch 17: SQL Server with Python – 2

• Using pandas.Series with SQL Server
• Indexing Methods and Realtime Use
• Convert series to data frame
• DataFrames with SQL Server
• Output values into data.frame
• Output Datasets and Usage
• pymssql package in SQL Server
• pip list & Package Manager
• Python runtime, Py Package Index
• pymssql.connect & Usage
• Query Execution & Result
• Cursor Variables & Usage

Ch 18: Power BI with Python

• Installing Power BI Desktop
• Using Python Script Visual
• PyScript Options & Tuning
• Settings, Labelling Options
• Running and Testing Scripts
• Data Validations in Power BI
• Power BI Cloud : ipynb Scripts
• Python in Desktop Vs Cloud
• Interactive Reports with Python
• Power Query Options (M Lang)
• Data Formatting with Python
• Integrate SQL, Power BI, Python

Ch 1: POWER BI INTRODUCTION

• Power BI : Introduction to Analytics
• Power BI Tools Suite, Advantages
• Power BI : Career Options, Plan
• Power BI Developer Job Role
• Microsoft Data Analyst Job Role
• Big Data Analyst Job Role
• Power BI Data Analyst (PL 300)
• Data Engineer*, Power BI (DP 500 *)
• Artificial Intelligence (AI) Visuals
• AI Enabled Power BI Features
• Course – Lab Plan with Design Tools
• Need for Power Query & DAX
• Power BI Licensing Types
• Power BI Cloud – Advantages
• Power BI Report Server Advantages

Ch 2: Basic Report Design

• Power BI Eco System: Architecture
• Data Sources & Types in Real-world
• Report Types: Interactive, Paginated
• Analytical Reports & Mobile Reports
• Data Sources : File, Database, Web
• Visualizations : Report Shapes
• Power BI Design Tools, Requirements
• Power BI Desktop Tool : Installation
• Desktop Interface: Overview, Canvas
• Get Data, Data View, Report View
• In-Memory Xvelocity Database
• Basic Visuals: Table, Tree Map
• Data Labels, Legend, Category
• Local Store: PBIX & PBIT Files
• Data Points and Tooltips

Ch 3: Visual Interaction, Visual Sync

• Visual Interaction with Data Points
• Disabling / Enabling Interactions
• Edit Interactions: Format Options
• Spotlight and Focus Mode
• Report Export to CSV, PDF
• Tooltip Options and Usage
• Working with Pages in PBI
• Rename, Duplicate, Hide Pages
• Slicer Visual : Real-time Usage
• Orientation, Selection Properties
• Slicer Settings : Tiles & Slider
• Single & Multi Select, Header
• Number, Text, Show Summary
• Date Slicer and Value Selections
• Slicer List, Dropdowns & Clear

Ch 4: Grouping & Hierarchies

• Grouping : Visuals with Pdf Sources
• List Grouping and Binning Options
• Grouping Static / Fixed Data Values
• Grouping Dynamic / Changing Data
• Bin Size and Bin Limits (Max, Min)
• Bin Count and Grouping Options
• Group with Bins & Clustering
• Group, Layer with Selection Pane
• Creating Hierarchies in Power BI
• Independent, Dependant Drill-Down
• Drill-Down with Interactive Reports
• Conditional Drilldowns, Data Points
• Drill Up Buttons and Operations
• Expand & Show Next Level
• Dynamic Data Drills Limitations

Ch 5: Filters & Bookmarks

• Filters : Types and Usage in Real-time
• Visual Filter, Page Filter, Report Filter
• Basic, Advanced and TOP N Filters
• Category and Summary Level Filters
• Data / Drill Options, DrillThru Filters
• Keep All Filters” Options in DrillThru
• CrossReport Filters, Include, Exclude
• Drill-thru Filters, Page Navigations
• Bookmarks : Report Navigations
• Buttons, Images with Actions
• Selection Pane, Actions, Text URLs
• Show Data and See Records
• Custom Tooltips, Table Visual
• Table Vs Matrix : Drill-downs
• Styles, Cell Properties, Databars
• Conditional Formatting, Divergent

Ch 6: Big Data Access, Visuals

• OLTP Databases, Big Data Sources
• Azure Database Access, Reports
• Import, Direct Query & Dual Mode
• Data Modeling: Do Not Summarize
• Data Modeling: Currency, Relations
• Power BI Archtiecture, Eco System
• Power BI Interface for Reports
• Stacked Chart, Clustered Chart
• Line Chart, Area Chart, Bar Chart
• 100% Stacked Bar & Column Chart
• Map Visuals: Tree, Filled, Bubble
• Small Multiples, Legends, Axis
• Cards, Funnel, Table, Matrix
• Scatter Chart : Play Axis, Labels
• Waterfall Chart, Multi Row Cards

Ch 7: POWER QUERY LEVEL 1

• Power Query M Language Purpose
• Power Query Architecture and ETL
• Data Types, Literals and Values
• Power Query Transformation Types
• Table & Column Transformations
• Text & Number Transformations
• Date, Time and Structured Data
• let, source, in statements @ M Lang
• Get Data, Table Creations and Edit
• ETL Operations with Power Query
• Merge Transformations in Power BI
• Join Kinds: Inner, Outer & Apply
• Union All Transformation & Appends
• Power Query Editor, Step Edits
• Close & Apply Options. Report Design

Ch 8: POWER QUERY LEVEL 2

• Query Duplicate, Query Reference
• Group By and Advanced Options
• Aggregations with Power Query
• Transpose, Header Promotion
• Reverse Rows and Row Count
• Data Type Changes & Detection
• Replace Columns: Text, NonText
• Advanced Query Edit Options
• Replace Nulls: Fill Up, Fill Down
• Pivot, Unpivot Transformations
• Move Column and Split Column
• Date & Time Transformations
• Derive Year, Quarter, Month, Day
• Add Column : Query Expressions
• Query Step Inserts and Step Edits

Ch 9: POWER QUERY LEVEL 3

• Big Data Loads : Parameter Queries
• Creating Parameters in Power Query
• Parameter Data Types, Default Lists
• Static & Dynamic Lists: List Queries
• Convert Tables to Lists, Use Cases
• Linking Parameters to Queries
• Testing Parameters with Canvas
• Multi-Valued Parameter Lists
• Creating Lists in Power Query
• Converting Lists to Table Data
• Invoke Function, Type Conversions
• Function Query & Parameter List
• Columns From Examples, Indexes
• Conditional Columns, Expressions
• Disable / Enable Data Loads

Ch 10: POWER BI CLOUD – 1

• Power BI Cloud Components
• App Workspaces, Report Publish
• Reports & Related Datasets Cloud
• Creating New Reports in Cloud
• Report Publish, Report Uploads
• Report Edits and New Reports
• Report Actions: Downloads
• Dataset Usage Options in Cloud
• Dashboards Creation and Usage
• Pining Visuals and Report Pages
• Visual Pin Actions in Dashboards
• Dashboard & LIVE Interactions
• Media Tiles: Images, Custom Links
• Q & A Option with Dashboards
• Pin with Q & A; Standard Visuals

Ch 11: POWER BI CLOUD – 2

• Report Actions : Share, Subscribe
• Report Actions : Lineage, Embed
• Report Actions : Export Options
• Report Actions : Public User Access
• Dashboard Actions : Share, Subscribe
• Dashboard Actions : Themes, Lineage
• Dashboard Actions : Share, Subscribe
• Favorite, Insights, Embed Code
• Gateways Configuration, PBI Service
• Gateway Types, Cloud Connections
• Gateway Cluster, Add Data Sources
• Data Refresh : Manual, Scheduled
• Power Query Parameters, Gateways
• DataFlows, Power Query in Cloud
• Lineage, Share, Subscribe, Insights
• Performance Inspector& Gateways

Ch 12: POWER BI CLOUD – 3

• Workbooks : Excel Online & Pins
• Power BI Apps: Creation & Usage
• Power BI Segments, Content
• Navigation Screens, Audience
• App Publish, Verification & Edits
• Export, Share & Subscribe
• List View & Lineage View Options
• Power BI Scorecards: Realtime Use
• Paginated Reports – Design & Usage
• Power BI Report Builder Tool
• Microsoft Report Builder Tool
• Report Builder : Datasets, Charts
• Report Builder : Bar Charts, Fields
• Report Builder : Creating RDL Files
• Paginated Reports : Deployments

Ch 13: DAX Functions – Level 1

• DAX : Importance in Real-time
• DAX Data Types, Syntax Rules
• DAX Measures and Columns
• ROW Context and Filter Context
• Operators, Special Characters
• DAX Functions, Vertipaq Engine
• DAX Cheat Sheet : Expressions
• Data Analytics with DAX
• DAX Measures : Expressions
• ISBLANK, IF, IN, SUM
• SUMX, AVG, AVERAGEX
• Data Models: Fact, Dimensions
• Detecting Relations for DAX
• Star & Snowflake Schemas
• Data Modeling Options in DAX

Ch 14: DAX Functions – Level 2

• Quick Measures in Power BI
• Average and Filtered Average
• Running Totals, EARLIER( )
• RELATED, COUNTROWS
• CALCULATE Function Conditions
• ALL Members Scope & IN
• Account and Time Calculations
• Star Rating, DAX Expressions
• Data Modeling Options in DAX
• 1:1, 1:M and M:1 Relations
• Working with Facts & Measures
• Modeling : Missing Relations
• Relationships & Importance
• Modeling : Relation Management
• Modeling with Multiple Keys

Ch 15: DAX Functions – Level 3

• DAX : Variables and Expressions
• Dynamic Expressions, RETURN
• Current Value, Previous Value
• SELECTED VALUE, Joins
• FORMAT Function with DAX
• RELATED, Joins in DAX
• DAX Expressions with SQL DB
• Time Intelligence Functions
• Date Dimension : Generation
• CALENDAR(), DATESYTD()
• TOTALYTD, TOTALQTD
• TODAY, DATE, DAY with DAX
• SELECTEDVALUE, FORMAT
• Date, Time and Text Functions

Ch 16: DAX Functions – Level 4

• RLS: Row Level Security
• Data Models in Power BI Desktop
• DAX Roles Creation and Testing
• DAX Expressions & Operators
• PBIX Uploads: Power BI Cloud
• Dataset Security with DAX Roles
• Entity Sets and Slicing in DAX
• Dataflows with Power BI
• Analytical Reports – DAX Usage
• Creating Data Models with DAX
• Datasets in Excel and Dashboards
• Using Excel Analyzer in Power BI
• Power BI Data Source in Excel
• Connection Strings and Refresh
• Analytical Reports – Limitations

Ch 17: Power BI Report Server

• Power BI Report Server Config
• SQL Server Instance Verifications
• Report Server DB, Temp Database
• WebService & WebPortal URL
• Uploading Interactive Reports
• End User Report Share (pdf)
• Power BI Desktop RS Tool
• Interactive Reports: Report Server
• Mobile Reports : Design Options
• Mobile Reports : Grids, Elements
• Mobile Reports : Uploads, Edits
• Paginated Reports : Deployments
• Paginated Vs Interactive Reports
• Paginated Vs Analytical Reports
• Paginated Vs Mobile Reports
• Power BI Report Server Vs Cloud

Ch 18: Power BI Admin & AI

• Power BI Cloud Management
• Power BI Admin : Alerts
• Workspace Management, Users
• Security: Report, Dataset Levels
• Security: Dataset, App Levels
• Security: Workspace Options
• PBI Performance Inspector
• Power BI & Artificial Intelligence
• Power BI & CoPilot Add-Ins
• AI Visuals & Big Data Analytics
• Smart Narrative and Q & A
• Infographics, Icons and Labels
• Key Influencer Visual in Power BI
• Metrics Visual, Performance
• Paginated Reports Visual