Generative AI Master Course

SQL Fundamental

• Introduction & Software Installation
• Overview of Oracle Database
• Introduction to SQL and its Development Environments
• Installing Oracle
• Installing Java SDK
• Installing SQL Developer Client

• Overview of RDBMS Concepts And Terminologies
• What is RDBMS
• Features of RDBMS
• Advantages of RDBMS
• Database Normalization
• SQL Constraints
• SQL RDBMS Concept
• Types of keys in DBMS

• Database Design and Basics
• Understanding of Database Terms
• What is Good Database Design
• The Design Process
• Determining the purpose of your Database
• Finding and Organizing the required Information
• Dividing the Information into Tables
• Turning Information Items into Columns
• Specifying Primary Keys
• Creating the Table Relationships
• Refining the Design
• Applying the Normalization Rules

• Database Security Concepts
• The Scope of Database Security
• Overview | Threats to the Database | Principles of Database Security
• Security Models
• Access Control | Authentication and Authorisation | Authentication | Authorisation | Access Philosophies and Management
• Database Security Issues
• Access to Key Fields | Access to Surrogate Information | Problems with Data | Extraction | Access Control in SQL | Discretionary Security in SQL | Schema | Level | Authentication | Table Level | SQL System Tables | Mandatory Security in SQL | Data Protection

• Database Performance
• Optimize Queries
• Create Optimal Indexes
• Memory Allocation
• Data Defragmentation

• Retrieve Data Using The Sql Select Statement
• List the Capabilities Of Sql Select Statements
• Generate a Report Of Data From the Output Of a Basic Select Statement
• Use Arithmetic Expressions and Null Values In the Select Statement
• Invoke Column Aliases
• Concatenation Operator, Literal Character Strings, Alternative Quote Operator, and the
• Distinct Keyword
• Display the Table Structure Using the Describe Command

• Restricted And Sorted Data
• Write Queries With a Where Clause to Limit the Output Retrieved
• Describe the Comparison Operators and Logical Operators
• Describe the Rules Of Precedence For Comparison and Logical Operators
• Usage Of Character String Literals In the Where Clause
• Write Queries With an Order By Clause
• Sort the Output In Descending and Ascending Order
• Substitution Variables

• Usage Of Single-Row Functions To Customize Output
• List the Differences Between Single Row and Multiple Row Functions
• Manipulate Strings Using Character Functions
• Manipulate Numbers With the ROUND, TRUNC, and MOD Functions
• Perform Arithmetic With Date Data
• Manipulate Dates With the DATE Functions

• Conversion Functions And Conditional Expressions
• Describe Implicit and Explicit Data Type Conversion
• Describe The TO_CHAR, TO_NUMBER, And TO_DATE Conversion Functions
• Nesting Multiple Functions
• Apply the NVL, NULLIF, and COALESCE Functions to Data
• Usage Of Conditional IF THEN ELSE Logic In a SELECT Statement

• Aggregated Data Using The Group Functions
• Usage Of The Aggregation Functions In SELECT Statements To Produce Meaningful
• Reports
• Describe the AVG, SUM, MIN, and MAX Function
• How to Handle Null Values In a Group Function
• Divide The Data In Groups By Using The GROUP BY Clause
• Exclude Groups Of Date By Using The HAVING Clause

• Display Data From Multiple Tables
• Write SELECT Statements To Access Data From More Than One Table
• Join Tables Using SQL:1999 Syntax
• View Data That Does Not Meet a Join Condition By Using Outer Joins
• Join A Table To Itself By Using a Self-Join
• Create Cross Joins

• Usage Of Subqueries To Solve Queries
• Use a Subquery To Solve a Problem
• Single-Row Subqueries
• Group Functions In A Subquery
• Multiple-Row Subqueries
• Use The ANY and ALL Operator In Multiple-Row Subqueries
• Use The EXISTS Operator

• SET Operators
• Describe The SET Operators
• Use A SET Operator To Combine Multiple Queries Into a Single Query
• Describe The UNION, UNION ALL, INTERSECT, and MINUS Operators
• Use The ORDER BY Clause In Set Operations

• Data Manipulation
• Add New Rows To a Table
• Change The Data In a Table
• Use The DELETE and TRUNCATE Statements
• How To Save and Discard Changes With The COMMIT and ROLLBACK Statements
• Implement Read Consistency
• Describe The FOR UPDATE Clause

• DDL Statements To Create And Manage Tables
• Categorize Database Objects
• Create Tables
• Describe The Data Types
• Understand Constraints
• Create a Table Using A Subquery
• How To Alter a Table
• How To Drop a Table

• Other Schema Objects
• Create, Modify, And Retrieve Data From a View
• Perform Data Manipulation Language (DML) Operations On a View
• How to Drop a View
• Create, Use, and Modify a Sequence
• Create and Drop Indexes
• Create and Drop Synonyms

Advance SQL

• Manipulating Data
• Default Values for Columns
• Virtual Columns
• Arithmetic Calculations on NULL Values
• Multi table Insert's
• Merge the Data

• Analytical Functions
• Analytical Functions Introduction
• Getting the Cumulative Sum of Sales
• Displaying Sales as a Percentage of Total Sales
• Ranking your Data
• Performing Top N Analysis
• Dividing your Data into Bands
• LAG and LEAD Function Examples
• Analyzing Sales Growth Across Time
• Analytical Functions Recap

• Transforming the Data
• Row Level Data to Column Level using CASE statement
• Row Level Data to Column Level using PIVOT
• Row Level Data to Column Level using LISTAGG
• Column Level Data to Row Level using UNION
• Column Level Data to Row Level using UNPIVOT

• Hierarchical Queries
• Hierarchical Queries Introduction
• Connect By Clause
• Creating the Hierarchy Tree
• Sorting the Hierarchy Tree
• CONNECT_BY_ROOT Unary Operator
• SYS_CONNECT_BY_PATH Function
• CONNECT BY for Number Generation

• Regular Expressions
• Regular Expressions Introduction
• Meta Characters . and +
• Meta Characters and *
• Interval Operator to Match the Number of Occurrences
• Matching the Characters in a List
• Combine Multiple Expressions Using |
• Check for an Expression in the Beginning or End of a String
• Search for Meta Characters by Placing a Escape Character

• Materialized Views
• Materialized Views Introduction
• Materialized Views Creation Options
• Materialized Views with ON COMMIT Option
• Materialized Views with ON DEMAND Option
• Materialized Views with REFRESH FAST Option
• Timing the Refresh

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

Power BI Desktop Visualisations

• How to use Visual in Power BI

• What Are Custom Visuals

• Creating Visualisations and Colour Formatting

• Setting Sort Order

• Scatter & Bubble Charts & Play Axis

• Tooltips and Slicers, Timeline Slicers & Sync Slicers

• Cross Filtering and Highlighting

• Visual, Page and Report Level Filters

• Drill Down/Up

• Hierarchies and Reference/Constant Lines

• Tables, Matrices & Conditional Formatting

• KPI's, Cards & Gauges

• Map Visualizations

• Custom Visuals

• Managing and Arranging

• Drill through and Custom Report Themes

• Grouping and Binning and Selection Pane, Bookmarks & Buttons

• Data Binding and Power BI Report Server

Introduction to Power BI Dashboard and Data Insights

• Why Dashboard and Dashboard vs Reports

• Creating Dashboards

• Conguring a Dashboard Dashboard Tiles, Pinning Tiles

• Power BI Q&A

• Quick Insights in Power BI

Direct Connectivity

• Custom Data Gateways

• Exploring live connections to data with Power BI

• Connecting directly to SQL Server

• Connectivity with CSV & Text Files

• Excel with Power BI Connect Excel to Power BI, Power BI Publisher for Excel

• Content packs

• Update content packs

Publishing and Sharing

• Introduction and Sharing Options Overview

• Publish from Power BI Desktop and Publish to Web

• Share Dashboard with Power BI Service

• Workspaces (Power BI Pro) and Content Packs (Power BI Pro)

• Print or Save as PDF and Row Level Security (Power BI Pro)

• Export Data from a Visualization

• Export to PowerPoint and Sharing Options Summary

Refreshing Datasets

• Understanding Data Refresh

• Personal Gateway (Power BI Pro and 64-bit Windows)

• Replacing a Dataset and Troubleshooting Refreshing

Introduction to Data Preparation using Tableau

• Data Visualization

• Business Intelligence tools

• Introduction to Tableau

• Tableau Architecture

• Tableau Server Architecture

• VizQL Fundamentals

• Introduction to Tableau Prep

• Tableau Prep Builder User Interface

• Data Preparation techniques using Tableau Prep Builder tool

Data Connection with Tableau Desktop

• Features of Tableau Desktop

• Connect to data from File and Database

• Types of Connections

• Joins and Unions

• Data Blending

• Tableau Desktop User Interface

Basic Visual Analytics

• Visual Analytics

• Basic Charts Bar Chart, Line Chart, and Pie Chart

• Hierarchies

• Data Granularity

• Highlighting

• Sorting

• Filtering

• Grouping

• Sets

Calculations in Tableau

• Types of Calculations

• Built-in Functions (Number, String, Date, Logical and Aggregate)

• Operators and Syntax Conventions

• Table Calculations

• Level of Detail (LOD) Calculations

• Using R within Tableau for Calculations

Advanced Visual Analytics

• Parameters

• Tool tips

• Trend lines

• Reference lines

• Forecasting

• Clustering

Level of Detail (LOD) Expressions in Tableau

• Count Customer by Order

• Profit per Business Day

• Comparative Sales

• Profit Vs Target

• Finding the second order date

• Cohort Analysis

Geographic Visualizations in Tableau

• Introduction to Geographic Visualizations

• Manually assigning Geographical Locations

• Types of Maps

• Spatial Files

• Custom Geocoding

• Polygon Maps

• Web Map Services

• Background Images

Advanced charts in Tableau