SQL Reference Contents

Chapter 1 Introduction

Intended Audience
Related Mimer SQL Publications
Suggestions for Further Reading
Acronyms, Terms and Trademarks

Chapter 2 Reading SQL Syntax Diagrams

Key to Syntax Diagrams
KEYWORDS
Parameters
Syntax Diagram Example
Reading Standard Compliance Tables

Chapter 3 Introduction to SQL Standards

X/Open-95 SQL
SQL92
Notation
Transitional Level SQL92
Intermediate Level SQL92
Full SQL92
SQL/PSM
SQL99

Chapter 4 Basic Mimer SQL Concepts

The Mimer SQL Relational Database
The Data Dictionary
Databanks
Idents
Schemas
Tables
Base Tables and Views
Primary Keys and Indexes
Routines - Functions and Procedures
Modules
Synonyms
Shadows
Triggers
Sequences
Data Integrity
Domains
Foreign Keys - Referential Integrity
Check Conditions
Check Options in View Definitions
Access Rights and Privileges
System Privileges
Object Privileges
Access Privileges
About Privileges

Chapter 5 Basic SQL Syntax Rules

Characters
Special Characters
Identifiers
SQL Identifiers
Naming Objects
Qualified Object Names
Outer References
Host Identifiers
Target Variables
Reserved Words
Standard Compliance
Data Types in SQL Statements
Binary Data
Numerical Data
Data Type Abbreviations
DATE, TIME and TIMESTAMP
Interval
The NULL Value
Data Type Compatibility
Datetime and Interval Arithmetic
Data Types for Parameter Markers
Host Variable Data Type Conversion
Standard Compliance
Literals
String Literals
Numerical Integer Literals
Numerical Decimal Literals
Numerical Floating Point Literals
DATE, TIME and TIMESTAMP Literals
Interval Literals
Standard Compliance
Assignments
String Assignments
Numerical Assignments
Datetime Assignment Rules
Interval Assignment Rules
Standard Compliance
Comparisons
Character String Comparisons
Numerical Comparisons
Datetime and Interval Comparisons
NULL Comparisons
Truth Tables
Standard Compliance
Result Data Types
Standard Compliance

Chapter 6 SQL Language Elements

Operators
Arithmetical and String Operators
Comparison and Relational Operators
Logical Operators
Standard Compliance
Value Specifications
Standard Compliance
Default Values
Standard Compliance
Set Functions
Available Set Functions
Restrictions
Results of Set Functions
Standard Compliance
Scalar Functions
Scalar Numeric Functions
Scalar String Functions
Scalar Interval Functions
Datetime "Pseudo Literals"
String "Pseudo Literals"
The ABS Function
The ASCII_CHAR Function
The ASCII_CODE Function
The BIT_LENGTH Function
The CHAR_LENGTH Function
The CURRENT_DATE Function
The CURRENT_PROGRAM Function
The CURRENT_USER Function
The CURRENT_VALUE Function
The DAYOFWEEK Function
The DAYOFYEAR Function
The EXTRACT Function
The IRAND Function
The LOCALTIME Function
The LOCALTIMESTAMP Function
The LOWER Function
The MOD Function
The NEXT_VALUE Function
The OCTET_LENGTH Function
The PASTE Function
The POSITION Function
The REPEAT Function
The REPLACE Function
The ROUND Function
The SESSION_USER Function
The SIGN Function
The SOUNDEX Function
The SUBSTRING Function
The TAIL Function
The TRIM function
The TRUNCATE Function
The UPPER Function
The WEEK Function
Standard Compliance
CASE Expression
Short Forms for CASE
Standard Compliance
CAST Specification
Standard Compliance
Expressions
Unary Operators
Binary Operators
Operands
Evaluating Arithmetical Expressions
Evaluating String Expressions
Standard Compliance
Predicates
The Basic Predicate
The Quantified Predicate
The IN Predicate
The BETWEEN Predicate
The LIKE Predicate
The NULL Predicate
The EXISTS Predicate
The OVERLAPS Predicate
Standard Compliance
Search Conditions
Standard Compliance
Select Specification
The SELECT Clause
The FROM Clause and Table-reference
The WHERE Clause
The GROUP BY and HAVING Clauses
Standard Compliance
Joined Tables
The INNER JOIN
The OUTER JOIN
Standard Compliance
SELECT Statements
Updatable Result Sets
Vendor-specific SQL
ISO-based Escape Clauses
Vendor-based Escape Clauses
Processing Escape Clauses
Standard Compliance

Chapter 7 SQL Statement Descriptions

Access Control Statements
Connection Statements
Data Definition Statements
Data Manipulation Statements
Declarative Statements
Diagnostics Statements
Dynamic SQL Statements
Embedded SQL Control Statements
Procedure Control Statements
System Administration Statements
Transaction Control Statements
Usage Modes
SQL Statement Syntax
ALLOCATE CURSOR
ALLOCATE DESCRIPTOR
ALTER DATABANK
ALTER DATABANK RESTORE
ALTER IDENT
ALTER SHADOW
ALTER TABLE
CALL
CASE
CLOSE
COMMENT
COMMIT
COMPOUND STATEMENT
CONNECT
CREATE BACKUP
CREATE DATABANK
CREATE DOMAIN
CREATE FUNCTION
CREATE IDENT
CREATE INDEX
CREATE MODULE
CREATE PROCEDURE
CREATE SCHEMA
CREATE SEQUENCE
CREATE SHADOW
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DEALLOCATE DESCRIPTOR
DEALLOCATE PREPARE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE SECTION
DECLARE VARIABLE
DELETE
DELETE CURRENT
DESCRIBE
DISCONNECT
DROP
ENTER
EXECUTE
EXECUTE IMMEDIATE
FETCH
GET DESCRIPTOR
GET DIAGNOSTICS
GRANT ACCESS PRIVILEGE
GRANT OBJECT PRIVILEGE
GRANT SYSTEM PRIVILEGE
IF
INSERT
LEAVE
LEAVE (program ident)
LOOP
OPEN
PREPARE
REPEAT
RESIGNAL
RETURN
REVOKE ACCESS PRIVILEGE
REVOKE OBJECT PRIVILEGE
REVOKE SYSTEM PRIVILEGE
ROLLBACK
SELECT
SELECT INTO
SET
SET CONNECTION
SET DATABANK
SET DATABASE
SET DESCRIPTOR
SET SESSION
SET SHADOW
SET TRANSACTION
SIGNAL
START
UPDATE
UPDATE CURRENT
UPDATE STATISTICS
WHENEVER
WHILE

Chapter 8 Data Dictionary Views

INFORMATION_SCHEMA
ASSERTIONS
CHARACTER_SETS
CHECK_CONSTRAINTS
COLLATIONS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
EXT_COLUMN_REMARKS
EXT_DATABANKS
EXT_IDENTS
EXT_INDEX_COLUMN_USAGE
EXT_INDEXES
EXT_OBJECT_IDENT_USAGE
EXT_OBJECT_OBJECT_USED
EXT_OBJECT_OBJECT_USING
EXT_OBJECT_PRIVILEGES
EXT_ROUTINE_MODULE_DEFINITION
EXT_ROUTINE_MODULE_USAGE
EXT_SEQUENCES
EXT_SHADOWS
EXT_SOURCE_DEFINITION
EXT_STATISTICS
EXT_SYNONYMS
EXT_SYSTEM_PRIVILEGES
EXT_TABLE_DATABANK_USAGE
KEY_COLUMN_USAGE
MODULES
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINE_COLUMN_USAGE
ROUTINE_PRIVILEGES
ROUTINE_TABLE_USAGE
ROUTINES
SCHEMATA
SQL_LANGUAGES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
TRANSLATIONS
TRIGGERED_UPDATE_COLUMNS
TRIGGER_COLUMN_USAGE
TRIGGER_TABLE_USAGE
TRIGGERS
USAGE_PRIVILEGES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
Standard Compliance
INFO_SCHEM
CHARACTER_SETS
COLLATIONS
COLUMN_PRIVILEGES
COLUMNS
INDEXES
SCHEMATA
SERVER_INFO
SQL_LANGUAGES
TABLE_PRIVILEGES
TABLES
TRANSLATIONS
USAGE_PRIVILEGES
VIEWS
Standard Compliance
FIPS_DOCUMENTATION
SQL_FEATURES
SQL_SIZING
Standard Compliance

Appendix A Reserved Words

Potential Reserved Words
Reserved Keywords in SQL92 and SQL/PSM

Appendix B Character Set

Appendix C Limits

Appendix D Deprecated Features

Host Variables
Operators
Statements
CONNECT
SELECT NULL
INCLUDE SQLCA
SET TRANSACTION
CREATE IDENT
ALTER IDENT
ENTER
SELECT
Communication Areas
SQLDA
SQLCODE
Program Idents
MIMER_SW
MIMER_BR
MIMER_SC
Datetime Scalar Functions
CURRENT_TIME
CURRENT_TIMESTAMP
Data Dictionary Views

Appendix E Return Status and Conditions

SQLSTATE Return Codes
List of SQLSTATE Values

Index