Data Management and Analytics

Duration: 8 weeks + 4weeks project
Language: English
Level: Beginners
Delivery Method: Online
Start Date: Jan 11, 2025
End Date: March 30th, 2025
Classes are weekends only

Overview

As a candidate for this course, you’re someone looking to build a strong foundation in database management and analytics. You may be:

Target Audience

  • Beginners in data management and analysis who have no prior experience with SQL and Databases.
  • Professionals transitioning to database management roles.
  • Business analysts, data scientists, and IT professionals seeking to improve their SQL and Database proficiency.
  • Students aiming to build foundational knowledge in database querying and management.
  • Anyone seeking to leverage data for strategic insights and data-driven decision-making.

The course is designed to:

  • Provide a solid foundation in SQL and database management and data warehousing concepts.
  • Enable participants to create, manage, and query relational databases.
  • Equip learners with the skills to manipulate and analyze data using SQL.
  • Teach advanced querying techniques and database optimization and administration.
  • Prepare participants for real-world applications of SQL in business and data analysis scenarios.
  • Master advanced Power BI skills to create dynamic dashboards and reports for informed decision-making.
  • Learn to analyze diverse industry data, including healthcare, sales, customer experience, product performance, market trends, and financial insights.

What you will learn

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

Vacancies on Top Job Websites