Data and Analytics - Category Banner

SQL for Data Analysis - Advanced

  • Length 1 day
  • Price  NZD 539.13 exc GST
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.

Nexacu Public Schedule

Nexacu is part of the Lumify Group, offering you the largest public schedule of end user applications and professional development training in Australia, New Zealand, and the Philippines. You can now access the schedule of courses and book, by clicking on the button below.

Request Course Information


What you’ll learn

After completing this course, students will be able to:

  • Use sophisticated techniques for querying databases

  • Reshape and manipulate result sets

  • Reshape data by pivoting and unpivoting

  • Write 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


Microsoft Solutions Partner - Cloud - Training Services Logo

Microsoft at Lumify Work

As part of Lumify Group, Lumify Work has skilled more people in Microsoft technologies than any other organisation in Australia and New Zealand. We have a campus in the Philippines, too. We offer the broadest range of instructor-led training courses, from end user to architect level.  We are proud to be the winner of the Microsoft MCT Superstars Award for FY24, which formally recognises us as having the highest quality Microsoft Certified Trainers in ANZ. 


Who is the course for?

This course will be of benefit for anyone working towards Microsoft Certification in Azure Data Fundamentals and for anyone interested in working with data stored in the cloud.


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.


Microsoft - Training Solutions Partner - Microsoft Certified Trainers - MCT Superstars Award FY24


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

Select and book a course

July
September
October

Can't find a date you like?

Contact sales


Offers

Accelerate Copilot adoption with up to 50% off training
The promise of AI and Microsoft 365 Copilot in particular is clear. Microsoft research demonstrates that Copilot adoption results in: Significant time savings across departments Improved efficiency in document creation and communication Enhanced employee satisfaction and reduced repetitive tasks Copilot boosts creativity and makes data-driven insights more accessible than ever before. But how can businesses realise these benefits at scale? As the 2024 Work Trend Annual Report noted: “We’ve come to the hard part of any tech disruption: moving past experimentation to business transformation. Just as we saw with the advent of the internet or the PC, business transformation comes with broad adoption.” Training drives effective AI adoption Key findings of a recent Australian Government trial of Microsoft 365 Copilot highlight the importance of AI skilling: Capability challenges were a key barrier to adoption; participants needed both tailored training that provided agency-specific use cases and general generative AI training in prompt engineering. 75% of participants who received three or more forms of training were confident in their ability to use Copilot -- 28 points higher than those who received just one form of training. Training enhanced confidence in Copilot use and was most effective when tailored to an agency’s specific context. Identifying specific use cases for Copilot could lead to greater use of Copilot. Lumify is here to help Setting up a Copilot trial? Rolling out this incredible technology across your business or within a specific team? Talk to us! We can design a tailored training plan for you. Unlock the offer code by 30 September 2025 and access these limited-time discounts: 50% off private, customised training for 10 or more students 25% off seats on our publicly scheduled training courses Terms and Conditions Offers valid only on Microsoft Authorised courses booked by 30 September 2025 and delivered by 31 December 2025. To redeem, the customer must unlock the code on the offer page and mention this to their Lumify Work Account Manager or a customer service representative during booking. Not applicable in conjunction with any other Lumify Work special offers or packages. All other standard Lumify Work Terms and Conditions apply. Lumify Work reserves the right to modify or terminate this promotion at any time.