dashDB SQL for Subqueries, Functions, Procedures, and Performance - eLearning

1.Using Subqueries
• Subquery in a basic predicate
• Subquery with IN predicate
• Subquery with a NOT IN predicate
• Subquery with ORDER BY
• Subquery with ALL predicate
• Subquery with ANY or SOME predicate
• Subquery with EXISTS predicate
• Activity
• SQL challenges
2. Using correlated subqueries
• Correlated subquery with an EXISTS predicate
• Scalar fullselect as a correlated subquery
• Update statement including a subquery
• Activity
• SQL Challenges
3. Scalar functions (other than DATE/TIME functions)
• Scalar function – SUBSTR – substring
• Scalar function – POSSTR – string position
• Scalar function – COALESCE/VALUE
• Scalar function – DECIMAL
• Scalar function – ROUND
• Scalar function – DIGITS
• Scalar function – SQRT and POWER
• Scalar function – CHAR with arguments other than date/time
• Scalar function – LENGTH
• Scalar functions – LTRIM/RTRIM – Left TRIM/Right Trim
• Activity
• SQL challenges
4. Scalar functions – DATE/TIME functions
• DATE, TIME, and TIMESTAMP formats
• Scalar function – CHAR with date/time arguments
• Scalar functions – date related (part 1)
• Scalar functions – date related (part 2)
• Scalar functions – time related
• Labelled DATE/TIME durations
• Activity
• SQL challenges
5. Table expressions
• Nested table expressions
• Nested table expressions in Joins
• Common table expressions (CTEs)
• SQL challenges
6. Recursive SQL
• SQL challenges
7. Introduction to UDTs, UDFs, and stored procedures
• User-defined distinct Types (UDTs)
• User-defined functions (UDFs)
• Sourced user-defined functions
• External user-defined functions
• User-defined SQL functions
• User-defined stored procedures
• Activity
• SQL challenges
8. SQL and dashDB performance
• Note on indexes
• dashDB optimizer
• Index overview
• Clustered and non-clustered indexes
• Index utilization
• Predicate processing
• General guidelines – correlated subqueries
• General guidelines – minimize dashDB sorts
• General guidelines – view usage
• General guidelines – expressions
• General guidelines – NOT EQUAL predicates
• General guidelines – arithmetic
• General guidelines – conversion
• General guidelines – retrieve only necessary data
• Monitor the SQL workload and use the EXPLAIN facility
• SQL challenges