SQL & Power BI: Your Data Analytics & Visualisation Journey
- Descrição
- Currículo
- FAQ
- Revisões
Reviews
“Excellent course, principles are explained clearly and then demonstrated…” – Mike Pease
“The trainer was very comprehensive” – Lyra Aitolya
“I just saw the course once in SQL section, then practiced it. Just follow his directions and you will learn the concepts very clearly” – Shital Parikh
“Useful Power BI lessons – Helped me make my dashboards. Enjoying SQL sessions as well” – Dan Pearson
“They really take you from a beginner to advanced. I am now able to freely use SQL to analyze data and Power BI to visualize it” – Naufil Arshad
“The course structure is amazing, the guide is very clear and to the point” – Shuheb Khan
“Yes it is a good learning for me and I can relate to it in the work that I do” – Sheldon Pais
————————-
Join this course and start gaining the key data analytic skills you’ve always wanted in your tool-belt!
This course will walk you through fundamental data concepts, with practical exercises, in order to build you from an absolute beginner to a skilled user, in both analysis and visualisation, working through the examples together to give you live practice.
Upon enrolling, you’ll gain instant, lifetime access to all sections of the course for Microsoft SQL Server and Power BI, as well as the data files and scripts necessary for the exercises.
Our approach is a stepped approach, meaning that you will keep gaining more and more building blocks as you work through the lectures. This means you will be equipped with the necessary knowledge to conduct data analytics and visualise your data to a professional standard.
You’ll get access to all sections of the course that will teach you the fundamentals from the ground up, supported with over 8 hours of clear, structured content to start your data journey.
The course starts with ‘Core SQL’, where you will gain an in-depth understanding of the core functions in SQL. You will learn creating databases, to the most frequently used queries, to advanced filtering, to bucketing results:
-
Introduction to the SQL interface and how to navigate and import data
-
Understanding data types and obtaining data from tables by using the SELECT and FROM statements
-
Creating new tables using the INTO statement
-
Applying basic and complex filters on data sets by using WHILE, IN and LIKE statements
-
Aggregating data to create reports by using the GROUP BY statement
-
Understanding how to apply non-aggregation and aggregation filtering
-
Creating new data columns based on constraints and conditions by using the CASE statement
-
Appending tables using UNION and UNION ALL statements
-
Utilising data from different tables to create master tables by using the JOIN statement
-
Things to look out for when managing databases
Next you’ll learn the more advanced SQL functions and concepts in the ‘Intermediate SQL’ section of the course. This includes data conversion, data cleansing, running totals, views, variables, stored procedures, and more:
-
Understanding and working with NULL values by using ISNULL and COALESCE statements
-
Using the CAST and CONVERT statements to change data types
-
Applying window functions (such as RANK and ROW_NUMBER) to introduce a row count and ranking to the data
-
Using the PARTITION statement to split the window function operations on data (such as running totals)
-
Using the VIEW statement to create virtual tables and manipulate data
-
Understanding variables and stored procedures to make SQL code and queries more dynamic
The next (bonus!) section is a ‘SQL Query Walkthrough Guide’ – so you’ll never get lost on how to script your queries correctly!
The final section of this course covers Microsoft’s intuitive ‘Power BI’ data visualisation tool. We go into the importance of data visualisation, show how powerful it is, and build multiple dashboards to gain important data insights:
-
Understanding the concepts and importance of data visualisation
-
Understanding how to import data into Power BI directly from SQL
-
How to navigate through the back end of Power BI and manipulate data to satisfy our needs
-
How to create meaningful visualisations and dashboards which can be used to show relevant findings
Our mission at Data Civilisation is to give you the building blocks for your journey!
So what are you waiting for? Jump right in and learn about data!
-
2
Core SQL: Welcome & Course ContentsAll the necessary course resources are attached to this lecture - this includes the scripts and the source data files that we will use in the exercises.
-
3
Software Installation for Windows - SQL Server & SSMS
Updated for 2024: How to install SQL Server and SSMS (SQL Server Management Studio) for Windows
-
4
Background: What is SQL?
This lecture provides an overview to explain what SQL is, its exponential use in the job market, and how it works as a key data analysis tool.
-
5
Importing Data - SQL Import Wizard Overview
This lecture introduces data types and covers the theory of using the SQL Import Wizard
-
6
Importing Data - Creating Database and Importing Walkthrough
This lecture explains how to get data into SQL by using the ‘import wizard’, working through an example together by importing a .csv file. Additionally, it covers important aspects to take into consideration when performing this task. After this lecture, you will understand how to import data.
Note: In this video the table name being imported is "Melbourne Housing Master_" i.e. with an underscore at the end. Please ensure that when importing the file via the import wizard, that this exact spelling / formatting is given to the table name, otherwise the script in the following lectures will not run.
-
7
Importing Data - Summary
Summary of key points regarding importing data
-
8
Importing Data - Preparing the Melbourne Housing Dataset
Steps to run stored procedures on a dataset - required for the course exercises.
-
9
Test your knowledge: Importing Your DataWhat type of files will we be importing into SQL in the course?
-
10
Data Types - Overview
This lecture covers the different data types in the world of data analytics and the importance they hold. At the end of this lecture, you will be able to comprehend the various types of data which SQL handles and appreciate the uniformity of data types in a data set.
-
11
Data Types - Walkthrough
Pratical demonstration in SQL of the data types
-
12
Data Types - NULL Values
We introduce the concept of ‘NULL’ values, why they need to be considered and what their significance is
-
13
Data Types - Summary
Summary of the key points regarding data types
-
14
Test your knowledge: Data Types
INT, DECIMAL and FLOAT are associated to which data types?
-
15
Course Scripts Overview
This lecture highlights the reference scripts for this course
-
16
'SELECT' and 'FROM' Statements - Overview
We go over how to view data from tables present in the database by using the SELECT and FROM statements
-
17
'SELECT' and 'FROM' Statements - Renaming via Aliases
The use cases of renaming columns whilst querying
-
18
'SELECT' and 'FROM' Statements - Column Operations
We cover how simple mathematical and string operations can be applied to different columns in a table
-
19
'SELECT' and 'FROM' Statements - Sampling Data
How to sample data, which is useful instead of calling a large number of rows which consumes time and computational power
-
20
'SELECT' and 'FROM' Statements - Querying Using Column Names vs Asterisk
The pros and cons of querying using specific column names compared to using an asterisk (to call all columns)
-
21
'SELECT' and 'FROM' Statements - Summary
Summary of key points regarding the SELECT and FROM statements
-
22
Test your knowledge: Selecting Data From Tables
If you want to select ALL the columns from a table AND create a new column which consists of the ratio of the columns 'Net' and 'Gross', which code would you use?
-
23
Using 'WHERE' - Overview (Introduction to Filtering)
This lecture goes over how to apply filters on a table by using the WHERE statement
-
24
Using 'WHERE' - Walkthrough
Practical walkthrough for filtering
-
25
Using 'WHERE' - Filtering On Multiple Columns
Using multiple columns as a filter conditions
-
26
Using 'WHERE' - Filtering With the 'IN' Statement
Introducing the IN statement in filtering
-
27
Using 'WHERE' - Filtering With the 'LIKE' Statement
Introducing the LIKE statement in filtering
-
28
Using 'WHERE' - Advanced Filtering With the 'LIKE' Statement
Advanced use cases of the LIKE statement in filtering
-
29
Using 'WHERE' - Filtering With the 'NOT' Operator
This covers the significance of the NOT operator
-
30
Using 'WHERE' - Introducing SUBSTRING, TRIM, and LENGTH functions
Utilising functions within the filter conditions to introduce a new dynamic
-
31
Using 'WHERE' - Complex Filtering Part A
Complex filtering is covered in this lecture
-
32
Using 'WHERE' - Complex Filtering Part B
Complex filtering is covered in this lecture
-
33
Using 'WHERE' - Using 'CHARINDEX' and 'PATINDEX'
Using these functions will improve your skills as a data analytics specialist
-
34
Using 'WHERE' - Additional Approaches
Using underscore characters to filter
-
35
Using 'WHERE' - Summary
Key take-home points regarding filtering using the WHERE statement
-
36
Test your knowledge: Filtering Data
A filter needs to be applied where the column [Type] is populated with 'Monthly' and [Value] is greater than 1000. What code needs to be used to facilitate this?
-
37
The 'INTO' Statement - Putting Data Into a Table
This lecture covers how to run a query and send the results into a physical table in the SQL database by using the INTO statement. You will learn how to send the outputs from your query into a new table in your SQL database.
-
38
Test your knowledge: Putting Results / Data Into A Table
Where does the 'INTO' statement go in a query if you want to utilise it to put the results from a query into a new table?
-
39
The 'ORDER BY' Statement - Sorting Outputs
This lecture shows how to sort data in a table using the ORDER BY statement, in either ascending or descending order based on the values/entries in a single column. We then expand on this by sorting data based on the values/entries in multiple columns. At the end of this lecture you will learn how to arrange your output table based on the values/entries in the column(s) of a table.
-
40
Test your knowledge: Sorting Data By Ordering Columns
In SQL, are you able to sort only on one column or can we sort the data on multiple columns?
-
41
Aggregating Data With 'GROUP BY' - Overview
An introduction to the concept of aggregations using GROUP BY
-
42
Aggregating Data With 'GROUP BY' - Preparing Data
Importing and preparing the data we need for the following videos
-
43
Aggregating Data With 'GROUP BY' - Approach
The approach and considerations when it comes to aggregating data
-
44
Aggregating Data With 'GROUP BY' - Using the COUNT Function
How to aggregate with the COUNT function
-
45
Aggregating Data With 'GROUP BY' - Using the SUM Function
How to aggregate with the SUM function
-
46
Aggregating Data With 'GROUP BY' - Using the AVG Function
How to aggregate with the AVG function (also known as the 'mean average')
-
47
Aggregating Data With 'GROUP BY' - Using the MAX and MIN Functions
How to aggregate with the MAX and MIN functions, including finding the range
-
48
Aggregating Data With 'GROUP BY' - Creating Reports
How to aggregate in order to create meaningful reports for various audiences
-
49
Aggregating Data With 'GROUP BY' - Interpreting Results
Interpreting aggregated results (similar to reading a report)
-
50
Aggregating Data With 'GROUP BY' - Summary
Key take-home points regarding aggregation
-
51
Test your knowledge: Aggregated Data
The following code is present in the SELECT section of a query:
[Employee Name], [Shop Name], SUM([Net Pay]), COUNT([Month]).
Which columns will be present in the GROUP BY statement?
-
52
Filtering Aggregated Data - 'HAVING' Statement Overview
Here we look at how filters are applied to a table after it has been aggregated, by using the HAVING statement
-
53
Filtering Aggregated Data - Using 'WHERE' and 'HAVING' Together
This covers how to apply both the WHERE and HAVING clauses in a SQL query and what the difference is between them. After this lecture, you will understand how to filter on aggregated data and know how to identify whether the WHERE clause or HAVING clause should be used
-
54
Filtering Aggregated Data - Summary
Key take-home points on the HAVING statement
-
55
Test your knowledge: Filtering on Aggregated Data
Are you able to use the WHERE and HAVING clauses in the same query?
-
56
The 'CASE' Statement - Overview
An introduction to the theory of the CASE statement
-
57
The 'CASE' Statement - Preparing Data
A step-by-step video on how to prepare the data for the exercises we will conduct in the next videos
-
58
The 'CASE' Statement - Walkthrough Part A
Part A
This lecture looks at how to create a new column in our table based on certain conditions being met, by using the CASE statement. We will look at how to apply constraints and also look at how the resultant columns which we produce can be used to our advantage when analysing data. After this lecture, you will understand how to utilise constraints and apply conditions to aid you in analysing your data.
-
59
The 'CASE' Statement - Walkthrough Part B
Part B
This lecture looks at how to create a new column in our table based on certain conditions being met, by using the CASE statement. We will look at how to apply constraints and also look at how the resultant columns which we produce can be used to our advantage when analysing data. After this lecture, you will understand how to utilise constraints and apply conditions to aid you in analysing your data.
-
60
The 'CASE' Statement - Filtering with 'CASE'
How to effectively utilise filtering for reports after having used CASE
-
61
The 'CASE' Statement - Additional Considerations
Specific considerations when using CASE, such as dealing with NULLs
-
62
The 'CASE' Statement - Summary
Key take-home points on using the CASE statement
-
63
Test your knowledge: Creating Columns Based on Conditions
When the CASE statement in used, what other statements need to be used?
-
64
Appending Data - Overview
This lecture introduces how to append tables together and the different configurations we can set when appending tables together.
-
65
Appending Data - UNION ALL (Keeping Duplicates)
Practical demonstration: The UNION ALL statement keeps duplicates when appending
-
66
Appending Data - UNION (Removing Duplicates)
Practical demonstration: The UNION statement removes duplicates when appending
-
67
Appending Data - Summary
The key take-home points regarding appending data
-
68
Test your knowledge: Appending Data
If you want to append two tables and delete any duplicate entries between the two table, which statement do you need to use?
-
69
Linking Tables Using 'JOIN' - Overview
In this lecture, you will learn the concept of data table joins.
-
70
Linking Tables Using 'JOIN' - Importing Data
Here we will import all of the relevant files for our analyses
-
71
Linking Tables Using 'JOIN' - Preparing Our Data via Stored Procedures
Here we will prepare the data (via stored procedures) which we imported in the prior lecture
-
72
Linking Tables Using 'JOIN' - Inner Join
An inner join only returns record which are present in all tables
-
73
Linking Tables Using 'JOIN' - Left Join
The left join returns all records in the parent table and only matching records from the right table
-
74
Linking Tables Using 'JOIN' - Right Join
The right join returns only matching records in the left table and all records from the right table
-
75
Linking Tables Using 'JOIN' - Full Outer Join
The full outer join returns all records from all tables
-
76
Linking Tables Using 'JOIN' - Unmatching Outer Join
The unmatching outer join returns only unmatching records from all tables i.e. records which did not successfully join
-
77
Linking Tables Using 'JOIN' - Joining On Multiple Columns (Composite Key)
Using multiple columns in the join key to ensure correct granularity of data
-
78
Linking Tables Using 'JOIN' - Additional Join Keys
-
79
Linking Tables Using 'JOIN' - Summary
Key take-home points on using the various JOIN types via keys
-
80
Test your knowledge: Joining Tables
If you want to join two tables and only have matching entries from both tables and exclude any non-matching entries, which type of join needs to be used?
-
81
SQL Management & Housekeeping - Overview
In this section you will learn simple housekeeping tasks in associated to your tables and databases in SQL.
-
82
SQL Management & Housekeeping - Summary
Summary of the key points from managing databases
-
83
Core SQL: Wrap Up & Congratulations!
Congratulations on completing this part of the course! Here are the next steps for you
