Upon completion of this course, the participant will be able to:
> Write for performance and test for quality
> Design, build, test, and effectively use Views
> Realize that great performance requires effective SQL design
> 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
> Know when SCALAR and AGGREGATE FUNCTIONs should be used
> Use the many types of sub query constructs most effectively
> Know when which one of the four JOIN types should be used for efficiencies
> Apply the typical SQL environments, Unix/Linux/Windows, and Mainframes
> Identify the FULL Select, Materialized Views, and Common Table Expressions
> Know Boolean constructs, CASE constructs, and multiple condition constructs
> Recognize how NULLs are helpful to reduce processing time and query complexity
> Utilize the 'nested table expression' to improve query performance
> Identify what and when the UNION operation is most effective vs. other techniques
> Apply how to establish tables, table constraints, referential integrity, and foreign keys
> Know the runtime LOCKING issues with INSERT, UPDATE and DELETE operations
> Know when to (and not to) use DISTINCT, IN, LIKE, BETWEEN and other predicates
> Recognize how DATE and TIME data types can effectively be used for audits and compliance