SQL Scripting

Prerequisites

No specific pre-requisites. The course is aimed at professionals at all levels who are required to analyse and interpret data as part of their role.

Designing and Creating a Database

Relational Database Design Principles
The Origins of Relational Design
Data Normalization
Understanding the Referential Integrity
Beyond Normalization
Implementing the Design
Database Storage
Creating Databases
Modifying Database Options
Creating Tables
Creating Constraints
Using Database Diagrams

Data Selection Queries

Understanding SQL
The SELECT Statement
Selecting All Columns
Concatenating Columns
Naming Columns
Using DISTINCT to Limit Values
The WHERE Clause
Transact-SQL Comparison Operators
The LIKE Operator
The BETWEEN Operator
Using IS NULL to Test for Nulls
Multiple Conditions with AND, OR, and NOT
Operator Precedence
Using the IN Operator
Using ORDER BY to Sort Data
Sorting on a Single Column
Sorting by Multiple Columns
Sorting with Expression
The GROUP BY Clause
Aggregate Functions
Counting Rows
Counting Columns
Counting with a WHERE Clause
Using GROUP BY
Using GROUP BY with GROUP BY
Using HAVING with GROUP BY
TOP Values Queries
Joining Tables
Cross Joins (Cartesian Products)
The Use of Keys in Joining
Join Notation
Inner Joins
Outer Joins
Self Joins

Modifying Data

Modifying Data
Inserting Data
Inserting a Single Value
Inserting Multiple Values
Inserting Multiple Rows
Creating a New Table Using SELECT INTO
Temporary Tables
Using Bulk Copy to Insert Data
Updating Data
Updating a Single Row
Updating Multiple Rows and Columns
Updating from Another Table
Updating with TOP
Deleting Data
Deleting a Single Row
Deleting Multiple Rows

Data analysis

Data import and data export.
Using Power Bi to analyze data
Using Excel to analyze data
Pivot Tables and Charts