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
| 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 |
| 09 - 11 Jul 25 | Wed - Fri | Online Live |
| 18 - 20 Aug 25 | Mon - Wed | Online Live |
Melbourne Class Dates
Level 12, 379 Collins Street, Melbourne , VIC 3000
| 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 |
| 09 - 11 Jul 25 | Wed - Fri | Online Live |
| 18 - 20 Aug 25 | Mon - Wed | Online Live |
Brisbane Class Dates
Level 6, 371 Queen Street, Brisbane , QLD 4000
| 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 |
| 09 - 11 Jul 25 | Wed - Fri | Online Live |
| 18 - 20 Aug 25 | Mon - Wed | Online Live |
Canberra Class Dates
All courses facilitated in, Online Live format ,
| 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 |
| 09 - 11 Jul 25 | Wed - Fri | Online Live |
| 18 - 20 Aug 25 | Mon - Wed | Online Live |
Adelaide Class Dates
All courses facilitated in, Online Live format ,
| 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 |
| 09 - 11 Jul 25 | Wed - Fri | Online Live |
| 18 - 20 Aug 25 | Mon - Wed | Online Live |
Perth Class Dates
All courses facilitated in, Online Live format ,
| 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 |
| 09 - 11 Jul 25 | Wed - Fri | Online Live |
| 18 - 20 Aug 25 | Mon - Wed | Online Live |
Course Units
⊕ Expand full topic list
Unit 1: Why SQL
- Setting up the Chapter
- Understanding the Purpose of SQL
- Enhancements to SQL
⊕ See more
Unit 2: Overview of SQL and T-SQL
- Introducing CASE, COALESCE and OVER Functional Enhancements
- Introducing T-SQL Programming Structures
- Creating Procedures and Functions
- Putting it all Together
⊕ See more
Unit 3: Loading Data from Excel Using SSMS
- Introducing the SQL Server Import and Export Wizard
- Starting the SQL Server Import and Export Wizard
- Selecting the Data Source
- Defining the Tables
- Previewing the Results
- Customising the Sales Table
- Customising the Country table
- Customising the Products table
- Defining Your Own Tables
- Importing Data into Existing Tables as Destination
⊕ See more
Unit 4: Presenting Custom Data using Views and Functions
- Options for Presenting our Data
- Building Useful Functions
- Reviewing and Using Views
- Profiling Data
- Cleaning Data in our Views Using TRIM and REPLACE
- Limitations of Views
- Using Table Valued Functions
- Using Multi-Statement Table-Valued Functions
⊕ See more
Unit 5: EXTRACTION: Fetching Data from Transactional Tables
- Understanding OLTP and OLAP databases
- Understanding ETL
- Creating the OLAP Database and Temporary Tables
⊕ See more
Unit 6: TRANSFORMATION: Modifying Rows and Column
- Removing Unnecessary Rows and Columns with DELETE and DROP COLUMN
- Modifying Column Definitions Using ALTER TABLE
- Fixing Errors Using REPLACE
- Handling Nulls
- Filling Down Using CURSOR
⊕ See more
Unit 7: LOADING: Loading Data into Production Tables
- Creating the Data Warehouse Tables
- Preparing the Tables for Loading
- Partitioning Data
- Splitting Columns
- Eliminating Duplicates Using DISTINCT
- Putting it all together
- Running and Testing our Production Tables
⊕ See more
Unit 8: Understanding Relational Data Models
- Understanding the Basic Relational Database Concepts
- Understanding Optionality
- Understanding Cardinality
- Resolving Many to Many Relationships
- Investigating Weak Versus Strong Relationships
- Conclusion
⊕ See more
Unit 9: Modelling Analytical Data
- Introducing Modelling and Analytical Database
- Investigating Dimension and Measure Columns
- Understanding Fact and Dimension Tables
- Investigating Hierarchies, Levels and Attributes
- Understanding Star and Snowflake Schemas.
⊕ See more
Unit 10: Working with Data Sets
- Setting up New Data
- Reviewing UNION
- Reviewing UNION ALL
- Investigating INTERSECT
- Investigating EXCEPT
⊕ See more
Unit 11: Working with Tables
- Setting up Data
- Understanding the Difference between UNION and JOIN
- Joining with IN and NOT IN
- Joining with EXISTS and NOT EXISTS
- Understanding and Implementing Anti-joins
⊕ See more
Unit 12: Adding Measures Using Standard SQL
- Creating Views for Common Queries on our Summary Data
- Adding Measures Using Calculations
- Adding Measures Based on Conditions Using CASE
- Adding Measures Using DISTINCT
- Adding Dimensions Using CASE
⊕ See more
Unit 13: Adding Measures Using Standard Aggregate Functions
- Calculating Aggregates Independently Using the OVER Clause
- Calculating Yearly Totals Using GROUP BY
- Calculating Running Totals Using ORDER BY
- Calculating Year-to-date using both OVER BY and PARTITION BY
- Calculating Three Monthly Averages Using ROWS/RANGE
⊕ See more
Unit 14: Adding Measures Using Specific Analytical Functions
- Generating Row Numbers Using ROW_NUMBER
- Ranking Data Using RANK and DENSE_RANK
- Rank Versus Dense Rank: A Deeper Dive
- Categorising Distribution of Data Using NTILE
⊕ See more
Unit 15: Preparing Data for Tabular Layout
- Querying Dimensions and Measures with GROUP BY
- Formatting Data with FORMAT function
- Renaming Headings
- Adding Totals with UNION and GROUPING SETS
⊕ See more
Unit 16: Preparing Data for Matrix reports
- Creating a Matrix Report Using CASE
- Creating a Matrix Using PIVOT
- Dealing with a Mismatch of Columns in Pivot
- Ordering the Columns and Rows
- Adding Totals Using CUBE
- Converting Data from a Matrix Report to a Table Using UNPIVOT
⊕ See more
Unit 17: Preparing Data for Other Visualisations
- Introduction
- Preparing Data for a BAR GRAPH using REPLICATE
- Preparing Data for a PIE CHART Using OVER
- Limits of SQL for Presentation
⊕ See more
Unit 18: Filtering on Prepared Data
- Filtering by Columns in the View Using WHERE clause
- Filtering by Columns Not in the View Using Table Based Functions
- Filtering by Multiple Columns Using Default Parameters
- Filtering by Multiple Columns Using Named Parameter Passing
- Filtering Using Dynamic SQL
- Investigating the Use of Dynamic SQL
⊕ 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.
