SQL Master Class For Data Analysis
4.7 (109 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
841 students enrolled
Wishlisted Wishlist

Please confirm that you want to add SQL Master Class For Data Analysis to your Wishlist.

Add to Wishlist

SQL Master Class For Data Analysis

Mastering SQL Beyond The Basics
4.7 (109 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
841 students enrolled
Created by Paul Scotchford
Last updated 4/2017
Current price: $10 Original price: $40 Discount: 75% off
1 day left at this price!
30-Day Money-Back Guarantee
  • 7.5 hours on-demand video
  • 1 Article
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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
  • Be a master at all types of table joins
  • Answer those gnarly interview questions, yes we will always have those !
View Curriculum
  • As you will learn the A-Z here, start easy then boom - hard work, no prior knowledge required
  • Only have a Mac ? You can use Virtual Box, Windows Server Trial edition and SQL 2014 or 16 express.


A few days ago (15th Apr) a student of this course sent me this message, which sums up exactly what I aim to achieve in all my course deliveries , and that is about using real world scenarios to prepare you for the type of work that you expect to perform at work ... 

"... I really liked the way you have explained the things like query from business and how we can answer it using SQL.. this kind of approach I never seen in any of course in udemy .. hope you going to add more and more such a realistic scenarios which could help us visualising real time business scenarios while learning the code… Thanks a lot "

SQL, Structured Query Language, when interacting with a relational database e.g. OLTP ,data warehouse, data mart etc, then we will always use SQL as the primary query language, this is how we connect and analyse the data before going further with other tools such as  'R' , SAS, Qlik etc to do the heavy lifting such as forecasting or data visualizations etc.  

I will teach you SQL from the ground up, and the SQL that other courses don't teach , after all , if you know more, you can do more! 

Understanding how to construct smarter SQL to bring the punk rebel out in you, making those discoveries that provide insight to influence business decisions. This can only be done if you know your SQL thoroughly and how to do it intelligently !

Ask yourself these simple questions ...

Do you know all of the TABLE JOIN types ? 

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

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

Where do you use a ROLLUP statement and why ? 

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

And I will teach you all of this and lots of other great stuff too, which will have you writing smarter SQL in no time at all.

In addition, the course has all of 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 ...

  • Sample databases for you to keep and use
  • All SQL scripts shown in the course are there for you to keep and use
  • Project practice scripts for you to keep and use
  • Quizzes and practice work (mini projects) if you want to do them, no pressure
  • Data viz apps used for demonstrating what the data will present as, are included too
  • No over the top geek speak here, keeping the tech language to an absolute minimum

How does this course flow?

Initially the course will explain the foundation SQL that everyone accessing relational databases needs to know, then we progress into more advanced SQL that teaches you the experts approach to writing SQL queries for data analysis.


  • The videos in here are all 1080p HD, but will also run on 720p if that's what you select on the browser video player during the course delivery
  • All the work you do is in SQL Server 2014 express which is totally free to download ,install and use you don't have to spend anything (ok, you spent a few bucks on the course) and the UI is nice to use too with built in help and intellisense (it's a dynamic code  checker/helper).
  • SQL Server is used pretty much every where, so when you learn SQL  here, you can be sure that you can use every where.
  • Heard of ANSI SQL? The standard for SQL, I teach you SQL that is ANSI standard, no exotic stuff here that cannot be used elsewhere, just great solid real world SQL taught
  • I will lead you through the download and install process for SQL Express 2014, which does not take much time at all and is actually easy to do.
  • Questions ? no problem, just ask in the course and I respond as quickly as I can, it's a time zone thing, I'm in Australia!
  • Yes we use SQLServer, but that is NOT what I am teaching you per se, we could have just as easily used MySQL,Oracle,Postgres etc because we are learning SQL (ANSI SQL) above all else.
  • The data visualisations are all done in QlikSense and are there for you to download from Dropbox, as part all of the downloads in the course.

Who is the target audience?
  • Analysts (Data/Scientists)
  • DBA's
  • Job hunters
  • Graduates
  • Students
  • Technical marketing people
  • Those looking to acquire new skills
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 57 Lectures Collapse All 57 Lectures 07:37:30
Getting started
5 Lectures 42:54


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



Preview 03:28

Step by Step instruction for download and installation of SQL Server 2014 from Microsoft

How to download and install SQL Server 2014 Express

Sales sample database download and restore instructions

How to download the course sample data (from Dropbox)

Getting the feel for SQL Management studio to write our queries.

Navigating SQL management studio

There are petabytes of data still stored in Relational Databases, lets have a look at what a relational database is.

Preview 09:40

Did you pay attention to key points

Pop Quiz - Key points before the next chapter
10 questions
Chapter 2 - SQL Basics
6 Lectures 58:17

Pivotal to your learning is understanding the anatomy of a SQL Query, and what makes it tick

The anatomy of a SQL Query (Primer)

Sales sample data to analyse, here you will learn about restoring a database backup.

The script files are also included in Dropbox for the Select statement lectures, to download these, select the Direct Download option in Dropbox, after download move the files to your "C:\SQL for punk data analytics\Chapter 2 data and scripts" folder .

Download chapter 2 sample database and restore it via the SQL Management Studio

You will always use the SELECT statement to retrieve data, this lecture starts your SQL journey

The SELECT statement

This is a prac, you will need to write queries based on what you were taught in these lectures to answer the questions.

Practice session using SELECT exercises
5 questions

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.

The SELECT statement with a WHERE predicate (Condition)

This is a prac, you will need to write queries based on what you were taught in these lectures to answer the questions.

Practice session - using SELECT and the WHERE clause excersises
5 questions

Let us answer a question for the marketing team to assist with a new campaign, we use Group By to provide some analytics, and then we shall visualise the data using QlikSense.

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.

Let's explore GROUP BY and HAVING to answer a marketing team question

This is a prac, you will need to write queries based on what you were taught in these lectures to answer the questions.

Practice session - Challenging brain teaser excercises
5 questions

A brief overview of data types and using the CAST function to convert from one type to another

What are data types ?

5 quiz questions to see if you paid attention

Pop Quiz - Do you remember the datatypes ?
5 questions
Chapter 3 - Forming relationships using JOIN's
16 Lectures 02:03:11

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.

Download chapter 3 sample database and restore it via SQL management studio

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.

Overview of Chapter 3 database and the PK,FK model

Just checking the model overview kept you awake !

Brain Teaser
10 questions

Learn how to form a JOIN aka INNER JOIN, they are functionally equivalent.

Working with the INNER JOIN (JOIN) statement

Create a JOIN on the tables Product and ProductSubcategory, select the ProductName,Color and ProductSubcategoryName where the subcategory is 'Jerseys'

Prac time
1 question

Understanding what the LEFT OUTER JOIN does

Working with the LEFT OUTER join

Create a LEFT JOIN on tables Currency and CurrencyHistory , select the CurrencyName and EndOfDayRate where the CurrencyName is 'Argentine Peso'

Prac time
1 question

Understanding what the RIGHT OUTER JOIN does

Working with the RIGHT OUTER join

Create a RIGHT JOIN on tables Product and ProductSupplier , select the ProductName and SupplierKey where the product name is 'Mountain-300 Black, 48'

Prac time
1 question

Understanding what the FULL OUTER JOIN does

Working with the FULL OUTER join

Create a FULL JOIN on tables Supplier and ProductSupplier selecting Supplier and the average lead time for the product supplier

Prac time
1 question

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.

Working with the CROSS join

Create a CROSS JOIN on tables Product and Product inventory selecting Product and Unit balance for the 30 April 2013

Prac time
1 question

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.

Preview 05:11

Answering a business question using the SELF join, here we have a bit more fun with complex joins

Working with the SELF join (Providing initial insight)

Lets add another join for fun to provide better actionable insight

Working with a SELF join (Add value to the insight)

This is the final join to provide enough insight to the product manager in this scenario

Working with a SELF join (Finally the last join)

Create a SELF JOIN on tables ProductSupplier where 1 product has more than one supplier reduce the list to product 405

Prac time
1 question

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 (Pt 1)

Bring it all together and provide the answer to the marketing department

A CROSS join can be your buddy (Final)

Did you find Cross Join and Left Join easy ?

Head banger
5 questions

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 (Pt1)

Now replace the Correlated Sub Query construct with the OUTER APPLY and compare the difference

Working with CROSS and OUTER APPLY (Pt2)

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.

Working with CROSS and OUTER APPLY (Final)

APPLY your understanding and don't be Cross

So you want more eh ?
5 questions
Getting started with SQL for quantitative analytics
9 Lectures 01:09:59

Insurance sample data to analyse, sample database download and restore walkthrough

Download sample insurance database

Use the Count() function to provide count aggregations and see how we can use partitioning in the count.

Preview 11:18

Write a query to return the count of claims where the notification date is in the year 2014 and the claimants age is between 33 and 48

Prac count()
1 question

It does add up, really ! show me the way and mix it up with other aggregations to understand how this all works together.

Adding it all up using the Sum() function

Write a query to aggregate the Claim paid for the year 2010 where the claimants reside in postal code 4061, ensure Claim Cause is returned in the query
Prac sum()
1 question

Finding the mean of a rowset using the Avg() function, an above average lecture 

Find the mean with the Avg() function

Write a query to find the average Claim Paid amount and Total Death Cover amount where claimants have insurance cover for death in the underwriting year of 2012 and were paid (hint status). The claim type to return is 'DTH' and the Cause Category is 'FATALITY'
Prac avg()
1 question

Being minimalistic using the Min() function.

Min() and Max() contribute to finding ranges in your data.

Using Min() to find first value

Write a query to find the minimum salary and date joined fund for the members with the status of '2) Medium Earner'
Prac min()
1 question

Maxing it out with the Max() function

Min() and Max() contribute to finding ranges in your data.

Using Max() to find the last value

Write a query to return the maximum IP cover premium for the underwriting year of 2012
Prac max()
1 question

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.

How the heck do I calculate a median value ?

Write a query to sum all claim paid amounts for the cause of 'Shoulder Injury' and calculate the Median claim paid amount for this cause
Prac median
1 question

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

Using the CASE...END statement (if,then,else)

Write a query to use a search case to create an abbreviated band where the age band is '(49 - 58) Baby boomers' , set the new band equal to 'Boomer' and ensure the else is included for the non matched age bands otherwise a NULL will appear in the band value , include the member key in the column and order by this key
Prac case
1 question

It's very easy to calculate quartiles in SQL to provide insight across grouped data.

Show me how to calculate quartiles

Write a query that will provide a list of Occupations, Purchase Total across countryregions then calculate the quartile sales for each occupation
Prac quartile
1 question
Immerse yourself into SQL analytics project scenarios
5 Lectures 43:19

Sample Google analytics data from my own website traffic analysis

Download and restore the sample Web Analytics database (and scripts)

A mini project to provide data for marketing about their website traffic

You will get to practice 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 trick in this lecture.

Project 1 - Website visitor analysis

Page views vs bounce rate by day

Construct an ordered (by DateKey asc) query to return the following metrics…

1: DateKey

2: Sum of Page views

3: Mean of Bounce rate pct

Prac Project 1 (Challenge #1)
1 question

Page views vs bounce rate by month

Construct an ordered (by MonthYearName asc) query to return the following metrics…

1: MonthYearName

2: Sum of Page views

3: Mean of Bounce rate pct

Prac Project 1 (Challenge #2)
1 question

Average session duration vs returning visitor by week

Construct an ordered (by Year,Week asc) query to return the following metrics …

1: YearNum

2: WeekNum

3: Average session duration (in seconds)

4: Count of ‘Returning visitor’

Prac Project 1 (Challenge #3)
1 question

New users vs page views by week 

Construct an ordered (by Year,Week asc) query to return the following metrics …

1: YearNum

2: WeekNum

3: Sum of new users

4: Sum of Page views

Prac Project 1 (Challenge #4)
1 question

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?

Project 2 - Insurance claims margin analysis

Construct a query to provide insight as to the profitability of the DTH insurance for the years 2012 to 2014.

You will need to compare claim paid for these years to the premium collected overall for these years.

The query has to return the following metrics …

1: Underwriting year

2: Claim type (DTH)

3: Claim count (DTH)

4: Total Death premium value

5: Count of policy holders (Death policies)

6: Total claim paid (DTH)

7: The margin value (Profit)

Prac Project 2 (Challenge yourself)
3 questions

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.

Project 3 - Ranking customers and products by sales

Construct a query to return a RANKED product sales value, by product category list to provide insight into the best and least performing products for the period of December 2013

The query has to return the following metrics …

1:  Product category

2: Product name

3: Total sales

4: Product rank

Prac Project 3 (How do you Rank ? )
3 questions

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

Project 4 - Category performance analysis (time series)

Construct an ordered (by Order date, Category, Product asc) query to provide insight into the performance of daily sales across product categories and products for the year 2013

The query has to return the following metrics …

1: Purchase date

2: Product category

3: Product name

4: Date the product was first sold

5: Total sales

6: Date the product was first stocked

Prac Project 4 (How do you perform ?)
2 questions
More advanced analytics using SQL
5 Lectures 40:44

Firstly I am presenting a quick brief to show you what Cohort analysis is, and why it is valuable in today's business.

What is cohort analysis ?

Prepping our query step by step and understand the derived table concept 

How do I do cohort analysis in SQL (Pt 1) ?

Let's start using date arithmetic to calculate customer life cycles, and afterwards view the cohort behaviours 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.

How do I do cohort analysis in SQL (Pt 2) ?

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 do customer acquisition in SQL (Pt 1) ?

Deep dive using LAG() and OVER in the query, LAG() obviates the need for a self join in this query

How to do customer acquisition in SQL (Pt 2) ?
Other stuff you really need to know
11 Lectures 01:19:06

How to create databases and tables quickly

Create database and a table from scratch

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 !

Create a table from a SQL Query

What is a view and how do I create it ?

This is DDL actually.

How to create a view

Understanding a union and how to use it in a query, don't confuse this with a Join !

What the heck is a union ?

CRUD , a mini series on what it is ...

Start here with ... Insert data using a "query" and the "simple values"  method

How to use the insert statement (crud 1)

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 use the update statement (crud 2)

The 3rd and final CRUD episode

Delete or Truncate is the question !

Truncate is faster, but is limited.

How to use the delete statement (crud 3)

Date arithmetic with DateDiff() and DateAdd()

What is date arithmetic and how to do it ?

Discrete grouping of data for analysis using a case statement

Using the case statement to discretize (Band) data

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 import raw data from files

How to get your Certificate Of Achievement
About the Instructor
Paul Scotchford
4.5 Average rating
1,240 Reviews
20,514 Students
3 Courses
Data Analyst and Qlik Expert


I have been a Data Analyst/Engineer for over 20 years specialising in SQL Data Analysis and Development.

I am also an experienced developer in data visualisation using QlikView and QlikSense and my work is predominantly in consulting and specialist analysis projects.

I do have a passion for sharing knowledge with anyone that is interested and wants to learn more about the field of Data Engineering, Visualisations and Quantitative Analysis.

Keep in touch.