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
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