SQL for Data Analysis, Hands-on industry skills for analysts

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.

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

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.

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.

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

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.

The SELECT DISTINCT statement allows us to return only distinct (unique) results from our queries

ORDER BY  is used to sort the result set in either ascending or descending order or a combination depending upon the criteria you choose

The WHERE clause is used to filter records so that we only return records that fulfil a specific selection criteria

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.

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

Further discussion on the topic of handling NULLs in SQL. Scripts are attached in Part 1 of this topic.

In this lesson we shall look at the following logical operators : AND,EXISTS,LIKE,IN,OR

To conclude our look at Logical Operators in SQL, we will look at BETWEEN, ANY, ALL, AND

We will now look at comparison operators to compare two separate expressions

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

In Part 2 of our lesson on how to use the UNION operator, we shall learn how to code our UNION statements

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

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.

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.

This lesson will cover the concepts around JOINs and discuss INNER, LEFT/RIGHT JOIN and OUTER JOIN

We will now learn how to code our JOIN operation

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.

We now discuss how to join three or more tables in a single SQL SELECT statement.

How to code a JOIN between three or more tables in SQL

In this lesson we shall look at the simple aggregation functions COUNT(), MIN(), MAX(), AVG() and how they allow you to summarise data effectively

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

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.

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

Extending our earlier discussion on DISTINCT by comparing to GROUP BY

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

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

We discuss different string manipulation functions in SQL

We learn how to use SQL to manipulate column string values

In this final lesson in the topic of string manipulation we look at trimming and padding out column values

We look at functions available in SQL that allow us to manipulate numerical column values

In this lesson we look at functions used to extract useful information from data fields values

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

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

In the second lesson on Pivots we learn how to pivot data using SQL so that we can extract value from it

We explain the value of using regular expressions in our data analysis

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.

In this lesson we take a look at the POSIX regular expression standard for pattern matching and how to use it in SQL

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

We shall use the example to determine how regular expressions can be used to validate any given email address

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

RANK and DENSE RANK allow us additional methods by which to label the position of a record within a partition

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.

We'll look at a few other useful functions : FIRAST_VALUE, LAST_VALUE, NTILE,CUME_DIST