| Course Topics: |
Great performance requires effective design
'Thought process' of developing an effective query
SQL common denominator among different engines
Use of the elusive ALL, ANY, and EXIST parameters
Efficiencies and the inefficiencies in the SQL language
What correlation variables can do to simplify query processing?
When SCALAR and AGGREGATE FUNCTIONS should be used
How to use the many types of sub query constructs most effectively
When which one of the four JOIN types should be used for efficiencies
Typical SQL environments, Unix/Linux/Windows, and Mainframes
Boolean constructs, CASE constructs, and multiple condition constructs
How NULLs are helpful to reduce processing time and query complexity
When to utilize the 'nested table expression' to improve query performance
What and when the UNION operation is most effective vs. other techniques
When to utilize INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL
When and how ‘recursive SQL’ could be the correct information access solution
When to (and not to) use DISTINCT, IN, LIKE, BETWEEN and other predicates
LOCKING issue with SELECT, INSERT, UPDATE, and DELETE operations
DATE and TIME data types can effectively be used for audits and compliance
‘Inline Views’ or ‘Nested Table Expressions’ are appropriate for performance
When to issue COMMIT and/or ROLLBACK and their effect on the transaction mix
Addendum topics VIEWS, REFERNCIAL INTEGRETY, DATA NORMALIZATION |