Intermediate SQL for data analysis (no installation needed!)

this clip shows how to connect to the Azure course database if you have a decent Windows or Mac machine and you can install software on it (or an admin can install it for you if it's a work computer). This is the best most convenient method. all the details you need you can copy and paste from the text file.

this clip shows how to connect to the Azure course database if you Windows or Mac machine but you cannot install software on it, for instance if it's a work computer and you don't have an admin user.  all the details you need you can copy and paste from the text file.

this clip shows how to connect to the Azure course database if you want to connect directly from a browser with no downloads. in the case you have a chromebook or some other non Win/Mac machine. all the details you need you can copy and paste from the text file.

This lesson is not an inherent part of the course. it's purpose is to inroduce CTE and temp tables as an alternative to subqueries, which are typically what beginners learn first, and is in many ways sub optimal to the other two. The lessons shows how to use all three, and emphasizes the pros and cons of each as a method to temporary store data.

in this lesson you will learn to us subqueries and subqueries over partitions to collect totals, subtotals and perform calculations such as percent of subtotal with them

this is the solution to the exercise on totals and subtotals using sub queries and partitions

in this lesson, learn how to use a correlated sub query to answer connect between customer specific data and their broader data

solution to exercise on correlated sub query

learn how to use case statements to easily categorize your data. case statements are similar to if or ifelse in other languages.

learn how to nest a case within a case to create more elaborate categories of data.

solution to exercise on nested case statements

learn how to pivot and aggregate data with case statements plus aggregation functions (sum, avg, max etc.)

solution to exercise on pivoting and aggregating data with case statements and aggregation functions

learn how to create data relationships between rows or "cells" of data like you would in a spread sheet using window functions - perform calculations on previous or next rows and "cells"

solution to the lag/lead exercise

learn to rank rows to obtain time - sensitive data such what status a customer is in when first or last performing any certain action

solution to exercise on ranking

learn how to create running totals and running subtotals and perform different calculations with them

solution to running totals exercise

learn how to use ntiles to perform powerful statistical analysis on a customer base

solution to exercise on ntiles

a refresher on joins - left, inner and right, plus intersect and except functions.

how to use full outer join (with case or coalesce functions) to reconcile disjointed data sources with no common unique identifier, as well as joining on multiple attributes and creating a mini "quasi join" with "or" operator.

solution to exercise on full outer join

learn how to use the rarely used but pretty cool "cross join" to create a powerful instant "whatif" analysis.

learn how to clean up messy text data with some text functions and regex-like functionality of sql server.

solution to the phone cleanup exercise

solution to the email cleanup exercise