| SQL Reference Empress v8.62 May 2006
--- | ---

  © 1983, 2006 Empress Software Inc.

  All rights reserved. Reproduction of this document in whole or part, by electronic or any other means, is prohibited without written consent from Empress Software Inc.

  RESTRICTED RIGHTS LEGEND

  Use, duplication, or disclosure by the Government is subject to restrictions as set forth in
  subparagraph (c) (1) (ii) of the Rights in Technical Data and Computer Software clause at 52.227-7013.

  05/06

  Part #: 001-06-A4-110

  This manual is the reference for the Query Language command syntax of the Empress Relational Database Management System. It covers the syntax and structure of available commands. Each description contains information on the command arguments, privileges, and in many cases, examples of command use.

  ## Table of Contents

  ### CHAPTER 1: Introduction

  | 1.1 | Empress - A Relational Database Management System
  | 1.2 | The Example Database

  ### CHAPTER 2: Empress Conventions

  | 2.1 | Command Syntax Notation
  | 2.2 | Entering Commands

  |  | 2.2.1 | Free-Form Commands
  |  | 2.2.2 | Single-Line Commands

  | 2.3 | Entering Comments

  | 2.4 | Databases, Tables, Attributes and Filenames

  |  | 2.4.1 | Simple Names
  |  | 2.4.2 | Complex Names
  |  | 2.4.3 | Accessing Tables in Other Databases
  |  | 2.4.4 | Distinguishing Attributes from Each Other
  |  | 2.4.5 | Distinguishing Attributes from Values
  |  | 2.4.6 | Operating System Filenames

  | 2.5 | The Use of Quotes
  | 2.6 | Missing Data Values
  | 2.7 | Reserved Keywords

  |  | 2.7.1 | Keywords recognized by Empress Parser for Interactive SQL
  |  | 2.7.2 | Keywords from Set of Empress Built-in Functions
  |  | 2.7.3 | Keywords from Set of Default Mathematics Functions supported by Empress

  | 2.8 | Synonyms

  | 2.9 | Variables and Strings
  | 2.10 | Error Messages

  | 2.11 | Erasing Characters and Aborting Commands
  | 2.12 | Access to an Empress Database
  | 2.13 | Empress Operational Parameters

  ### CHAPTER 3: Data Types

  | 3.1 | Introduction
  | 3.2 | Data Type Ranges

  |  | 3.2.1 | Parametric Data Types
  |  | 3.2.2 | Generic Data Types

  | 3.3 | Character Data

  |  | 3.3.1 | CHARACTER Data Type

  |  |  | 3.3.1.1 | Entering Very Long Lines

  |  | 3.3.2 | TEXT Data Type

  |  |  | 3.3.2.1 | Display Length
  |  |  | 3.3.2.2 | Primary Storage Length
  |  |  | 3.3.2.3 | Overflow Storage Length
  |  |  | 3.3.2.4 | Directly Accessible Extents

  |  | 3.3.3 | NLSCHARACTER Data Type
  |  | 3.3.4 | NLSTEXT Data Type

  | 3.4 | Date Data

  |  | 3.4.1 | DATE Data Type

  |  |  | 3.4.1.1 | Today's Date
  |  |  | 3.4.1.2 | Universal Date Format
  |  |  | 3.4.1.3 | Customized Date Entry Format
  |  |  | 3.4.1.4 | Interpreting Dates
  |  |  | 3.4.1.5 | Dates in Expressions
  |  |  | 3.4.1.6 | Century Indicator for Two-Digit Year DATE Data Type

  |  | 3.4.2 | TIME Data Type

  |  |  | 3.4.2.1 | Current Time

  |  | 3.4.3 | MICROTIMESTAMP Data Type

  | 3.5 | Decimal Data

  |  | 3.5.1 | DECIMAL Data Type
  |  | 3.5.2 | DOLLAR Data Type

  |  |  | 3.5.2.1 | Customized Dollar Formats

  | 3.6 | Float Data
  | 3.7 | Integer Data
  | 3.8 | User Interpreted Data

  ### CHAPTER 4: Data Definition Commands

  | 4.1 | Introduction
  | 4.2 | CREATE TABLE
  | 4.3 | DROP TABLE
  | 4.4 | ALTER TABLE
  | 4.5 | RENAME
  | 4.6 | DISPLAY TABLE
  | 4.7 | CREATE INDEX
  | 4.8 | DROP INDEX
  | 4.9 | CREATE RANGE CHECK
  | 4.10 | DROP RANGE CHECK
  | 4.11 | CREATE REFERENTIAL
  | 4.12 | DROP REFERENTIAL
  | 4.13 | CREATE VIEW
  | 4.14 | DROP VIEW
  | 4.15 | CREATE COMMENT
  | 4.16 | DROP COMMENT
  | 4.17 | GRANT PRIVILEGE
  | 4.18 | DISPLAY PRIVILEGE
  | 4.19 | DISPLAY GRANT PRIVILEGE
  | 4.20 | REVOKE PRIVILEGE
  | 4.21 | LOCK LEVEL
  | 4.22 | CREATE MODULE
  | 4.23 | UPDATE MODULE
  | 4.24 | DROP MODULE
  | 4.25 | DISPLAY MODULE
  | 4.26 | CREATE TRIGGER
  | 4.27 | DROP TRIGGER
  | 4.28 | DISPLAY DATABASE
  | 4.29 | CREATE REPLICATION MASTER
  | 4.30 | DROP REPLICATION MASTER
  | 4.31 | CREATE REPLICATION REPLICATE
  | 4.32 | DROP REPLICATION REPLICATE
  | 4.33 | CREATE REPLICATE TABLE
  | 4.34 | CREATE ROLE
  | 4.35 | DROP ROLE
  | 4.36 | GRANT ROLE
  | 4.37 | DISPLAY ROLE
  | 4.38 | REVOKE ROLE

  ### CHAPTER 5: Data Manipulation Commands

  | 5.1 | Introduction
  | 5.2 | INSERT
  | 5.3 | SELECT
  | 5.4 | UPDATE
  | 5.5 | DELETE
  | 5.6 | EMPTY TABLE
  | 5.7 | CALL
  | 5.8 | LOCK TABLE
  | 5.9 | SORT

  ### CHAPTER 6: Data Control Commands

  | 6.1 | Introduction
  | 6.2 | START WORK
  | 6.3 | COMMIT WORK
  | 6.4 | ROLLBACK WORK
  | 6.5 | SAVEPOINT
  | 6.6 | DISPLAY WORK

  ### CHAPTER 7: The Executive Interface

  | 7.1 | Introduction
  | 7.2 | CHANGE WORD
  | 7.3 | DATABASE
  | 7.4 | DO and !
  | 7.5 | EDIT
  | 7.6 | EXIT
  | 7.7 | HELP
  | 7.8 | HOLD
  | 7.9 | RECALL
  | 7.10 | RUN
  | 7.11 | RUN REPORT
  | 7.12 | SET and UNSET
  | 7.13 | SHOW
  | 7.14 | STORE

  ### CHAPTER 8: The Interactive Interface

  | 8.1 | Introduction
  | 8.2 | Interactive Interface Commands
  | 8.3 | Interactive ALTER Command
  | 8.4 | Interactive CREATE Command
  | 8.5 | Interactive INSERT Command
  | 8.6 | Interactive UPDATE Command
  | 8.7 | Using Editors

  ### CHAPTER 9: Expressions

  | 9.1 | Introduction
  | 9.2 | Operators

  ### CHAPTER 10: Functions

  | 10.1 | Introduction
  | 10.2 | Aggregate Functions

  |  | 10.2.1 | COUNT Function
  |  | 10.2.2 | AVG, MAX, MIN and SUM Functions
  |  | 10.2.3 | Suppressing Duplicates

  | 10.3 | Build-In Functions

  |  | 10.3.1 | abs ( )
  |  | 10.3.2 | ceiling ( )
  |  | 10.3.3 | datenext ( )
  |  | 10.3.4 | floor ( )
  |  | 10.3.5 | is_decimal ( )
  |  | 10.3.6 | is_integer ( )
  |  | 10.3.7 | is_white ( )
  |  | 10.3.8 | length ( )
  |  | 10.3.9 | lpad ( )
  |  | 10.3.10 | lscan ( )
  |  | 10.3.11 | ltrim ( )
  |  | 10.3.12 | nullval ( )
  |  | 10.3.13 | round ( )
  |  | 10.3.14 | rpad ( )
  |  | 10.3.15 | rscan ( )
  |  | 10.3.16 | rstrindex ( )
  |  | 10.3.17 | rtrim ( )
  |  | 10.3.18 | sign ( )
  |  | 10.3.19 | strdel ( )
  |  | 10.3.20 | strindex ( )
  |  | 10.3.21 | strins ( )
  |  | 10.3.22 | substr ( )
  |  | 10.3.23 | tolower ( )
  |  | 10.3.24 | toupper ( )
  |  | 10.3.25 | trunc ( )

  | 10.4 | Math Library Functions

  ### CHAPTER 11: GROUP BY Clause

  ### CHAPTER 12: HAVING Clause

  ### CHAPTER 13: WHERE Clause