Understanding & Using SQL

Virtual Classroom Environment dates - click to book!

UK Start Times

What do I need?

• webcam
• headphones with microphone
• sufficient bandwidth, at least 1.5 Mb/s in each direction.

What you will learn

On successful completion of this course you will be able to:

• code SQL SELECT statements including those using built-in functions and techniques such as joins, nested table expressions, subqueries, unions and case expressions
• code SQL INSERT, UPDATE, DELETE and MERGE statements to modify data
• select the best options to use with SQL to achieve the required results
• (for Db2 for z/OS users) use EXPLAIN to evaluate SQL access path and performance
• (for Db2 for z/OS users) understand the factors influencing the Db2 optimizer and their effect on access path.

Who Should Attend

All of those requiring a thorough understanding of coding SQL data manipulation statements. The course is not suitable for those needing to embed the SQL in application programs.

Prerequisites

Attendees should have experience of a relational database environment.

Duration

3 days

Fee (per attendee)

£1680 (ex VAT)

This includes free online 24/7 access to course notes.

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

DSQL

Contents

Introduction to Structured Query Language

Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer.

Introduction to DB2 Interactive (DB2I)

DB2I primary option menu; DB2I option D - DB2I defaults; DB2I option 1 - SPUFI; Current SPUFI defaults; SPUFI SQL statement creation; Browsing SPUFI output; SPUFI commit or rollback panel; SPUFI AUTOCOMMIT options; DB2I option 2 - DCLGEN (Declarations Generator); DCLGEN output; DB2I option 3 - Program Preparation; DB2I option 4 - Precompile; DB2I option 5 - Bind / Rebind / Free; DB2I option 6 - Run; DB2I option 7 - DB2 commands; DB2I option 8 - DB2 utilities; Query Management Facility; Reporting options - SPUFI versus QMF; SPUFI report; QMF report.
.

Basic SELECT Statements

Supplied database structure; Supplied table formats; Specifying table names; Using view, alias or synonym; Specifying column names; Selecting all columns; Column sequence; Naming columns; Eliminating duplicate rows; Row sequence; Row restriction; Row restriction examples; Comparing columns from the same row; Multiple conditions; The BETWEEN operator; The IN operator; The LIKE operator; The SQL ESCAPE character; Searching for apostrophes; Arithmetic in SELECT statements; Arithmetic in the WHERE clause; Naming derived columns; Using literals in the SELECT list; Special registers: The concatenation operator; Date and time columns.

SQL Built-in Functions

Column functions; GROUP BY; HAVING; Rules for GROUP BY / HAVING; Scalar functions; Data conversion functions; Date manipulation functions; Numeric manipulation functions; String manipulation functions; Handling null values; User defined functions.

Joins & Nested Table Expressions

Inner JOIN; Correlation names; A JOIN of three tables; Joining a table to itself; A three-way join of two tables; Cartesian product; Outer join; Full outer join; Full outer join using COALESCE; Left / right outer joins; Nested table expressions - examples.

Unions, Exceptions, Intersections & CASE Expressions

UNION, INTERSECT and EXCEPT; UNION and UNION ALL; UNION with named derived result columns; INTERSECT and EXCEPT; CCASE expressions; CASE expression formats; CASE expressions in functions; CASE expressions in the WHERE clause.

Subqueries

Simple (non-correlated) subquery; Simple subquery with multiple rows; Subquery with multiple columns; Quantified predicates; Correlated subquery; Tests for existence with correlated subqueries; Performance consideration; NNull values and SQL.

Table Modification Statements

The INSERT statement; Inserting all columns, single row; Inserting a subset of columns, single row; Inserting multiple rows using SELECT; Inserting multiple rows using row set; Inserting using expressions; The UPDATE statement; Updating rows; Updating & case expressions; Updating and subselects; The DELETE statement; Deleting rows; The MERGE statement; Merging rows; Referential Integrity: & INSERT, & UPDATE, & DELETE; Referential Integrity review.

The DB2 Optimiser and Access Path Selection

DB2 optimiser; Access path selection; Access paths: index usage, direct row access, matching index access, non-matching index access, table(space) scan; Input to the optimiser; Filter factors; Filter factor and clustering; Filter factor and column cardinality; Filter factor and column correlation; Filter factor and column distribution; Influencing the optimiser; Influencing the optimiser: manually adjusting statistics, using optimisation hints, modelling production values; Catalog statistics; Updating catalog statistics using RUNSTATS; Statistics columns; RUNSTATS examples.

DB2 EXPLAIN

EXPLAIN; EXPLAIN table enhancements; EXPLAIN syntax enhancements; PLAN_TABLE (DB2 version 1); PLAN_TABLE (Versions 2 to 6); PLAN_TABLE (DB2 versions 7 to 11); DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN example 1 (basic access paths); EXPLAIN example 2 (multi-index access); EXPLAIN example 3 (nested queries).

Practical exercises

Throughout the course there are hands-on exercises covering basic DML, built-in functions, joins and unions, subselects and subqueries, table modification commands and (optionally) DB2 EXPLAIN.