IT Universe documentation - SQL
| Id | Description | Syntax | E/W |
|---|---|---|---|
| ALTER TABLE | You must use either the ADD, DROP, ALTER, or TRIGGER clause to modify a table. To use the ADD clause to add a table constraint, you must have write permissions on the VOC file in the current directory. |
ALTER TABLE tablename {ADD clause | DROP clause | ALTER
clause | TRIGGER clause} ; |
N |
| CALL | The CALL statement has two syntaxes. The first follows the ODBC pattern, in which a comma-separated list of arguments is enclosed in parentheses. The second follows the UniVerse syntax pattern, in which a space-separated list of arguments not enclosed in parentheses follows the procedure name. |
CALL procedurename [( [parameter [, parameter] . . .] )] [;]
CALL procedurename [argument [argument] . . .] [;] |
|
| CREATE INDEX | Using secondary indexes can improve the performance of certain queries where the table lookup is not based on the primary key. |
CREATE [UNIQUE] INDEX indexname ON tablename (columnname
[ASC | DESC] [, columnname [ASC | DESC]] . . . ) ; |
E |
| CREATE SCHEMA | A schema is created as a UniVerse account. If the schema directory does not contain a UniVerse account, CREATE SCHEMA creates all the necessary UniVerse files. When you create a schema, its name is added to the UV_SCHEMA table in the SQL catalog. |
CREATE SCHEMA [schema] [AUTHORIZATION owner]
[HOME pathname] [CREATE TABLE statements] [CREATE VIEW statements] [CREATE INDEX statements] [CREATE TRIGGER statements] [GRANT statements] ; |
E |
| CREATE TABLE | When you create a table, you define one or more columns for it. You also define each column's data type. You can put constraints on columns that protect the integrity and consistency of the data. And you can associate multivalued columns so each value in one column is associated with its corresponding values in the associated columns. |
CREATE TABLE tablename [DATA pathname] [DICT pathname]
([file_format ,] column_definitions [, associations] [,table_constraints] ) ; |
N |
| CREATE TRIGGER | A trigger specifies actions to perform before or after each row is changed by certain triggering events (SQL statements or BASIC I/O operations). |
CREATE TRIGGER triggername {BEFORE | AFTER} event [OR
event] . . . ON tablename FOR EACH ROW CALLING 'program' ; |
EN |
| CREATE VIEW | CREATE VIEW defines a virtual table derived from one or more base tables or views. CREATE VIEW also creates a file dictionary for the view. A view behaves in many ways like a table created with the CREATE TABLE statement. |
CREATE VIEW viewname [(columnnames)] AS query_expression
[WITH [LOCAL | CASCADED] CHECK OPTION] ; |
EN |
| Column | Use column syntax when you see column in a syntax line. You can use column syntax in the following clauses of the SELECT statement: |
You can specify a column in one of three ways: as a column
name, an EVAL expression, or an alias. column has one of the following syntaxes: |
|
| Condition | Use condition syntax when you see condition in a syntax line. You use conditions to determine whether data meets specified criteria. You can use conditions in the following statements: |
This list summarizes the syntax of conditions that compare
expressions: |
|
| DELETE | If you try to delete multiple rows and the DELETE statement fails (due to a constraint violation, for example), no rows are deleted. | DELETE FROM table_expression [WHERE clause] [qualifiers] ; | E |
| DROP INDEX | DROP INDEX removes the specified secondary index from the table. | DROP INDEX tablename.indexname; | EN |
| DROP SCHEMA | DROP SCHEMA removes the specified schema and all its tables and views from the SQL catalog. You cannot drop a schema if any of its tables are referenced by tables in other schemas. | DROP SCHEMA schema [CASCADE]; | EN |
| DROP TABLE | DROP TABLE removes the table and all views depending on it from the SQL catalog. It also deletes the UniVerse data file, its associated dictionary, and any secondary indexes. | DROP TABLE tablename [CASCADE]; | E |
| DROP TRIGGER | When you drop a trigger, its name is removed from the table's SICA, but the corresponding BASIC program is not deleted. | DROP TRIGGER tablename {triggername | ALL} ; | E |
| DROP VIEW | DROP VIEW removes the view from the SQL catalog. It also deletes its associated dictionary. | DROP VIEW viewname [CASCADE] ; | E |
| Data Type | The data type of a column specifies the kind of data the column contains. You specify data types in the following statements and functions: | To specify a data type, use the following syntax: | |
| Expression | An expression is one or more data elements combined using arithmetic operators, the concatenation operator, and parentheses. You can use expressions in the following statements: | E | |
| FOR UPDATE Clause | ----------------------------------------------------------------- | ||
| FROM Clause | The FROM clause specifies one or more tables or UniVerse files from which to select data. Its syntax is as follows: | E | |
| GRANT | ON UNIX SYSTEMS |
GRANT database_privilege TO users;
GRANT table_privileges ON tablename TO {users | PUBLIC} [WITH GRANT OPTION] ; |
E |
| GROUP BY Clause | The GROUP BY clause groups rows that have identical values in all grouping columns and returns a single row of results for each group. The syntax is as follows: | E | |
| HAVING Clause | The HAVING clause specifies the criteria that data in a group must meet for the group to be selected. The syntax is as follows: | E | |
| INSERT | The INSERT statement creates one or more new rows in a table or file. You can specify the values you want to insert in one row, or you can use a SELECT statement to insert a set of rows from another table. |
INSERT INTO table_expression [( columnnames )] values
[qualifiers] ; |
E |
| Identifier | Identifiers specify the names of the following SQL objects: | N | |
| Literal | There are four kinds of literal in UniVerse SQL: | N | |
| ORDER BY Clause | The ORDER BY clause sorts the results of a query. The syntax is as follows: | E | |
| REVOKE | When you revoke a user's ability to grant a privilege to others (privileges granted using WITH GRANT OPTION), you do not break the chain of privileges granted by that user. Only that user's grant option is revoked; all privileges granted by that user remain in effect. |
REVOKE database_privilege FROM users;
REVOKE [GRANT OPTION FOR] table_privileges ON tablename FROM {users | PUBLIC} ; |
E |
| Relational Operator | The relational operators are as follows: | ||
| Report Qualifiers | Report qualifiers format the output of interactive SELECT statements. In programmatic SQL the only report qualifiers you can use are SAMPLE, SAMPLED, and NO.OPTIMIZE. For more information about report qualifiers, see UniVerse User Reference. | ||
| SELECT | The SELECT statement can comprise up to nine fundamental clauses. The SELECT and FROM clauses are required. |
SELECT clause FROM clause
[WHERE clause] [WHEN clause [WHEN clause] . . .] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [FOR UPDATE clause] [report_qualifiers] [processing_qualifiers] [UNION SELECT statement] ; |
E |
| Set Function | Use set function syntax when you see set_function in a syntax line. You can use set functions in the following clauses of the SELECT statement: |
COUNT( * )
{AVG | MAX | MIN | SUM | COUNT} (DISTINCT column) {AVG | MAX | MIN | SUM | COUNT} ( [ALL] select_expression) |
|
| Subquery | Use subquery syntax when you see subquery in a syntax line. You can use subqueries in the following statements: |
There are three ways to include a subquery in a condition. The
syntax is as follows: |
E |
| Table | Use table expression syntax when you see table_expression in a syntax line. You use table expressions in the following statements: | The syntax of a table expression is as follows: | |
| UNION Operator | The UNION operator combines the results of two SELECT statements into a single result table. A set of SELECT statements joined by UNION operators is called a query expression. You can use query expressions as interactive SQL queries, programmatic SQL queries, and in the CREATE VIEW statement. However, you cannot use a query expression as a subquery or in the INSERT statement. | EN | |
| UPDATE | You must specify clauses in the UPDATE statement in the order shown. If you do not specify a WHERE clause, all rows are updated. If you specify a WHEN clause, only values in the specified association rows are updated. If you are updating multivalued columns or an unassociated multivalued column and you do not specify a WHEN clause, all multivalues in the selected rows are updated. |
UPDATE table_expression
SET set_expressions [WHERE clause] [WHEN clause [WHEN clause] . . .] [qualifiers] ; |
N |
| WHEN Clause | The WHEN clause limits output from multivalued columns to rows in an association that meet the specified criteria. WHEN lists selected multivalues in associated columns without having to unnest the association first. The syntax is as follows: | ||
| WHERE Clause | The WHERE clause specifies the criteria that data in a row must meet for the row to be selected. The syntax is as follows: |
All documents copyright by their respective owners.