SUMMARY:   AS/400 and iSeries users who wish to access data and use the facilities provided by SQL. Programmers who need a solid understanding of the SQL language, and how to use the language effectively and efficiently. PREREQUISITES:   Working familiarity with the iSeries 400 environment and some general knowledge about relational databases is desirable. Users or programmers who will use the Query/400 environment are the target audience. DURATION:   3 Days APPROACH:   Instructor-led lectures, demonstrations, and progressive hands-on workshops. OBJECTIVES:   Use SQL DDL (Data Definition) and DML (Data Modification) with STRSQL Use the Operations Navigator interface Use SQL statements to extract, display, and update data Create and drop files (tables) Use the SQL Query Manager COURSE CONTENT:   INTRODUCTION TO RELATIONAL DATABASE SYSTEMS The origin and history of IBM relational products Terminology pertinent to relational environments Overview of DB2/400 components How data is physically stored in a relational DBMS The logical organization of the database How the system retrieves data from the database Data relationships SQL syntax rules Using STRSQL, and how to build and execute interactive SQL queries USING SQL FOR BASIC DATA RETRIEVAL Syntax of SELECT statements Tailoring a Select List to choose specific columns Building expressions Using the WHERE clause to choose specific rows How to eliminate duplicate data Coping with NULL values during data retrieval Using DISTINCT, ANY and ALL Rules for logical comparison of data Boolean comparison, using NOT, AND and OR Rules for arithmetic comparison of data Using IN and NOT IN to build include lists Using BETWEEN and NOT BETWEEN to define ranges Pattern-matching and building search arguments, using LIKE, NOT LIKE, and wildcard values Sorting output, using the ORDER BY clause The EXISTS and NOT EXISTS predicates SRTSQL and OPS NAV SQL language features to avoid! USING SQL FOR ADVANCED DATA RETRIEVAL Using Aggregate Functions, including AVG, MIN, MAX, COUNT (*), COUNT (DISTINCT), and SUM Using Scalar Functions, including data-type conversion, character string manipulation, and date-time functions GROUP BY and HAVING clauses UNION, INTERSECT and EXCEPT keywords Joining tables properly and efficiently Uses for Sub-queries Uses for Views USING SQL FOR DATA MODIFICATION The INSERT statement, including examination of statement parameters and referential integrity implications The UPDATE statement, including examination of statement parameters and referential integrity implications The DELETE statement, including examination of statement parameters and referential integrity implications The COMMIT and ROLLBACK statements and database recovery JB/07