Learn: in:
» back to Search Results

Course rating of 0 Vendor rating of 4

Intended to give experienced application developers better query testing techniques, more depth with complex SQL syntax, a good grasp of performance considerations, and an overview of writing stored procedures, functions and triggers.

Course Outline
This course is intended to give experienced application developers better query testing techniques, more depth with complex SQL syntax, a good grasp of performance considerations, and an overview of writing stored procedures, functions and triggers.

At the end of this course, students will be able to:
  • Sample a large database and create testing strategies
  • View the optimizer's execution plan and evaluate the performance of logically equivalent statements
  • Choose the most correct, clear and efficient way of combining data from multiple tables, including inner and outer joins, recursive or self-joins, Exists and Not Exists subqueries, Unions, etc
  • Handle common data types, cast them, properly round numbers, handle date/time values, and work with text data, including using Regular Expressions and Full Text Indexing
  • Use functions and expressions as constraints on database columns
  • Use Case logic in all Select clauses, Updates, and nested within functions
  • Summarize data, including crosstab, rollup and cube reports
  • €Write basic types of stored procedures, user-defined functions, and triggers
  • Introduction
  • Creating and Updating Tables
  • Using Multiple Tables
  • Data Types
  • Text Handling Issues
  • Case Logic
  • SQL Summarization
  • XML and related issues
  • Stored Procedures
  • User Defined Functions
  • Triggers

This course is designed for application developers, data analysts, and others who work in any relational database environment, including Oracle, SQL Server, Sybase, DB2 for z/OS or Universal Database, Access, Ingres, Informix, and many others.

Course Outline

I. Introduction
A. Objectives, Chapter Table of Contents
B. Overview
C. DBMSs covered in the course, online reference info
D. Learning about the database structure
E. Catalog tables or data dictionary
F. Sampling a large database: Optimization
G. Table size, Key range
H. Limiting the size of the result set
I. Key cardinality/selectivity/filter factor
J. Key distribution
K. Table relationships
L. Productivity tools: synonyms and views
M. Performance considerations
N. Maximizing index use
O. Statistics and Access Paths
P. Viewing a query execution plan
Q. Optimizer hints
R. Course database definition

II. Creating and Updating Tables
A. Objectives, Chapter Table of Contents
B. Creating tables2
C. Create Table and Create Index examples
D. Creating temporary tables
E. Insert; Transactions
F. Insert examples
G. Inserting rows from other tables
H. Make-table statements
I. Update and Merge
J. Update examples
K. Merge examples
L. Delete
M. Creating views
N. Create View examples

III. Using Multiple Tables
A. Objectives, Chapter Table of Contents
B. Intersection: inner joins
C. Join performance considerations
D. Inner joins vs. Outer joins
E. Left Outer Joins
F. Right and Full Outer Joins
G. Joins vs. Subqueries
H. Recursive joins and complex relationships
I. More complex relationships
J. The "bill of materials" problem
K. Recursive With
L. Oracle Connect By
M. SQL Server 2008 HierarchyID
N. Difference: Not Exists and Not In5
O. Difference set operator: Except or Minus
P. Other correlated subqueries
Q. Set operators: Union, Union All

IV. Data Types
A. Objectives, Chapter Table of Contents
B. Data types and casting
C. Working with unlike types
D. Conversion functions, the Cast function
E. Null values
F. Numeric functions: Round, Ceiling, Floor
G. Date and time functions
H. Date part extraction
I. Century issues
J. Date differences (intervals, ages)
K. Date increments
L. Additional date/time types in SQL Server 2008 and Oracle 10g

V. Text Handling Issues
A. Objectives, Chapter Table of Contents
B. Text handling functions
C. Character data types
D. Functions and expressions €" overview
E. Text functions in Select statements
F. Text functions as constraints
G. Text functions in Updating
H. Soundex
I. Case (upper and lower)
J. Regular Expressions
K. Examples
L. Brief Regular Expression reference
M. Additional Oracle examples
N. Regular Expressions as constraints
O. Full Text Indexing
P. Examples of creating full text index

VI. Case Logic
A. Objectives, Chapter Table of Contents
B. Simple When clauses
C. Alternatives: Oracle Decode, database tables
D. Searched When clauses
E. Case in other contexts
F. Within functions ad expressions
G. In a From clause
H. In an Order By clause
I. In an Update

VII. SQL Summarization
A. Objectives, Chapter Table of Contents
B. Review: Group By
C. Column (aggregate) functions used with Group By
D. Filtering groups with Having
E. Having clauses with subqueries
F. Quantified predicate examples
G. Additional summarizing examples
H. Missing values
I. Creating temporary tables to fill in missing values
J. Crosstab reports
K. Using Case logic
L. Subtotals and grand totals: rollup and cube
M. Other considerations: logical and performance issues

VIII. XML and related issues
A. Chapter introduction
B. Overview of XML
C. XML in Relational Databases Support in versions of DBMSs
D. Creating and loading XML data
E. Creating a table with a weakly-typed XML column
F. Inserting data into XML columns
G. Querying XML data
H. XQuery
I. XPath
J. Examples
K. Strongly-typed XML
L. Nested tables and arrays Overloading stored procedures

IX. Stored Procedures
A. Objectives, Chapter Table of Contents
B. Overview
C. Stored procedures in n-tier application design,
D. Stored procedure support by DBMS; languages
E. Simple stored procedures
F. SQL Server, Oracle, DB2, MySQL examples
G. DB2 COBOL example
H. Invoking stored procedures from applications
I. Dropping and replacing stored procedures
J. Using the catalog or data dictionary to locate stored procedures
K. Input and output parameters
L. SQL Server examples
M. Oracle examples
N. DB2 and MySQL examples
O. DB2 COBOL example
P. Using stored procedures from VB, C#, Java
Q. Optional parameters and default values
R. Returning a result set; cursors
S. SQL Server examples
T. Oracle, DB2 and MySQL examples
U. Client examples in VB, C# and Java
V. Debugging

X. User Defined Functions
A. Objectives, Chapter Table of Contents
B. Overview
C. Creating, altering and dropping functions, using catalog/data dictionary 9.2
D. Scalar functions
E. SQL Server, Oracle, DB2, MySQL examples
F. Optional parameters and default values
G. Using scalar functions in SQL queries
H. Performance issues
I. Scalar functions with procedural logic
J. SQL Server and Oracle examples
K. DB2, MySQL and COBOL examples
L. Table functions
M. SQL Server, DB2 and MySQL examples
N. Oracle table and row types, table function and pipelined function

XI. Triggers
A. Objectives, Chapter Table of Contents
B. Overview: creating, altering, dropping triggers
C. Uses of triggers
D. Support for trigger features, by DBMS
E. Administering triggers, locating in the catalog/data dictionary
F. "After" triggers: SQL Server, Oracle, DB2, MySQL examples
G. Row-level triggers and transition variables
H. SQL Server example using deleted and inserted tables
I. Oracle, DB2, MySQL examples
J. "Before" and "Instead Of" triggers
K. SQL Server "instead of" trigger
L. Oracle, DB2 and MySQL examples
Prerequisites & Certificates

Students should have taken the SQL for Application Developers (also known as SQL Basics, or SQL Level 1) or an equivalent course, or had at least six months of experience using SQL in any relational DBMS environment, including DB2, Oracle, SQL Server, Sybase, MySQL, Informix, Ingres, Access, etc. The course assumes a good knowledge of the Select, Insert, Update and Delete statements, basic joins and grouping, and of some editor such as SQL Server Management Studio, SQL*Plus, SQL Developer, Toad, etc.

Certificates offered

Certificate of completion

Cancellation Policy
10 Day cancellation required for all courses in order to get a full refund.
Map & Reviews
Protech Training
[ View Provider's Profile ]


This course has not yet been rated by one of our members.

If you have taken a course through this vendor please log into your account and leave feedback for this vendor. You will be helping ensure our members get directed to the best training facilities.

Here are some reviews of the training vendor.
I took the training remotely so I cannot comment on some of the questions. I think that this seminar has too much content to squeeze it into one week.... might be better as a two week course?
Reviewed by 2013
found the material very helpful - although there was a lot of material covered - the pace was well set. Would definitely recommend people to have explored Microsoft Access beforehand to be familiarized with the program before taking the course. I would imagine someone without any exposure would find it confusing. Environment really good - people very, very nice and helpful. Would definitely take the next level of access with the same training facility.
Reviewed by 2013
I was not in the physical classroom. Feedback centered around online training would be good because now I have to rate items that I was not present for. Those items are getting half a star because I was not there to rate them. The course material must go through an editing process. There are a lot of grammar mistakes, but also a lot of actual procedure/activity mistakes.
Reviewed by 2012

This course currently does not have any dates scheduled. Please call 1-877-313-8881 to enquire about future dates or scheduling a private, in house course for your team.

This page has been viewed 818 times.