Upon completion of this course, the participant will be able to:
> Develop great performance that requires effective design
> Learn how to write for performance and test for quality
> Learn how to design, build, test, and effectively use Views
> Know the 'thought process' of developing an effective query
> Apply the SQL common denominator among different engines
> Apply the use of recursive SQL, the good, the bad, and the ugly
> Apply the use of the elusive ALL, ANY, and EXIST parameters
> Recognize the efficiencies and the inefficiencies in the SQL language
> Know what correlation variables can do to simplify query processing
> Recognize when SCALAR and AGGREGATE FUNCTIONs should be used
> Learn how to use the many types of sub query constructs most effectively
> Know when which one of the four JOIN types should be used for efficiencies
> Learn the typical SQL environments, Unix/Linux/Windows, and Mainframes
> Apply the FULL Select, Materialized Views, and Common Table Expressions
> Know Boolean constructs, CASE constructs, and multiple condition constructs
> Explain how NULLs are helpful to reduce processing time and query complexity
> Know when to utilize the 'nested table expression' to improve query performance
> Know what and when the UNION operation is most effective vs. other techniques
> Apply the runtime LOCKING issues with INSERT, UPDATE and DELETE operations
> Recognize when to (and not to) use DISTINCT, IN, LIKE, BETWEEN and other predicates
> Explain how DATE and TIME data types can effectively be used for audits and compliance
> Describe when ‘Inline Views’ or ‘Nested Table Expressions’ are appropriate for performance
> Know when to issue COMMIT and/or ROLLBACK and their effect on the transaction mix
> Learn how CUSROR processing should be coded in correct logic structures in 5 constructs
> Recognize how to determine when CURSOR logic is most effective in a host language structure
> Apply additional topics VIEWS, REFERNCIAL INTEGRETY, DATA NORMALIZATION