Skip to content

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Advanced SQL

Course Summary

The Advanced SQL training course is designed to teach students how Structured Query Language (SQL) works under the covers.

The course begins with a lesson on how to write elegant, high-performance queries and how to troubleshoot and solve complex challenges with ease. The course then dives into establishing some often-overlooked theoretical aspects of SQL, while laying layer after layer of deep foundational understanding and emphasizing practical applied implications. The curse will then cover the most efficient ways to use SQL efficiently and elegantly using things like set operators, window functions, common table expressions, lateral joins and more.

Purpose
 Learn a deep understanding of the logical query processing aspects of both traditional and the most advanced, modern constructs of the SQL language.
Audience
 Graduates of the "Introduction to SQL" course, and any developer or Analyst wanting a deep understanding of SQL.
Role
Business Analyst - Data Engineer - Data Scientist - Software Developer
Skill Level
Advanced
Style
Targeted Topic - Workshops
Duration
3 Days
Related Technologies
SQL

 

Productivity Objectives
  • Troubleshoot, optimize and solve complex SQL challenges.
  • Evaluate common pitfalls.
  • Demonstrate high-performing solutions minimizing bugs and resources, improving consistency, productivity, and performance.

What You'll Learn:

In the Advanced SQL training course, you'll learn:
  • Understanding query logical and physical processing
    • Advanced JOIN processing
      • Cartesian products
      • Qualified joins
      • Reserving tables
      • ANSI 89 vs. ANSI 92 joins
      • Lateral derived tables
    • Filtering and dealing with missing data complexities
      • The case for NULL and I-values vs. A-values
      • Ternary logic and predicates
      • Advanced logical operators
    • Understanding grouping and group filters
      • The need for grouping data
      • Aggregate functions and the logical limitations of grouped sets
      • DISTINCT vs. grouped queries
      • The do's and don'ts of group filters
      • Introduction to ordered set functions
    • Efficient paging and ordering
      • The role of presentation ordering in multi-tier applications
      • Understanding the LIMITs of FETCH and OFFSET
  • Advanced querying constructs
    • Changing to a 'set oriented' thinking
      • Sets vs. bags, rows vs. records, fields vs. columns
      • The 'all-at-one' principal
      • RBAR (Row by Agonizing Row) processing
    • Advanced subqueries and query compositions
      • Correlation predicates
      • Expression subqueries and derived tables
      • Value list subqueries
      • The benefits of EXISTS processing
    • Unleashing the power of set operators
      • Advanced use of UNION, EXCEPT, and INTERSECT
  • Recursions, window functions, and imperative processing
    • The use cases for recursive and iterative constructs in SQL
      • Recursive common table expressions
      • The use case for imperative constructs
    • Using window functions to the max
      • Understanding window function processing
      • Framing and filtering windows
      • Aggregate, ranking, analytic and offset window functions
    • Imperative processing in SQL
      • Using vs. abusing cursors
      • Loops
      • Temporary objects
real world content

Real-World Content

Project-focused demos and labs using your tool stack and environment, not some canned "training room" lab.
expert practitioners

Expert Practitioners

Industry experts that bring their battle scars into the classroom.
experiential learning

Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
tailored outlines

Tailored Outlines

One-size-fits-all doesn't apply to training teams. That's where we come in!
“I appreciated the instructor's technique of writing live code examples rather than using fixed slide decks to present the material.”

VMware

Dive in and learn more

When transforming your workforce, it's important to have expert advice and tailored solutions. We can help. Tell us your unique needs and we'll explore ways to address them.

Let's chat

By filling out this form and clicking submit, you acknowledge our privacy policy.