trainocate-ibm-training-b
Home > Vendors > IBM > k04009g

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

Overview

Duration: 1 day

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

Objectives

Please refer to course overview

Content

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

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

Certification

Schedule

Show Schedule for: