Course 509:
SQL Query Optimization
(Available on Google BigQuery, MySQL, PostgreSQL, Oracle DB, Microsoft SQL, IBM DB2)

(2 days)

 

Learning Objectives

  • Understanding SQL Server components and architecture
  • Perform single and multi-table database queries
  • Understand the structure of the SELECT statement

 Who Should Attend

Analysts, programmers, and team leaders using SQL on a regular basis or who communicate regularly with data managers/administrators.

Prerequisites

  • Hands-on experience writing SQL SELECT statements
  • Ability to write a JOIN statement that includes three tables
  • Knowledge of Group By and Having

Course Outline

Unit 1: SQL Join

  • ANSI JOIN and SQL JOIN
  • Managing Null Values
  • Inner and Outer JOIN
  • Removing Duplicates
  • Creating Views
  • Using a Graphical Tool to Incorporate a JOIN

Unit 2: UNION, INTERSECT, and MINUS

  • Designing Queries with UNION
  • Using INTERSECT Effectively
  • Determining Differences between Databases with MINUS

Unit 3: Nesting Select Statements (Sub-Queries)

  • Nesting SELECT Statements (Sub-Queries)
  • Returning Single Values from Sub-Queries
  • Returning Multiple Values from Sub-Queries
  • Writing Sub-Queries Effectively

Unit 4: Query Optimization

  • How SQL Queries Are Written vs. How SQL Queries Are Processed
  • Reducing Complexity
  • Writing Efficient Queries
  • Ranking Results
  • Row Numbering
  • Windowing
  • Beating the Database Engine

Unit 5: User-Defined Functions (UDF)

  • What Is a User-Defined Function (UDF)?
  • Improving Performance with User-Defined Function
  • Avoiding Inefficient User-Defined Functions
  • How Do User-Defined Functions Differ from Stored Procedures?

Unit 6: Analyzing Existing Queries

  • Breaking Down and Rewriting Multi-Table Join
  • Breaking Down and Rewriting Query that Uses Sub-queries
  • Breaking Down and Rewriting Unions and Intersects
  • Recommendations for Improving Performance

Unit 7: Classroom Challenge

  • Write Queries to Answer Classroom Problems
  • Perform Peer-Reviews of Queries
  • Good Query Writing Best Practices and Avoiding the Pitfalls 

Please Contact Your ROI Representative to Discuss Course Tailoring!