# CHAPTER 5: Data Manipulation Commands

## 5.1 Introduction

The Data Manipulation Language (DML) commands allow users to access or manipulate data. A summary of the available DML commands is listed below:

Table 5-1: Data Manipulation Language Commands

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

## 5.2 INSERT

The `INSERT` command takes a set of attribute values from the command line, other tables, files or directly from the output of the operating system commands and inserts the data into a table.

Syntax

  Syntax 1:

  |  |  | `INSERT [INTO] table [(` | `| * |attr {, attr}` | `|)] |`

  |  |  |  |  |  | `|VALUES [(] attr_value {, attr_value} [)] |SET TO attr_value {, attr_value} |select_command |FROM data_source` | `|; | | |`

  Syntax 2:

  |  |  | `INSERT [INTO] table SET attr` | `|TO |=` | `| |` | `attr_value {, attr` | `|TO |=` | `| |` | `attr_value};`

  Syntax 3:

  |  |  | `INSERT [INTO] table SET` | `| * | attr {, attr}` | `| |`

  |  |  |  |  |  | `|VALUES [(] attr_value {, attr_value} [)] |select_command |FROM data_source` | `|; | |`

  where:

  `attr_value` |
  is one of: | `|expr |NULL |DIRECT_FROM data_source` | `| | |`
  `expr` | is any legal value for the given attribute. Details are provided in the chapter Expressions.
  `data_source` |
  is one of: | `|file |!os_command` | `| |`
  `os_command` | is an operating system command that generates output to be inserted into the table directly. This feature available in UNIX only.
  `select_command` | is the `SELECT` statement. Refer to the `SELECT` in this chapter.

Notes

1. If no attribute is specified, the attribute values are assigned to the attributes in the order that are listed in the `DISPLAY TABLE` command. If some attributes are specified, any attributes that are not on the list will be set to null. Once the attribute values have been successfully assigned to their attributes, the record is added to the table.
2. Several records may be added to a table at once. The number of `attr_value` must be a multiple number of `attr` in the command list, ie. # of `attr_value` = n * (# of listed `attr`). If numbers are not matched, the last incomplete record will not be inserted into the table. In this case, an error message will be generated.
3. The command `DIRECT_FROM data_source` reads `BULK` or `TEXT` data into the table from a file or directly from the output of the operating system command.
4. The `attr_value` of character constants and date values must enclosed in quotes. Numeric values (`DECIMAL`, `DOLLAR`, `INTEGER`, `SHORTINTEGER`, `LONGINTEGER`, `FLOAT`, and `LONGFLOAT`) must not have quotes. For `DOLLAR` data type, if dollar sign is included as `attr_value`, it must be prefixed with another dollar sign (eg. $$100.00). This is to differentiate from the Empress Variable. It is possible to insert a numeric or date value into a `CHAR` attribute, but attempting to do the opposite will produce an error message.
5. Data may be selected from tables and inserted into another table by using `INSERT INTO table` with the `select_command`. If the inserted table does not exist, it will be automatically created before the data insertion.
   If the `select_item` includes header (Refer to `SELECT` in this chapter), the header will be used as the attribute name for the new table.
   The output from the `SELECT` command must contain uniform row output. For example, using `GROUP BY` clause output a function result under the groups will generate an error message.
6. Data can be insert into table from a file. There must be a correct number of correspondence between the data in the file and the attributes listed for the table. The format of the data must be in one of the following format:

   - the same format as produced by the `SELECT ... DUMP INTO file;` command, or
   - one attribute value per line, or
   - one record per line with attribute values separated by the string specified by `MSVALSEP` (the default value is <Ctrl+V> or octal 26). Records are separated by new lines. A data line may be split over more than one line if new lines are masked with the line continuation character specified by `MSLINECONT` (the default value is a backslash (\)).

   When error occurs during the batch insert (insert from file), the process may aborted by Empress and leave the data file partially inserted into the table. At this point, data file need to be corrected and restart the insert operation from the point when error occurred. This process may be tedious in some situation. In this case, it is best to start the batch insert with:

   - start transaction,
   - start batch insert,
   - ensure the completion of insertion,
     - if data insertion complete, commit the transaction and
       the job is done.
     - if error occurs, cancel the transaction,
       correct data file and start from the beginning.
7. The output from an operating system command may be inserted directly into a table by using `!os_command` instead of a file. The operating system command may be complex and include pipes if desired. This feature only available in UNIX.

Privileges Required

- `INSERT` privilege, for insert data,
- `SELECT` privilege on the appropriate tables, if the `select_command` is used,
- `INSERT` privilege on the `sys_tables`, if new table need to be created.

Example

1. Insert record with numeric, date and character string values

   Add a record for a loan of $100.00 made to Kilroy on June 27, 1990 with:

          INSERT INTO loans VALUES (10, 'Kilroy', '27 June 1990', 100.00);

   Note that the order of the data is important and is matched up from left to right with the attribute names listed in the `DISPLAY TABLE` command. Thus, if the above command were:

          INSERT INTO loans VALUES (10, '27 June 1990', 'Kilroy', 100.00);

   The corresponding name would be `27 June 1990` and the date would be `Kilroy` which would cause an Empress data conversion error message for the date and unsuccessful insert operation.

2. Insert record with dollar value

   The following command produces the same results as the first example (note the doubled dollar sign used for the amount):

          INSERT INTO loans (name, date, amount) VALUES ('Kilroy','27 June
             1990','$$100.00';

3. Insert record with NULL values.

   Add a record to `personnel` with null values for `credit_limit` and `phone` using:

          INSERT INTO personnel (number, name, phone, credit_limit) VALUES (14,
             'Martin', null, null);

4. Insert record with partial attribute values

   Nulls are entered automatically for attributes that are not specified, as in the following example, which has the same effect as the previous one.

          INSERT INTO personnel (number, name) VALUES (14, 'Martin');

5. Insert records with various syntax

   To illustrate the variety of syntax which may be used in the non-interactive insert command, any of the following will add a record for Martin, personnel number `20`, home phone `962-2587`, and a credit limit of `$500` in the `personnel` table:

          INSERT INTO personnel
               SET number TO 20,
               name TO 'Martin',
               phone TO '962-2587',
               credit_limit TO 500;

          INSERT INTO personnel
               (number, name, phone, credit_limit)
               SET TO 20, 'Martin', '962-2587', 500;

          INSERT INTO personnel
               SET TO 20, 'Martin', '962-2587', 500;

          INSERT INTO personnel
               SET number, name, phone, credit_limit
               20, 'Martin', '962-2587', 500;

          INSERT INTO personnel
               SET number, name, phone, credit_limit
               VALUES 20, 'Martin', '962-2587', 500;

6. Insert multiple records

   More than one record may be added at a time:

          INSERT personnel VALUES
               20, 'Martin', '962-2587', 500,
               21, 'Stevens', '973-7721', 250,
               22, 'DuRoche', '922-8047', 500;

7. Insert records from other tables

   Create a new table called `jonesloans` containing all the loans to Jones from the `loans` table with the command:

          INSERT INTO jonesloans
               SELECT FROM loans
               WHERE name = 'Jones';

   This table will have the same structure as the `loans` table, but will contain only Jones' loans.

8. Insert records from a file

   With the separator string set to `|`, and an operating system file called `newloans` containing:

          10|Kilroy|August 24, 1990|200
          5|Mosca|September 3, 1990|100
          4|Scarlatti|September 23, 1990|75

   You can insert these as three new records for the `loans` table with the command:

          INSERT loans FROM 'newloans';

9. Insert record from an operating system command

   Suppose you wish to enter some customer comments into the `customers` table, and you have a file containing the comments ready to be run through `nroff`. If the file of comments is called `comments.n`, you can `nroff` it and insert it into the table with the command:

          INSERT customers SET COMMENTS
             DIRECT_FROM '!nroff comments.n';

## 5.3 SELECT

A `SELECT` command retrieves data from the database. The results are usually displayed as a table on the screen, but may be printed in list form or redirected to the operating system. The `SELECT` command may be classified as simple or join. Simple selects operate on one table. Join selects operate on two or more tables. The `SELECT` command must specify the table or tables to be searched. Other items that may be specified are:

- A list of items forming the columns of the output. These items may be attributes or functions and expressions involving attributes. If the list is absent all the attributes are retrieved in the order shown by a `DISPLAY` table command. If a list is given, output is in the order of the list. Each item may include a `PRINT` clause (or an `AS` clause) giving an alternate column heading, width for the column and justification.
- A `WHERE` clause restricting the records requested to those satisfying certain conditions. The `WHERE` clause consists of boolean expressions (expressions that evaluate to either `true` or `false`). Each record that satisfies all the conditions is included in the output. A `WHERE` clause is also used to express join conditions to allow access to multiple tables. (See the chapter WHERE Clause for more information).
- A `GROUP BY` clause grouping the output according to a list of attributes. Those records having the same value for an attribute in the list will appear sequentially. Precedence is from left to right in the list; that is, a group on an attribute in the list will be a subgroup of the attribute to its left in the list. If the `GROUP BY` clause is absent, the entire output is considered a group. (See the chapter GROUP BY Clause for more information.)
- A `HAVING` clause restricting the groups requested to those satisfying certain conditions. The `HAVING` clause consists of boolean expressions, and each group that satisfies all the conditions is included in the output. (See the chapter HAVING Clause for more information.)
- A `SORT` or `ORDER BY` clause to order the output within each group.

Syntax 1

      SELECT [|BYPASS     |] [|DISTINCT|] [|*                          |]
              |BYPASS_LOCK|   |ALL     |   |select_item {, select_item}|

         FROM table_expression {, table_expression }

            [where_clause]

            [GROUP BY attr {, attr}]

            [having_clause]

            [sort_clause]

            [|LIST  |] [DUMP] [|INTO| file];
             |REPORT|          |ONTO|

Syntax 2

      SELECT [|BYPASS     |] [|DISTINCT|] [|*                          |]
              |BYPASS_LOCK|   |ALL     |   |select_item {, select_item}|

         FROM table_expression {, table_expression }

            [|LIST  |] [DUMP] [|INTO| file]
             |REPORT|          |ONTO|

            [where_clause]

            [GROUP BY attr {, attr}]

            [having_clause]

            [sort_clause];

Syntax 3

      SELECT [|BYPASS     |] [|DISTINCT|] [|*                          |]
              |BYPASS_LOCK|   |ALL     |   |select_item {, select_item}|

         FROM table_expression {, table_expression }

            [where_clause]

            [GROUP BY attr {, attr}]

            [having_clause]

            [sort_clause]

         INSERT [INTO] table [|(attr {, attr})  |] ;
                              |SET *             |
                              |SET attr {, attr} |
                              |(*)               |

Syntax 4

      TABLE table [ sort_clause ]

                   [ |LIST  | ] [DUMP] [ |INTO| file ];
                     |REPORT|            |ONTO|

Syntax 5

       query_expression  [ |LIST  | ] [DUMP] [ |INTO| file ]
                           |REPORT|            |ONTO|

                            [ sort_clause ];

where:

  `select_item` | is one of:
       |table.*                                   |
       |alias.*                                   |
       |expr                       [print_clause] |
       |column_alias = expr                       |
       |function ([DISTINCT] attr) [print_clause] |
       |COUNT [|(*)              |][print_clause] |
               |([DISTINCT] expr)|
  or when attr is of type bulk or text:
        attr |DIRECT_INTO| file
             |DIRECT_ONTO|
  `table_expression` | is one of:
      a) table [[| ALIAS |] alias ]
                 | AS    |

      b) ( query_expression ) alias

      c) table_expression CROSS JOIN table_expression

      d) table_expression NATURAL |INNER         | JOIN table_expression
                                  |LEFT  [OUTER] |
                                  |RIGHT [OUTER] |
                                  |FULL  [OUTER] |

      e) table_expression  |INNER         | JOIN table_expression
                           |LEFT  [OUTER] |
                           |RIGHT [OUTER] |
                           |FULL  [OUTER] |

               | ON where_clause_condition |
               | USING ( attr {, attr } )  |
  `expr` | is an expression. Details are provided in the chapter Expressions.
  `column_alias` | is an alternative name to replace column name in the output.
  `function` | is one of the aggregate functions, i.e. `AVG`, `MAX`, `MIN`, `SUM`. They act over several records at once. The function is applied to the values of the specified attribute and the result printed. The functions `SUM` and `AVG` apply only to numeric attributes. Detail description is in the chapter Functions in this manual.
  `print_clause` |
      |PRINT| header [WIDTH integer][ |LEFT      |]
      |AS   |                         |RIGHT     |
                                      |CENTRE    |
                                      |CENTER    |
                                      |LEFTRIGHT |

                [WRAPMARGIN integer]
  `sort_clause` | has the following syntax:
        |SORT | [ |BY|] |attr      | [|ASCENDING |]
        |ORDER|   |ON|  |attr_num  |  |ASC       |
                        |column_num|  |DESCENDING|
                                      |DESC      |

           {, |attr      | [|ASCENDING |]}
              |attr_num  |  |ASC       |
              |column_num|  |DESCENDING|
                            |DESC      |
  The keywords `ASC` and `DESC` are synonyms for `ASCENDING` and `DESCENDING` respectively. The default order is `ASCENDING`. `SORT` and `ORDER` are synonyms.
  `having_clause` | has the syntax given in the chapter HAVING Clause in this manual.
  `where_clause` | has the syntax given in the chapter WHERE Clause in this manual.
  `query_expression` | contains one or more select_commands combined by set operators. Supported set operators are: `UNION`, `EXCEPT` and `INTERSECT`. The `query_expression` may be:
          |select_command| { |UNION    | [ALL] |select_command| }
          |TABLE table   |   |EXCEPT   |       |TABLE table   |
                             |INTERSECT|
  `UNION` operator returns all rows selected by either `select_command`, excluding all duplicates. `UNION ALL` operator returns all rows selected by either `select_command`, including all duplicates. `EXCEPT` operator returns the difference between two sets of rows selected by two `select_command` operands, excluding all duplicates. The optional `ALL` flag retains duplicates. `INTERSECT` operator returns the intersection of the two sets of rows selected by two `select_command` operands, excluding all duplicates. The optional `ALL` flag retains duplicates. The two `select_command` operands of a `UNION`, `EXCEPT` or `INTERSECT` operation must be of the same degree. It means that their `select_item`s must have the same number of columns, and corresponding columns must be of the same data types. The combined result of a `UNION`, `EXCEPT` or `INTERSECT` operation has the same number of columns, column names (alias) and corresponding data types as the `select_item` of the first `select_command` operand.
  1. `select_command` | may be either of:
             SELECT  [ |DISTINCT| ] | *           |
                       |ALL     |   | select_item |

                FROM [OUTER] table [ [ALIAS] alias]
                  {, [OUTER] table [ [ALIAS] alias]}

                        [ where_clause ]

                        [ GROUP BY attr {, attr} ]

                        [ having_clause ]
  2.         TABLE table

Notes

1. The `DISTINCT` option eliminates duplicate records from the output. `UNIQUE` is a synonym for `DISTINCT`. `ALL` is the default option and includes duplicates. Note that the keyword `DISTINCT` may be used more than once in a `SELECT` statement. If, however, it is used other than with an aggregate function, all affected attributes must be in a `GROUP BY` clause.
2. The `BYPASS_LOCK` option provides Dirty Read functionality which allows data to be read from an Empress database without any concern with locking and provides the data in its potentially unclean state. The dirty read functionality does not alter in any way or manner the locking mechanisms for normal Empress operations. Normal locking still applies unless dirty read is specified. Dirty read just adds another set of options making it possible for curious and hasty users to bypass the placing of read locks. Dirty read does not override privileges. Please refer to the Empress: Database Administrator's Guide for invoking dirty read feature.
3. Using * selects all attributes; otherwise, the `select_items` form the columns of the output. If nothing is specified in this list then all attributes are output. If attribute names are not preceded by a table specification and more than one table is involved in the `SELECT`, then attributes will be selected from the table in which they are first encountered, starting with the first listed table. The names of selected attributes are printed out above each column of values. Expressions and functions in the list are reproduced in headings. In all cases alternate column headings may be printed using a `print_clause`.
4. In a `print_clause` the `header` string must be enclosed in quotes if it contains colons, semicolons, periods, slashes, blanks or new lines. Additional formatting is available through system variables which may be set to draw a box, change the row, column and heading separators, and affect column widths.
5. The `table [ALIAS] aliasname` may be used to specify the tables from which data is selected. When an alias is assigned, any references to the `table` in the command may use the `aliasname`
.
6. The keyword `OUTER` is used in the `SELECT` statement to perform an outer join. An outer join returns all the rows by the simple join and also returns the rows from a table which are not part of an inner join.
7. The `LIST` option produces output with one attribute value per line. The attribute name (or `header` if specified) followed by a colon and a space are placed at the beginning of the line in front of the attribute value. A blank line separates records.
8. The `REPORT` option is used in conjunction with the `DUMP` option. It prefaces `DUMP` output with information about the attributes selected and their data types.
9. The `DUMP` option suppresses attribute labels and prints attribute values alone. The values are separated by the Empress separator string which stored in `MSVALSEP`. The default is normally <Ctrl+V>. If `LIST` is used as well as `DUMP`, the attribute values are printed one per line with no labels.
10. The output of the command may be diverted from the terminal into a new file or appended to an existing file by specifying `INTO` or `ONTO` respectively, and a filename.
11. The `where_clause` restricts output at the record level. It retrieves only those records satisfying its conditions. The `GROUP BY` clause forms groups of records having the same values for the attributes named in the clause. It causes an implicit sort on those attributes. A `having_clause` restricts output to those groups satisfying the conditions in the clause. A `having_clause` can only be used when every selected item is either grouped, a function, or a constant.
12. A `sort_clause` sorts the output by the specified attribute name, attribute number or the output column number. If groups are formed in the command, sorting is done within the groups. If any `UNIQUE` or `DISTINCT` attributes are requested, an implicit sort on those attributes is done following the implicit sort on groups and preceding the sort in the `sort_clause`.

    When a number is given in the `sort_clause` instead of an attribute name, the sorting is done according to the referred column number in the `select_item`, if `select_item` is given, otherwise, sorting is done according to the referred attribute number in the data dictionary (`sys_attrs` table).

    Using `column_num` is valuable when sorting on a column resulting from an expression, which may have no attribute name associate with it. For example, in the following command:

           SELECT a * b, c, d, e * d FROM T1, ORDER BY 1;

    The output is sorted by the result of the expression `a * b`.

13. The syntax for subqueries and nested selects is given in the appropriate subsections below. This topic is also treated in the chapter on Subqueries and in the Empress SQL: User's Guide.
14. The `COUNT` function prints out the number of records retrieved. The star (*) is used to count all retrieved records. If `DISTINCT` is used, all non-duplicate values are retrieved and counted. Note that `DISTINCT` may only be used once to qualify a list of selected items.
15. When `INSERT INTO table` is used with a select command, will divert the output of the `SELECT` into the table instead of onto the terminal or into a file. If the table does not exist, it will be created. The attributes in an existing table into which output is diverted must be compatible with those being selected. If a new table is created, its attributes will have the same names and data types as the attributes specified in the `SELECT` statement.

    The `INSERT INTO table` may also be placed before the `SELECT`, (e.g., `INSERT INTO table SELECT FROM table...`).

    When attributes are inserted into an existing table then the attributes selected from the old table(s) can be renamed in the new table by specifying `attrs` in the corresponding list. This allows records with only a few attribute values to be added to an existing table. It also provides a mechanism for altering attribute names and order in a new table which is being created.

    If a `select_item` includes a `header` (a string, enclosed in quotes if it contains embedded blanks), the corresponding attribute in a new table will have the name given by the `header` rather than its name in the old table; it will not affect the attribute name of an existing table. If both a `header` and an `attr` are specified, `attr` will take precedence over `header` as the name of the newly created attribute.

    If a `GROUP BY` clause is used, the output must consist only of rows. Output in which a function result is printed under each group will cause an error. Use of the `GROUP BY` clause is explained in the chapter GROUP BY Clause.

    Note that data cannot be inserted into a table from which it was selected.

16. Syntax 4 equals syntactically to:

            SELECT * FROM table

            [ sort_clause ]

            [ |LIST  | ] [DUMP] [ |INTO| file ];
              |REPORT|            |ONTO|

Privileges Required

- `SELECT` privilege on the appropriate tables,
- `INSERT` privilege on the appropriate table and on `sys_tables` (if a table is created), when using `INSERT INTO table` command.
- `BYPASS_LOCK` privilege on the appropriate tables if dirty read is used.

Example

Simple selects

1. To retrieve the entire `loans` table, use the command:

          SELECT * FROM loans;

   which produces:

          number     name            date                    amount

           5          Mosca           2 February 1990        $150.00
           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

   The command below produces exactly the same results as the previous command:

          SELECT FROM loans;

2. Select with simple qualification `WHERE` clause

   To retrieve only those loans made after May 1990, use:

          SELECT * FROM loans WHERE date > "31 May 1990";

   This produces:

          number      name          date                  amount

           8          Peterson       6 June 1990           $50.00
          17          Wladislaw     25 June 1990           $75.00
           3          Jones         12 August 1990        $300.00
          10          Kilroy        27 June 1990          $100.00
           4          Scarlatti      9 September 1990     $150.00
           4          Scarlatti     14 October 1990       $125.00

3. Select with multiple qualifications `WHERE` clause

   To retrieve the addresses and comments for the customers Mr. Lucas, Mr. Wilson, and Ms. Marshall, use:

          SELECT * FROM customers WHERE name MATCH "*Lucas*"
             OR name MATCH "*Wilson*"
             OR name MATCH "*Marshall*";

   which gives:

          name             address                comments

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

          Mr. B. Wilson    14 Valley View Drive,  Good customer - give
                           Bayville, CA 92308     credit if he asks for it

          Ms. C. Marshall  12 Berkeley St.        Suggest vacuum &
                           Gull Point, CA 92309   wash, she keeps 3
                                                  dogs. Look for dog hair
                                                  collecting in engine.

4. Select one attribute from a table

   You can select a specific attribute from a table:

          SELECT name FROM personnel;

   produces:

          name

          Kilroy
          Mosca
          Wladislaw
          Jones
          Peterson
          Scarlatti
          Jordan

5. Select some attributes with simple qualification `WHERE` clause

   To retrieve the `name` and `number` of all personnel with a `credit_limit` of more than $250.00, use:

          SELECT name, number
             FROM personnel WHERE credit_limit > 250.00;

   which produces:

          name       number

          Kilroy     10
          Mosca       5
          Jones       3

6. Select attributes in different orders

   You are not confined to printing data in the order in which the attributes were specified when the table was created. To retrieve the entire `loans` table showing the amount followed by the name followed by the date, use:

          SELECT amount, name, date FROM loans;

   which produces:

          amount      name          date

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

7. Select attributes with different headings

   The keyword `PRINT`, followed by a character string, may be used in a `SELECT` command to replace an attribute name with a heading. If the heading contains blanks, periods, slashes, colons, semicolons, tabs, or new lines, it must be enclosed in quotes as though it were a complex attribute name.

   The command:

          SELECT name PRINT Employee,
             amount PRINT "Loan Amount" FROM loans;

   produces:

          Employee      Loan Amount

          Mosca         $150.00
          Jones          $33.95
          Kilroy        $250.00
          Wladislaw      $55.00
          Jones          $25.00
          Mosca         $200.00
          Wladislaw      $25.00
          Peterson       $50.00
          Wladislaw      $75.00
          Jones         $300.00
          Kilroy        $100.00
          Scarlatti     $150.00
          Scarlatti     $125.00

   The same result might be obtained using the keyword `AS`, instead of `PRINT`.

          SELECT name AS Employee,
             amount AS "Loan Amount" FROM loans;

   The Empress variable `MSQLSELHEAD`, if unset, will suppress the printing of headers.

8. Selects with computations

   Computations may be made on attribute values as they are selected. This is one use of expressions. Expressions may be simple or quite complex, for example:

   - amount * 2
   - (credit_limit - (amount * 1.02))/credit_limit * 100

   You can select loans showing the current amount, 2% interest, and the amount plus the interest, with the command:

          SELECT name, amount, amount * 0.02 PRINT 'interest',
             amount * 1.02 PRINT 'new balance'
             FROM loans;

   which produces the following:

          name            amount           interest    new balance

          Mosca           $150.00          3.00        153.00
          Jones            $33.95          0.67         34.62
          Kilroy          $250.00          5.00        255.00
          Wladislaw        $55.00          1.10         56.10
          Jones            $25.00          0.50         25.50
          Mosca           $200.00          4.00        204.00
          Wladislaw        $25.00          0.50         25.50
          Peterson         $50.00          1.00         51.00
          Wladislaw        $75.00          1.50         76.50
          Jones           $300.00          6.00        306.00
          Scarlatti       $150.00          3.00        153.00
          Scarlatti       $125.00          2.50        127.50

   To print the dollar sign ($) for `interest` and `new balance` on the output, use:

          SELECT name, amount,
             amount * 0.02 CONVERT DOLLAR PRINT 'interest',
             amount * 1.02 CONVERT DOLLAR PRINT 'new balance'
             FROM loan;

   which produces the following:

          name            amount           interest     new balance

          Mosca           $150.00          $3.00        $153.00
          Jones            $33.95          $0.67         $34.62
          Kilroy          $250.00          $5.00        $255.00
          Wladislaw        $55.00          $1.10         $56.10
          Jones            $25.00          $0.50         $25.50
          Mosca           $200.00          $4.00        $204.00
          Wladislaw        $25.00          $0.50         $25.50
          Peterson         $50.00          $1.00         $51.00
          Wladislaw        $75.00          $1.50         $76.50
          Jones           $300.00          $6.00        $306.00
          Scarlatti       $150.00          $3.00        $153.00
          Scarlatti       $125.00          $2.50        $127.50

9. Select from multiple tables and insert into another table

   To combine the loans and credit limit of each employee in a new table called `loanlist`, with the attribute storing the names of the employees to be called `Employee`, use:

          SELECT name PRINT Employee, credit_limit, date, amount
             FROM loans, personnel
             INSERT INTO loanlist
             WHERE loans.name = personnel.name;

   A display of the contents of the database now shows:

          *** repairs ***

          auto parts
          customers
          loanlist
          loans
          personnel

   and,

          SELECT * FROM loanlist;

   produces:

          Employee     credit_limit     date                 amount

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

   This new table can be treated exactly like any other table in the database.

10. Select using UNION feature:

    To get the combination of of two loan tables, `loans` and `loanlist` to see all the people receiving loans, use:

           SELECT name, amount FROM loans
           UNION
           SELECT Employee, amount FROM loanlist;

    Note that the operands of a UNION operation must be of the same degree. It means that the set of attributes in two sides of the UNION operation must have the same number of attributes, and corresponding attributes must be of the same data types. So here, name and Employee must be of the same data type and amount attributes must must be of the same data type too.

11. Select using UNION Feature, when two tables have exactly the same structure:

    If loans and loanlist have the same structure, and we want to get the union of all the contents of the two tables, we can use:

           TABLE loan UNION TABLE loanlist;

## 5.4 UPDATE

The `UPDATE` command changes attribute values in one or more existing records in a table.

Syntax

  Syntax 1:

  |  |  | `UPDATE table SET attr` | `|TO |=` | `| |` | `attr_value {, attr` | `|TO |=` | `| |` | `attr_value}`

            [where_clause];

  Syntax 2:

  |  |  | `UPDATE table [` | `|(*) |(attr {, attr})` | `|] |`

  |  |  |  |  |  | `|VALUES [(] attr_value {, attr_value} [)] |attr_value {, attr_value} [END] |SET TO attr_value {, attr_value} |FROM data_source` | `|; | | |`

            [where_clause];

  Syntax 3:

  |  |  | `UPDATE table SET` | `| * | attr {, attr}` | `| |`

  |  |  |  |  |  | `|VALUES [(] attr_value {, attr_value} [)] |attr_value {, attr_value} [END] |FROM data_source` | `|; | |`

            [where_clause];

  where:

  `attr_value` |
  is one of: | `|expr |NULL |DIRECT_FROM data_source` | `| | |`
  expr | is any legal value for the given attribute. Details are provided in the chapter Expressions.
  data_source |
  is one of: | `|file |!os_command` | `| |`
  os_command | is an operating system command that generates output to be inserted into the table directly. This feature available in UNIX only.
  `where_clause` | the syntax is found under the chapter WHERE Clause in this manual.

Notes

1. Data values must be placed in the same order of their corresponding attributes. If attributes are specified on the command line, data must be for only those attributes, in the named order; attributes not specified will be unchanged.
2. To be certain that only the desired records get updated, the WHERE clause should be used to qualify those records.
3. A table may be updated from a file. The data in the file may be one attribute value per line, or one record per line with attribute values separated by the string specified by `MSVALSEP` (the default is <Ctrl+V> or octal 26). Records are separated by new lines. A data line may be split over more than one line if new lines are masked with the line continuation character specified by `MSLINECONT`. The default value of `MSLINECONT` is a backslash (\).
4. See the chapter on Interactive Interface for another way of updating records one at a time with full prompting, using the Interactive Interface `UPDATE` command.

Privileges Required

  `UPDATE` privilege.

Example

1. Simple update

   To change all date entries in `loans` to `1 January 1990`, use the command:

          UPDATE loans
            SET date = '1 Jan 1990';

2. Update specific records

   To change all occurrences of `Mosca` to `Kilroy` in the `loans` table, you could use the command:

          UPDATE loans
             SET name TO 'Kilroy'
             WHERE name = 'Mosca';

   This will change only the name in two of the records from `Mosca` to `Kilroy`.

3. Update records with AND qualification

   If the intention is to change `Mosca` to `Kilroy` only in the record where the date is `2 February 1990`, then the `WHERE` clause must be more specific. The correct command is:

          UPDATE loans
             SET name TO 'Kilroy'
             WHERE name = 'Mosca'
             AND date = '2 February 1990';

4. Update records with OR qualification

   Changing `Mosca` and `Jones` in all records to `Kilroy` could be done with the following:

          UPDATE loans
             SET name TO 'Kilroy'
             WHERE name = 'Mosca'
             OR name = 'Jones';

   Note that in English, the phrase refers to both `Mosca` and `Jones`, while the `WHERE` clause asks for `Mosca` or `Jones`. An `AND` in the `WHERE` clause would require that any given name be both `Mosca` and `Jones` at the same time; this is clearly impossible.

5. Examples of various `UPDATE` syntax

   To change Mosca's phone number to `923-1796` and the credit limit to `$250`, you may use any of the following:

          UPDATE personnel
             SET phone TO '923-1796',
             credit_limit TO 250
             WHERE name = 'Mosca';

          UPDATE personnel
             (phone, credit_limit)
             SET TO '923-1796', 250
             WHERE name = 'Mosca';

          UPDATE personnel
             (phone, credit_limit)
             '923-1796', 250 END
             WHERE name = 'Mosca';

          UPDATE personnel
             (phone, credit_limit)
             VALUES '923-1796', 250
             WHERE name = 'Mosca';

          UPDATE personnel
             VALUES 5, 'Mosca', '923-1796', 250
             WHERE name = 'Mosca';

6. Update with computation

   You can add 2% interest to all the loans with the command:

       UPDATE loans
            SET amount TO amount * 1.02;

7. Update records from a file

   To change all the `credit_limit` in the table `personnel` from a file (a file of data containing the new values either one per line or separated by the character string specified by the Empress system variable `MSVALSEP`) called `newlimits` containing the following:

          600
          500
          250
          750
          500
          250

   use the following command:

          UPDATE personnel
             SET credit_limit
             FROM newlimits;

   A `SELECT` from `personnel` table now shows:

          number     name           phone             credit_limit

          10         Kilroy         426-9681          $600.00
           5         Mosca          544-2243          $500.00
          17         Wladislaw      723-6073          $250.00
           3         Jones          667-2951          $750.00
           8         Peterson       978-6060          $500.00
           4         Scarlatti      961-7363          $250.00

## 5.5 DELETE

The `DELETE` command removes one or more records from a table.

Syntax

      DELETE [FROM] table [where_clause];

  where:

  `where_clause` | the syntax is given in the chapter WHERE Clause in this manual.

Notes

1. All records satisfying the `WHERE` clause will be deleted.

   Care should be taken to specify sufficient conditions to limit the records deleted to those you intend to delete.

2. If no `WHERE` clause is specified, all records in a table will be deleted. If a referential delete constraint has been placed on the table, only those records that satisfy the constraint will be deleted. In contrast, the `EMPTY` command will delete all records in a table regardless of any referential delete constraints that might have been placed on it.
3. The Interactive Interface provides for a fully-prompted record by record delete (see The Interactive Interface chapter in this manual). Using the Interactive Interface is generally a safer way to delete records.

Privileges Required

  `DELETE` privilege.

Example

1. Simple delete

   To delete all the records in the `loans` table for loans made by Jones, use:

          DELETE FROM loans
             WHERE name = 'Jones';

   This will delete all records associated with the name `Jones`.

2. Delete with `AND` condition

   To delete a single record, enough information has to be specified in the `WHERE` clause to select only that record. Suppose the record in question is for the loan made to Jones on August 12, 1990. Since Jones has several loans outstanding, the `DELETE` command must specify both the `name` and the `date`:

          DELETE FROM loans
             WHERE name = 'Jones'
             AND date = '12 August 1990';

   If in doubt, the safest way is to specify all the attributes in the `WHERE` clause.

## 5.6 EMPTY TABLE

The EMPTY command deletes all the records from the named table.

Syntax

      EMPTY table;

Notes

1. All the records of the table will be deleted but the table will still exist. This contrasts with the `DROP TABLE` command which deletes both the records and the table.
2. The `EMPTY` command should be used with caution to avoid inadvertently deleting all the records from a table.
3. Recovered space from the empty command is automatically re-allocated to the operating system.
4. The `EMPTY` command does not respect referential delete constraints.
5. The `EMPTY` command will create a new table file (rel file) within the database directory before deleting the old table file. The UNIX permissions in the new table file may be different than on the old table file if the umask has changed, and if the owner ID has changed.

Privileges Required

  `EMPTY` privilege.

Example

  To delete all the records in the `loans` table, use:

         EMPTY loans;

## 5.7 CALL

The CALL command calls named stored procedure. It can also be used to evaluate an expression.

Syntax

  `CALL` | `|expr |procedure_name [([expr {,expr}])]` | `| [ |` | `|INTO |ONTO` | `| filename ]; |`

Notes

1. The expression in the `CALL` command can not invoke any table or attribute name.

2. The `CALL` command will not deal with any data type conversion in the Interactive SQL.

3. Only procedure and function can be called. If aggregate function or operator is called, an error message will be given.

Privileges Required

  None.

Example

1. To call stored procedure `log_event`, use:

          CALL log_event;

2. To get the date of 32 days from today, use:

          CALL TODAY + 32 DAYS;

3. Some Math Library Functions:

          CALL sin(30);
          CALL cos(60);

## 5.8 LOCK TABLE

Sometimes situation arises that a guaranty of information (reading or updating from a table) during a time period must be met. This can be done by restricting table access from others during a transaction using the `LOCK TABLE` command. Locking is only in effect for the current transaction and takes effect immediately following the issuing of the `LOCK TABLE` command.

Syntax

  `LOCK table [IN]` | `|EXCLUSIVE |EXCL |SHARE` | `| [MODE]; | |`

Notes

1. A table may be locked in `EXCLUSIVE` or `SHARE` modes. An `EXCLUSIVE` mode lock gives exclusive access to the table. No one else may select from or update the table and no other locks may be placed on it concurrently. A `SHARE` mode lock guarantees only that you will be able to select from the table and precludes update operations by others.

2. Several `SHARE` mode locks can be placed on a table from different sources at one time. If more than one `SHARE` mode lock exists on a given table it may not be updated. If you have placed the only `SHARE` mode lock on a table you may perform updates on that table. Records so updated will not be accessible to others for the remainder of the transaction.

3. The `LOCK TABLE` command will only have effect if some level of locking has been set with the `LOCK LEVEL` command. If the lock level has been set to null-level locking, `LOCK TABLE` will have no effect. Otherwise `LOCK TABLE` will set locking to the table-level, regardless of the level specified in the `LOCK LEVEL` command.

4. `LOCK TABLE` in `EXCLUSIVE` mode will failed if someone else is accessing (reading or updating) a record in the table. `LOCK TABLE` in `SHARE` mode will failed if someone else is updating a record in the table.

Privileges Required

  `SHARE` mode requires `SELECT` or `UPDATE` privilege.

  `EXCLUSIVE` mode requires `UPDATE`, `INSERT` or `DELETE` privilege.

Example

  Assuming that a transaction has been started, the `loans` table can be locked for the duration of the transaction by issuing:

         LOCK loans EXCLUSIVE;

  If someone else tries to access the `loans` table they will see the following message:

         *** Lock Busy *** table 'loans' lock busy

## 5.9 SORT

The `SORT` or `ORDER` command physically (on disk) reorders the data in a table. A table may be sorted on any number of attributes at once, in ascending or descending order for each attribute.

Syntax

  `|SORT |ORDER` | `| table [ |` | `|BY |ON` | `|] attr [` | `|ASCENDING |ASC |DESCENDING |DESC` | `|] {, attr [ | | |` | `|ASCENDING |ASC |DESCENDING |DESC` | `|]}; | | |`

Notes

1. `SORT` and `ORDER` are synonymous. The keywords `ASC` and `DESC` are synonyms for `ASCENDING` and `DESCENDING`, respectively

2. The output of a `SELECT` command may be sorted dynamically, as described in the section on `SELECT` in this manual. Generally, if sorted output is desired from a table which is changed only rarely (there are few updates or insertions), then it is most efficient to sort the table. If the table is changed frequently, then it is best to sort in the `SELECT` command.

3. The system variable `MSSORTSPACE` can be used to limit the amount of memory space Empress will attempt to acquire for the sort. Details will be found in the Empress: Database Administrator's Guide.

4. The system variable `MSSORTBYPASS`, if set (it is set by default), causes sorting to skip locked records and print a warning message. If the variable is not set, locked records will cause the `SORT` to fail with a `lock busy` message.

5. The `SORT` command will create a new table file (`rel` file) within the database directory before deleting the old table file. The disk space required by this command is therefore at least twice the size of the table. Once the sort is completed and the old table file is deleted, the space associated with the original unsorted table will be reclaimed by the operating system.

6. If the sort cannot be done in memory (or `MSSORTSPACE` is set) Empress will use temporary files within the `MSTMPDIR` directory. These files will be deleted when the `SORT` command is complete.

7. Since the `SORT` command creates a new table file, the UNIX permissions and the owner on the the new file may change depending on the umask setting, and on the owner ID.

8. Under no circumstances should the `SORT` command be used on the data dictionary tables.

Privileges Required

  `SORT` privilege.

Example

  To sort the `personnel` table so that it is ordered by personnel number with the lowest numbers earliest in the table, use either one of the following:

         SORT personnel BY number;

         ORDER personnel BY number;

  To see the data in the sorted table, issue:

         SELECT FROM personnel;

  It shows:

         number     name          phone             credit_limit

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