dashDB SQL for Subqueries, Functions, Procedures, and Performance - eLearning (K04009G-WBT)

Overview

This course is intended for Developers, Database Administrators, and System Programmers who require further insight into the SQL language.

Note: Guided eLearning is a self-paced offering which includes web-based content for self-study and videos (including audio) that demonstrate activities.

If you are enrolling in a Self Paced Virtual Classroom or Web Based Training course, before you enroll, please review the Self-Paced Virtual Classes and Web-Based Training Classes on our Terms and Conditions page, as well as the system requirements, to ensure that your system meets the minimum requirements for this course. http://www.ibm.com/training/terms

Audience

This course is intended for Developers, Database Administrators, and System Programmers who require further insight into the SQL language.

Prerequisites

• dashDB SQL for Basic Queries (K04001)
• dashDB SQL for tables, views, advanced queries, and analytic constructs (K04004)
• Or equivalent experience or knowledge

Objective

Please refer to course overview

részletek megjelenítése

Course Outline

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