Greetings to all,
Welcome, this is a SQL course that I am sure you will complete and benefit a lot from.
The course is aimed at Beginners to Intermediate levels.
It covers all the bases for you to get up to speed with writing SQL before you progress on to more advanced SQL for transforming and analysing data.
I will explain everything step by step and in detail so you get to know SQL very well.
The course uses SQLServer 2014 express, and we all know the popularity of SQL Server and the thousands of enterprises that use it.
All data and scripts are included (for download) in the course so that you can practice what I teach and more.
You can contact me in the Q&A sections if you have any questions.
Data Analyst and SQL Developer
Let's take a look at what the course will teach you
Step by step guide to downloading and installing SQL Express 2014.
This software is free to download,install and use.
A brief overview of the UI , where we will construct the SQL queries
An overview of what makes a Relational Database and how it is stored in SQL Server
A quick quiz to ensure you understood this section aok
How the SQL query is constructed and why
This lecture will provide instructions ...
How the SELECT statement works
Are you happy with the SELECT statement and it's basic function ?
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.
As well as many more predicate options !
Test your memory on filtering returned data within the SELECT statement
How to use the Group By clause and we look at how to use HAVING.
In addition we have a look at what the data looks like on a chart in QlikSense, reading tabular data returned from your query is never pretty so let's visualise this to reveal insight!
Are you cool with Group By and Having ?
Understand the various data types for storing data values and see how Cast() works
Did you understand data types ?
Chapter 3 has a sample database and scripts
Step by Step download and restore of the sample data and scripts
The database demonstrates Primary and Foreign key modelling
We will review this database and it's model in the diagram tool, from here we can understand why Primary(PK) and Foreign (FK) Keys are used, we'll also have a look at how to create these keys.
And get a handle on a multi field Primary Key.
Just checking you are awake :) !
The workings of the Inner Join statement (aka JOIN)
Create a JOIN on the tables Product and ProductSubcategory, select the ProductName,Color and ProductSubcategoryName where the subcategory = 'Jerseys'
The workings of the Left Outer Join statement (aka LEFT JOIN)
The workings of the Right Outer Join statement (aka RIGHT JOIN)
The workings of the Full Outer Join statement (aka FULL JOIN)
Create a FULL JOIN on tables Supplier and ProductSupplier selecting Supplier and the average lead time for the product supplier
The workings and dangers of the Cross Join
Don't make the DBA cross :)
Step by step instructions for download and restore sample database etc
Counting data, and an introduction to using the OVER clause for partitioning , which removes the need for Group By when necessary
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
Learn how to aggregate data and I will show you the Top(n) statement as well
Learn to use the Avg() function to calculate the mean value in your rowset
Working on the UNION clause
Inserting to a table is the "C" in the term CRUD (Create-Read-Update-Delete)
In this lecture I will show you a couple of methods to do this
Introduction to importing raw data files
How to backup a database in SQLServer, backups are your insurance against loss
Case is like If-Then-Else , lets look at how to code it, there are 2 types of Case statement
Please review the instructions and follow the steps to receive your certificate.
I have been a Data Analyst/Engineer for over 20 years specialising in SQL Data Analysis and Development.
I am also an experienced developer in data visualisation using QlikView and QlikSense and my work is predominantly in consulting and specialist analysis projects.
I do have a passion for sharing knowledge with anyone that is interested and wants to learn more about the field of Data Engineering, Visualisations and Quantitative Analysis.
Keep in touch.