Business
Data & Analytics
SQL
SQL - Master Class For Data Analysis
4.3 (876 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,087 students enrolled

SQL - Master Class For Data Analysis

Writing SQL Server queries for Data Analysis
4.3 (876 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
5,087 students enrolled
Created by Paul Scotchford
Last updated 6/2020
English
English [Auto]
Preview this course
SQL - Master Class For Data Analysis
Current price: $20.99 Original price: $29.99 Discount: 30% off
5 hours left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 8 hours on-demand video
  • 1 article
  • 2 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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 !
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

Hi  

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.

Stay safe and well

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.
Featured review
Gabriel Penate
Gabriel Penate ( 71 courses, 19 reviews )
a year ago
To make this course perfect, I wish you had included a video at the end, summarizing every single skill introduced throughout the course, with a very brief explanation on how to choose each skill for the task at hand.
Course content
Expand all 58 lectures 07:50:51
+ – Getting started
5 lectures 38: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.    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

Introduction
Preview 02:47

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

How to download and install SQL Server 2014 Express
10:41

Sales sample database download and restore instructions.

See Section 8 for the QlikSense download and install video.

How to download the course sample data (from Dropbox)
04:57

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

Navigating SQL management studio
10:38

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

What is a relational database ?
Preview 09:42

Did you pay attention to key points

Pop Quiz - Key points before the next chapter
10 questions
+ – Chapter 2 - SQL Basics
6 lectures 58:12

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

The anatomy of a SQL Query (Primer)
Preview 05:52

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
07:22

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

The SELECT statement
10:23

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)
12:22

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
12:04

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 ?
10:09

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:24

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
04:20

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
11:43

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
11:13

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
08:26

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
05:46

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
06:39

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
06:59

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.

Working with the SELF join (Overview)
05:13

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)
07:29

Lets add another join for fun to provide better actionable insight

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

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)
08:00

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)
08:50

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

A CROSS join can be your buddy (Final)
09:27

Did you find Cross Join and Left Join easy ?

Brain teaser
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)
11:14

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

Working with CROSS and OUTER APPLY (Pt2)
08:05

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)
05:13

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:12:21

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

Download chapter 4 insurance database and restore it via SQL management studio
05:49

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

Counting by using Count() and OVER()
Preview 11:22

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
10:16
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 

Finding the mean with the Avg() function
08:21
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
07:29
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.

Let's visualise this in QlikSense as well, visualisation data will reveal more than a massive row set returned from a query.

Using Max() to find the last value
Preview 05:47
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.

Just how do we calculate a median value ?
07:17
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)
08:07
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
07:53
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 44:33

Sample Google analytics data from my own website traffic analysis

Download and restore the sample Web Analytics database (and scripts)
03:25

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
12:54

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
09:51

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 #1)
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
08:10

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 (Challenge #1 )
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)
10:13

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 (Challenge #1)
2 questions
+ – SQL How To Sessions - Coding more advanced SQL analytics
5 lectures 40:54

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 ?
04:42

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

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

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) ?
04:02

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 analyse customer acquisition in SQL (Pt 1) ?
09:33

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

How to analyse customer acquisition in SQL (Pt 2) ?
11:19
+ – Other stuff you probably should know
11 lectures 01:19:50

How to create databases and tables quickly

Create database and a table from scratch
09:42

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 !

Creating a table from a SQL Query
11:06

What is a view and how do I create it ?

This is DDL actually.

How to create a view
08:14

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

What is a Union query ?
06:42

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 insert statement (crud 1)
09:47

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 update statement (crud 2)
09:10

The 3rd and final CRUD episode

Delete or Truncate is the question !

Truncate is faster, but is limited.

How to code the delete statement (crud 3)
04:46

Date arithmetic with DateDiff() and DateAdd()

What is date arithmetic and how to code it ?
06:48

Discrete grouping of data for analysis using a case statement

Using the case statement to discretize (aka Banding,Bucketizing) data
07:12

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
06:23
How to obtain your certificate
00:00
+ – QlikSense Desktop Installation
1 lecture 12:52

If you plan to use the Qlik Sense visualisations , you will need to install Qlik Sense on your PC

The process is detailed in this lecture

How to download and install the QlikSense desktop software
12:52