SQL : Master class for SQL data analytics

Learn to code smarter SQL and get more from your data exploration and discovery
4.8 (30 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.
256 students enrolled
$19
$40
52% off
Take This Course
  • Lectures 56
  • Length 7.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2016 English

Course Description

G'day,

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.

FAQ's:

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



What are the requirements?

  • As you will learn the A-Z here, no prior knowledge required

What am I going to get from this course?

  • Analyse data looking for patterns and behaviours
  • Expertly code quantitative analysis using SQL
  • Write SQL using LAG(), NTILE(), OVER, PARTITION and many more analytic 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 !
  • A minimum of 2 new course topics added each month to keep your skills in shape

What is the target audience?

  • Analysts
  • DBA's
  • Job hunters
  • Graduates
  • Students
  • Technical marketing people
  • Those looking to acquire new skills

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Getting started
03:28

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

10:38

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

08:27

Sales sample database download and restore instructions

10:41

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

09:40

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

10 questions

Did you pay attention to key points

Section 2: Chapter 2 - SQL Basics
05:56

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

07:24

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 .

10:21

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

5 questions

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

12:19

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.


5 questions

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

12:12

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.

5 questions

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

10:05

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

5 questions

5 quiz questions to see if you paid attention

Section 3: Chapter 3 - Forming relationships using JOIN's
04:31

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.

11:40

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.

10 questions

Just checking the model overview kept you awake !

11:10

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

1 question

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

08:23

Understanding what the LEFT OUTER JOIN does

1 question

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

05:58

Understanding what the RIGHT OUTER JOIN does

1 question

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

06:36

Understanding what the FULL OUTER JOIN does

1 question

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

06:57

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.

1 question

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

05:11

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.

07:26

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

04:44

Lets add another join for fun to provide better actionable insight

07:57

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

1 question

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

08:49

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.

09:26

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

5 questions

Did you find Cross Join and Left Join easy ?

11:11

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!

08:02

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

05:10

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.

5 questions

APPLY your understanding and don't be Cross

Section 4: Getting started with SQL for quantitative analytics
03:55

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

11:18

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

1 question

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


10:12

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

1 question
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
08:16

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

1 question
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'
07:27

Being minimalistic using the Min() function.

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

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

Maxing it out with the Max() function

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

1 question
Write a query to return the maximum IP cover premium for the underwriting year of 2012
07:14

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.

1 question
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
07:57

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

1 question
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
07:51

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

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

Sample Google analytics data from my own website traffic analysis

12:50

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.

1 question

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

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


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’


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

09:47

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?

3 questions

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)

08:07

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.

3 questions

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

10:11

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.

2 questions

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

Section 6: More advanced analytics using SQL
04:45

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

11:15

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

03:58

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.

09:30

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

11:16

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

Section 7: Other stuff you really need to know
09:37

How to create databases and tables quickly

11:01

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 !

08:10

What is a view and how do I create it ?

This is DDL actually.

06:37

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

09:43

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

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

09:06

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 ! 

04:42

The 3rd and final CRUD episode

Delete or Truncate is the question !

Truncate is faster, but is limited.

06:42

Date arithmetic with DateDiff() and DateAdd()

07:07

Discrete grouping of data for analysis using a case statement

06:18

As a data analyst I often receive data as CSV's, Excel files etc, how do I import it all into a database ?

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Hi there,

I am a Data Analyst, QlikView, QlikSense & SQL Developer that has worked with data since 2003 (when I started working with the Microsoft SQL stack) but moved into the QlikView environment in 2012 and commenced working with QlikSense in 2014 whilst it was still a beta release.

I still work with SQL as it is foundation to a substantial amount of my DA work.

Paul

Ready to start learning?
Take This Course