SQL - Master Class For Data Analysis

Writing SQL Server queries for Data Analysis

4.85 (1036 reviews)
Udemy
platform
English
language
Data & Analytics
category
instructor
5,710
students
7.5 hours
content
Feb 2021
last update
$74.99
regular price

What you will 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 !

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


Content

Getting started

Introduction
How to download and install SQL Server 2014 Express
How to download the course sample data (from Dropbox)
Navigating SQL management studio
What is a relational database ?
Pop Quiz - Key points before the next chapter

Chapter 2 - SQL Basics

The anatomy of a SQL Query (Primer)
Download chapter 2 sample database and restore it via the SQL Management Studio
The SELECT statement
Practice session using SELECT exercises
The SELECT statement with a WHERE predicate (Condition)
Practice session - using SELECT and the WHERE clause excersises
Let's explore GROUP BY and HAVING to answer a marketing team question
Practice session - Challenging brain teaser excercises
What are data types ?
Pop Quiz - Do you remember the datatypes ?

Chapter 3 - Forming relationships using JOIN's

Download chapter 3 sample database and restore it via SQL management studio
Overview of Chapter 3 database and the PK,FK model
Brain Teaser
Working with the INNER JOIN (JOIN) statement
Prac time
Working with the LEFT OUTER join
Prac time
Working with the RIGHT OUTER join
Prac time
Working with the FULL OUTER join
Prac time
Working with the CROSS join
Prac time
Working with the SELF join (Overview)
Working with the SELF join (Providing initial insight)
Working with a SELF join (Add value to the insight)
Working with a SELF join (Finally the last join)
Prac time
A CROSS join can be your buddy (Pt 1)
A CROSS join can be your buddy (Final)
Brain teaser
Working with CROSS and OUTER APPLY (Pt1)
Working with CROSS and OUTER APPLY (Pt2)
Working with CROSS and OUTER APPLY (Final)
So you want more eh ?

Getting started with SQL for quantitative analytics

Download chapter 4 insurance database and restore it via SQL management studio
Counting by using Count() and OVER()
Prac count()
Adding it all up using the Sum() function
Prac sum()
Finding the mean with the Avg() function
Prac avg()
Using Min() to find first value
Prac min()
Using Max() to find the last value
Prac max()
Just how do we calculate a median value ?
Prac median
Using the CASE...END statement (if,then,else)
Prac case
Show me how to calculate quartiles
Prac quartile

Immerse yourself into SQL analytics project scenarios

Download and restore the sample Web Analytics database (and scripts)
Project 1 - Website visitor analysis
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 analysis
Prac Project 2 (Challenge #1)
Project 3 - Ranking customers and products by sales
Prac Project 3 (Challenge #1 )
Project 4 - Category performance analysis (time series)
Prac Project 4 (Challenge #1)

SQL How To Sessions - Coding more advanced SQL analytics

What is cohort analysis ?
How do I do cohort analysis in SQL (Pt 1) ?
How do I do cohort analysis in SQL (Pt 2) ?
How to analyse customer acquisition in SQL (Pt 1) ?
How to analyse customer acquisition in SQL (Pt 2) ?

Other stuff you probably should know

Create database and a table from scratch
Creating a table from a SQL Query
How to create a view
What is a Union query ?
How to code the insert statement (crud 1)
How to code the update statement (crud 2)
How to code the delete statement (crud 3)
What is date arithmetic and how to code it ?
Using the case statement to discretize (aka Banding,Bucketizing) data
How to import raw data from files
How to obtain your certificate

QlikSense Desktop Installation

How to download and install the QlikSense desktop software

Screenshots

SQL - Master Class For Data Analysis - Screenshot_01SQL - Master Class For Data Analysis - Screenshot_02SQL - Master Class For Data Analysis - Screenshot_03SQL - Master Class For Data Analysis - Screenshot_04

Reviews

Bikram
March 5, 2023
I absolutely loved the course by instructor Paul Scotchford, as it provides excellent content for anyone seeking to enhance their skills in data analysis and SQL programming. The course delves into the intricacies of data analysis, enabling learners to identify patterns and behaviours within datasets. It covers expert-level coding techniques for quantitative analysis using complex functions, while also emphasizing the importance of developing a solid foundation in database management. Additionally, the course extensively covers the relational database model and equips learners with the ability to make data-driven decisions in their respective professional settings. With comprehensive explanations and practical exercises, including complex apply joins, this course is highly recommended for aspiring data analysts seeking proficiency in SQL programming.
Jeremie
February 10, 2022
Paul, I'm a quarter of the way through, and love the way you're teaching. I'm currently a BA looking to get into analytics, and this course has helped me extract the data I need from sql to do analysis in excel and create some dashboard views. Really pleased thus far.
Jennipher
June 8, 2021
I chose this course because my role changed and I needed to upskill. The databases used in this course are great and applicable to a variety of verticals like insurance or an online retailer. Thankfully, we never see the old go-to Adventure Works or Employee.employee examples that have been overdone elsewhere. The course is well organized up until the final section, but if you've gone through the course, it will be easy to pick up. I learned quite a bit and think you will too! Paul is very responsive to questions, so if you get stuck don't be kept back thinking you won't get your question answered until next week.
Evija
April 20, 2021
Paul sure knows his thing, and I can see he is passionate about data analysis. I loved that the course is full with pracs where I could see if I fully understood what was done in the video lectures. I loved that during the course I could get the real-life feeling that I am already working at my new job because there were various databases prepared and I could do real analysis with real data, not just type the code. This course is stuffed with useful staff and I definitely recommend it. Thank you, Paul!
Francis
March 18, 2021
The course was fantastic. I had some prior knowledge of SQL but I really enjoyed the way Paul teaches the foundations. The approach he takes to building a query from scratch was extremely helpful. I've promoted this course within my work team and will suggest it to anyone who wishes to get into SQL/analytics.
David
February 22, 2021
The course is great. Very informative and to the point. Teaches different methods to the same outcome which is always useful to know.
Nadezhda
January 2, 2021
The course is so fascinating and well presented that I looked over half of it in one breath. It gives information about many different types of analysis. The various exercises to it really helps to practice and improve what has been learned.
John
December 16, 2020
Paul's teaching style is very clear and methodical. The exercises are short and simple to give you the core of what you need to know about all of the concepts in easy bite-sized sections.
Buyinza
December 9, 2020
This courses is well presented and easy to follow . All the topics are logical and well organised , a great courses for any one who wants to make a start
Beth
February 9, 2018
very clear and concise without any extra fluff. Only downside, is this would be great if you could choose your database. For example...I am running MySQL and MySQL workbench on a Mac, so would prefer to be able to choose db.
Kevin
February 3, 2018
Not sure if it is the Aussie accent of Paul, but they way he speaks in this course seems to stick a lot more in my mind. To be fair, it looks like Paul puts in a lot of effort to prepare the course content and presents it in a comfortable pace and zooms into detail where necessary. Loving this course thus far.
Araya
February 1, 2018
i like the Quiz, which is a gr8 way to evaluate self and point out areas to focus on, the lecture volume is a little less
Namazbai
January 18, 2018
Explanations are great and easy to understand. The lecturer teaches you to see the bigger picture behind the trees and makes the main concept lucid.
David
January 16, 2018
i thought this was a very good course, certainly quite comprehensive, with some solid exercises to help the learning process. I liked some of the detailed explanations and the manner in which the instructor built "foundational" queries and built the solutions bit by bit, very helpful!
Balazs
January 11, 2018
Well organized course so far, I'd recommend it. The pace is a bit slow, so listening at 1.25x speed is recommended! The FAQ could be added to the videos (e.g. it is necessary to select the database on the top before executing query you will get invalid object error) The early rating request is annoying but it is a fault of Udemy I guess.

Charts

Price

SQL - Master Class For Data Analysis - Price chart

Rating

SQL - Master Class For Data Analysis - Ratings chart

Enrollment distribution

SQL - Master Class For Data Analysis - Distribution chart
573008
udemy ID
8/4/2015
course created date
11/22/2019
course indexed date
Bot
course submited by