Learning ANSI SQL Programming
Duration
Two Days
Course Pre-requisites
This course is intended for those who have previous exposure to the Windows Operating System.
Who Should Attend
This class is for programmers, systems analysts, programmer/analysts and other data processing professionals who require an in depth knowledge of programming in structured Query Language (SQL).
Course Description
Learning ANSI SQL Programming is a discussion of relational databases, followed by a presentation of table operations. The Structured Query Language (SQL) is also discussed. The rest of the course is devoted to accessing SQL interactively using Microsoft Access and embedding SQL. Five computer case studies are assigned: three interactive exercises and two programs using SELECT, UPDATE, INSERT, and DELETE and cursor processing.
Course Topics
Overview of SQL
- Structured Query Language (SQL)
- Purpose and Scope of SQL
- Relational databases
- Tables
- Table operations
- Select
- Update
- Delete
- Insert
- Join, Union
- Views
SQL DDL (Data Definition Language)
- Create Table
- Create Index
- Alter
- Drop
DML (Data Manipulation Language) Statements: Select
- Select
- Projection of columns and expressions
- WHERE Clause
DML: More on Select
- Predicates
- Basic
- BETWEEN
- IN
- IS NULL
- LIKE
- Functions
- Aggregate (MIN, MAX, SUM, COUNT, AVG)
- Scalar
- Use of GROUP BY and HAVING
Joins and Subselects
- Join
- Inner vs. Outer Join
- Subselects
- Simple
- Correlated
Other DML Statements
- INSERT
- UPDATE
- DELETE
Applications Programming
- EXEC SQL format
- SQL Communication Area
- Use of DCLGENs
- Error Handling
- Host variables/structures
Preparing a Program for Execution
- Preprocessing
- Binding
- PLANS
- PACKAGES
Cursor Processing
- DECLARE CURSOR
- OPEN
- FETCH
- CLOSE
Additional Considerations in Application Programming
- Handling NULL columns
- Indicator Variables
- Handling VARCHAR columns
- Detailed Error Checking
