SQL Server Advanced SQL Server Database


Course Facts

  • 2 day course
  • Our Trainers are Authorised Professionals
  • Live Online or Classroom-based
  • Certificate of completion
  • Maximum class size of 6 for scheduled courses
  • Unlimited support after your course
  • Courses at all levels: Introduction, Intermediate, Advanced and Masterclasses

Course Outline

  • Review of SQL Basics
  • Typical data challenges
  • Advanced Functions Use
  • Advanced Joins
  • Advanced Data Organisation
FULL OUTLINE

Related Courses

Private One-to-One Training

One-to-one tuition tailored to your exact requirements. Your choice of duration, dates and content.

Enquire Now

Team Training for Companies

Group bookings provide you with the most cost-effective way to train your team.

Enquire Now

Team Training and Bespoke Sessions

Booking a private training session allows you to focus on your exact needs on dates that work best for you or your team.

  • Sessions can be run online or in person, on your site or at our London training centre.
  • We manage training for many of the UK’s largest companies, taking care of all the admin, working with you to ensure the perfect course.
  • Our expert trainers will first understand your requirements and then tailor each session to your exact specification.
  • We offer a free consultation service to work out every detail of your course.

Prices for bespoke sessions with our expert trainers start at £525 + VAT.

Overview

The advanced SQL database querying course is aimed at anyone with challenging business intelligence and data analytics querying and SQL requirements or advanced SQL development needs. This course builds on your basic SQL knowledge and goes beyond the basic SELECT statement to guide you through how to integrate and manipulate complex SQL data sets, break large data sets down into manageable subsets for further processing, and derive challenging business calculations and categorisations through SQL queries.

This course is relevant to: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Azure SQL Database, Azure SQL Data Warehouse, Azure Synapse.
Read More...

Course Outline

Review of SQL Basics

Typical data challenges

  • Data preparation for Data Warehouse loading
  • Data preparation for Business Intelligence & Reporting
  • Data retrieval for DBAs
  • Data retrieval for troubleshooting

Advanced Functions Use

  • Specialist SQL functions
  • Combining SQL functions
  • Data cleansing, transformation and preparation

Advanced Joins

  • Cartesian product CROSS Joins
  • Using OUTER Joins
  • Mixing INNER and OUTER Joins
  • Parent Child Self Joins
  • Equi-Joins, Non equi-Joins and non-key Joins

Advanced Data Organisation

  • The GROUP BY Clause
  • The HAVING Clause
  • Ranking Grouped Data
  • ROW_NUMBER
  • RANK and DENSE_RANK
  • NTILE
  • The CUBE and ROLLUP options
  • GROUPING SETS
  • PIVOT

Breaking Complex Challenges into manageable and efficient stages

  • Temporary Tables
  • Sub Queries: Simple & Correlated
  • Views: Using Views for Inserts, Updates and Deletes
  • Common Table Expressions (CTEs): Simple, recursive, hierarchical, named sets

Advanced SQL Data Grouping

  • The GROUP BY & HAVING Clauses
  • Ranking Grouped Data: ROW-NUMBER, RANK, DENSE_RANK, NTILE
  • GROUPING SETS
  • PIVOT & UNPIVOT

Advanced Data Manipulation Statements

  • Simple Update & Delete Statements
  • UPDATES and DELETES With Sub queries
  • Merging Records with the MERGE Statement

Efficient & Robust SQL Query Writing

  • Avoid Sub queries
  • Using Derived Tables instead of Sub Queries in select list
  • Complex Code & Fast Execution v Ease of Maintenance & Slower Execution
  • Integrating Multiple SQL query techniques

Interpreting Query Execution Plans

  • Graphical Execution Plans
  • Estimated Plans
  • Actual Plans
  • Query Plan Costs

SQL Differences between On-Premise SQL Server & Azure Synapse/Azure Data Warehouse

Related Courses

Our Clients

Our Recent Success Stories

Photoshop

Kiren's Story

Marketing Manager at Dataminer

Photoshop

Karen's Story

Picture Editor at Reach PLC

Animate

Stuart's Story

Graphic Designer at S4C

30 years working with Microsoft
  600+ Reviews

Latest Help Station Articles

Streamlining Your Design Workflow with Figma's Multi-Edit Feature
Adobe Introduces AI Assistant
Web Design Trends in 2024