Data Analysis using Advance Excel and SQL Training DA Batch 06

PKR 30,000

Starting in

Learn how to effectively analyze data using advanced Excel and SQL.
Start Date
Nov 25, 2023
End Date
Jan 14, 2024
Timing
10:00 AM-03:00 PM
Location
Online
Type
Instructor Led
Duration
80 Hours
training image

Overview

Learn the essentials of data analysis using Advance Excel and SQL in our 8-weeks live training. 

This 8-weeks training on data analysis using Advance Excel and SQL will equip you with the necessary skills to analyze data and make informed decisions. You will learn to use Excel and SQL to create powerful data visualizations, analyze data and create meaningful insights using pivot tables and advanced formulas.

By the end of this training, you will be able to use advanced formulas and functions to manipulate data in Excel, create meaningful data visualizations, analyze and interpret data to identify patterns and trends, master the use of pivot tables, write and execute SQL queries to filter and join data from multiple tables, use SQL to build reports and dashboards to visualize data and utilize data to gain insights and make data-driven decisions. This training is suitable for beginners and advanced users who want to improve their data analysis skills. 

Tools Covered

  • Degree Levels: Academy Profession,Associate Degree,Bachelor's,Master
  • Field of Studies: Data Analytics,databases
  • Prior Job Experience: Not Required
  • Other Programs: Data Engineering using SQL Server Tools Training

  • Recent graduates, third-year and final-year students of any degree.
  • Professionals from any domain who want to learn Excel tools to create effective reports and spreadsheets for work assistance.
  • Executives and Department Heads who want to gain a better understanding of MS Excel.

  • Understand the fundamentals of data analysis and data manipulation using Excel and SQL.
  • Learn to use basic formulas and functions in Excel.
  • Develop an understanding of SQL queries and how to use them to manipulate data.
  • Learn how to use PivotTables, charts, and other visualization techniques to effectively analyze data.
  • Understand the principles of data modeling and how to apply them to construct relational databases.
  • Learn how to use SQL to query and join multiple tables.
  • Develop an understanding of how to use Excel and SQL together to perform advanced data analysis.
  • Explore data mining techniques and how to use them to uncover patterns and trends.
  • Understand the principles of data visualization and how to use them to communicate complex data.
  • Develop proficiency in managing and analyzing large data sets.

Meet the Instructor

Neman Shahid

Business Intelligence | Businesses Process Management | IT Governance and Compliance | Lead Implementor & Auditor ISO 9001, ISO 20000-1 and ISO 27001 | Technical Project Manager

Neman Shahid

Business Intelligence | Businesses Process Management | IT Governance and Compliance | Lead Implementor & Auditor ISO 9001, ISO 20000-1 and ISO 27001 | Technical Project Manager


Working in an organized and professional environment that helps to build and polish skills related to IT Governance, Auditing and Project Management in order to meet the competitive industry needs through self-motivation and result-oriented approach


Course Outline

  • Basic Excel Functions
  • Conditional Formatting
  • Data Cleaning
  • Intermediate Excel Functions
  • VLOOKUP, HLOOKUP and XLOOKUP
  • Array Formulas
  • How to create basic charts?

  • What is Data Modelling and how to achieve it in Excel?
  • Understanding and exploring Pivot Table and Pivot Charts along with their applications
  • Learning Power Query basics with examples

  • Power Pivot
  • Excel Slicers
  • Trend Analysis
  • Time Analysis
  • Comparison Analysis

  • Microsoft PowerPoint Overview
  • Importance of Power Point in Data Analysis for Story Telling
  • Creating Presentations
  • Understanding Layouts and Sizing
  • Slides Basics
  • Text Basics
  • Applying Themes
  • Applying Transitions
  • Managing Slides
  • Slide Show Basics
  • Indent and Line Spacing
  • Adding and Formatting Pictures
  • Adding Videos in Slide Deck
  • Adding Shapes and 3D Diagrams
  • Using Slides Templates
  • Animating Objects in Power Point
  • Insert Tables
  • Insert Charts
  • Shortcuts in Power Point

  • What is a Database?
  • Different Database Management Softwares
  • Microsoft SQL Server Overview
  • SQL Management Studio
  • Select Statement
  • Execution Order of SELECT Statements
  • Ordering Results/Orderby Clause
  • Additional SELECT Options
  • Column aliases
  • Table aliases
  • Where Clause
  • Rows Filtering using pattern matching
  • Date types and Date functions
  • DML and DDL Statements
  • Inserting rows (INSERT)
  • Inserting rows using subquery
  • Updating rows (UPDATE)
  • Deleting rows (DELETE)
  • Create Table
  • Delete/Truncate Table
  • Drop Table/Database
  • Primary Key & Foreign Key Concept

  • Basics of ERD Diagrams
  • Group By Clause
  • Count, Distinct Count, Sum, Average, Min, Max
  • Having Clause
  • Difference Between Where and Having Clause
  • Handling Multiple Tables Using Joins
  • Inner Join
  • Left Join
  • Right Join
  • Full Outer Join
  • Cross/Product Join
  • Joining Three or More Tables
  • Self-join
  • Alternate Syntax, Implicit Joins
  • Set Operations
  • Working with Multiple Set Operators
  • Viewing Graphical Execution Plans
  • CASE Statements in SQL
  • NULL vs Blank
  • = vs IS NULL
  • ISNULL function
  • COALESCE
  • Concatenating NULL Data
  • Subqueries in SQL
  • Subqueries in the SELECT Clause
  • Subqueries in the WHERE Clause

  • Window Aggregate Functions
  • Partition By Clause
  • Over Clause
  • Qualification Clause
  • Rank, Row Number and Dense Rank Functions
  • Running Sum and Running Difference
  • Lag and Lead Functions
  • First and Last Function
  • NTILE
  • Range vs Row Function
  • Data Type Precedence
  • String Related Data Types
  • Numeric Data Types
  • Date and Time Data Types
  • Converting Between Data Types
  • How to Find Help on Functions?
  • Understanding Data Type Conversion

  • Interactive Dashboards
  • Dashboard Reporting
  • Build a KPI Dashboard
  • Protecting and Sharing your Dashboard
  • Linking your Dashboard to Power Point

Our Methodology

Industry Usecases

With real world projects and immersive content built in partnership with top tier companies, you’ll master the tech skills companies want.

Technical Support

Our knowledgeable mentors guide your learning and are focused on answering your questions, motivating you and keeping you on track.

Career Mentorship

You’ll have access to resume support, portfolio review and optimization to help you advance your career and land a high-paying role.

Frequently Asked Questions

  • Recent graduates, third year and final year students of any degree
  • Professionals from any domain who want to learn Excel tool to create effective reports and spreadsheets for work assistance
  • Executives and Department Heads who want to gain better understanding of MS Excel.

Duration: 8 weeks
Class Days: Saturday & Sunday
Timings: 11:00 AM to 04:00 PM

Since our instructors are industry experts so they do train the students about practical world and also recommend the shinning students in industry for relevant positions.

Yes, you can rejoin the training within the span of an year of your registration. Please note following conditions in case you’re rejoining.
1) There are only 5 seats specified for rejoiners in each iteration.
2) These seats will be served on first come first basis.
3) If you have not submitted your complete fee, you may not be able to rejoin. Your registration would be canceled.

Don’t worry! We have got you covered. You shall be shared recorded lectures after each session, in case you want to revise your concepts or miss the lecture due to some personal or professional commitment.

Since our trainings are led by Industry Experts so it is made sure that content covered in workshop is designed with hand on knowledge of more than 70-75 % along with supporting theory.

For this professional workshop, you need to have a PC with minimum 4GB RAM and ideally 8GB RAM.

Yes, you will be awarded with a course completion certificate by Dice Analytics. We also keenly conduct an annual convocation for the appreciation and recognition of our students.

So, what's your plan?

Follow the footsteps of thousands of successful alumni...

Reserve Seat
svg img
Reserve Seat