Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

SQL Master Class For Data Analysis

Name: SQL Master Class For Data Analysis
Rating: 4.8 (1058 reviews)

Learn to write T-SQL queries for Data Analysis in a day. See how the results are easily visualised using Qlik Sense

Created byPaul Scotchford

Last updated 3/2026

English

What you'll learn

Install and use SQL Server database engine and studio
Analyse data looking for patterns and behaviours
Expertly code quantitative analysis using SQL
Write SQL using LAG(), NTILE(), OVER, PARTITION logic and many more analytics functions
Understand Primary and Foreign keys
Understand the difference between DML and DDL
Understand the relational database model
Make data driven decisions in your enterprise
Master all types of table joins (incl. Complex Apply joins)
Answer those gnarly interview questions, yes we will always have those !

Course content

8 sections • 60 lectures • 7h 35m total length

Introduction2:45
G'day,
SQL is the everyday language used by anyone working with data.
Look at any knowledge (data analyst etc.) worker job advertisement and SQL is on the list of skills, most of the time the SQL skills have to be better than basic.
This course will teach you how to be expert and work smarter with SQL.
All the SQL you learn here is covered in detail, not 2 or 3 minute overviews, but real world code for real world analysis, hence you learn from the start to advanced, if you have never used SQL then this course is for you, if you are already have started with SQL then this course is still for you as you will extend your skills from here, everyone will benefit from this course.
Have you seen a course that teaches you all JOIN types for example, including APPLY, which is so powerful to use?
Can you write a running total in a single line of SQL, yep! I teach you this too and a ton of other great stuff too.
Also the course has all the downloads you need so you can be hands on in every lecture , so don't just watch, roll your sleeves up and get involved hands on with ...
1.    Sample databases
2.    All source SQL scripts
3.    Project prac scripts
4.    Quizzes and more importantly prac work (mini projects)
5.    Qlik Sense data visualization apps used for demonstrating what the data will present as , are included
In addition, the skills you gain here are mobile, which means you are learning ANSI compliant SQL and that enables you to write SQL in other SQL based products!
Join me and accelerate your SQL skills to the next level of expert.
And my support for your Q&A is always available, buying my course also buys my support.
See you in the course.
Regards
Paul
How to download and install SQL Server Express10:04
Step by Step instruction for download and installation of SQL Server 2014 from Microsoft, any later version will work for the database(s) in this course
How to download the course sample data (From Here)3:31
Sales sample database download and restore instructions.
Note that the dropbox location has been removed and all files (1 zip file) are direct from the lecture (i.e. Stored in Udemy)
See Section 8 for the QlikSense download and install video.
Navigating SQL Management Studio8:57
Getting the feel for SQL Management studio to write our queries.
What is a relational database ?9:40
There are petabytes of data still stored in Relational Databases, lets have a look at what a relational database is.
Pop Quiz - Key points before the next chapter

The anatomy of a SQL Query (Primer)5:19
Pivotal to your learning is understanding the anatomy of a SQL Query, and what makes it tick
Download chapter 2 sample database and restore it via the SQL Management Studio5:37
Sales sample data to analyse, here you will learn about restoring a database backup.
Download the zip file that contains the database and sript files to use in this section/chapter
The SELECT statement10:14
You will always use the SELECT statement to retrieve data, this lecture starts your SQL journey
Practice session using SELECT exercises
The SELECT statement with a WHERE predicate (Condition)12:07
How to filter the data inside the SELECT statement using the WHERE clause , we also look at using LIKE and comparison operators in our predicate.

We'll also mix these up and look at the OR statement for alternate condition checking.
Practice session - using SELECT and the WHERE clause excersises
Let's explore GROUP BY and HAVING to answer a marketing team question9:58
Let us answer a question for the marketing team to assist with a new campaign, we use Group By to provide some analytics.
Here we shall look at finding the Mean and provide a filter using the HAVING clause in the Group By and we shall also look at using the Rollup feature to provide group sub totals.
The included Qlik Sense app visualizes this lecture query results
Practice session - Challenging brain teaser excercises
What are data types ?9:47
A brief overview of data types and using the CAST function to convert from one type to another
Pop Quiz - Do you remember the datatypes ?

Download Section 3 Database & Restore via SQL Management Studio3:47
A Sales sample database to analyse, relationship (PK/FK) modeled database for download. This will enable you to understand relational models if you have never encountered one before.
Overview of Chapter 3 Database and the PK,FK model11:42
Primary and Foreign keys have been added, this enables a clearly defined data model, which assists with understanding the way data is related, mostly all SQL based databases will be the result of a model design.
Let's see how Primary and Foreign keys work in a relational database.
Brain Teaser
Working with the INNER JOIN (JOIN)11:05
Learn how to form a JOIN aka INNER JOIN, they are functionally equivalent.
Prac time
Working with the LEFT OUTER (JOIN)8:25
Understanding what the LEFT OUTER JOIN does
Prac time
Working with the RIGHT OUTER (JOIN)5:46
Understanding what the RIGHT OUTER JOIN does
Prac time
Working with the FULL OUTER (JOIN)6:37
Understanding what the FULL OUTER JOIN does
Prac time
Working with the CROSS (JOIN)6:58
Cartesian joins are useful if used correctly.
Most analysts I know tend to avoid Cartesian joins, they were educated to avoid them, especially due to the mass of data returned from a badly formed join, don't worry they are very useful if constructed carefully.
How to make your enterprise DBA cross ! Form a bad Cross Join :)
See the lecture "A cross join can be your buddy" which teaches you to not fear them and use them correctly.
Prac time
Working with the SELF JOIN (Overview)5:07
Explaining how SELF join the self join works, which is a table join on itself.
As an analyst I have another trick up my sleeve to avoid a self join and a bunch of code, I will show you this later in the course, just a single line of code can do it.
Working with the SELF JOIN (Providing initial insight)7:26
Answering a business question using the SELF join, here we have a bit more fun with complex joins
Working with the SELF JOIN (Add value to the insight)4:24
Lets add another join for fun to provide better actionable insight
Working with the SELF join (Finally the last join)7:56
This is the final join to provide enough insight to the product manager in this scenario
Introducing you to the UNION clause
Prac time
A CROSS JOIN can be your buddy - Part 18:44
The marketing manager requested a list of all the product sales by territory and for a specified date range, but also wants to report the products that were not sold!
This is how to use a well formed cross join to your advantage and keep the DBA off your back!
DBA's are a great bunch (I have worked as a DBA before), but clog their server with a badly performed query and they go from Dr Jekyll to Mr Hyde and the stress levels rise.
A CROSS JOIN can be your buddy - Part 29:17
Bring it all together and provide the answer to the marketing department
Brain teaser
Working with CROSS and OUTER APPLY - Part 111:13
The promotions manager wants to know if customers purchased products again during a specific promo month and if so does it correlate to the promo ?

But first we shall evaluate if a Correlated Sub Query construct will answer the question.
A sub query aka Correlated Sub Query, is messy in a large query body, and if you have more than one then it's potentially going to be an issue!
Working with CROSS and OUTER APPLY - Part 28:04
Now replace the Correlated Sub Query construct with the OUTER APPLY and compare the difference
Working with CROSS and OUTER APPLY - Final5:12
Dude, what the heck does the CROSS APPLY do ? We'll explore this now !
APPLY's are in many instances overlooked and often I see forum members looking for a reason to use them, but they provide enormous power and elegance in your query.
So you want more eh ?

Download Section 4 (Insurance) Database & Restore via SQL Management Studio3:19
Insurance sample data to analyse, sample database download and restore walkthrough
Counting by using Count() and OVER()11:13
Use the Count() function to provide count aggregations and see how we can use partitioning in the count.
Finding duplicates is an example of using Having as a filter in the Group By clause
Prac count()
Adding it all up using the Sum() function10:13
It does add up, really ! show me the way and mix it up with other aggregations to understand how this all works together.
The included Qlik Sense app visualizes this lecture query results
Prac sum()
Finding the mean with the Avg() function8:16
Finding the mean of a rowset using the Avg() function, an above average lecture
The included Qlik Sense app visualizes this lecture query results
Prac avg()
Using Min() to find first value7:27
Being minimalistic using the Min() function.
Min() and Max() contribute to finding ranges in your data.
Prac min()
Using Max() to find the last value5:43
Maxing it out with the Max() function Min() and Max() contribute to finding ranges in your data.
The included Qlik Sense app visualizes this lecture query results
Prac max()
How do we calculate the Median value ?7:16
Using a standard statistical SQL function to calculate a median value
The median value is the middle number in an ordered number set, it is very easy to get the median with a single line of code.
Prac median
Using the CASE...END statement (if,then,else)7:50
How to work with "Simple case" and "Search case" statements, what's the difference ?

Using case allow you to make choices using conditions during the execution of your SQL
Prac case
Calculating Quartiles using the NTILE() function7:47
It's very easy to calculate quartiles in SQL to provide insight across grouped data.
The included Qlik Sense app visualizes this lecture query results
Prac quartile

Download and restore the sample Web Analytics database (and scripts)3:39
Sample Google analytics data from my own website traffic analysis
Project 1 - Website visitor analysis12:43
A mini project to provide data for marketing about their website traffic
You will get to code joins, aggregations and a trick for working with time data in session duration, when you receive data that contains time values, you cannot simply work on these like you would numeric data, I will show you a trick to overcome this.
The included Qlik Sense app visualizes this lecture query results
Prac Project 1 (Challenge #1)
Prac Project 1 (Challenge #2)
Prac Project 1 (Challenge #3)
Prac Project 1 (Challenge #4)
Project 2 - Insurance claims margin analysis9:46
A mini project to build margin analysis across insurance and claims
You will get to practice your APPLY, aggregations and join predicates. Are you up for the challenge?
The included Qlik Sense app visualizes this lecture query results
Prac Project 2 (Challenge #1)
Project 3 - Ranking customers and products by sales8:06
Using sales data, let's look at using Ranking to identify the best and least performing Customers and products.
Hit the joins again and practice the Over Partition technique as part of the Ranking code.
The included Qlik Sense app visualizes this lecture query results
Prac Project 3 (Challenge #1 )
Project 4 - Category performance analysis (time series)10:10
Providing time series analytics across product categories and in this case it was OK to revisit a correlated sub query to find a single value of significance.
The included Qlik Sense app visualizes this lecture query results
Prac Project 4 (Challenge #1)

Cohort analysis explained4:23
Firstly I am presenting a quick brief to show you what Cohort analysis is, and why it is valuable in today's business.
How do I code cohort analysis in SQL ? - Part 111:17
Prepping our query step by step and understand the derived table concept
How do I code cohort analysis in SQL ? - Part 23:59
Let's start using date arithmetic to calculate customer life cycles, and afterwards view the cohort behaviors in a chart.
Did you know that you can use functions in the group by clause ? Take a look in the lecture to see how it's done.
The included Qlik Sense app visualizes this lecture query results
How to analyze customer acquisition in SQL - Part 19:28
Leveraging what we learned from the cohort analysis query and enhance the query for more analytics, we also will explore the LAG() and OVER() clauses in these 2 lectures
How to analyze customer acquisition in SQL - Part 211:17
Deep dive using LAG() and OVER in the query, LAG() obviates the need for a self join in this query
The included Qlik Sense app visualizes this lecture query results

Create database and a table from scratch9:41
How to create databases and tables quickly
Create a table using a SQL Query (Insert into)11:02
Using a query to create a table, select a rowset from different database , cast the datatypes and populate the new table all in the same query, how to have fun !
Simplify data retrieval by creating views8:07
What is a view and how do I create it ?
This is DDL actually.
The Union query explained6:40
Understanding a union and how to use it in a query, don't confuse this with a Join !
How to code the INSERT statement (crud 1)9:45
CRUD , a mini series on what it is ...

Start here with ... Insert data using a "query" and the "simple values" method
How to code the UPDATE statement (crud 2)9:10
The second episode of our CRUD mini series ...

Update is simple, and you can do an update via a JOIN statement, come in and checkout how !
How to code the DELETE statement (crud 3)4:43
The 3rd and final CRUD episode

Delete or Truncate is the question !
Truncate is faster, but is limited.
Date arithmetic coding explained6:39
Date arithmetic with DateDiff() and DateAdd()
Use the case statement for discretization (aka Banding, Bucketizing)7:10
Discrete grouping of data for analysis using a case statement
How to import raw data from files6:10
As a data analyst I often receive data as CSV's, Excel files etc, how do I import it all into a database ?
How to get your score based certificate0:01
Please follow the instructions in the PDF to retrieve your training certificate

Requirements

Only have a Mac ? You can use Virtual Box, Windows Server Trial edition and SQL 2014 Express or later
Want to learn from a rated Top-responding instructor (Yes, I answer Q&A within 24 hours or less!) ?
As you will learn the A-Z here, start easy then boom - the hard work of data analysis starts
This course is presented using SQLServer 2014 Express which is free from Microsoft (& later versions e.g 2016 Express can be used)

Description

Greetings,

With so much data stored in SQL databases, how can we get at it ?

You use SQL to get at this data, no matter what the SQL Database product is (e.g SQL Server, Oracle and many more).

Coding SQL to analyse data can be a daunting and complex task, hence the reason for building this course.

In this course I will teach you how code SQL to perform data analysis in order to answer complex questions that you will encounter daily in your job.

Ask yourself these simple questions ...

Do you know all of the TABLE JOIN types that can be applied in a SQL query ?

Have you ever used OUTER APPLY, which is so easy and powerful to use ?

Can you write a running total in a single line of SQL ?

Where do you use a ROLLUP statement and why would you use it ?

What is LAG() ?

If you could not answer these simple questions then this course will definitely change all of that for you.

You definitely should know all of this to survive the SQL environment you work in !

All course materials are provided e.g. Sample databases (with real data), code scripts, practice exercises and quizzes.

Don't forget, there is a 30 Day money back guarantee if this course does not deliver what you expected.

Best Regards

Paul

Data Analyst and Visualisation Engineer

Who this course is for:

Data Analysts and Data Scientists
Students
DBA's
New graduates
Technical marketing people
Those looking to acquire new skills
Research shows students don’t learn by hearing or seeing, they learn by doing, a model often called active learning.

SQL Master Class For Data Analysis

What you'll learn

Explore related topics

Course content

Getting started5 lectures • 35min

SQL Basics6 lectures • 53min

Forming relationships using JOIN's16 lectures • 2hr 2min

Getting started with SQL for quantitative analytics9 lectures • 1hr 9min

Immersive SQL analytics project scenarios5 lectures • 44min

SQL How To Sessions - Coding more advanced SQL analytics5 lectures • 40min

Other stuff you should really know11 lectures • 1hr 19min

Qlik Sense Desktop Installation3 lectures • 13min

Requirements

Description

Who this course is for: