Course Description
Advanced SQL for DB2 online training is designed for the seasoned SQL professional who wants to get more "bang for the buck" out of DB2, especially DB2 Database on the Linux, UNIX, and Windows platforms. This DB2 course presents advanced SQL syntax in an easy-to-understand format, demystifying even the most complex of the latest features. It will enable you to return to work much better prepared to avail yourself of the full power of the DB2 engine.
In order to take this course you need to have taken SQL Fundamentals for DB2 and have working experience in coding SQL statements. You should also be proficient with Windows and general PC knowledge. If you are a Linux/UNIX programmer, you should have the similar experience and knowledge before taking this course.
In This Course You Will Learn:
- Use relational database concepts and terminology fluently, and code simple to complex SELECT, INSERT, UPDATE, and DELETE statements
- Differentiate between the syntax elements of subselect, fullselect, and select-statement, and the terms query and sub¬query, and code both correlated and noncorrelated subqueries
- Identify various types of two-dimensional inputs to a query, including the two-dimensional output of a prior query, and combine and contrast multiple two-dimensional results with the UNION, INTERSECT, and EXCEPT set operators
- Use advanced expressions and scalar functions, as well as concatenations and calculations, including sophisticated date-time manipulation and arithmetic
- Differentiate between and code inner joins and left, right, and full outer joins, as well as Cartesian products
- Perform aggregate processing using advanced column functions, grouping sets, and the super groups ROLLUP and CUBE
- Code recursive SQL using common table expressions or views, and identify and avoid situations that cause infinite loops
- Code SQL that utilizes the vast array of object-relational features available in DB2 for Linux, UNIX, and Windows, including data-integrity constraints, user-defined distinct types (UDT’s), user-defined functions (UDF’s), triggers, stored procedures, and compound SQL
- Adhere to some simple guidelines to ensure that your SQL—whether extremely basic or impossibly complex—performs as well as possible
Prerequisites
Before taking this Advanced SQL for DB2 course, you should be able to code basic to intermediate SQL statements and have real-world experience coding such statements.
You can develop these skills by attending SQL Fundamentals for DB2. Download the SQL Self-Test to see if you meet the course prerequisites.
Proficiency with Windows, Notepad (or another Windows-based text editor), the mouse, and the PC in general is assumed. For a Linux/UNIX audience, familiarity with equivalent tools and concepts is assumed.
Outline
NOTE: Workshop environment, exercises and solutions are now available in the Downloads section this course page.
Module 01: Review of SQL Fundamentals Part 1
- Concepts and Terminology
- Simple SELECT Statements
Module 02: Review of SQL Fundamentals Part 2
- Types of Joins
- Aggregate Processing
- Subqueries and Unions
- Modification Statements
Module 03: Review of SQL Fundamentals Part 3
- Workshop 1: Familiarization and Review
Module 04: Subqueries
- Syntax Elements
- Noncorrelated and Correlated Subqueries
- Subquery Predicates
- Modification Statements with Subqueries
- Workshop 2: Subqueries
Module 05: Two-dimensional Matrices
- Two-dimensional Input and Output
- Views and Table Expressions
- Literal Tables: The VALUES Clause
Module 06: Set Operations
- Union
- Intersection
- Difference
- Workshop 3: VALUES, Table Expressions, and Set Operators
Module 07: Advanced Types of Expressions
- CASE Expression
- CAST Specification
- Labeled Duration
- Scalar Fullselect
Module 08: Advanced Functions and Calculations Part 1
- Data-type Conversion with Scalar Functions
- Numeric Scalar Functions and Arithmetic Operations
Module 09: Advanced Functions and Calculations Part 2
- String Scalar Functions and Concatenation
- Chronological Scalar Functions and Date-time Arithmetic
- Miscellaneous Scalar Functions
- Workshop 4: Expressions and Scalar Functions
Module 10: Inner and Outer Joins
- Syntax Options for Inner Joins
- Left, Right, and Full Outer Joins
- Filtering Rows in Outer Joins
- Case Studies
- Workshop 5: Inner and Outer Joins
Module 11: Aggregation
- Aggregate Processing
- Advanced Column Functions
- Grouping Sets and Super Groups
- Workshop 6: Grouping Sets and Super Groups
Module 12: Recursive SQL
- What Is Recursion?
- Recursion with Common Table Expressions
- Recursion with Views
- Rules for Recursive Queries
- Workshop 7: Recursive SQL
Module 13: Object-Relational Features
- Database-managed Data Integrity
- Triggers and Stored Procedures
- User-defined Distinct Types (UDT's)
- User-defined Functions (UDF's)
- Topics Not (Yet) Covered
- Workshop 8: User-defined Database Objects
Module 14: Performance Considerations
- Performance Monitoring
- Database Design Guidelines
- Application Design Guidelines
- The Optimizer's Three Primary Inputs
- Explain: Visual and Otherwise
- SQL Tweaking Techniques
- Workshop 9: Visual Explain