# CHAPTER 1: Empress SQL

## 1.1 Introduction

A Database Management System (DBMS) is a computer program you can use to store, organize and access a collection of interrelated data. The collection of data is usually referred to as the database. The primary goal of a DBMS is to provide a convenient and effective way to store and retrieve data from the database. There are several types of data models (a data model is used to describe the structure of a database) and Empress is a Relational Database Management System (RDBMS) with Object Oriented extensions. Empress is capable of managing data in multiple databases. The data stored in each database is organized as tables with rows and columns. In relational database terminology, these tables are referred to as relations, rows are referred to as records, and columns are referred to as attributes.

Empress provides a set of Structured Query Language (SQL) commands that allows users to request information from the database. The Empress SQL language has three levels of operations:

1. A basic level at which data management commands are typed in without any prompting. The full range of data management commands is available at this level.

   Data Definition Language commands are concerned with the structure of the database and its tables.

   Data Manipulation Language commands are concerned with the maintenance and retrieval of the data stored in the database tables.

   Data Control Language commands provide facilities for assuring the integrity and security of the data.

2. An Executive Interface is concerned with the management of commands in a given session with Empress. This interface keeps a history of commands and provides a line editor for editing command lines. In addition, it is possible to escape to the operating system and to issue operating system commands without terminating an Empress session.

3. An Interactive Interface provides a conversational, prompt and response, type of interaction with Empress. A subset of the basic data management commands is available. The Interactive Interface includes commands to control the conversation.

## 1.2 Data Definition Language Commands

The overall structure of the database is defined by the Data Definition Language (DDL) commands. The result of compiling DDL commands is a set of tables called the data dictionary which contains information about the data in the database. Detailed descriptions of the Empress DDL commands are provided in the Empress SQL: Reference manual. A summary of the available DDL commands follows:

Table 1-1: Data Definition Language Command

Command | Description
`ALTER TABLE` | Changes the structure of an existing table without having to dump and re-load its records. This also includes enable/disable trigger, define table type for replication, enabling and disabling replication relations, and setting checksum for a table.
`CREATE COMMENT` | Attaches a comment to a table or attribute.
`CREATE INDEX` | Sets up a search-aiding mechanism for an attribute.
`CREATE MODULE` | Creates the definition of a persistent stored module into the data dictionary.
`CREATE RANGE CHECK` | Sets up data validation checks on an attribute.
`CREATE REFERENTIAL` | Sets up data referential constraints on attributes.
`CREATE REPLICATION MASTER` | Assings replication master entries to a replication table.
`CREATE REPLICATION REPLICATE` | Assings replication replicate entries to a replication table.
`CREATE REPLICATE TABLE` | Creates replicate table from a replication master table.
`CREATE TABLE` | Creates a new table or replicate table including its name and the name and data type of each of its attributes.
`CREATE TRIGGER` | Sets up trigger events into data dictionary.
`CREATE VIEW` | Creates a logical table from parts of one or more tables.
`DISPLAY DATABASE` | Shows the tables in the database.
`DISPLAY GRANT PRIVILEGE` | Shows privilege grant options for a table.
`DISPLAY MODULE` | Shows the persistent stored module definition.
`DISPLAY PRIVILEGE` | Shows access privileges for a table.
`DISPLAY TABLE` | Shows the structure of a table.
`DROP COMMENT` | Removes a comment on a table or attribute.
`DROP INDEX` | Removes an index on an attribute.
`DROP MODULE` | Removes a persistent stored module definition from the data dictionary.
`DROP RANGE CHECK` | Removes data validation checks on an attribute.
`DROP REFERENTIAL` | Removes a data referential constraints from attributes.
`DROP REPLICATION MASTER` | Removes replication master entry from a replication table.
`DROP REPLICATION REPLICATE` | Removes replication replicate entry from a replication table.
`DROP TABLE` | Removes an existing table.
`DROP TRIGGER` | Removes a trigger event from the data dictionary.
`DROP VIEW` | Removes a logical table.
`GRANT PRIVILEGE` | Changes access privileges for tables or attributes.
`LOCK LEVEL` | Sets the level of locking on a table.
`RENAME` | Changes the name of a table or attribute.
`REVOKE PRIVILEGE` | Removes table or attribute access privileges.
`UPDATE MODULE` | Links a persistent stored module definition with the module shared library.

## 1.3 Data Manipulation Language Commands

Data Manipulation Language (DML) commands allow users to access or manipulate data. The syntax and the description of the Empress DML commands are provided in the Empress SQL: Reference manual. A summary of the available DML commands follows:

Table 1-2: Data Manipulation Language Commands

Command | Description
`CALL` | Call stored procedure or an expression.
`DELETE` | Removes unwanted records from a table.
`EMPTY` | Removes all records from a table.
`INSERT` | Adds new records to a table.
`LOCK TABLE` | Locks a table while in a transaction.
`SELECT` | Retrieves information from a table. Queries may be of varying complexity, including joins on more than one table.
`SORT` | Sorts a table on one or more attributes.
`UPDATE` | Modifies existing records in a table.

The `UPDATE`, `DELETE` and `SELECT` commands can also have a WHERE clause, which is used to restrict the records that are modified, deleted or selected.

The `WHERE` clause limits the records used from a given table to those satisfying the conditions imposed by the `WHERE` clause.

## 1.4 Data Control Language Commands

Data Control Language (DCL) commands provide facilities for data integrity and security. The syntax and the description of the Empress DCL commands are provided in the Empress SQL: Reference manual. A summary of the available DCL commands follows:

Table 1-3: Data Control Language Commands

Command | Description
`COMMIT WORK` | Ends the current transaction, making permanent all changes to the database made during the current transaction.
`DISPLAY WORK` | Displays the status of the current transaction, or of transactions in progress affecting the database.
`ROLLBACK` | Reverses the effects of commands in the current transaction allowing you to change your mind or recover from a mistake.
`SAVEPOINT` | Sets a save point in a transaction, starting a nested transaction. A transaction can be rolled back to a save point with the `ROLLBACK` command.
`START WORK` | Begins a set of commands which do not have a permanent effect on the database until a `COMMIT WORK` command is issued or a `ROLLBACK` command cancels them.

## 1.5 Executive Interface Commands

Empress provides a set of commands dealing with the management of DDL, DML and DCL commands in a given SQL interface session. The syntax and description of the Executive Interface commands are provided in the Empress SQL: Reference manual. A summary of the available Executive Interface commands follows:

Table 1-4: Executive Interface Commands

Command | Description
`CHANGE WORD` | Allows quick, simple alterations to commands.
`DATABASE` | Shows the current database, or when followed by a new database name, enables you to change databases without having to leave Empress and invoke it again.
`DO` | Allows you to communicate with the operating system and use other system utilities, such as the editor, without leaving Empress.
`EDIT` | Invokes a simple line editor for altering a command.
`EXIT` | Exits from an Empress SQL session.
`HELP` | Provides an on-line reference summary of the SQL language available using the current interface.
`HOLD` | Parses a command without executing it, allowing commands to be checked for syntax or table/attribute name errors. `HOLD` is also used to make a previous command from the history list the current command.
`RECALL` | Examines the command history list and shows previous commands.
`RUN` | Re-executes a previous command from the history list without having to re-type it. `RUN` followed by a filename executes the commands stored in the named file, allowing convenient packaging of frequently-used commands.
`RUN REPORT` | Invokes the Empress Report Writer, on a report script.
`SET` | Allows you to set variables. You can use variables to save typing or in files of commands to make them more general. Special system variables can also be set to customize aspects of your working environment.
`SHOW` | Shows the value of a variable.
`STORE` | Saves a command from the history list in an operating system file.
`UNSET` | Removes variables.

## 1.6 Interactive Interface Commands

Interactive Interface commands allow you to carry out database operations in a conversational, prompt and respond type of interaction with Empress. The detail descriptions of the Interactive Interface commands are provided in the Empress SQL: Reference manual. A summary of the available Empress Interactive Interface commands follows:

Table 1-5: Interactive Interface Commands

Command | Description
`ALTER TABLE` | Changes the structure of an existing table.
`CREATE TABLE` | Allows you to set up a table interactively, with prompting for attribute names and data types and provision for making changes to the attributes as the table is constructed.
`INSERT` | Adds new data to a table.
`UPDATE` | Modifies existing data in a table.

## 1.7 The Example Database

Note that this manual is not a tutorial, the examples in this manual section are based on the tables `personnel`, `loans`, `auto parts`, and `customers` described in this section. The effects of previous commands are not considered when the output of a command is shown. Please refer to the Empress SQL Reference manual Empress Conventions chapter for the command syntax notation used in this manual.

Examples given in this manual feature a database called `repairs` which contains these four tables:

1. A `personnel` table of personnel records which containing employees' personnel numbers, names, phone numbers, and credit limits for loans. It has attributes named `number`, `name`, `phone`, and `credit_limit`. The `personnel` table has one record for each employee.
2. A `loans` table containing information about loans made to employees which contains the employee's name, the date on which the loan was made, and the amount. It has attributes called `number`, `name`, `date`, and `amount`. The `loans` table may have more than one record for each employee.
3. An `auto parts` table which holds records of various auto part suppliers with the attributes `supplier`, `phone`, `part name`, `part no.`, and `price`.
4. A `customers` table which holds customers' names and addresses, and some comments about them with the attributes `name`, `address`, and `comments`.

The four tables hold the following data:

  *** Table: personnel ***

  number | name | phone | credit_limit
  10 | Kilroy | 426-9681 | $500.00
  5 | Mosca | 544-2243 | $750.00
  17 | Wladislaw | 723-6073 | $50.00
  3 | Jones | 667-2951 | $500.00
  8 | Peterson | 978-6060 | $250.00
  4 | Scarlatti | 961-7363 | $100.00
  9 | Jordan | 964-3335 | $250.00

  *** Table: loans ***

  number | name | date | amount
  5 | Mosca | 2 February 1990 | $150.0
  3 | Jones | 7 February 1990 | $33.95
  10 | Kilroy | 16 February 1990 | $250.00
  17 | Wladislaw | 27 February 1990 | $55.00
  3 | Jones | 3 April 1990 | $25.00
  5 | Mosca | 4 May 1990 | $200.00
  17 | Wladislaw | 12 May 1990 | $25.00
  8 | Peterson | 6 June 1990 | $50.00
  17 | Wladislaw | 25 June 1990 | $75.00
  3 | Jones | 12 August 1990 | $300.00
  4 | Scarlatti | 9 September 1990 | $150.00
  4 | Scarlatti | 14 October 1990 | $125.00

  *** Table: auto parts ***

  supplier | phone | part name | part no. | price
  AAA Automotive | 922-8624 | Hubcap | 9836653 | $18.40
  AAA Automotive | 922-8624 | Fender rubber | 9837521 | $47.95
  AAA Automotive | 922-8624 | Fender chrome | 9837522 | $67.95
  Rick's Repairs | 675-4436 | Spark plugs | 243 | $1.25
  Speedy Body Shop | 643-8994 | Rear wing | 87003 | $75.00
  Speedy Body Shop | 643-8994 | Front wing | 87005 | $79.99
  Rick's Repairs | 675-4436 | Radiator | 493 | $175.00
  Auto Wholesalers | 922-6219 | Spray paint | 2198 | $1.25
  Auto Wholesalers | 922-6219 | Chrome paint | 3286 | $1.99

  *** Table: customers ***

  name | address | comments
  Mr. R. Lucas | 1423 Beach Drive, Bayville, CA 92308 | Can always be sold something new and flashy - like chrome and colored stuff, also little gadgets.
  Mr. J. Phillips | 63 Shore Bvde., Gull Point, CA 92309 | Pays bills 2 months late -- needs to be threatened with a collection agency.
  Mr. B. Wilson | 14 Valley View Drive, Bayville, CA 92308 | Good customer -- give credit if he asks for it.
  Ms. C. Marshall | 12 Berkeley St., Gull Point, CA 92309 | Suggest vacuum & wash; she keeps 3 dogs. Look for dog hair collecting in engine.
  Mr. J. Lee | 3260 Church St., Gull Point, CA 92309 | Father pays bills -- check with R. Lee before accepting a job, but don't let Johnny know you're doing so.

In the following chapters, the `personnel`, `loans`, `auto parts`, and`customers` tables will be used extensively to illustrate the various Empress features and commands.

The commands and syntax used to create the tables are found in the section `CREATE TABLE` in Empress SQL Reference manual.