Advanced SQL

Duration:   2 days

Participants:   Application programmers and developers, support personnel, and experienced end users who know the basics of SQL (Structured Query Language) and now need to master the full range of SQL's capabilities.

Objectives:   Upon successful completion of this course you will be able to: -  Code sophisticated SQL select statements, including joins, unions, and subqueries, to handle complex data requests. -  Code insert, update, and delete statements to access and change data in DB2 tables. -  Describe the impact of referential integrity on insert, update, and delete processing. -  Query the DB2 catalog. -  Identify indexable predicates. -  Use the DB2 EXPLAIN command to analyze the performance characteristics of a query. -  Code SQL queries for optimal performance.

Overview:   -  This course provides you with an in-depth understanding of SQL. Your mastery of Structured Query Language will be strengthened through extensive workshops and classroom lecture. -  Topics such as EXPLAIN and DB2 catalog are only lightly covered or omitted. If extensive coverage of EXPLAIN and DB2 catalog are needed at your site please look at the 4-day course outline Advanced SQL with EXPLAINs.

Prerequisites:   Experience coding basic SQL statements under SPUFI or QMF or similar interactive interface, and a basic understanding of relational DB concepts. You can gain this knowledge by attending either our QMF Workshop or our Introduction to ANSI SQL course.

Format:   Lecture and discussion with extensive hands-on exercises.

Topic Outline:   -  Creating Objects with Data Definition Language (DDL)         Creating the EMPLOYEE table         CREATE TABLE syntax         DB2 indexes         DB2 index structure - the "B" tree         Creating an INDEX         Inserting rows into the EMPLOYEE table         Creating the ORG TAB table with a FOREIGN KEY         Creating a DB2 VIEW         Retrieving data from a DB2 VIEW         Creating a DB2 SYNONYM         Dropping an object -  SELECT Statement Review         Clauses of the select statement         The SELECT statement syntax         SQL formatting rules         Simple SELECT statement         The DISTINCT keyword         Eliminating duplicate rows         Effect of NULLS         Selecting specific rows (coding the WHERE clause)         What is an SQL predicate?         DB2 SQL statement processing         DB2 predicate types         SQL OPERATORS         Coding constants in the WHERE clause         Using the IN operator         Using the BETWEEN operator         Using the LIKE operator         Multiple predicates (using the AND operator)         Using the AND and OR Operators         Using the NOT operator         Sorting the result (coding the ORDER BY clause)         Using numbers in the ORDER BY clause -  Aggregate Functions and GROUP BY         Aggregate functions         Using the GROUP BY and the aggregate functions         Understanding the GROUP BY clause         The COUNT built-in function         Multiple columns in the GROUP BY clause         The HAVING clause -  Scalar Functions and Special Registers         Scalar (row) functions example         Using the CHAR, DATE, SUBSTR, and concatenation functions         Scalar function summary         DB2 special registers         Date and time considerations -  Updating Tables         INSERT Statement         Multi-row insert processing         UPDATE Statement         Restrictions on updating primary key         DELETE Statement -  Joining Tables         Understanding the join operation              Cartesian product              Equijoin              Natural join              Greater than join              Joining rows of the same table              Outer joins (left, right, full)         Join common column chart         Coding a two-way join         Multi-column join         Greater Than join         Joining a table to itself         Coding a three-way join         Coding a four-way join         Mixing JOIN and GROUP BY         Mixing JOIN, GROUP BY, and HAVING         Line numbers with JOIN and GROUP BY -  Subqueries         What is a subquery?         Converting a subquery to a join         Using the IN operator         Using the IN operator in a Multi-level subquery         Using the ALL operator         Using the ANY operator         Using the EXISTS operator         Subquery in the HAVING clause         Multiple subqueries in the HAVING clause -  Correlated Subqueries         What is a correlated subquery?         Correlated subquery         Correlated subquery in the HAVING clause         Correlated subquery - using the EXISTS operator         Correlated subquery - using the NOT EXISTS operator         Correlated subquery - the kitchen sink! -  Coding the UNION Operation         The UNION operation         UNION              Union of two SELECTs              Union with ORDER BY              Union coding rules         Coding the outer join -  Querying the DB2 Catalog  (Optional)         What is the DB2 catalog?         Listing all your tables (SYSTABLES)         List all columns in a table (SYSCOLUMNS)         List all columns of all your tables (SYSCOLUMNS + SYSTABLES)         List dependent plans (SYSPLANDEP)         LIST database privileges (SYSDBAUTH) -  Using EXPLAIN to Analyze a Query  (Optional)         Benefits of EXPLAIN         DB2 V3 access paths         Relative join performance         Creating a PLAN TABLE         PLAN TABLE columns         Clearing your PLAN TABLE         Executing EXPLAIN         EXPLAIN examples and analysis -  DB2 Performance Tuning         DB2 performance tuning         Denormalization techniques         Stogroup guidelines         Database guidelines         Tablespace guidelines         Table definition guidelines         Indexing guidelines         SQL guidelines -  Additional Topics         Parallel query processing         Nested table expressions

Mainframe List       Database List       Course List       Site Map       Home