Course Outline – Introduction To SQL

Objectives

This is an introduction to SQL for beginners.  Delegates will finish the course with a broad understanding of SQL statements and how to use SQL to extract data from databases and transform it into the shape needed for their business purposes. This can be run as a one day or two day course.

Course Schedule

Introduction to SQL and Databases

SQL Overview.  Why is it important?  SQL’s declarative nature.  Whetting your appetite – a quick demo of what SQL can do and what the course will cover (using the KAD data lake database).  The main objects, tables, views and stored procedures.

Getting Started

This introduces the demo data that the course will use. This is in a SQL Azure database (in the cloud) and we’ll launch and have a first look at the (free) SSMS tool.

Session – SQL SELECT Basics

The structure of a simple SELECT statement explained

Filter data – the WHERE clause

Use the WHERE clause to get exactly the rows that you require from an (often very large) table

  • The typical cases
  • IN to specify a list of values
  • BETWEEN to specify a range of dates

GPOUP BY and aggregations

How to summarise your data (and why this is often useful)

  • The GROUP BY clause
  • Common aggregation functions: e.g. COUNT(), SUM(), AVG()

Tables And Relationships

Understand the structure of tables (columns, data types, nullability, cardinality, primary keys) and relationships between tables (common columns, foreign keys, referential integrity). View related tables in the database designer

Get data from several tables using JOINs

Create a SQL statement that retrieves data more than one table.  Use the JOIN clause to specify the common columns

  • The JOIN keyword
  • How to use table aliases to improve clarity
  • Types of JOIN (INNER, LEFT, others)

Session – Create calculated columns

Add calculated columns in your SQL statement and use some typical SQL functions and operations: e.g. ROUND(), TRIM()

Dates and Calendars

Nearly all analytic database have a date / time component at their core and this needs a calendar table and date arithmetic

  • How to build a calendar table
  • SQL date functions to get the year, quarter, month from a date
  • SQL date arithmetic; calculate durations between two dates

Subqueries and CTEs

Subqueries and common table expressions (CTEs) increase the analytical power of our SQL statements.

  • Simple subqueries
  • Correlated subqueries
  • CTEs and the WITH clause and why this is often a better alternative to a subquery

CASE statements

The CASE statement gives great flexibility to create new calculated columns.

  • The basic CASE WHEN THEN ELSE END structure
  • Typical uses: counting rows that meet a certain criterion
  • Using CASE with aggregate functions e.g. calculating percentages

Window functions

Window functions are powerful, flexible and applicable to many problems that would otherwise be hard to specify in SQL

  • Creating a Window function – The OVER() clause
  • The PARTITION clause – apply the windows (and group/ aggregate) over the distinct values in one or more columns
  • The ORDER BY clause – example of use: e.g. ranking by category
  • the ROWS PRECEDING / FOLLOWING clause – sliding windows and moving averages
  • Typical examples of using Window functions

Using SQL with popular visualisation tools like Power BI

So far we have used SSMS to run our SQL queries which send our results to the screen.  We can use SQL when connecting to Power BI to reduce and shape our data while importing it from a database

Changing Data (INSERT, UPDATE and DELETE)

If we can create objects in our database, then useful options become available.  We can create tables to create to store results and can modify the data in those tables. We can write stored procedures to package and simplify our SQL statements.  We can write views over several tables which allows us to get all the data required in one simple SELECT statement.

  • Create a table
  • Alter a table
  • Create and alter a view
  • Use the INSERT statement to add rows
  • Use the UPDATE statement to change values of existing rows
  • Use the DELETE statement to remove certain rows
  • Create and execute simple stored procedure