DATABASE MANAGEMENT
Module 1: Introduction to SQL and Databases (SQL Server)
- Data and Database Fundamentals
-
Types of databases: Relational (RDBMS) vs. Non-relational
(NoSQL)
- The architecture of SQL Server
- Database Structure and Schema
- SQL Server Editions and Versions
Module 2: Environment and Database Set-up
- Installation of SQL Server
- Installation of SSMS and Azure Data Studio
- Database Restore and Backup
- Database Attach and Detach
- Database Creation via Scripting
- Data Import and Export
- Disaster Recovery Lessons: RPO and RTO
Module 3: Introduction to Transact-SQL (T-SQL)
- SQL Components – DML/DQL, DDL, DCL
-
Language Elements (Predicates, Functions, Variables and
Operators)
- Understanding Set Theory
- Understanding Predicate Logic
Module 4: SELECT Statements (DQL/DML)
- Writing Simple SELECT Statements
- Eliminating Duplicates with Distinct
- Using Column and Tables Aliases
- Filtering using WHERE, BETWEEN, IN and LIKE
- Sorting using ORDER BY (ASC and DESC)
- Aggregating using Functions
- GROUP BY and HAVING
-
Understanding the Logical Order of Operations in SELECT
Statements
Module 5: CASE Expressions and Querying Multiple Tables
- Writing CASE Expressions
- Understanding Joins
- Inner Joins
- Outer Joins(LEFT, RIGHT, FULL)
- Cross Joins and Self Joins
Module 6: Advanced Filtering and Ranking
- Filtering with TOP and OFFSET-FETCH
- Working with NULLS and NOT NULLS
-
Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
Module 7: Database Development and Definition
- Understanding Datatypes
- Understanding Table Structures
- Creating Databases, Tables and Columns
- RDBMS Concepts I: ACID Properties and Normalization
-
RDBMS Concepts II: Primary Keys, Foreign Keys, Indexes,
Constraints
- ERD: Database Design and Modelling
Module 8: Working with Datatypes
- Working with character data
- Working with Date and Time Data
- Working with Numeric Data
Module 9: Data Definition Language (DDL)
- Creating Database Objects and constraints
-
Altering and Modifying Database Objects and Constraints
- Deleting Database Objects and Constraints
Module 10: Data Manipulation Language (DML)
- Inserting data into Tables
- Deleting and Truncating data from a Table
- Updating data in a Table
- Generating Automatic Column Values
- Inserting Data into Foreign Key Columns
Module 11: T-SQL Built-In Functions
- Writing Queries with Built-In Functions
- Using Conversion Functions
- Using Logical Functions
- Using Functions to Work with Null
Module 12: Subqueries and Nested Queries
- Writing Self-Contained Subqueries
- Writing Correlated Subqueries
-
Using the EXISTS and NOT EXISTS Predicate with Subqueries
Module 13: Table Expressions
- Using views
- Using Inline TVFs
- Using Derived Tables
- Using CTEs
Module 14: Using Set Operators and Window Ranking
- Writing Queries with the UNION Operator
- Using EXCEPT and INTERSECT
- Using APPLY
- Creating Windows with OVER
- Partitioning Windows
- Ordering and Framing
- Exploring Window Functions
Module 15: Pivoting and Grouping Sets
- Writing queries with PIVOT and UNPIVOT
- Working with Grouping Sets
- CUBE and ROLLUP
Module 16: Executing Stored Procedures
- Querying Data with Stored Procedures
- Passing Parameters to Stored Procedures
- Creating Simple Stored Procedures
- Working With Dynamic SQL
Module 17: Case Studies
-
Adventure Works Stores - Retail Store Analysis using SQL
-
Dining Insights: Optimizing Restaurant Experience using SQL
- HR: People Analytics using SQL
-
Finance and Credit: Risk Management and Analysis using SQL
-
Aviation: Process Optimization and Customer Experience using
SQL
Data Analysis and Visualization using PowerBI
Module 1: PowerBI Overview and Data Governance
- Understanding data strategy and utilization needs
- PowerBI Features, Interfaces, and Services
- ETL - Analytics Workflow
Data Preparation, ETL and Connection Essentials
- Connecting to data sources
-
Working with SQL Server databases and other databases
- Working with CSV, text files, and Excel datasets
- Working with PDF tables and website tables
Data Transformation
- Working with datatypes
- Handling missing data (replace, remove, impute)
- Error Handling
- Working with dates
- Creating columns from example
- Deduplication
- String, row, and column manipulation
- Creating conditional columns
- Creating calculated columns and tables
- Pivot and Unpivoting
- Grouping and Aggregation
- Data Sampling
- Working with Dates
- Data Profiling
- Creating distinct values
Data Modelling
- Merging and Appending multiple datasets
- Primary Keys and Unique Identifiers
- Relationship between tables and Foreign Keys
- Understanding Star and Snowflake schema
-
Managing relationships (one-to-one, one-to-many,
many-to-many)
Module 2: Visualization Datasets and Reports
- Utilizing Card Visualization
- Utilizing Map Visualization
- Utilizing Chart Visualization
- KPI Cards and Gauges
- Utilizing Matrix Visualization
- Tables, Matrices & Table Conditional Formatting
- Utilizing Slicers
- Custom Visuals
- Bookmarks and Buttons
- Decomposition Trees
- Utilizing Filters, Drillthrough, Drill Down/Up
Module 3: Advanced Analytics and Time Intelligence
- Introduction to Data Expression Language (DAX)
- Creating Measures and Calculated Columns
- Creating a Calendar Table
- Common DAX functions (SUM, COUNT, AVERAGE, MIN, MAX)
-
Time intelligence functions (DATEADD, DATESYTD,
SAMEPERIODLASTYEAR, Rolling Totals, Moving Averages)
-
Advanced DAX functions (CALCULATE, ALL, FILTER, RELATED)
Module 4: Dashboarding and Storytelling
- Building Dashboards
- PowerBI Service and Report Publishing/Sharing
- Principles of storytelling
- Forecasting and Key Influencers
Module 5: Case Studies
- Case 1: Customer Analytics Dashboard
- Case 2: Product and Sales Performance Dashboards
- Case 3: HR Dashboard
- Case 4: Revenue Assurance Dashboard
- Case 5: Hospital/Health Management Dashboard