Informix Structured Query Language

Introduction to Structured Query Language

• Define the term Structured Query Language (SQL)
• Identify the different categories of SQL statements
• Identify the topics covered in this course

Informix Data Types

• List and describe the Informix data types

The Demonstration Database

• Describe the major concepts of relational databases
• Identify the tables in the stores_demo database
• Identify the relationships between these tables
• Install a copy of this database
• Use DB-Access to edit and run queries

Single Table SELECT Statements

• Write a single table SELECT statement
• List the optional clauses of a SELECT statement
• Use the WHERE clause
• Use the ORDER BY clause

SELECT Clauses and Aggregates

• Recognize arithmetic expressions
• Use aggregate functions in a SELECT statement
• Use the GROUP BY clause
• Use the HAVING clause
• Put SELECT data into a temporary table with the INTO TEMP clause
• Use NULL values in expressions

Built-In Functions

• Use date and time functions
• Convert DATE or DATETIME values to character strings
• Perform case-insensitive searches
• Use string manipulation functions
• Use the concatenation operator
• Remove leading and trailing characters
• Use numeric functions
• Write conditional expressions

Joining Tables in a SELECT Statement

• Access data from two or more tables with SELECT
• Avoid a Cartesian product
• Assign aliases to table names

Complex Joins

• Describe the differences between inner and outer joins
• Describe the different types of outer joins
• Use various types of complex joins in queries
• Describe the purpose of a self join and use in a query
• Use the UNION operator in an SQL statement

Subqueries

• Write queries that use subqueries
• Write a SELECT statement that uses nested subqueries
• Explain the purpose of a correlated subquery

Temporary Tables

• Write queries that explicitly create temporary tables
• List reasons why temporary tables are needed in queries
• Use the WITH NO LOG option when creating temporary tables

INSERT, UPDATE, and DELETE Statements

• Write an INSERT statement to enter data into a table
• Write an UPDATE statement to change data in a table
• Write a DELETE statement to remove data from a table
• Write a LOAD statement to add rows into a table from a file
• Write an UNLOAD statement to create a file that contains data from a database

Other SQL Features

• Use synonyms and views in place of tables in a query
• Access remote tables in a query
• Write queries to select and insert simple and smart large objects
• Explain how to query on tables that contain user-defined data types
• Explain how to use user-defined routines in a query

SQL Optimization

• Explain the purpose of the Informix optimizer
• Define the choices of access paths available to the optimizer
• Describe the different types of indexes and their importance to query optimization
• Use SET EXPLAIN to obtain information about how a query is optimized
• Identify the choices made by the optimizer based on information written to the sqexplain.out file
• Use optimizer directives to influence the optimization path for a query