Data, Analytics & AI Category Banner Image

SQL for Data Analysis - Intermediate

  • Length 1 day
  • Price  $550 inc GST
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.

Nexacu powered by Lumify - master logo

Request Course Information


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 logo PNG

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.


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

Personalise your schedule with Lumify USchedule

Interested in a course that we have not yet scheduled? Get in touch, and ask for your preferred date and time. We can work together to make it happen.