CHAPTER 2: Using Empress SQL

Although Empress is a large and complex program, you do not need to know anything about computer programming or how data is stored or retrieved by the system. However, you do have to learn the commands that Empress understands. They are not very many of them, nor are they complex. In this chapter, we will show you the basic Empress SQL commands. You will find that you can easily remember the basic commands and will soon be confident in using them.

The Empress database is an operating system directory. The directory contains several operating system files where the data is stored. Empress is the interface between you and these data files. For example, When you create a table, Empress will create an operating system file to store the table's data. When you issue a query to access the tables, Empress will retrieve the information from the corresponding operating system files.

A database contains a collection of tables. Each table contains zero or more records. Each record contains a value of a set of attributes. Each attribute contains a definition of the characteristics of the data. For example, in our sample database:

(in the personnel table)

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 

(in the presonnel table)

number           integer
name             char (25,1)
phone            char(15,1)
credit_limit     dollar 

Empress provides a wide variety of data types. The following is a list of these data types:

Table 2-1: Empress Data Types

Missing data values are stored internally in a special form, and you may enter and recall these values by using the NULL keyword.

For detail description of Empress data types, please refer to the Empress SQL: Reference manual.

With Empress, you may store CHARACTER data type in fixed-length fields using one of three types:

Table 2-2: Empress CHARACTER Data Type Types

For example, the attribute name in the personnel table:

   name   CHAR (25,1)

will have character data with a fixed record length of 25 characters with no blanks in front or after the data.

The TEXT data type will store character data varying from several characters to many pages, using only as much storage as needed for the data.

For example, the attribute comments in the customers table:

   comments  TEXT (25, 0, 80, 1)

will have character data of variable lengths with a display length of 25 characters per line, with no character stored with the main table. The rest of data will be stored in the overflow table with a fixed record length of 80 and 1 pointer to the overflow extend will be stored in the main table.

Empress supports any national language 8-bit character set conforming to the ISO Latin1 sorting mechanism. 16-bit KANJI and HANGUL language support are also available.

For example, if your terminal allows you to enter French characters, the name in the personnel table can be declared as:

   name   nlschar (25,1)

The DATE data type stores dates. There are nine date data formats:

Table 2-3: Empress DATE Data Type Formats

*

nn is defined by the Empress system variable MSDATELIMIT, the value of which is assigned by the user to indicate the preferred century for entering dates.

For any DATE type, entering today or now, in any combination of upper- and lowercase characters, stores the current date in the appropriate format.

For example, the attribute date in the loans table:

   date    DATE (1)

specifies a date data of June 1, 1998 to have the display format of 1 June 1998.

The TIME data type stores dates plus the time-of-day information. There are nine time data formats. The different styles of output can best be shown by an example. For 2:56:15 PM, August 28, 1998, the nine TIME data types produce:

Table 2-4: Empress TIME Data Type Formats

For any TIME data type, entering today stores the current date with time 00:00:00; entering now stores the current date and time.

For example the following attribute:

   time   TIME (1)

specifies a time data of June 1, 1998 2:30 PM with a display format of:

   1 June 1998 14:30:00

The MICROTIMESTAMP data type stores time data plus microseconds information.

The nine different MICROTIMESTAMP formats are shown below. The output is for August 28, 1998 2:56:15 p.m. and 234599 microseconds.

Table 2-5: Empress MICROTIMESTAMP Data Type Formats

*

nn is defined by the Empress system variable MSDATELIMIT, the value of which is assigned by the user to indicate the preferred century for entering dates.

Empress can store DECIMAL data type up to 38 digits. The number of decimal places is specified by the user.

For example, the following specifies that the attribute circumference will store up to a maximum of 7 digits with 3 decimal places.

   circumference   DECIMAL (7, 3)

The DOLLAR data type is designed for storing monetary values. You may store up to 13 dollar digits using one of the three formats:

Table 2-6: Empress DOLLAR Data Type Formats

For example, the attribute credit_limit in the personnel table:

   credit_limit     DOLLAR (6,1)

specifies monetary data displayed with a maximum of 6 dollar digits and a display Type 1.

Floating point numbers are stored as:

• REAL
• FLOAT
• LONGFLOAT or DOUBLE PRECISION

Data are represented in scientific notation on retrieval.

For example, 10342 can be represented as 1.0342e+04.

Integer numeric values are stored as:

• INTEGER or INT
• SHORTINTEGER or SMALLINT
• LONGINTEGER

For example, the attribute number in the personnel table:

   number   INTEGER

specifies integer value with a range from -32767 to +32767.

The MULTIMEDIA or BULK data type stores unstructured data such as image, voice, sound, etc. For example, to declare that an attribute called picture which contains a person's picture:

   picture BULK (80,0,1024,1)

In this section, we will show you how to create and access a database and its tables. The general procedure for creating and accessing an Empress database is as follows:

1. Log in as required by the operating system.

2. Change into your working directory or the directory where you wish to create the database. If this directory is called work, then type:

      cd work
   
   

3. Create the database if it does not exist. (If it does, go on to Step 4.) Type in empmkdb followed by a space then the name of your database. The name should consist of a sequence of characters with no embedded blanks. To create the database repairs, type:

      empmkdb repairs
   
   

4. The command empmkdb stands for Empress make database. It creates a system directory with the given name and creates certain files (data dictionary) in it so that Empress can recognize it as a database.

5. Start up Empress by typing in the command empsql, then a space, followed by the database name. For example:

      empsql repairs
   
   

6. If this does not work, then the Empress startup command may have been renamed at your installation. Consult your system administrator.

7. Empress will prompt you with an asterisk (*) when it is ready to accept commands. You may now access the database using all of the Empress SQL commands available in the version of Empress you are using.

Before we can store or retrieve data, we must first create a table. For example, our sample database has a loans table which contains information about loans made to employees: the employee's name, the date on which the loan was made and the amount involved. It has the attributes name, date and amount.

To create the loans table, issue the following SQL command:

   CREATE TABLE loans (name CHAR (25,1),
                       date DATE (1),
                       amount DOLLAR (6,1));

The CREATE TABLE command tells Empress the name of your table followed by the specifications of the attributes. The loans table has the following attributes: name contains character data with a maximum length of 25, date contains a date and amount contains a dollar value.

The Interactive Interface, a feature of Empress SQL, is another easy method to create a table. To create the personnel table using Interactive Interface commands, type in:

CREATE TABLE personnel;

Empress prompts you for the attribute names and their data types.

   Name for Attribute #1: number
   Data Type: integer
   Allow Null Values: y
   
   Ready .e
   
   Name for Attribute #2: name
   Data Type: char
   Maximum Number of Characters: 25
   Type: 1
   Allow Null Values: y
   
   Ready .e
   
   Name for Attribute #3: phone
   Data Type: char
   Maximum Number of Characters: 15
   Type: 1
   Allow Null Values: y
   
   Ready .e
   
   Name for Attribute #4: credit_limit
   Data Type: dollar
   Maximum Number of Dollar Digits: 6
   Type: 1
   Allow Null Values: y
   
   Ready .e
   
   Name for Attribute #5: .q

You may follow the above example to create auto_parts and customers tables as defined in the sample database.

To obtain information on the database, type in either one of the following commands:

   DISPLAY DB;
   
   DISPLAY DATABASE;

Empress displays the following information:

   **** Database: repairs ****
   
   auto_parts
   customers
   loans
   personnel

To view the structure of the personnel table, type in:

   DISPLAY TABLE personnel;

Empress displays:

   *** Table: personnel ***
   
   Attributes:
   
   number          integer
   name            char(25, 1)
   phone           char(15, 1)
   credit_limit    dollar(6, 1)

Now that we have created the tables to store data, we can start inserting records. Within Empress SQL, data can be inserted into tables using a single command, using the Interactive Interface, or using data from a file.

You may follow any of the examples to complete the data insertion into personnel, loans, auto_parts and customers tables as specified in the sample database.

To insert the first record of the personnel table using a single command, type in:

   INSERT INTO personnel
      VALUES 10, "Kilroy", "426-9681", 500;

Empress displays:

   Number of Records Entered = 1

The INSERT INTO command specifies the name of the table, personnel, into which this data is to be stored in, followed by the list of data values (10, "Kilroy", "426-9681", 500).

Using the Interactive Interface, issue the following SQL command and fill in the data for each prompt:

   INSERT INTO personnel;
   
   number: 5
   name: Mosca
   phone: 544-2243
   credit_limit: 750
   
   Ready .e
   
   number: .q
   
   Number of Records Entered = 1

To insert data from a file, first create a file named datafile using the system editor which contains the following data:

   17
   Wladislaw
   723-6073
   50
   3
   Jones
   667-2951
   500

You may then issue the following command to enter this data into the personnel table:

INSERT INTO personnel FROM datafile;

Empress displays:

Number of Records Entered = 2

You now have entered four records into the personnel table using three different methods.

To enter the first record into the customers table using the Interactive Interface command, type in:

   INSERT INTO customers;

Empress prompts you for data:

   name: Mr. R. Lucas
   address: 1423 Beach Drive,\
   Bayville, CA 92308
   comments: Can always be sold\
   something new and flashy - likes\
   chrome and colored stuff,\
   also little gadgets.

   Ready .e
   
   name: .q

The backslash (\) is used to indicate a continuation of the text data input and will not be entered as part of the data. This is defined by the Empress system variable MSLINECONT. Refer to the Empress: Database Administrator's Guide for more information on the system variable MSLINECONT.

The most common SQL operation is the retrieval of data. Retrieving data from a database is called a query and the SELECT command is used to issue a query. To retrieve records from the personnel table, type in:

   SELECT * FROM personnel;

The asterisk (*) will list all records from the personnel table. Empress displays:

   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

In the previous example, we retrieved all the data from the personnel table. If you want to select specific information such as a list of personnel names and phone numbers, type in:

   SELECT name, phone FROM personnel;

The SELECT clause will only list records for the attributes you have specified. Empress displays:

   name        phone

   Kilroy      426-9681
   Mosca       544-2243
   Wladislaw   723-6073
   Jones       667-2951
   Peterson    978-6060
   Scarlatti   961-7363
   Jordan      964-3335

The last example demonstrated the retrieval of specific attributes of information. If you want to be more selective and only retrieve particular records, then you need to add a WHERE clause in your SELECT statement. This will only retrieve records which satisfy the WHERE clause conditions you have specified.

For example, to list the names from the personnel table with a credit limit of more than $500, type in:

   SELECT number, name, phone FROM personnel
      WHERE credit_limit > 500;

Empress displays:

   number   name        phone

        5   Mosca       544-2243

A WHERE clause tells Empress to search and retrieve only the information which meets the search condition.

The following examples illustrates the use of search conditions:

   SELECT number, name, phone FROM personnel
      WHERE credit_limit < 500
      AND credit_limit > 100; 

   SELECT name FROM personnel
      WHERE credit_limit > 500
      OR credit_limit < 100; 

   SELECT name FROM personnel
      WHERE credit_limit != 500; 

   SELECT name FROM personnel
      WHERE credit_limit BETWEEN 100 AND 500; 

   SELECT name FROM personnel
      WHERE credit_limit IN (100, 200, 500); 

   SELECT name FROM personnel
      WHERE name MATCH "C*"; 

Refer to the Empress SQL: Reference manual for a more detailed discussion of Empress WHERE clause operators.

To further illustrate the power of Empress SQL, records requested by your SELECT command may be grouped by a common attribute value. Using the GROUP BY clause, the output can be grouped by the specified attribute. For example:

   SELECT * FROM loans GROUP BY name;

Empress displays:

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

The GROUP BY clause is often used with the computing functions such as COUNT, MAX, MIN, SUM, AVG, etc. For example:

SELECT name, SUM(amount) FROM loans GROUP BY name;

Empress displays:

   name                          SUM(amount)

   Jones                              358.95
   Kilroy                             250.00
   Mosca                              350.00
   Peterson                            50.00
   Scarlatti                          275.00
   Wladislaw                          155.00

Just as a WHERE clause is used to specify a search condition for individual rows, a HAVING clause is used to specify search conditions for a group of rows. For example:

   SELECT name, SUM(amount) FROM loans GROUP BY name
      HAVING SUM(amount) > 300;

Empress displays:

   name                          SUM(amount)

   Jones                              358.95
   Mosca                              350.00

To avoid any duplicate values, specify the DISTINCT keyword in your SELECT statement. For example:

   SELECT DISTINCT (name) FROM loans:

Empress displays:

   name

   Mosca
   Jones
   Kilroy
   Wladislaw
   Peterson
   Scarlatti

In all our examples so far, we have queried only one table. Sometimes, the information you seek might be stored in more than one table. In this case, we must select data from two or more tables and combine them.

To get a list of all records from the loans table and also include the phone number of the person from the personnel table, type in:

   SELECT name, phone, date, amount
      FROM loans, personnel
      WHERE loans.name = personnel.name;

Empress displays:

   name             phone                   date      amount
   
   Mosca           544-2243     2 February  1992     $150.00
   Jones           667-2951     7 February  1992      $33.95
   Kilroy          426-9681    16 February  1992     $250.00
   Wladislaw       723-6073    27 February  1992      $55.00
   Jones           667-2951     3 April     1992      $25.00
   Mosca           544-2243     4 May       1992     $200.00
   Wladislaw       723-6073    12 May       1992      $25.00
   Peterson        978-6060     6 June      1992      $50.00
   Wladislaw       723-6073    25 June      1992      $75.00
   Jones           667-2951    12 August    1992     $300.00
   Scarlatti       961-7363     9 September 1992     $150.00
   Scarlatti       961-7363    14 October   1992     $125.00

So far, the display output of our query results has been determined by Empress. We can control the output display by adding an ORDER BY clause to our SELECT statement.

For example, to get a list of numbers and names from the personnel table for employees with credit limits less than or equal to $100 and sort the output on the personnel number in ascending order, type in:

   SELECT number, name, credit_limit
      FROM personnel WHERE credit_limit <= 100
      ORDER BY number ASCENDING;

Empress displays:

   number   name         credit_limit

       4    Scarlatti         $100.00
      17    Wladislaw          $50.00

The DESCENDING keyword is used in the same fashion and lists the output in descending order. For example:

   SELECT number, name, credit_limit
      FROM personnel WHERE credit_limit <= 100
      ORDER BY number DESCENDING;

From time to time, it will be necessary to update the information in our database. For example, we might need to change addresses and phone numbers, or add new employees.

To update Kilroy's phone number from 426-9681 to 513-8888 you can use the following command:

   UPDATE personnel SET phone TO "513-8888"
      WHERE name = "Kilroy";

The UPDATE command specifies the table to be modified (personnel) and the SET clause tells Empress to change the attribute value to a specified value (513-8888). The WHERE clause specifies the records to be affected by this command.

To update a record using the Interactive Interface, issue the following command:

   UPDATE personnel WHERE name = "Kilroy";

Empress prompts you with:

   number: 10
   name: Kilroy
   phone: 426-9681 513-8888
   credit_limit: $500.00

   Ready .e

   Number of Records Updated = 1

To delete Kilroy's record from the personnel table, use:

   DELETE personnel WHERE name = "Kilroy";

Note that the DELETE command specifies the table and the WHERE clause tells Empress which record is to be removed. Without the WHERE clause, all records in the table will be removed.

To change the structure of a table after it has been created, the ALTER TABLE command is used. This command allows you to add or delete attributes or change the definition of the attributes.

To change the length of the name attribute in the personnel table from 25 to 30 characters, type in:

   ALTER personnel
      CHANGE name CHAR (30,1);

To add an address attribute as a character data type with a length of 50 into the personnel table, type in:

   ALTER personnel
      ADD address CHAR (50,1);

To delete the credit_limit attribute from the personnel table, type in:

   ALTER personnel
      DELETE credit_limit;

You also can combine all the above changes in one single command:

   ALTER personnel
      CHANGE name CHAR (30,1)
      ADD address CHAR (50,1)
      DELETE credit_limit;

To change the table name from personnel to employees, type in:

   RENAME personnel TO employees;

To change the attribute name in the auto_parts table from part no. to numbers, type in:

   RENAME auto_parts."part no." TO numbers;

The attribute part no. is a complex name. A complex name may begin with digits or other non-letter characters and may include blanks, tabs, periods, etc. When used in Empress commands, complex names must be enclosed in quotes.

To remove the personnel table from the database, type in:

   DROP personnel;

Now the personnel table no longer exist in the database.

Use this command with extreme caution. Once the table is dropped, it is permanently removed from the database. The only way to retrieve the table is to recreate it (or if your are in a transaction, you may roll back the transaction to recover the table).

Empress has a HELP command for on-line help with the Empress SQL. The syntax for the HELP command is:

   HELP [topic];

This will produce a list of topics on which help is available. To obtain help for a particular command, enter:

   HELP command;

This will produce a summary of the command. For example, to get help with the DATABASE command, use:

   HELP DATABASE;

which produces:

   ********** Database **********

   This command prints the name of the current database or changes 
   to a new database.

   Syntax:

   The keyword DATABASE alone prints the name of the current database.
   It can be shortened to DB. Specifying another database name makes 
   database the current database.

   The output of a DATABASE command can be diverted from the terminal into a
   new file or appended to an existing file by specifying INTO or ONTO, 
   respectively, and a filename.

   For further information, enter:

   HELP display;

Help is also available from the Interactive Interface. To obtain a summary of the commands available for the interactive CREATE, INSERT or UPDATE enter ".?".

To exit from the database, type in:

   STOP

or

   EXIT

This will bring you back to the operating system.