Search
START TYPING AND PRESS ENTER TO SEARCH

    Have a Question About This Course?





    Image

    Advanced SQL Course

    Web Development Series
    Structured Query Language (SQL) is the foundation of all relational database operations. In this practical Advanced SQL course, you will learn how to unlock the full potential of the SELECT statement to create robust queries, apply the best query methods for your application, test results effectively, and avoid common mistakes. The course also presents alternative approaches to problems, enabling you to choose the most efficient solution in each case.
    There are instructor materials available for this course.

    Audience profile

    This course is designed for novices and professionals who are interested in interacting with databases using the SQL language. This audience typically includes people who are moving into a database role or expect their role to require interacting with a database server that uses SQL. As an introductory course, it doesn’t require any previous knowledge of SQL, databases, or information technologies.

    Why choose this course?

    - Meticulous content relevance, tailored to Microsoft SQL 2022.

    - Content on obsolete technology has been removed.

    - Offers an array of demonstrations that bolster the learning material.

    - Expert instructional design ensures a superior learning experience.

    - Labs are developed by Waypoint in parallel with courseware, so they are 100% aligned.

    This is the updated version of 55356AC from the retired Microsoft Courseware Marketplace.

    Advanced SQL Course Objectives

    • In this Advanced SQL course you will learn how to:
    • Maximise SQL’s capabilities to build powerful complex and reliable queries.
    • Query multiple tables using inner joins outer joins and self joins.
    • Construct recursive common table expressions (CTEs).
    • Summarise data with aggregation and grouping techniques.
    • Apply analytic functions to calculate ranks.
    • Develop simple and correlated subqueries.
    • Thoroughly test SQL queries to prevent common errors.
    • Select the most efficient solution to complex SQL challenges.

    Need Assistance Finding the Right Training Solution

    Our Consultants are here to assist you
    Arrange a consultation

    Key Point of Training Programs

    • Advanced SQL Course Outline

      Module 1: Introduction and Overview

      - SQL fundamentals

      - Why SQL can be both straightforward and complex

      - Recommendations for thorough testing

      - Retrieving data with SELECT
      - Expressions
      - Literals
      - Correctly handling NULL values

      - Executing queries
      - Analysing query plans
      - Improving query performance
      - Returning partial results with FETCH and OFFSET
      - Choosing the best alternatives
      - Avoiding common pitfalls

      Module 2: Querying Multiple Tables

      - Implementing different types of joins
      - Inner joins
      - Cross joins
      - Left, right, and full outer joins
      - Equijoins vs. theta joins

      - Performance implications of joins

      - Adding filter conditions to outer joins
      - Writing self joins
      - Joining a table to itself
      - Chaining self joins
      - Solving time-interval problems
      - Combining queries with set operators
      - UNION
      - UNION ALL
      - INTERSECT
      - EXCEPT

      Module 3: Aggregate Functions

      - Summarising data with aggregate functions
      - COUNT, SUM, AVG, MIN, MAX

      - Managing NULL values

      - Identifying duplicates

      - Grouping data
      - GROUP BY
      - Using HAVING condition

      - Calculating moving averages

      - Building crosstab reports

      - Extending group queries

      - Nested grouped aggregates
      - Joins with grouping
      - Subtotals with CUBE and ROLLUP

      Module 4: Performing Extensive Analysis with Analytic Functions

      - The OVER clause
      - Ordering before applying functions
      - Dividing result sets into partitions

      - Calculating ranks
      - RANK and DENSE_RANK
      - ROW_NUMBER with ordered sets
      - Calculating percentiles

      - Extending aggregates

      - Multi-level partitioning
      - Running totals

      - Comparing row and aggregate values

      - Top-N queries

      - Defining sliding window boundaries

      Module 5: Building Subqueries

      - Self-contained subqueries
      - In conditions and column expressions
      - Multilevel subqueries
      - Avoiding issues with NULL results
      - Handling multirow subqueries
      - Finding gaps in number sequences

      - Correlated subqueries
      - Referencing values from the outer query
      - EXISTS vs. IN
      - Detecting duplicates
      - Avoiding unintentional correlations

      - Common table expressions
      - Reusable subqueries
      - Recursive subqueries
      - Navigating hierarchical data

      Module 6: Breaking Down Complex Queries

      - Overcoming SQL limitations

      - Reducing complexity while improving performance

    • SQL Querying: Fundamentals Format

      In-Person

      Online

    • Advanced SQL Course Prerequisites

      None