SQL for Data Analysis - Advanced

Length 1 day

Course overview

View dates &
book now

Why study this course

A crucial skill for anyone working with data is the ability to access and analyse that data. Structured Query Language (SQL) enables users to access data from databases, combine data from multiple related tables and to aggregate, sort, filter, or limit the data retrieved as needed. This advanced course builds upon the skills learnt in our SQL for Data Analysis - Intermediate course.

In this course, students will use more sophisticated techniques for querying databases and reshaping and manipulating result sets. Participants will learn to reshape data by pivoting and unpivoting. They will write more complex queries using subqueries, temporary tables, and common table expressions. They will use recursive techniques to query hierarchical data and create date dimension tables. Students will build on their understanding of scalar user-defined functions and local variables to create table-valued functions and use table variables.

Students will also learn to simplify querying and increase their efficiency by using views and stored procedures. Students work with an Azure SQL database and write queries in Microsoft’s Transact-SQL (T-SQL). Much of what is covered will be useful for users working with other database products.

Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.

What you’ll learn

Upon completion of this course students will be able to:

use more sophisticated techniques for querying databases
reshape and manipulate result sets
reshape data by pivoting and unpivoting
write more complex queries using subqueries, temporary tables and common table expressions
use recursive techniques to query hierarchical data and create date dimension tables
write more efficient SQL code

SQL at Lumify Work

A crucial skill for anyone working with data is the ability to access and analyse that data. Structured Query Language (SQL) enables users to access data from databases; to combine data from multiple related tables; and to aggregate, sort, filter, or limit the data retrieved as needed. The knowledge you gain at SQL training could mean the difference between failing, meeting, and exceeding goals.

View all SQL courses

Stay ahead of the technology curve

Don’t let your tech outpace the skills of your people

Get in touch today

Quality instructors and content

Expert instructors with real world experience and the latest vendor-approved in-depth course content.

Partner-Preferred Supplier

Chosen and awarded by the world’s leading vendors as preferred training partner.

Ahead of the technology curve

No matter your chosen technologies or platforms, we can help you stay one step ahead.

Course subjects

Introduction

SQL and T-SQL

Reshaping data

Use PIVOT to turn rows into columns
Use UNPIVOT to rotate columns into rows

Writing More Complex Queries

Subqueries
Local and global temporary tables
Create, modify, and delete temporary tables
Common table expressions
Nested CTEs
Recursive CTEs
Query hierarchical data
Create date dimension tables

More Complex Joins

Cross joins
Self joins
Anti joins
Semi joins

Variables

Local variables
Table variables
Temporary tables vs CTEs vs table variables

User-Defined Functions

Scalar user-defined functions
Table-valued functions
Multi-statement table-valued functions
Schemas
Use APPLY to join output from table-valued functions to a table

Control of Flow

IF…ELSE
BEGIN…END

Simplifying Code Reuse

Working with views
Table-valued functions
Working with stored procedures
Executing stored procedures with parameters

Query Performance

Improving query performance
Basic principles
View execution plans
Temporary tables, views, CTEs, and table variables
Functions
Reusing code

Prerequisites

This course builds on the knowledge gained and skills taught in our SQL for Data Analysis - Intermediate course.

Students attending this course should have completed our Beginner and Intermediate courses or be very familiar with writing SQL queries.

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.

Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.

Looking for more course options?

View all SQL courses View all Data, Analytics and AI courses