SQL for Data Analysts, Hands-on analysis skills for industry
- Description
- Curriculum
- FAQ
- Reviews
SQL really is the language of Data Analysis.
-
This course will teach you the most useful querying techniques for industry.
-
Unlike other courses you’ll be taught with industry-style data.
Learning to use SQL well, allows you to query the data in your organisation’s databases so that you can answer the questions you have as a data and business professional. It is the “bread and butter” skill that data professionals have used for decades and it’s as in-demand today as its ever been – especially on the cloud!.
I’ve put together all of the useful topics I already teach in my day-job, into one convenient course for you!
For anyone wanting to move into the field of Data Analysis or Data Science, or those who have only worked with spreadsheets, or relied on others to query the data for them, this course will help you grasp the most useful SQL functionality and enable you to grow in your role.. even if you have no programming experience. Once you have that skill to query data in different ways and without reliance on others, you’ll be able to find useful ways to extract it and analyse it. It’s only then that you gain insight into what organisational databases hold.
This course comes with a financial database based upon an insurance claims model (something easily understood) which you’ll learn to query and build your skills with. All the lessons and exercises will be based on this database so you’ll have the freedom to explore and investigate relationships in the data that other courses aren’t usually able to offer. You’ll have access to all of the code used by the instructor so you can follow along, as well as the solution to exercises that you’ll use to shore-up your skills. You’ll also have access to me through Q&A of-course!
The techniques you learn can be applied on multiple platforms. Whether you are using PostgreSQL, MySQL, AWS Athena, or any other environment that requires SQL for querying. Once you get started, you’ll quickly be able to adapt what you learn for use anywhere.
We’ll take you through from the very basics, through to advanced functionality most commonly used in industry and not just the vanilla content you’ll see in most other courses. You’ll soon be a SQL hero.
So what are you waiting for?
-
1
Welcome to Hands-On SQL for Data AnalystsIn this lesson I will introduce myself and why I believe SQL is the most important query language to learn as a Data Analyst or Data Scientist.
-
2
Objectives of this course
I will go through what we want to cover during this course. This course covers from beginner to intermediate so there's a lot to cover to bring you up to a competent level depending upon your experience, but I'll be there every step of the way with you!
-
3
Installing PostgreSQL and PGAdmin on MacOS
Installing the tools for this course will allow you to follow along with the lectures and practice the concepts we learn. This lecture will walk you through the installation on a MacOS personal computer. Skip to the MS Windows installation video if you don't use MacOS.
-
4
Installing PostgreSQL and PGAdmin on Microsoft Windows
Installing the tools for this course will allow you to follow along with the lectures and practice the concepts we learn. This lecture will walk you through the installation on a personal computer running Microsoft Windows operating system
-
5
Creating our database for the course
In this lesson we will go through the process of setting up the data structures and putting the data into our tables that we shall be querying for the purpose of this course. We will also learn a little about the PGAdmin tool. The script is included in the Resources section. DOWNLOAD the script in the Resources section of this lesson because you will need to use it. Please be careful not to amend the script and please note: the scripts are for your personal use and not to be distributed. Thanks.
-
6
Understanding the Wardown Park Insurance database
In this lesson I will show you how the Motor Claims business of Wardown Park Insurance works. You'll then be able to understand the relationship between the tables in our database.
-
7
The SELECT statement
In our first lesson, we shall learn how to form a SQL statement. For any of the lessons, you can copy and paste the text from the attached scripts into PGAdmin.
Download and open the attached script in an editor of your choice. I don't recommend opening the attached scripts using PGAdmin Open File as some scripts include notes for you as well. Just copy and paste what you need into PGAdmin Query Editor -
8
The COUNT() function
The COUNT() function returns the number of rows that matches a specified criteria. Its a useful way of determining how many records meet the conditions of your SQL query.
-
9
The DISTINCT statement
The SELECT DISTINCT statement allows us to return only distinct (unique) results from our queries
-
10
Using ORDER BY to arrange your results
ORDER BY is used to sort the result set in either ascending or descending order or a combination depending upon the criteria you choose
-
11
WHERE condition (and an introduction to SQL Operators)
The WHERE clause is used to filter records so that we only return records that fulfil a specific selection criteria
-
12
Decision making using CASE
The CASE expression is a powerful construct that allows us to determine the output of our query based upon existing column values. The CASE expression goes through a list of specified conditions and returns a value when the first condition is met. If no conditions are true, it returns the value in the ELSE clause or if there is no ELSE, it returns NULL.
-
13
Handling NULLs (Part 1)
In these lectures we shall learn to handle NULL values in our table and how to deal with them for our result set.
Note that the resource file is relevant to all lectures covering NULLS -
14
Handling NULLs (Part 2) : Using COALESCE
Further discussion on the topic of handling NULLs in SQL. Scripts are attached in Part 1 of this topic.
-
15
Practice SQL Basics
-
16
SQL Logical Operators (Part 1)
In this lesson we shall look at the following logical operators : AND,EXISTS,LIKE,IN,OR
-
17
SQL Logical Operators (Part 2)
To conclude our look at Logical Operators in SQL, we will look at BETWEEN, ANY, ALL, AND
-
18
Practice SQL Logical Operators
-
19
SQL Comparison Operators
We will now look at comparison operators to compare two separate expressions
-
20
Practice SQL Comparison Operators
-
21
UNION (Part 1)
In this lesson we learn about how to use the UNION operator to combine the result-sets of multiple SELECT statements. In part 1 we learn the rules on how we can form successful UNIONs of data
-
22
UNION (Part 2)
In Part 2 of our lesson on how to use the UNION operator, we shall learn how to code our UNION statements
-
23
Practice SQL UNION Operator
-
24
Subqueries Part 1 : Scalar and Multi-column Subqueries
In this lesson we shall look at Scalar, as well as Multiple Column subqueries. There's a lot of coding in this lesson so please use the scripts in the resources section to follow along and try out the examples
-
25
Subqueries Part 2 : Correlated Subqueries
In this lesson we discuss Correlated Subqueries where the outer and inner query are intrinsically linked. Scripts are attached to Part 1 of this topic.
-
26
Subqueries Part 3 : Common Table Expressions
In this lesson we look at other ways of nesting SQL statements as well as the use of Common Table Expressions. Scripts are attached to Part 1 of this topic.
-
27
Practice Subqueries
-
28
Table Join concepts
This lesson will cover the concepts around JOINs and discuss INNER, LEFT/RIGHT JOIN and OUTER JOIN
-
29
Coding Joins
We will now learn how to code our JOIN operation
-
30
Self Join
In this lesson we will understand and code self-joins in SQL. This is a powerful construct allowing us to exploit the data from a table where one record has a relationship to other records within the same table. Scripts are attached to the Coding Joins lesson.
-
31
Understanding Multi-table Joins
We now discuss how to join three or more tables in a single SQL SELECT statement.
-
32
Coding Multi-table Joins
How to code a JOIN between three or more tables in SQL
-
33
Practice JOINs
-
34
Simple Aggregation
In this lesson we shall look at the simple aggregation functions COUNT(), MIN(), MAX(), AVG() and how they allow you to summarise data effectively
-
35
GROUP BY Part 1
GROUP BY allows us to group rows that have the same values in a specified column, so that we can apply functions against each group of data
-
36
GROUP BY Part 2
In this lesson we continue our journey with GROUP BY to explore more ways on how we can use it in our exploration of data. Scripts are attached to Part 1 of this topic.
-
37
HAVING
HAVING allows us to filter data when using GROUP BY. It works similar to WHERE but you'll learn how we use them both together. In this lesson we will go through how to use it
-
38
DISTINCT revisited
Extending our earlier discussion on DISTINCT by comparing to GROUP BY
-
39
Practice SQL Aggregation
-
40
Data Types, Constraints, Primary-Keys Explained
Understanding data types ensures that we understand the format and the value of each column value, so we can interpret it appropriately. In this lesson we look at the common data types to expect from any project you are involved in
-
41
Considering data types when building queries
In this lesson we shall look at how to consider certain data types when coding your queries including how to find the data types of columns using PGAdmin
-
42
Manipulating Column Data Part 1
We discuss different string manipulation functions in SQL
-
43
Manipulating Column Data Part 2
We learn how to use SQL to manipulate column string values
-
44
Manipulating Column Data Part 3
In this final lesson in the topic of string manipulation we look at trimming and padding out column values
-
45
Number Manipulation
We look at functions available in SQL that allow us to manipulate numerical column values
-
46
Date and Time Part 1
In this lesson we look at functions used to extract useful information from data fields values
-
47
Date and Time Part 2
We continue our exploration of extracting components of date-time columns and how to determine differences between two date fields for the purpose of determining time-passed
-
48
Practice Column Data Manipulation
-
49
Pivot Data Part 1
In Part 1 of Pivots we discuss the value of being able to pivot data for analysis. This is often done in spreadsheets to see data in different ways and extract value
-
50
Pivot Data Part 2
In the second lesson on Pivots we learn how to pivot data using SQL so that we can extract value from it
-
51
Practice Pivoting Data
-
52
Regular Expressions explained
We explain the value of using regular expressions in our data analysis
-
53
LIKE and Wildcard in Regular Expressions
We look at how to use he LIKE and SIMILAR TO operators to match patterns in data. The scripts for all the lessons in this topic are attached to this lecture.
-
54
POSIX operators
In this lesson we take a look at the POSIX regular expression standard for pattern matching and how to use it in SQL
-
55
Ranges in Regular Expressions
When matching patterns, we sometimes need to express a range of possible patterns that a particular column value should match. We explain how to use this in our code when comparing our column data to our regular expression
-
56
Worked example using Regular Expressions
We shall use the example to determine how regular expressions can be used to validate any given email address
-
57
Practice Regular Expressions
-
58
ROWNUM
We begin with looking at one of the most useful Analytical functions available, the use of ROWNUM to number the records returned from our query when using a partition in SQL
-
59
RANK and DENSE RANK
RANK and DENSE RANK allow us additional methods by which to label the position of a record within a partition
-
60
LAG and LEAD
LAG and LEAD allow us to compare data in a row to the record before or after the one being processed within a partition. This allows us to compare the difference in dates, sums, amounts etc .. very useful for analytics as we'll see.
-
61
Other useful Window Functions
We'll look at a few other useful functions : FIRAST_VALUE, LAST_VALUE, NTILE,CUME_DIST
-
62
Practice Window Functions
