Course Details:
Intermediate SQL for Ingres

Location: On-site, Worldwide
Format: Lecture and Labs
Course Code: ISI

For experienced programmers who want to expand their skill set to include SQL. This 4-day class covers the basics of SQL (including embedded SQL) plus commonly used Ingres™-specific extensions. Training is provided using Ingres on UNIX® or Windows®. An abbreviated 3-day version of this program is also offered at a reduced rate for the non-programmer. Includes thorough and correct treatment of grouping and aggregation, outer-joins, correlated updates and case-expressions.

Prerequisites:

There are no firm prerequisites, however some previous programming experience (e.g. C, Cobol, ABF/4GL, OpenROAD, Java™, or other language) may be helpful. Although C is used as the host programming language for the embedded SQL lab exercises, all necessary supporting C code is supplied as part of the exercise. C programming skills are not specifically required.

Contents

Day 1

Chapter 1: Introduction to SELECT

introducing SQL / scope of SQL / tables / SELECT / restriction / projection /logical order of execution / result column names / ORDER BY / ISQL / SQL / HELP pseudo-statement / the exercise database

Chapter 2: Data Types, Expressions, and Functions

numeric types / integers / arithmetic / relational operations / physical storage / casting and type conversion / implicit casting / explicit type conversion / decimal / floating point / nulls / ifnull function / strings types / varchar(n) / char(n) / string manipulation / zero-length strings / byte / byte varying / abstract types / date / date arithmetic / data functions / money /BLObs / user-defined types / scalar functions

Chapter 3: Creating Tables and Views

CREATE TABLE / constraints / primary key / references / unique / check / CREATE VIEW / DECLARE GLOBAL TEMPORARY TABLE

Day 2

Chapter 4: Introduction to Updating

UPDATE / logical order of execution / INSERT / column mapping / DELETE / transactions / COMMIT / ROLLBACK / CREATE AS SELECT

Chapter 5: Grouping and Aggregation

eliminating duplicates / aggregation / nulls / special rules for COUNT() / GROUP BY / logical order of execution / HAVING

Chapter 6: Joins and Outer-Joins

multiple tables / cartesian products / meaningful combinations /correlation names / multi-column joins / more than two tables / self-join /missing entities / outer-join / left-join / right- and full-join / multiway joins

Day 3

Chapter 7: Subqueries and Unions

select-expressions / composing select-expressions / unions of select-expressions / subqueries / correlated subqueries / using subqueries / list-membership / existentially quantified queries / scalar subqueries / any-or-all predicates

Chapter 8: Correlated Updates and COPY

UPDATE revisited / logical order of execution / UPDATE FROM / correlated DELETE / COPY...FROM / COPY...INTO / COPY syntax / binary versus ASCII copy / bulk copy / incremental copy

Day 4

Chapter 9: Introduction to Embedded SQL-Part I

using SQL in a 3GL / 3GLs / embedded SQL / running a preprocessor / API code generation / supported SQL / CONNECT / host language variables / dereferencing / SELECT...INTO / scope of variables / permitted uses / select-loops / cursors / read-only cursors / embedded INSERT / UPDATE / DELETE / REPEATED queries / indicator variables / returning nulls / INCLUDE

Chapter 10: Introduction to Embedded SQL-Part II

detecting errors / error indicators / SQL communication area / explicit error checking / inquire_sql() / implicit error checking / WHENEVER / custom error-handlers / row counts / dynamic SQL / EXECUTE IMMEDIATE