Advanced SQL for DB2 Course

Course Description

The Advanced SQL for DB2 course covers more advanced aspects of the Structured Query Language (SQL), with specific reference to the IBM DB2 database. Contents include advanced table joins, advanced operators, expressions, operators, views, user defined functions, stored procedures (DB2 SQL PL) and database performance considerations.

Course Objectives

Delegates who complete the dvanced SQL for DB2 course will be able to:

• Create more sophisticated joins between tables.
• Create advanced queries using expressions and operators.
• Understand factors that affect database performance.
• Use type conversion functions to produce formatted text.

Intended Audience

The Advanced SQL for DB2 course is suitable for anyone who needs to use advanced SQL to update or query the IBM DB2 database, either on its own or from within another development environment.

Course Prerequisites

A basic understanding of relational databases and previous experience writing SQL statements is essential.

Please ensure you meet the prerequisites for the Advanced SQL for DB2 Course before you attend the course. Read the importance of course prerequisites to understand why this is necessary.

Course Contents

Database Overview

Review of database components. •  Review of basic SQL statements.

Data Types

Date and time types. •  Type conversions for dates, times and numbers. •  Producing specifically formatted text. •  Effect of type conversions on performance.

Expressions

Understanding various operators. •  Building functions. •  Server-side (custom) functions. •  Using literals, constants and pseudo-constants. •  Using regular expressions in the LIKE clause. •  Effect on performance.

Joining Tables

The INNER join. •  The OUTER join. •  FULL, LEFT and RIGHT in joins. •  Result and cost of the type of join.

Advanced Data Query Commands

The UNION operator. •  The INTERSECT operator. •  The EXCEPT operator. •  Limiting the number of rows and columns returned by a query. •  Creating sub-queries.

Views

Creating and using views. •  Effect of views on performance.

Data Management Commands

The TRANSACTION command. •  The COMMIT command. •  The ROLLBACK command. •  The SAVEPOINT command.

Database Performance

Configuration aspects that influence the behaviour and performance of a database, from a programmer's perspective. •  Creating indices. •  Effect of indices on perfomance. •  Understanding and interpreting the results of the EXPLAIN clause. •  Effect of server-side objects, triggers and stored procedures on performance.

Download the Advanced SQL for DB2 course contents in PDF format for easy printing.

Home   |    Top of this page   |    Contact Us    Schedule   |    Course List   |    FAQ   |    Sitemap

Essential Skills for IT