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! |