After completing this course, you will be able to
- correctly code single and multiple OUTER/INNER JOINS to handle NULLS.
- use the UNION, EXCEPT, and INTERSECT operators to combine multiple result sets into a single result set.
- code Nested and Common Table Expressions.
- write recursive SQL using Common Table Expressions.
- use Row Expressions to extend the power of predicate evaluation.
- nest sub-queries and use them to enhance update and insert SQL as well as the HAVING clause.
- optimize sub-query execution using Order By and Fetch First.
- use Data Change Tables.
- use the Case Expression within Functions.
- perform PIVOT operations on result sets.
This course gives you advanced SQL query coding. We place significant emphasis on practical tips and techniques as well as performance considerations.
Firm up your knowledge of selects, predicates, scalar and aggregate functions, group by, simple and correlated sub-selects, union, and inner and outer joins.
Who should attend?
Anyone who needs to code complex SQL in interactive or programmed DB2, Oracle, MySQL, or SQL Server applications
Any prerequisites?You should have a basic working knowledge of SQL, and a solid understanding of how to code basic SQL SELECT statements. Ideally, you should have completed one of the following IB-Learning's courses:
Advanced Multi-table Inner and Outer Joins
- Multi-Table Inner Joins
- Multi-Table Outer Joins
- Table Join Processing
- Join Guidelines
Advanced Unions, Exists and Intersects
- Advanced Unions
- Nested Table Expressions
- Common Table Expressions
- Recursive SQL
Advanced Sub-Query Usage and Optimization
- Type of Sub-Queries
- Scalar Full Selects
- List Full Selects
- Row Expressions
- Optimization Options
Data Change Tables, Case Expressions and Pivots
- Data Change Tables
- Case Expressions
- Pivot Tables