Empress Software SQL User's Guide
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

This manual is a general guide to the Query Language of the Empress Relational Database Management System. It contains an overview of the Query Language Interface to Empress, introduces concepts such as transactions and discusses topics of general relevance to using the Query Language.

Table of Contents

CHAPTER 1: Empress SQL

1.1 Introduction
1.2 Data Definition Language Commands
1.3 Data Manipulation Language Commands
1.4 Data Control Language Commands
1.5 Executive Interface Commands
1.6 Interactive Interface Commands
1.7 The Example Database

CHAPTER 2: Using Empress SQL

2.1 Introduction
2.2 Structure of a Database
2.3 Empress Data Types

2.3.1 Character Data
2.3.2 Text Data
2.3.3 National Language Support (NLS) Data
2.3.4 Date Data
2.3.5 Time Data
2.3.6 Microsecond Data
2.3.7 Decimal Data
2.3.8 Dollar Data
2.3.9 Floating Point Data
2.3.10 Integer Data
2.3.11 Multimedia Data

2.4 Create and Access a Database
2.5 Create Table
2.6 Display Database and Table
2.7 Insert Data into a Table

2.7.1 Single Command Insert
2.7.2 Interactive Insert
2.7.3 Insert Data from a File
2.7.4 Inserting Text Data

2.8 Selecting Data from Tables

2.8.1 Selecting Partial Data

2.8.1.1 Selecting specific attributes
2.8.1.2 Selecting specific records
2.8.1.3 Search conditions

2.8.2 Selecting Data by Groups
2.8.3 Selecting Unique Values
2.8.4 Querying Multiple Tables

2.9 Sort Data Output
2.10 Updating Records

2.10.1 Single Command Update
2.10.2 Interactive Update

2.11 Delete Records
2.12 Change Table Structure
2.13 Change Table or Attribute Name
2.14 Remove a Table
2.15 Getting Help
2.16 Exiting the Database

CHAPTER 3: Query Language Topics

3.1 Introduction
3.2 Joining Tables

3.2.1 Designing Joins

3.3 Selects and Indices
3.4 Creating and Using Views
3.5 Update Type Operations on Views

3.5.1 The Basic Condition
3.5.2 First Additional Condition
3.5.3 Second Additional Condition

3.6 Views Into Distributed Data
3.7 Subqueries

3.7.1 Examples of Subquery

3.8 Date Arithmetic
3.9 Converting Expression Values to Data Types
3.10 Privileges in the Database
3.11 Transactions

3.11.1 Nested Transactions and Save Points
3.11.2 Automatic Transactions
3.11.3 Locking on Transactions
3.11.4 Placing Comments to the Transaction Log
3.11.5 Displaying Transaction Status
3.11.6 Two-Phase Commit
3.11.7 Transactions Over Several Databases
3.11.8 Warm Restart
3.11.9 Manual Warm Restart
3.11.10 Recovering to a Save Point
3.11.11 Transaction Protection

3.12 Locks and Table Access

CHAPTER 4: Interfacing with the Operating System

4.1 Introduction
4.2 Issuing Operating System Commands
4.3 Submitting Queries from the Operating System
4.4 Reading Data from the Operating System

4.4.1 Inserting Records from a File
4.4.2 Updating Records from a File
4.4.3 Creating Tables from a File
4.4.4 Reading From Pipes

4.5 Sending Output to the Operating System

4.5.1 Redirecting Output to a File
4.5.2 Displaying Output by Pages
4.5.3 Sending Output to the Printer
4.5.4 Writing To Pipes
4.5.5 Output in Dump Format
4.5.6 Generating a Data File for Empress Report Writer

CHAPTER 5: Empress Variables

5.1 Introduction
5.2 Empress System Variables

5.2.1 Setting Variables
5.2.2 List of Empress System Variables

CHAPTER 6: Non-Printing Data Characters