SQL: Data Analytics Applications in the Real World

Here we will introduce the course, ranging from understanding table schemas, to which different technical operations we will perform in SQL

This section goes over how the skills that we learnt in the previous course (SQL & Power BI: Your Data Analytics & Visualisation Journey) and the skills which we will learn in this course will be utilised in real world situations in corporate or industry environments

In this section, we introduce the concept of table schemas and show how tables can be linked together. Students will understand the concept of primary and foreign ‘keys’ and what their importance is

This section builds on from our knowledge on table schemas and goes over how to optimise the keys associated to the tables to meet our analysis requirements, as well as covering row numbers and concatenations

This section will go over a new approach on how to create brand new tables in SQL by using the CREATE TABLE statement. It also covers how to assign the correct data types to the columns when defining the table so that students can do this independently

Building on the foundations of the previous section (Creating New Tables), this section covers how to insert data into our newly created tables by using the INSERT INTO statement so that students gain experience using this function

Now that we have learnt how to create tables and insert data into them, we will learn how to apply different constraints to different columns by using the ALTER TABLE and ALTER COLUMN statements.

The columns which we will be applying constraints to define primary and foreign keys, avoid duplicate values and prevent NULL values being present in the defined columns.

In this section, students will learn how to change or alter tables which are present / already exist in the database. This includes adding and removing columns, changing the data types associated to columns, and defining keys

In this section, we will learn how to create virtual tables in SQL (known as Common Table Expressions or ‘CTEs’) using the WITH statement.

We then move on to how to utilise this functionality in order to provide us with dynamic and flexible coding, as well as covering nested CTEs.

This section introduces the case studies which we will be doing. Now that you have learnt the technical details from the previous sections, let’s now apply them!

Data preparation for Case Study A

This is the first case study where we will be understanding the tables, creating the table schema and providing insights into the data by using analytical techniques, such as creating a master table

Data preparation for Case Study B

This is the second case study where we will be looking at a more complex table schema which consists of three tables. New join concepts are introduced, and more complex analytics is carried out to provide valuable insights

Data preparation for Case Study C

This is the third case study where we will be going over an even more complex table schema consisting of four tables and a more complex relationship between the table keys.

We will be applying complex analytics techniques so you can see how powerful the queries can be, such as creating synthetic primary keys.

Data preparation for Case Study D

This is a bonus case study where we will be seeing how a bank can utilise two different table schemas. Advanced analytics is carried out in this section, applied on Forex and Stock & Index data

We will be wrapping up this course and mentioned what we have learnt!