SQL Essentials for Business Analysts and Data Professionals

In this section, you will learn a brief history of databases and why databases came about in the first place to address data storage issues.  Today, nearly every company will have their data stored on a database or a datawarehouse, be it social media updates, financial transactions, purchase orders, retail store inventory or genomic sequence data. 

In this section you will learn about the 3 types of categories that SQL statements are broken down into: 1) Data Manipulation Language, 2) Data Definition Language and 3) Data Control Language.

In this video we take a look at how to capture project requirements. What considerations you should take into account when sizing your data needs? and who to review data considerations with?

An overview of physical vs logical data representations.  Databases Vs Schemas and Tables Vs Views.  We will also discuss the importance of building logical business views and ensuring business gold standards.

In this section, you will learn the process of data normalisation.  Normalising data is a means of reducing data redundancy and identifying key dimensional data within our raw data.

In this section, you will learn what are fact tables, and how they are used in conjunction with database table design.

In this section, we will review aggregate data. Aggregating data is a means to summarise our collective data and there are many functions, which we can use to summarise our data by, such as Sum, Maximum, Minimum and Mean. In addition we also look at how aggregate data is typically stored in a database or schema.

Entity Relationship Diagrams are used by Database Architects as a means to diagram a design of a set of tables or logical views. You will learn the basics of how to read an ERD and understand such notations as Crow's Feet notation, used to describe relationships between entities.

In this section, you will learn about the ETL (Extract, Transform and Load) process, which is the process used for updating a databases tables and logical views. ETL is often scheduled to meet the business needs and can vary from hourly loads of data, to weekly, monthly and yearly loads.

In this section, I will talk you though the process of installing and configuring MySQL Server, which we will be using during the course for learning SQL programming. 

NB: If you do not have access to install MySQL Server on your laptop, it is still possible to follow along with the course, as the SQL programming samples are quite intuitive.  However, as with any programming language, I would recommend trying to put in as much practice as possible to get up to speed with the language.  Other platforms such as MS SQL Server, Teradata, Oracle, DB2 all meet the SQL ANSI standards. Therefore, the lessons provided throughout the course in SQL should also work on these systems for the majority of cases.

In this section, you will learn how to start and stop the MySQL server. When you are not developing any SQL on your machine, the MySQL server is set to automatically run in the background. Having the means to disable MySQL server will free up precious resources on your machine for work on other projects.

In this section, we will connect to the MySQL Sakila sample database. This database mimics a DVD store from the early 90's and was created by MySQL for demonstrating their database capabilities. Although DVD stores are more a less obsolete, the data model is still very valid in today's eCommerce society. The data contains data models for customer, movie and payments transactions.

In this section, we will review how to write comments in SQL. Comments are a very useful for documenting your SQL code and if you are working within a data analytics team, having good comments for your team mates will go along way to speeding up future development. Comments can also be used to provide version control details in your production Batch SQL and if you are using software source control systems such as GIT or Microsoft Visual Source Safe, then knowing how to write comments into your code, will also help with version control in your source control systems.

In this section, I review how to connect to database in MySQL and run a number of select statements across different tables.  I also look at how to refine these statements using the Where and Like clauses and also review what to do when SQL returns an error

In this section we will review SQL Windows Functions, also known as OLAP (On Line Analytical Processing) functions.  These are special SQL functions that allow an analyst to summarise and slice data from an overall result data set / cube.  Also referred to as Ordered Analytical Processing, as every OLAP function will first order the data before performing the required action.

While there are a standard set of OLAP functions that adhere to SQL-99 standards, many database implementations also provide their own additional OLAP functions.  For now we will cover a few that would be regularly used by analysts. CSUM (Cumulative Sum), Rank, Row_Number, Lead, Lag, MDiff (Moving Difference), MAvg (Moving Average).

In this section we review the various types of indexes that can be applied to a table.  Table indexes are used primarily to join tables,  together and also help in the back end of the database with distribution of data.

In this section we will review table and column aliases and how they can help us speed up our coding.

In this section we will review how to join two or more tables together by means of the inner join statement in SQL.

In this section we will review how to use the left and right outer table joins.  Such joins are used where we want to bring back all records from one of our sets, regardless of the join criteria being met.

In this section we will review how to use the Union (All) clause, which is used for combining tables and datasets together.  I will also show how the Union clause can be used to exclude data from your results by means of a sub-query.

In this section I will provide an overview of how I go about project management on a daily basis, and how I learnt that it is sometimes a good thing to say No to requests.  I will also touch on development methodologies I've used such as waterfall life-cycle and agile development.  Knowing how to project management, will ensure that you deliver projects on time and meet your customers expectations.

In this section I provide an overview of how to use Kanban Boards for project management, using the online application Trello. 

In this section I will show you to prioritise requirements using the Moscow analogy.

In this section I will introduce you to the Sales Report data project we will work on for the next few sections.  This project will provide you with an overview of how to go about running a data project request from start to finish based on a typical scenario I've been faced with many times as a business data analyst.

In this section we will go through the process of creating a database, how to raise a request if you don't have database administrator privilages, database naming conventions and finally a live coding example.

In this section I will describe the process I use for developing queries and when to use permanent tables versus temporary tables. 

In this section we will start work on our Sales Project Analysis, where we have been asked to create a piece of analysis to show weekly sales figures.  We will first start by defining a list of customers to perform our analysis on, which we can expand at a later stage in our analysis.  In addition, I will also demonstrate a number of techniques for checking the status of your tables and indexes.

In this section, part 2 of our Sales Project Analysis, we will build out out customer payment details table, which will be a combination of our customer California table from our Sales databases, joined to a number of tables within the Sakila database. 

In this section we will produce a Summary data table of our customer transaction analysis.  In addition, we will review a number of SQL functions such as Drop, Alter, Year, Month, Max, Sum, Count and Distinct.

In this section we will make a copy of our Sales SQL analysis script to be sent to production.  Before we can send the SQL script to production, we first need to make it production ready and clean up the SQL. At the end, we will perform a full test of the script to ensure it runs without any errors.

In this section I will review the MySQL Query Explain Plan tool, and how the output from this tool can be used to potentially optimise our queries to run faster.

In this section we will review Common Table Expressions (CTE) using the WITH clause.  Common Table Expressions are very useful if an analyst does not have access to create tables within a database and can define table like structures within a databases memory.  For rapid analysis, CTE's prove very useful, as you don't need to worry about defining a tables structure and data is stored within a databases memory, making them very fast for accessing.

In this section I will show you how you can perform user administration on a users account by using data control language statements such as: create, grant, revoke and drop.

How to set the MySQL path as a Windows environment variable.

In this section I will demonstrate how to use the MySQL Command Line, which can be useful for database administration and running SQL commands outside of the MySQL Workbench.  I finish up with showing you how you can run your SQL scripts in a DOS batch file and automate your script using the Windows Task Scheduler.

In this section I will demonstrate how to export results from MySQL into Notepad and Microsoft Excel spreadsheet.

In this section I will show you how to setup the MySQL ODBC connector, which will allow you to connect Microsoft Excel director to our MySQL database. I also provide troubleshooting methods, which I encountered when originally attempting to install these connectors on my laptop.

In this section we will use our customer payment total data from within our MySQL Sales database, to generate a summary report using Microsoft Excel.  I will demonstrate how to build a pivot table with chart and to add in calculated fields such as moving diff.  To finalise the report, I will add in a report details page with summary of metrics and how to use the report.