UD08 - DB2 for LUW - Advanced SQL Workshop - 2 Days

Course Description

This training course teaches the delegate how to write advanced SQL statements including many new features introduced in recent DB2 releases.

Pre-requisites

A working knowledge of the DB2 host environment is advantageous but not essential.

The delegate should be able to code basic to intermediate SQL statements - these skills can be acquired by attending our SQL Workshop.

Objectives

The aim of this course is to provide the delegate with the necessary skills to write advanced SQL queries using the DB2 for LUW workbench.

On completion of this course the student will be able to:

  • use recent DDL enhancements
  • use new data types
  • use the MERGE statement
  • code inner and outer joins
  • code complex subqueries
  • code UNION, INTERSECT and EXCEPT statements
  • use the EXISTS clause
  • use SQL Scalar Functions
  • use the various GROUP BY features
  • code Common Table Expressions
  • code recursive SQL statements
  • store, generate and manipulate XML data
  • use Business and System Temporal Tables

Environment

The course applies to DB2 running in a Linux, Unix or Windows environment.

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

Review of SQL Fundamentals
Recap of SELECT, INSERT, UPDATE and DELETE
Recap of IN, LIKE, NOT and BETWEEN

Recent DDL Enhancements
New Data Types
Hidden Columns
Row Change Timestamps
Temporary Table usage
Locking enhancements

Advanced DML Statements
The MERGE Statement
SELECT from INSERT / UPDATE / DLEETE MERGE
Non Correllated Subqueries
Correllated Subqueries
The xxALLxx Subquery
The xxANYxx Or xxSOMExx Subquery
UNION and UNION ALL
INTERSECT and INTERSECT ALL
EXCEPT and EXCEPT ALL
The EXISTS clause
SQL Expressions
Scalar functions
Inner Joins
Left / Right Outer Joins
Full Outer Joins
The GROUP BY Clause
Additional GROUP BY Features
GROUP BY ROLLUP
The GROUPING Function
GROUP BY CUBE
GROUP BY Grouping Sets
The CASE Statement
Common Table Expressions
Recursive SQL

XML PROCESSING
eXtensible Markup Language Introduction
Well Formed Documents
XML Data
Integration of XML Data
XML Serialization Function - XML2Clob
XML Publishing Function - XMLElement
XML Publishing Function - XMLAttributes
XML Publishing Function - XMLForest
XML Publishing Function - XMLConcat
XML Publishing Function - XMLAgg
XML Publishing Function - XMLNamespaces
Overview of pureXML
XML Data Model
XML Parser
XML Schema Repository (XSR)
XPATH
XPath Axes
XPath Functions
XML Document Storage Infrastructure
Accessing XML Data
SQL/XML Publishing Functions
XMLCOMMENT
XMLDOCUMENT
XMLPI
XMLTEXT
XMLSERIALIZE
XMLPARSE
XMLQUERY
XMLEXISTS
XMLCAST
XMLTABLE
XML Indexes
Index Considerations
Application development
Database Administration Support
XML Schema
XML Decomposition

TEMPORAL TABLES
Temporal Tables
Temporal Tables and Versioning
Temporal Tables – Versioning Example
System Temporal Tables – Data Access
Temporal Tables – Considerations
Temporal Tables – Application Controlled
Business Temporal Tables – Data Selection
Business Temporal Tables – Update and Delete
Business Temporal Tables – Update Example
Business Temporal Tables – Delete Example

Course Enquiry