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. |
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 |
| |||
| expr | is any legal value for the given attribute. Details are provided in the chapter Expressions. | |||
| data_source |
| |||
| 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
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:
Privileges Required
Example
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.
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';
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);
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');
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;
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;
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.
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';
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';
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:
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 ];
| 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_items 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. |
| select_command | may be either of:
|
Notes
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.
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.
SELECT * FROM table
[ sort_clause ]
[ |LIST | ] [DUMP] [ |INTO| file ];
|REPORT| |ONTO|
Privileges Required
Example
Simple selects
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;
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
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.
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
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
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
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.
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:
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
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.
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.
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;
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 |
| |||
| expr | is any legal value for the given attribute. Details are provided in the chapter Expressions. | |||
| data_source |
| |||
| 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
Privileges Required
UPDATE privilege.
Example
Simple update
To change all date entries in loans to 1 January 1990, use the command:
UPDATE loans
SET date = '1 Jan 1990';
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.
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';
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.
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';
Update with computation
You can add 2% interest to all the loans with the command:
UPDATE loans
SET amount TO amount * 1.02;
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
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
Care should be taken to specify sufficient conditions to limit the records deleted to those you intend to delete.
Privileges Required
DELETE privilege.
Example
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.
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.
The EMPTY command deletes all the records from the named table.
Syntax
EMPTY table;
Notes
Privileges Required
EMPTY privilege.
Example
To delete all the records in the loans table, use:
EMPTY loans;
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
The expression in the CALL command can not invoke any table or attribute name.
The CALL command will not deal with any data type conversion in the Interactive SQL.
Only procedure and function can be called. If aggregate function or operator is called, an error message will be given.
Privileges Required
None.
Example
To call stored procedure log_event, use:
CALL log_event;
To get the date of 32 days from today, use:
CALL TODAY + 32 DAYS;
Some Math Library Functions:
CALL sin(30); CALL cos(60);
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
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
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
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