SQL for Data Analysis Course
About the Course
During this 3-day SQL for Data Analysts course, you will learn more advanced aspects of the SQL language that will allow you to perform each of the stages (get, transform, enrich and model and visualise) essential in Data Analysis reporting.
This course will cover the Extract, Transform, Load (ETL) process, discuss data modeling in detail, and demonstrate the use of advanced SQL and T-SQL techniques that allow you to code right at the source, simplifying your analytical reporting by potentially avoiding the need for Python, M, or DAX.
Who should do this course?
This course is suitable for anyone seeking to extend their knowledge of SQL, as well as gain a better understanding of its capabilities. It covers everything required by Data Analysts as well as offering advanced skills for other SQL developers who wish to take their knowledge to a deeper level.
Prerequisites
This course assumes a reasonably advanced understanding of SQL before attending. At a minimum, participants should have completed our SQL Essentials course or have equivalent skills and knowledge. Less experienced students might also benefit from attending the Advanced SQL Queries course before attending this course.
Course Details
$1584 incl GST
- Duration:3 Days
- Max. Class Size:10
- Avg. Class Size:5
-
Study Mode:
Classroom Online Live
- Level:Advanced
- Times: Classroom: 9.00am to 5.00pm approx(Local Time) Online Live: 9.00am to 5.00pm approx(AEST or AEDT)
- Download Course PDF
Course Dates
Sydney Class Dates
Level 11, 32 Walker Street, North Sydney , NSW 2060
| 09 - 11 Dec 24 | Mon - Wed | Classroom |
| 13 - 15 Jan 25 | Mon - Wed | Online Live |
| 22 - 24 Jan 25 | Wed - Fri | Classroom |
| 03 - 05 Feb 25 | Mon - Wed | Online Live |
| 19 - 21 Feb 25 | Wed - Fri | Classroom |
| 05 - 07 Mar 25 | Wed - Fri | Online Live |
| 17 - 19 Mar 25 | Mon - Wed | Classroom |
| 14 - 16 Apr 25 | Mon - Wed | Online Live |
| 28 - 30 Apr 25 | Mon - Wed | Classroom |
| 28 - 30 May 25 | Wed - Fri | Online Live |
| 11 - 13 Jun 25 | Wed - Fri | Classroom |
Melbourne Class Dates
Level 12, 379 Collins Street, Melbourne , VIC 3000
| 13 - 15 Jan 25 | Mon - Wed | Online Live |
| 03 - 05 Feb 25 | Mon - Wed | Online Live |
| 05 - 07 Mar 25 | Wed - Fri | Online Live |
| 14 - 16 Apr 25 | Mon - Wed | Online Live |
| 28 - 30 May 25 | Wed - Fri | Online Live |
Brisbane Class Dates
Level 6, 371 Queen Street, Brisbane , QLD 4000
| 13 - 15 Jan 25 | Mon - Wed | Online Live |
| 03 - 05 Feb 25 | Mon - Wed | Online Live |
| 05 - 07 Mar 25 | Wed - Fri | Online Live |
| 14 - 16 Apr 25 | Mon - Wed | Online Live |
| 28 - 30 May 25 | Wed - Fri | Online Live |
Canberra Class Dates
All courses facilitated in, Online Live format ,
| 13 - 15 Jan 25 | Mon - Wed | Online Live |
| 03 - 05 Feb 25 | Mon - Wed | Online Live |
| 05 - 07 Mar 25 | Wed - Fri | Online Live |
| 14 - 16 Apr 25 | Mon - Wed | Online Live |
| 28 - 30 May 25 | Wed - Fri | Online Live |
Adelaide Class Dates
All courses facilitated in, Online Live format ,
| 13 - 15 Jan 25 | Mon - Wed | Online Live |
| 03 - 05 Feb 25 | Mon - Wed | Online Live |
| 05 - 07 Mar 25 | Wed - Fri | Online Live |
| 14 - 16 Apr 25 | Mon - Wed | Online Live |
| 28 - 30 May 25 | Wed - Fri | Online Live |
Perth Class Dates
All courses facilitated in, Online Live format ,
| 13 - 15 Jan 25 | Mon - Wed | Online Live |
| 03 - 05 Feb 25 | Mon - Wed | Online Live |
| 05 - 07 Mar 25 | Wed - Fri | Online Live |
| 14 - 16 Apr 25 | Mon - Wed | Online Live |
| 28 - 30 May 25 | Wed - Fri | Online Live |
Course Units
⊕ Expand full topic list
Unit 1: Why SQL?
- Introducing the Course
- Understanding the Purpose of SQL
- Understanding the Purpose of BI
- When to Use SQL versus BI
⊕ See more
Unit 2: Overview of SQL and T-SQL
- Overview of Incorporating Logic using CASE
- Overview of nulls in SQL using COALESCE
- Overview of Views
- Overview of Basic Construct in T-SQL
- Overview of CURSORS in T-SQL
- Overview of Table Variables in T-SQL
- Incorporating SQL in your T-SQL Code
- Creating Procedures and Functions in T-SQL
⊕ See more
Unit 3: Loading Data from Excel using SSMS
- Introduction to SQL Server Import Export Data Wizard
- Selecting Source and Destination Data
- Transforming the Data
- Saving the Steps using SSIS
- Running the Import
- Defining Your Own Tables
- Importing Data into Existing Tables as Destination
⊕ See more
Unit 4: Presenting Custom Data using Views and Functions
- Creating a Function to Split Text by a Delimiter
- Creating a Procedure to Perform a Fill down using CURSORS
- Detecting Errors by Profiling Data
- Cleaning Data using a View
- Using Table-Valued Functions Instead of Views
- Using Multi-Statement Table Valued Functions to Customise the Data
⊕ See more
Unit 5: Introducing the Extract, Transform and Load (ETL) Process
- Understanding Transactional (OLTP) Databases
- Understanding Analytical (OLAP) Databases
- Understanding the Principle of Normalisation.
- Understanding the purpose of Normalisation
- De-Selectively De-normalising Your Data into Star and Snowflake Schemas
- Investigating a Typical OLAP Model
- Investigation the Consequences of Star and Snowflake Schemas
⊕ See more
Unit 6: EXTRACTION
- Creating Staging Tables
- Extracting Data to our Staging Tables
- Using Temporary Tables
⊕ See more
Unit 7: TRANSFORMATION: Modifying Rows and Columns
- Dropping or Renaming Column using ALTER TABLE
- Changing Datatypes using ALTER TABLE
- Removing Unnecessary Rows using DELETE
- Eliminating duplicates using DISTINCT
- Detecting Errors by
- Replacing Data Entry Errors using UPDATE
- Handling Missing Data using COALESCE
- Filling Down using a Custom Function
⊕ See more
Unit 8: LOADING data into production tables
- Creating Production Tables
- Splitting Columns using a Customised Function
- Eliminating duplicates using DISTINCT
- Partitioning Data
- Putting it all together using a Procedure
- Running TSQL Scripts from the Command Line
⊕ See more
Unit 9: Understanding Data Modelling
- Considering Primary Keys and Foreign Keys in your Tables
- Auto-generating Primary Keys using IDENTITY
- Determining Cardinality: Many to One
- Determining Cardinality: One to One
- Determining Cardinality: Many to Many
- Implementing Many to Many Relationships with Bridging Tables
- Implementing Optionality using NOT NULL
- Understanding Weak v Strong Relationships
- Implementing Strong Relationships using Composite Primary Keys
⊕ See more
Unit 10: Working with Data Sets
- Investigating INTERSECT Operator
- Investigating EXCEPT (MINUS) Operator
- Setting Order of Precedence with Set Operators
- Investigating IN, NOT IN
- Investigating EXISTS, NOT EXISTS
- Investigating ANTIJOINS
⊕ See more
Unit 11: Generating Measures using Standard SQL
- Generating Time Dimensions using DATEPART
- Generating Measures Based on Aggregates
- Generating Measures Based on Calculations
- Generating Measures Based on Distinct Valued
- Using CASE to Categorise Data
- Generating Measures Based on Conditions
⊕ See more
Unit 12: Generating Measures using Analytical Functions
- Combining Different Levels of Granularity using Subqueries
- Understanding the OVER Clause
- Generating Grand Totals using OVER
- Generating Subtotals using PARTITION BY
- Generating Running Totals using ORDER BY
- Generating Year-to-Date by Combining ORDER BY and PARTITION BY
- Comparing Year-to-Date Data by Reordering Results
- Generating Three-Monthly Rolling Totals using WINDOWING
- Adjusting the Offset of a Window using PRECEDING and FOLLOWING
- Understanding ROWS versus RANGE
⊕ See more
Unit 13: Ranking and Categorising Data using Other Analytical Functions
- Auto-generating Primary Keys using ROW_NUMBER
- Identifying Alternative Rows using ROW_NUMBER and %
- Generating Ranking using RANK and DENSE_RANK
- Dynamically Categorising Data using NTILE
⊕ See more
Unit 14: Preparing Data for Tabular Layout
- Including Subtotals and Grand Totals using UNION
- Introducing GROUPING SETS
- Using Grouping Sets to Customise Which Totals and Subtotals to Display
- Using the ROLLUP Shortcut to Automatically Generate Grouping Sets
- Using the CUBE Shortcut to Automatically Generate Grouping Sets
⊕ See more
Unit 15: Preparing Data for Matrix reports
- Creating a Matrix Report using CASE
- Turning Columns into Rows using UNPIVOT
- Turning Rows into Columns using PIVOT
- Creating a Matrix Report using PIVOT
- Including Totals in Your Matrix
⊕ See more
Related Courses
Enquire Now
Fill in your details to have a training consultant contact you to discuss your training needs.
Note: Form fields marked with * are required.