K04009G: dashDB SQL for Subqueries, Functions, Procedures, and Performance

Module 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
Module 2: Using correlated subqueries
• Correlated subquery with an EXISTS predicate

• Scalar fullselect as a correlated subquery

• Update statement including a subquery

• Activity

• SQL Challenges
Module 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
Module 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
Module 5: Table Expressions
• Nested table expressions

• Nested table expressions in Joins

• Common table expressions (CTEs)• SQL challenges
Module 6: Recursive SQL
• SQL challenges
Module 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
Module 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