Data, Analytics & AI Category Banner Image

SQL for Data Analysis - Intermediate

Length 1 day

Course overview

View dates &
book now

Why study this course

Our Intermediate SQL course builds upon the skills learnt in our SQL for Data Analysis - Beginner course.

In this SQL course, you will move beyond using basic SELECT clauses for retrieving data. Learn to work with many different types of functions and expressions that modify the way data is returned in the result set. Students will use built-in T-SQL functions for working with string, date, and numeric data types and will calculate moving averages, running totals, and percentages using window functions.

Learn to use subqueries to perform more complex querying and to use views to simplify querying. Students will also create their own user-defined functions to encapsulate business logic and improve efficiency.

Nexacu Public Schedule

With Lumify Group's acquisition of Nexacu, we're pleased to now offer you the largest public schedule of end user applications training in Australia and New Zealand. As we move to consolidate our End User offering with Nexacu, as an interim measure you can now access the schedule of the most closely aligned courses and book, by clicking on the link below.

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

After completing this course, students will be able to:

Work with an Azure SQL database
Write queries in Transact-SQL (T-SQL)
Use T-SQL built-in functions
Create user-defined functions
Write subqueries and create views

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.

Stay ahead of the technology curve

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

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

Data Types

Conversion between data types
CAST, CONVERT and PARSE

Functions in SQL

Built-in Functions
Aggregate Functions
Scalar Functions
Ranking Functions
User-defined functions

Aggregate Functions and Group By Extensions

GROUP BY CUBE, ROLLUP and GROUPING SETS
CASE and CASE alternatives
COALESCE
NULLIFF

String Functions

Concatenate, replace and format

Date and Time Functions

Get today's date
Calculate date/time differences
Calculate a new date
Extracting date components
Creating dates

Logical Functions

IIF
CHOOSE
GREATEST and LEAST

Mathematical Functions

ROUND, CEILING, and FLOOR

Writing More Complex Queries

Subqueries
Local Variables
Views

Window (Analytic) Functions

OVER clause
PARTITION BY
ORDER BY
ROWS or RANGE
Calculate group totals, averages, percentages, and running totals
LAG and LEAD
FIRST_VALUE, LAST_VALUE

Ranking Functions

ROW_NUMBER
RANK and DENSE RANK
NTILE

User-Defined Functions

Create, use, and modify scalar user-defined functions
Schemas

Prerequisites

This course builds on the knowledge gained and skills taught in our SQL for Data Analysis - Beginner course. Students must be 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.