SQL & Power BI: Your Data Analytics & Visualisation Journey

All 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.

Updated for 2025: How to install SQL Server and SSMS (SQL Server Management Studio) for Windows

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.

This lecture introduces data types and covers the theory of using the SQL Import Wizard. Students do not need to import files/tables during this lecture, as the next lecture covers the practical 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.

Summary of key points regarding importing data

Steps to run stored procedures on a dataset - required for the course exercises.

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.

Pratical demonstration in SQL of the data types

We introduce the concept of ‘NULL’ values, why they need to be considered and what their significance is

Summary of the key points regarding data types

This lecture highlights the reference scripts for this course

We go over how to view data from tables present in the database by using the SELECT and FROM statements

The use cases of renaming columns whilst querying

We cover how simple mathematical and string operations can be applied to different columns in a table

How to sample data, which is useful instead of calling a large number of rows which consumes time and computational power

The pros and cons of querying using specific column names compared to using an asterisk (to call all columns)

Summary of key points regarding the SELECT and FROM statements

This lecture goes over how to apply filters on a table by using the WHERE statement

Practical walkthrough for filtering

Using multiple columns as a filter conditions

Introducing the IN statement in filtering

Introducing the LIKE statement in filtering

Advanced use cases of the LIKE statement in filtering

This covers the significance of the NOT operator

Utilising functions within the filter conditions to introduce a new dynamic

Complex filtering is covered in this lecture

Complex filtering is covered in this lecture

Using these functions will improve your skills as a data analytics specialist

Using underscore characters to filter

Key take-home points regarding filtering using the WHERE statement

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.

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.

An introduction to the concept of aggregations using GROUP BY

Importing and preparing the data we need for the following videos

The approach and considerations when it comes to aggregating data

How to aggregate with the COUNT function

How to aggregate with the SUM function

How to aggregate with the AVG function (also known as the 'mean average')

How to aggregate with the MAX and MIN functions, including finding the range

How to aggregate in order to create meaningful reports for various audiences

Interpreting aggregated results (similar to reading a report)

Key take-home points regarding aggregation

Here we look at how filters are applied to a table after it has been aggregated, by using the HAVING statement

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

Key take-home points on the HAVING statement

An introduction to the theory of the CASE statement

A step-by-step video on how to prepare the data for the exercises we will conduct in the next videos

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.

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.

How to effectively utilise filtering for reports after having used CASE

Specific considerations when using CASE, such as dealing with NULLs

Key take-home points on using the CASE statement

This lecture introduces how to append tables together and the different configurations we can set when appending tables together.

Practical demonstration: The UNION ALL statement keeps duplicates when appending

Practical demonstration: The UNION statement removes duplicates when appending

The key take-home points regarding appending data

In this lecture, you will learn the concept of data table joins.

Here we will import all of the relevant files for our analyses and use conversion scripts. Please follow the table naming exactly as in the videos!

Here we will prepare the data (via stored procedures) which we imported in the prior lecture

An inner join only returns record which are present in all tables

The left join returns all records in the parent table and only matching records from the right table

The right join returns only matching records in the left table and all records from the right table

The full outer join returns all records from all tables

The unmatching outer join returns only unmatching records from all tables i.e. records which did not successfully join

Using multiple columns in the join key to ensure correct granularity of data

Key take-home points on using the various JOIN types via keys

In this section you will learn simple housekeeping tasks in associated to your tables and databases in SQL.

Summary of the key points from managing databases

Congratulations on completing this part of the course! Here are the next steps for you

This video is the introduction to the 'Intermediate SQL' course.

All 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.

In this lecture, we will import the required data into SQL. As we have imported a lot of data in the ‘Core SQL' course, this will build on your foundation of importing data and should be good practice!

This lecture expands our knowledge on NULL values and the different approaches we can take in handling them; we will be using the ISNULL and COALESCE functions to do this. At the end of this lecture, you will have learnt the different approaches in dealing with NULL values and the best approach to take depending on the circumstance.

This lecture goes over how to change data from one type to another type. We will use the CAST and CONVERT functions to do this. By the end of this lecture, you will have learnt how to change numeric, date and string data types, and the leading practices to keep in mind when performing conversions.

This lecture covers the general approach to take and the key factors to look out for when cleansing data in a table. You will see how the knowledge gained in the ‘Dealing with NULL Values’ section and the ‘Converting Data Types’ section can be used in conjunction with each other when cleansing data. Finally, you will be introduced to the REPLACE function.

After this lecture, you will understand the importance of cleansing data and the different approaches you can take when carrying out data cleansing operations.

This lecture goes over how to utilise "window functions" in our SQL queries so that we can have row numbers and rankings in our data set. We will then build on this by using the PARTITION statement; this introduces a segregation to our data when carrying out window functions and mathematical operations. By the end of this lecture, you will have gained knowledge of how window functions work and how to apply them in your queries.

Building on from what we learnt in the ‘Windows Functions’ section, we will be looking at how to set up a running total for every row in the table as a whole, as well as setting up a running total by segregating the data into different sections. By the end of this lecture, you will know how window functions can be taken further to create these types of running totals.

In this lecture, we will look at how we can 'nest' queries to help us solve more complex problems. We will go over general subqueries and how they can be used when filters are applied to a dataset. By the end of this lecture, you will understand how to nest queries to help you solve problems that you will face in your analytics journey.

This lecture looks at how to create ‘virtual tables’ and what their importance is. At the end of this lecture, you will know the difference between a physical and virtual table, as well as when and where to use them.

Here we will look at how to check for duplicate values in a column by utilising aggregations. By the end of this lecture, you will have learnt how to check for duplicate values in a dataset in an effective and efficient manner.

This lecture introduces variables and shows how to add flexibility to our SQL queries. We will then look at how we can utilise variables to script ‘dynamic’ SQL queries. By the end of this lecture, you will have gained an understanding of how to create variables and the different situations in which they can be utilised.

This lecture looks at how code/scripts can be saved directly in the database by using stored procedures. Additionally, we will use variables in stored procedures to see how they are applied. At the end of this lecture, you will know how to create a stored procedure and how to go one step further by utilising variables in them.

In this lecture, we go over the structure of SQL queries. In the ‘Core SQL’ course/section, we learnt how to construct SQL queries, and this walkthrough shows you how everything comes together and the order in which you should enter your statements.

By the end of this lecture, you will know how the more complex SQL queries are structured. Make sure that you keep referring to this walkthrough as it will always help you in ensuring that you are constructing your queries correctly, with the statements being in the right order!

In this lecture, we show you how to connect Excel to SQL and then pull relevant tables through. This is an important functionality to know, especially in order to create front-end user reports from processed tables!

The introduction to the 'Power BI' course.

In this lecture, we show you how to install Power BI. This is simpler than installing SQL and will get you ready for the data visualisation course.

This lecture goes over what data visualisation is and how it can be used to enhance our analysis. The demand for data visualisation has grown exponentially in recent times, so it is an essential part to add to your skillset as a data analyst. By the end of this lecture, you will have an insight into what data visualisation is and what some of the dashboards created in Power BI look like.

This lecture goes over how techniques of a similar nature learnt in the SQL courses can be applied in Power BI when creating data models. You will gain an insight into the data operations which can be carried out in Power BI - for example, creating conditional columns and relating tables.

Covering how one-to-one, one-to-many, and many-to-one relationships work in Power BI

This lecture introduces the ‘backend' of Power BI, where we transform data and carry out the desired operations on a data set. By the end of this lecture, you will understand the importance of the backend and how to access it.

We begin preparing the data for the first dashboard. We will build a foundation on the operations which can be carried out in the backend of Power BI. We will import data and convert data types

Preparing data for the first dashboard, mainly focusing on conditional columns (and comparing with the SQL CASE statement as a reference point)

Preparing data for the first dashboard, mainly focusing on creating tables manually

In this lecture, we introduce the big picture of what our first dashboard will look like over the course of the next few lectures, by utilising visuals such as the clustered, donut and waterfall charts.

Learn how to start building bar charts in Power BI

Here we add donut charts and clustered bar charts to our first dashboard. We also cover the important concept and functionality of 'drilling down' to other dimensions and grains of data.

Here we add the extremely visually appealing and famous 'waterfall chart' - a coveted visualisation!

Here we bring in slicers and aggregated tables to our dashboard, leveraging insights.

Our final lecture for the first dashboard where we add Cards to highlight critical KPIs

In this lecture, we will prepare the data which will be used in the second dashboard. We will explore additional methods on how data can be prepared and manipulated to suit our needs.

We continue preparing the data used in the second dashboard. By the end of this lecture, you will understand how to filter on datasets, creating more complex conditional columns and creating duplicate columns.

We introduce geographical data and utilise it in our dashboard, using the map visualisation (and toggling it in the settings - remember to quit and re-open Power BI after performing this step as explained in the video)

To continue building the second dashboard, we add in treemap and stacked area charts.

To add the final touches to our second dashboard, we introduce the all important 'cards' (which are in essence analysing KPIs).

In this lecture, we will prepare the data for the third dashboard. Building on the foundation and skills we have learned so far, we will look at more advanced ways of preparing and manipulating data. By the end of this lecture, you will know how to create custom columns by carrying out mathematical operations as well as joining tables in the backend, accessing the Model View.

In this lecture, we will create our third and final dashboard, focusing on visuals which are uncommon but very powerful. We start off here creating gauge charts.

Here we continue with the third dashboard by building funnel charts

We add scatter charts, with conditional colouring

We finalise the third dashboard with cards to highlight KPIs

In this lecture, we will go over how to efficiently navigate through the dashboards which you have created! We will learn how to drill down into data and apply filters so that we can gain very useful insights. At the end of this lecture, you will have understand the prerequisite principles of exploring and navigating through Power BI dashboards.

This video covers provides an overview about our new SQL course - now that you are an experienced SQL user, we are offering a discount code for our new course - this is aimed at students looking to develop and apply new data analysis skills on real world scenarios!

If you would like to obtain a discount code for this course, please message us directly and we will send your coupon across to you!