Next: 16. Ovrimos SQL Up: Ovrimos version 3.0 On-line Previous: 14. Ovrimos Files and

Subsections

15. Ovrimos SQL

Although the present documentation is not intended as an SQL tutorial, it is useful to provide a chapter about the supported SQL statements.

Most of the statements supported by Ovrimos are standard SQL2 (or SQL/92) statements. There are a few, however, such as CREATE USER, etc. that are SQL-like, but do not belong to the 'body' of SQL.

Conventions of this chapter
All SQL statements and functions, as well as SQL reserved words are CAPITALIZED. They are written in bold when they appear for the first time and when the statement syntax is described. Ovrimos SQL, is not case sensitive.

Examples are written in teletype fonts.

15.1 Supported Data Types

This section makes a brief reference to the supported data. For data definition , the SQL/92 synonyms are supported for the following types: #12095#>

dec = decimal
int = integer
char = character
double = double precision Precision, scale, and string length are optional, in accordance with SQL/92.

: char = char(1)
same for varchar, binary, varbinary
numeric(5) = numeric (5,0)
numeric = numeric (17,0)
same for decimal

time and timestamp have an optional precision, signifying fractional seconds.
time = time(0)
timestamp = timestamp(0)

15.1.1 Expressions

In the description of commands or functions, the term 'expression' appears whenever necessary. Expressions can be names of columns, results of scalar functions, literals etc. The underlying data types for these expressions are presented below.

numeric_expression

: NUMERIC
DECIMAL
TINYINT
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE

float_expression

: FLOAT

integer_expression

: TINYINT
SMALLINT
INTEGER
BIGINT

string_expression::= ascii_expression | unicode_expression | binary_expression

ascii_expression

: CHAR
VARCHAR

unicode_expression

: UNICODE_CHAR
UNICODE_VARCHAR

binary_expression

: BINARY
VARBINARY

long_expression

: LONG
LONG VARBINARY
LONG VARCHAR

date_expression

: DATE
TIMESTAMP

time_expression

: TIME
TIMESTAMP

timestamp_expression

: TIMESTAMP

DATE, TIME and TIMESTAMP are defined and used according to the SQL2 Standard.

Thus, the format for DATE is yyyy-mm-dd. Whenever DATE is used in an SQL statement, its value must be enclosed in single quotes (') and preceded by the reserved word DATE.
Thus, the 2nd of December of the year 1962 is described as:

DATE '1962-12-02'.

Legal dates range from '0001-01-01' to '9999-12-31'.

The format of TIME is hh:mm:ss or hh:mm:ss.tttttt (with the .tttttt part, signifying fractional seconds, optional). Whenever TIME is used in an SQL statement, its value must be enclosed in single quotes (') and preceded by the reserved word TIME.
Thus, 9.30 a.m. is described as:

TIME '09:30:00' or '09:30:00.000000'.

Legal times range from '00:00:00.000000' to '23:59:59.999999'.

It is possible to completely omit the last part (0 digits), but it is also permitted to have from 1 to 6 digits in the time format. Thus, time:

TIME '09:30:15.50' is legal.

The TIMESTAMP is the DATE and TIME combined, with an optional time zone interval in the form of +hh:mm. The TIME part has an optional fractional seconds part (as TIME).

Whenever used in an SQL statement, the timestamp expression must be enclosed in single quotes (') and preceded by the reserved word TIMESTAMP. The time interval is the time difference of a place from the UTC (Universal Coordinated Time), also known as GMT.

For example, the interval for Athens, Greece is +02:00 and the timestamp for the 11th of September of 1998, at 7.30 PM in Athens is:

TIMESTAMP '1998-09-11 19:30:00+02:00'.

If the timestamp interval is not explicitly set, then the local time zone (i.e. the server time zone is used.) The user may modify the local time zone through the statement SET TIME ZONE. See also, B.4.2.(Supported SQL Statements: Set Time Zone).

15.1.2 SQL parameters

It is possible to have a dynamic parameter, denoted by the question mark symbol ("?"), in places where literals are permitted. This is called placeholder or parameter. Parameters are used in statements to defer the assignment of a value until some later time. Parameters must definitely be replaced by literals at execution time. An example of parameter (?) usage appears in section B.9.(BLOBs and the URI function) of this chapter. There, the parameter is used to insert the address of a BLOB.

15.2 Keywords

The following identifiers are reserved words in Ovrimos SQL.
NOTE: Emphasized keywords are listed as not reserved in SQL2, but since this is not very meaningful, they are reserved in Ovrimos SQL.

ABSOLUTE, ACTION, ADD, ALL, ALLOCATE, ALTER, AND, ANY, ARE, AS, ASC, ASSERTION, AT, AUTHORIZATION, AVG.

BEGIN, BETWEEN, BIT, BIT_LENGTH, BOTH, BY.

CASCADE, CASCADED, CASE, CAST, CATALOG, CHAR, CHARACTER, CHAR_LENGTH, CHARACTER_LENGTH, CHECK, CLOSE, COALESCE, COLLATE, COLLATION, COLUMN, COMMIT, COMMITTED, CONNECT, CONNECTION, CONSTRAINT, CONSTRAINTS, CONTINUE, CONVERT, CORRESPONDING, COUNT, CREATE, CROSS, CURRENT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, CURSOR.

DATE, DEALLOCATE, DAY, DEC, DECIMAL, DECLARE, DEFUALT, DEFERRABLE, DEFERRED, DELETE, DESC, DESCRIBE, DESCRIPTOR, DIAGNOSTICS, DISCONNECT, DISTINCT, DOMAIN, DOUBLE, DROP.

ELSE, END, END-EXEC, ESCAPE, EXCEPT, EXCEPTION, EXEC, EXECUTE, EXISTS, EXTERNAL, EXTRACT.

FALSE, FETCH, FIRST, FLOAT, FOR, FOREIGN, FOUND, FROM, FULL.

GET, GLOBAL, GO, GOTO, GRANT, GROUP.

HAVING, HOUR.

IDENTITY, IMMEDIATE, IN, INDICATOR, INITIALLY, INNER, INPUT, INSENSITIVE, INSERT, INT, INTEGER, INTERSECT, INTERVAL, INTO, IS, ISOLATION.

JOIN

KEY

LANGUAGE, LAST, LEADING, LEFT, LEVEL, LIKE, LOCAL, LOWER

MATCH, MAX, MIN, MINUTE, MODULE, MONTH

NAMES, NATIONAL, NATURAL, NCHAR, NEXT, NO, NOT, NULL, NULLIF, NUMERIC

OCTET_LENGTH, OF, ON, ONLY, OPEN, OPTION, OR, ORDER, OUTER, OUTPUT, OVERLAPS

PARTIAL, POSITION, PRECISION, PREPARE, PRESERVER, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE, PUBLIC

QUIT

READ, REAL, REFERENCES, RELATIVE, RESTRICT, REVOKE, REPEATABLE, RIGHT, ROLLBACK, ROWS

SCHEMA, SCROLL, SECOND, SECTION, SELECT, SESSION, SESSION_USER, SERIALIZABLE, SET, SIZE, SMALLINT, SOME, SQL, SQLCODE, SQLERROR, SQLSTATE, SUBSTRING, SUM, SYSTEM_USER

TABLE, TEMPORARY, THEN, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TO, TRAILING, TRANSACTION, TRANSLATE, TRANSLATION, TRIM, TRUE, TYPE

UNCOMMITTED, UNION, UNIQUE, UNKNOWN, UPDATE, UPPER, USAGE, USER, USING, VALUE, VALUES, VARCHAR, VARYING, VIEW

WHEN, WHENEVER, WHERE, WITH, WORK, WRITE

YEAR

ZONE

Additional ODBC 2.0 SQL Keywords:

BIGINT, BINARY, TINYINT, VARBINARY

Additional Ovrimos SQL Keywords:

CALL, FIND_COLUMN, FUNCTION, INDEX, UNICODE, UNSIGNED

15.3 Sample Data

A demonstration database , testbase , is provided, to help users familiarize themselves with Ovrimos features. See 2.3.1.(Populating testbase), for setting up testbase. For better understanding of the examples, the contents of some sample tables are presented here. All these tables may be found in testbase after populating it with sample data.

Solar System

Tables Planets and Satellites contain data about the Solar system.

Table Planets

Name	Descr	S	Distance	Small_pic	Big_pic
Mercury	The planet where days last longer than years	1	36.0	image/gif	image/jpeg
Venus	The greenhouse planet	2	67.1	image/gif	image/jpeg
Earth	Mostly harmless	3	92.7	image/gif	image/jpeg
Mars	The red planet	4	141.5	image/gif	image/jpeg
Jupiter	The sun that failed	5	483.4	image/gif	image/jpeg
Saturn	The ringed planet	6	886.7	image/gif	image/jpeg
Uranus	The rolling planet	7	1782.7	image/gif	image/jpeg
Neptune	The blue planet	8	2794.0	image/gif	image/jpeg
Pluto	The paired planet	9	3666.1	image/gif	image/jpeg

Table Satellites

Name Planet Small_pic Big_pic

Moon Earth image/gif image/jpeg

Phobos Mars image/gif image/jpeg

Deimos Mars image/gif image/jpeg

Callisto Jupiter image/gif image/jpeg

Ganymede Jupiter image/gif image/jpeg

Io Jupiter image/gif image/jpeg

Europa Jupiter image/gif image/jpeg

Enceladus Saturn image/gif image/jpeg

Dione Saturn image/gif image/jpeg

Titan Saturn image/gif image/jpeg

Tethys Saturn image/gif image/jpeg

Rhea Saturn image/gif image/jpeg

Mimas Saturn image/gif image/jpeg

Oberon Uranus image/gif image/jpeg

Titania Uranus image/gif image/jpeg

Umbriel Uranus image/gif image/jpeg

Triton Neptune image/gif image/jpeg

Charon Pluto image/gif image/jpeg

Columns Small_pic and Big_pic are .gif and .jpg images respectively. In the table columns these are links to a place containing the image. See B.9.(BLOBs and the URI function).

Sales System

Tables Customers, Salespeople, and Orders contain data on a sales system.

Table Customers

Cid Cname City Rating Sid

2001 Hanson Helsinki 100 1001

2002 Gerard Athens 200 1003

2003 Longman Rome 200 1002

2004 Gable London 300 1002

2006 Conrad Helsinki 100 1001

2007 Carlton Paris 300 1007

2008 Peterson New York 100 1004

Table Salespeople

Sid Sname City Commission

1001 Pelee Helsinki 0.11

1002 Sarmani Rome 0.12

1004 Moss Helsinki 0.13

1007 Randall Paris 0.09

1003 Anakin New York 0.12

Table Orders

Ordid Quantity Odate Cid Sid

3001 25 1998-12-22 2008 1007

3002 38 1998-12-23 2007 1001

3003 129 1998-12-23 2001 1004

3004 72 1999-01-04 2004 1002

3005 18 1999-01-04 2002 1007

3006 31 1999-01-05 2006 1003

3007 55 1999-01-07 2004 1001

3008 62 1999-01-07 2003 1002

Table Orders is later altered during the course of an example. A new column ordtime of type TIME is added. The column is originally NULL. When the rows are added, data are inserted into the new column ordtime, but only in the new rows.

Bookstore

One more table Books, used for the Sample Bookstore Application. (This table is not generated by the script that populates testbase).

Table Books

ISBN TITLE AUTHOR PRICE COVER

999-4444-22-1 50 ways to feed a cat D. H. Hackleberry 12.50 image/gif

999-4434-21-1 Ancient Egypt D. H. Hackleberry 35.00 image/gif

989-2444-22-9 What I saw in Congo Apple J. Pie 34.00 image/gif

499-4144-52-8 Teach yourself Cobol in 21 days Egbert H. Cailloux 34.50 image/gif

499-6447-12-4 Surfing USA D. Boy 45.00 image/gif

Unicode Strings

Finally, there is a table called uni, used for examples of unicode insertion and retrieval.

Table Uni

Initial_char Uni_symbols

This table is empty. If you wish to fill-in sample data, look at the examples in B.10.(Storage and Retrieval of Unicode Data) .

At some point, a user pandora and a user friend are added to the database.

15.4 Supported SQL Statements

This section describes briefly the SQL statements that are supported by Ovrimos. Before presenting the statements, we make a list of the Data Definition and the Data Manipulation statements. Data Manipulation statements are divided into Cursor Operations and Non-cursor Operations. There are also statements related to Security issues.

Data Definition Statements

Alter Table
Create Domain
Create Index
Create Table
Create User
Create View
Drop Domain
Drop Index
Drop Table
Drop User
Drop View

Data Manipulation Statements

Cursor Operations

Every select statement can be said to return a set of results (result set). A result set is fully defined when we can traverse it and study its values. Cursors are a mechanism to traverse the result set. A cursor is a pointer, that can be used to run through the rows of a result set . Cursors are named either by the user or automatically by the system. There are two statements, Delete Positioned and Update Positioned, which are used to delete or update the row where a cursor traversing a single base table is currently positioned.

Delete Positioned
Update Positioned

Non-cursor Operations

Call
Delete Searched
Insert
Select
Select for Update
Update Searched

Security related Statements

Set Transaction
Commit
Rollback
Grant
Revoke

Session Parameter Statements

Set Time Zone

15.4.1 Definition Grammar

This text does not provide a full description of the SQL grammar. Most of the description needed may be found in the statement description. But, since some of the statements share parts of the description, we present fragments of grammar here, especially if the grammar is lengthy.

column_definition_list

column_definition_list ::= column_definition, ...

column_definition ::=
column_name {domain_name| data_type }
[column_constraint ...]
[DEFAULT default value];

and
column_constraint ::=
PRIMARY KEY | REFERENCES table_name [(column_name_list)]
| NOT NULL | UNIQUE | CHECK (expression)

column_list

column_list ::= column_name, ...

query_expression

query_expression ::=
SELECT [DISTINCT]
{ {aggregate function... | value_expression
[AS column_name]},...
} | {qualifier.*} | *
FROM ¯{{table_name [[AS] correlation_name] [(column_name,...)]}
|{ subquery [AS] correlation_name [column_name],... }},...
[WHERE predicate]
[GROUP BY {{[table_name | correlation_ name}.]column_name},...]
[HAVING predicate]

15.4.2 Statement Description

Each statement is followed by a short description text, the statement syntax and a short example.

Alter Table

The Alter Table statement is used to modify the definition of an existing table, by adding one column to it.

SQL Syntax:
ALTER TABLE table name ADD column_definition;
or
ODBC Syntax:
ALTER TABLE table name ADD (column_definition_list);

See B.4.1.(column_definition_list) for details of column definition.

Example:
ALTER TABLE orders ADD Ordtime TIME;

So, if the table was:

Ordid Quantity Odate Cid Sid

The altered table will be:

Ordid Quantity Odate Cid Sid Ordtime

Note: Only ADD is permitted in ALTER TABLE.

Call

The Call statement is used to invoke a stored procedure. Stored procedures are written by the user in one of the languages supported by Ovrimos and stored in executable form. See also 10.5.(Stored Procedures).

Syntax:
CALL proc-name [arguments]

Example:
CALL insert_data

where insert_data is a stored procedure.

Create Domain

The Create Domain statement is used to define a new domain, i.e. a new data type.

Syntax:
CREATE DOMAIN domain_name [AS] data type
[constraint_definition]
where
constraint_definition ::= conditional_expression;

See more about constraints in B.4.1.(Definition Grammar).

Example:
CREATE DOMAIN nat_number integer check value > 0;

Inside the conditional_expression the special identifier value denotes the value being checked.

The newly created domain nat_number will be used in Data Definition (usually when creating a table) as an integer number greater than 0, i.e. as a natural number.

Create Index

The Create Index statement defines a new index on an existing table.

Syntax:
CREATE [UNIQUE] INDEX index_name ON table_name
(column_name [ASC|DESC].,...);

Example:
CREATE UNIQUE INDEX ind1 ON planets (Serial ASC); The new index ind1, will be used as an index in ascending order. Its values must be unique.

Note: This is not a standard SQL statement, although it's quite common.

Create Table

The Create Table statement creates a new table.

Syntax:
CREATE TABLE table_name
({column_definition|[table_constraint]},...)

where
table_constraint ::=
PRIMARY KEY (column_name_list)
| FOREIGN KEY (column_name_list)
REFERENCES table_name [(column_name_list)]
| CHECK (expression)
| UNIQUE (column_name_list)

For a detailed description of column_definition see B.4.1.(column_definition_list).

Example:
CREATE TABLE Planets ( Name varchar(15) PRIMARY KEY, Descr varchar(50), Serial_number nat_number UNIQUE, Distance_mmiles decimal(5,1), Small_pic long varbinary, Big_pic long varbinary );

The newly created table Planets was presented in the beginning of this chapter.

Similarly, one can see another example of table creation, where foreign keys also exist, in the definition of table Satellites, which is also presented in the beginning of the chapter.

Example:
CREATE TABLE Satellites ( Name varchar(15) PRIMARY KEY, Planet varchar(15) REFERENCES Planets(name), Small_pic long varbinary, Big_pic long varbinary );

Create User

The Create User statement defines a new database user. It is possible to use a LIKE clause that creates a user with the privileges of a previously defined prototype user, thus allowing privilege inheritance. See section 13.4.5.(Privilege Inheritance).

Syntax:
CREATE USER username FOR 'full-user-name'
WITH 'password'| NULL
[LIKE prototype user]
[USING 'attribute=value;attribute=value...'];

where
attribute::=REMARKS | MAILBOX

Users with NULL password may not login.

The attribute remarks is only a string that appears next to the user description and has no other use. Attribute mailbox however, contains the mail address of a particular user. This address may be a simple user name (if the mailbox resides in $MAILSERVER) or a full mailbox otherwise. See also, A.2.2.(Database Parameters).

The mailbox attribute appears next to the user description in Roadmap. It may be used by admin to send mail to a user. It is also used by Ovrimos to inform admin of unsuccessful logins. See 13.4.3.(Unauthorized Logins).

Example:
CREATE USER pandora FOR 'Mary Adams' WITH 'pass1' LIKE admin;

This statement will create a user with name pandora and initial password pass1. This new user is like admin, which means she has full privileges. This is an example of privilege inheritance.

Example:
CREATE USER friend FOR 'Joe Phillips' WITH 'pass2'; This user has no admin privileges.

Example:
CREATE USER Farad FOR 'John Faraday' WITH 'pass3' USING 'mailbox=jfar';

A mailbox address, set in a local network mailserver, is set for user Farad.

Note! Users who communicate with a secure database are required to have a mail address if the authorization is done via certificates. The mailbox must be the same as the one given for the client's certificate. If authorization is checked only through passwords, the mailbox is not necessary.

For more information about passwords see 13.4.2.(Password Policy).

Note: This is not a standard SQL statement.

Create View

The Create View statement defines a view, i.e. a virtual table on one or more tables or already existing views.

Syntax:
CREATE VIEW table name [(column_list)]
AS SELECT statement;

Example:
CREATE VIEW Planet_list AS SELECT Name, Descr FROM planets WHERE Serial >= 5;

The Create View statement will make a view called planet_list, where only two columns of Planets will be visible. For users of planet_list the view will appear like:

Planet_list

Name Descr

Jupiter The sun that failed

Saturn The ringed planet

Uranus The rolling planet

Neptune The blue planet

Pluto The paired planet

All views have CASCADED CHECK OPTION as default.

The CHECK OPTION does not allow users to insert into the view or update rows to values that violate view constraints. For example, if a view is created as:

CREATE VIEW outer_planets AS SELECT * FROM planets WHERE Serial>4;

it is not allowed to insert a row where Serial <= 4.

Should you be allowed to insert such a row, then because of the existing view constraint (set at creation time), you would not be able to retrieve this row!

Following the same line of thought, CASCADED CHECK OPTION, means that new views based on this view, 'inherit' the CHECK of the base view. Thus, if we have a view created as:

CREATE VIEW gas_planets AS SELECT * FROM outer_planets WHERE ...;

this new view, besides all its other constraints, will also check the constraint of Serial > 4, set by the base view.

Delete Positioned

The positioned Delete statement is used to remove a row from a table. The row is at the point where the cursor has been previously positioned.

Syntax:
DELETE FROM table name
WHERE CURRENT OF cursor name;

Example:
DELETE FROM planets WHERE CURRENT OF Cur1;

The cursor Cur1 is previously defined to traverse the table. The cursor is positioned to a certain row, usually after the execution of a select statement. The statement will delete the row where the cursor is positioned. After deletion of the row, the position of the cursor is undefined.

Delete Searched

The searched Delete statement is used to remove rows from a table. If a predicate is specified, then only rows matching the predicate are deleted, otherwise the entire table contents are removed.

Syntax:
DELETE FROM table name [WHERE predicate];

Example:
DELETE FROM planets WHERE Distance_mmiles>3000;

This statement will delete the rows (if any), where the planet distance is greater than 3000 million miles. In our example, Pluto will be removed from the table.

Warning! Do not use the DELETE statement to remove users from the database. It may prove detrimental! Use the provided Drop User instead.

Drop Domain

The Drop Domain statement is used to discard a previously created domain.

Syntax:
DROP DOMAIN domain_name;

Example:
DROP DOMAIN nat_number;

After the Drop Domain statement, the identifier nat_number may no longer be used as a data type. The columns of tables already defined by this domain are not affected from the domain deletion, since the domain is used as a macro, which is expanded at the time of column definition and does not appear anywhere else.

Drop Index

The Drop Index statement is used to discard a previously created index.

Syntax:
DROP INDEX index name;

Example:
DROP INDEX ind1;

After the Drop Index statement, the columns involved in the ind1 index are no longer used as a key. If the index was defined as UNIQUE, the constraint, that was checked during insertion or update on columns participating in the index, is no longer checked.

Note: This is not a standard SQL statement.

Drop Table

The Drop Table statement is used to discard a previously created table.

Syntax:
DROP TABLE table name [CASCADE|RESTRICT];

The option RESTRICT is the default. If CASCADE is specified, then, when a table is removed all its dependent views are also removed. The option RESTRICT does not allow table removal when it has dependent views.

Example:
DROP TABLE planets;

The table planets is removed and all its data are deleted. Since no option is explicitly stated, any views dependent on it (such as planet_list) are also dropped. Foreign keys constraints are also dropped.

Drop User

The Drop User statement is used to remove a database user.

Syntax:
DROP USER user name;

Example:
DROP USER friend;

After the statement, user friend is no longer in the system. All tables and views created by user friend are now owned by user admin, who may decide to grant privilege to some other user for management and data manipulation of these tables.

Note: This is not a standard SQL statement.

Drop View

The Drop View statement is used to discard a previously defined view.

Syntax:
DROP VIEW view name [CASCADE|RESTRICT];

The option CASCADE is the default. When a view is removed all its dependent views are also removed. The option RESTRICT does not allow view removal when it has dependent views.

Example:
DROP VIEW outer_planets;

The view outer_planets is removed and all its data are deleted. Since no option is explicitly stated, any views dependent on it are also dropped.

Grant

The Grant statement is used to give privileges to users.

Syntax:
GRANT privilege,... ON object_name
TO {grantee,...}|PUBLIC
[WITH GRANT OPTION];

where
privilege::=
{ALL PRIVILEGES} |
{SELECT
| DELETE
| {INSERT [(column name,...)]}
| {UPDATE [(column name,...)]}
| {REFERENCES [column name,...)]}
}

object_name::= [TABLE] table_name

and
table_name ::= base_table_name | view_name

Two examples about granting privileges follow:

Example:
GRANT ALL PRIVILEGES ON planets TO pandora;

GRANT SELECT, INSERT ON satellites TO pandora with GRANT OPTION;

The first statement allows full privileges to user pandora on table Planets. The second allows SELECT and INSERT to user pandora on table Satellites. The GRANT OPTION allows the user to pass this privilege to other users as well.

We must note at this point, that granting privileges to a view grants automatically privileges to the base table. This is not the standard SQL treatment of privileges.

Insert

The Insert statement is used to insert rows into a table.

Syntax:
INSERT INTO table_name [(column name,...)]
query_expression|table_value_constructor;

where
table_value_constructor ::= VALUES (value, ...)

A detailed description of query_expression may be found in B.4.1.(Definition Grammar).

Example:
INSERT INTO Planets (Name, Descr, Serial) VALUES ('Mercury','The planet where days last longer than years', 1);

Warning! Do not use the INSERT statement to directly add users to the database. It may prove detrimental! Use the provided Create User instead.

Revoke

The Revoke statement is used to deprive a user from previously granted privileges.

Syntax:
REVOKE [GRANT OPTION FOR]
{ALL PRIVILEGES} | {privilege,...}
ON object_name
FROM PUBLIC | {grantee,...};

Two example about revoking privileges follow:

Example:
REVOKE INSERT, DELETE ON Planets FROM pandora;

REVOKE GRANT OPTION FOR SELECT ON Satellites FROM pandora;

The first statement revokes the INSERT and DELETE option from user pandora, while the second disallows user pandora to grant the SELECT option to other users. (User pandora retains her privilege to SELECT from Satellites.)

Select

The Select statement is used to return rows from one or more tables.

Syntax:
query_expression
[UNION[ALL] select_statement | TABLE table_name
[ORDER BY {{output_column [ASC|DESC]},...}
| {{positive integer [ASC|DESC]},...}];

The first part of the Select syntax is query_expression, which is described in detail in B.4.1.(Definition Grammar).

Example:
SELECT Descr FROM Planets WHERE Name = 'Earth';

Descr

Mostly harmless

Example:
SELECT count(*) FROM planets WHERE Distance_mmile>100;

count

6

The following example is a SELECT with a subquery, where the two tables are connected with the matching name of the planet.

Example:
SELECT Planets.Name, Descr FROM planets WHERE Name = (SELECT Planet FROM Satellites WHERE Satellites.Name = 'Titania');

Name Descr

Uranus The rolling planet

The following example will return the planets ordered by decreasing distance.

Example:
SELECT Name FROM planets ORDER BY distance_mmiles DESC;

Name

Pluto

Neptune

Uranus

Saturn

Jupiter

Mars

Earth

Venus

Mercury

The following SELECT statement joins the tables Planets and Satellites and produces a sequence of rows with all planets and satellites that have Serial less than 6. Thus, Saturn, Uranus, Neptune and Pluto are not included in the output.

Example:
SELECT planets.name, satellites.name FROM planets, satellites WHERE planets.name = satellites.planet AND serial<6;

Name Name

Earth Moon

Mars Phobos

Mars Deimos

Jupiter Io

Jupiter Europa

Jupiter Ganymede

Jupiter Callisto

A few more Select examples based on the Customers, Salespeople and Orders sample tables follow, to show how complex queries may be written in SQL.

Select within a select

Example:
SELECT * FROM Orders WHERE Sid IN (SELECT Sid FROM Salespeople WHERE City = 'Helsinki');

Ordid Quantity Odate Cid Sid

3003 129 1998-12-23 2001 1004

3007 55 1999-01-07 2004 1001

Select with implicit table join

Example:
SELECT Customers.Cname, Salespeople.Sname, Salespeople.City FROM Salespeople, Customers WHERE Salespeople.City = Customers.City;

This query returns the names of Customers and Salespeople who work in the same city, as well as the name of the city.

Cname Sname City

Hanson Pelee Helsinki

Conrad Pelee Helsinki

Longman Sarmani Rome

Hanson Moss Helsinki

Conrad Moss Helsinki

Carlton Randall Paris

Peterson Anakin New York

Example:
SELECT Customers.Cname, Salespeople.Sname FROM Customers, Salespeople WHERE Salespeople.Sid = Customers.Sid;

This query returns the salesperson corresponding to each customer.

Cname Sname

Hanson Pelee

Conrad Pelee

Longman Sarmani

Gable Sarmani

Peterson Moss

Carlton Randall

Gerard Anakin

Select with ANY

Example:
SELECT * FROM Salespeople WHERE City = ANY (Select CITY FROM Customers) ORDER BY City;

Sid Sname City Commission

1001 Pelee Helsinki 0.11

1004 Moss Helsinki 0.13

1003 Anakin New York 0.12

1007 Randall Paris 0.09

1002 Sarmani Rome 0.12

Select with UNION
The following query returns the UNION of Salespeople who work in Helsinki and of Customers who dwell there.

Example:
SELECT Sid, Sname FROM Salespeople WHERE City = 'Helsinki' UNION SELECT Cid, Cname FROM Customers WHERE City = 'Helsinki';

1001 Pelee

1004 Moss

2001 Hanson

2006 Conrad

Notice that there are no headers in the example above, since the returned rows come from different tables.

Select for Update

The Select for Update statement selects rows from one or more tables in order to perform an update to the values of the rows returned.

Syntax:
Select_statement FOR UPDATE OF;

where
select_statement ::=
query_expression
[UNION[ALL] select_statement | TABLE table_name];

A detailed description of query_expression may be found in B.4.1.(Definition Grammar).

Example:
SELECT Distance_mmiles FROM planets WHERE Serial = 4 FOR UPDATE OF;

This statement sets the cursor to the selected row for a future update. It may not be used directly from an UPDATE statement.

Note also that when a Select for Update is executed, the selected rows are locked for update and they remain locked until the transaction is committed, while a simple Select statement locks the rows only when the Isolation Level is REPEATABLE READ or SERIALIZABLE. See also 13.1.2.(Transaction Atomicity).

Set Time Zone

The Set Time Zone statement defines a local time zone displacement.

When a session starts, the default time zone is the time zone of the server. (Note! Not the local time zone of the computer where the user works, but the local time zone of the computer where Ovrimos resides.)

If the default time zone is not convenient, users may modify their time zone with the SET TIME ZONE statement. This does not affect internal representation of times and timestamps. It only affects the way information will be displayed for the user.

Syntax:
SET TIME ZONE value | LOCAL;

Example:
SET TIME ZONE LOCAL;

The above statement sets the time zone to the local time zone of the server, not the client computer.

Example:
SET TIME ZONE '+02:00';

The above statement sets the time zone to +2 hours of UTC (GMT). Times are always kept in UTC internally, but they are returned as local times (according to the time zone of the session).

Example of time zone usage:
Suppose table Orders is altered (as shown in the Alter Table statement) and contains now a new column Ordtime. The time zone of the server is set +2 hours of UTC and a new order is inserted:

Example:
SET TIME ZONE '+02:00';

INSERT INTO Orders VALUES (3010, 40, DATE '1999-11-11', 2008, 1003, TIME '11:05:00');

This is local time 11:05:00, which corresponds to UTC 09:05:00, since the time zone was set to +2.

Selecting the new order from the table will return the following:

Example:
SELECT * FROM Orders WHERE Ordid = 3010;

Ordid Quantity Odate Cid Sid Ordtime

3010 40 1999-11-11 2008 1003 11:05:00

We may now set the local time to +10 from UTC.

Example:
SET TIME ZONE '+10:00';

The previous SELECT * FROM Orders WHERE Ordid = 3010;

statement will return the following:

Ordid Quantity Odate Cid Sid Ordtime

3010 40 1999-11-11 2008 1003 19:05:00

Of course, the internal representation of the time is not affected by either of the SET TIME ZONE statement. Internally, the time is UTC, i.e. 09:05:00.

The presentation of columns defined as TIMESTAMPS is similarly affected by the SET TIME ZONE statement.

Set Transaction

The Set Transaction statement sets the attributes of the next transaction. One attribute is the Isolation Level See 13.1.2.(Transaction Atomicity). The other attribute is the option to make updates to the database during a transaction.

Syntax:
SET TRANSACTION
{ISOLATION LEVEL
{READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE}
} | {READ ONLY|READ WRITE}

Example:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The next transaction will be performed on the READ COMMITTED level.

Update Positioned

The positioned Update statement is used to update the contents of the row where the cursor has been previously positioned.

Syntax:
UPDATE table name
SET {column_name = {value_expression | NULL}},...
WHERE CURRENT OF cursor name;

Example:
UPDATE planets SET Distance_mmile = 21000000 WHERE CURRENT OF cur1;

The Distance_mmile field of the row where cursor cur1 has been placed will be updated.

Update Searched

The searched Update statement is used to update the contents of the rows returned after a search by a defined predicate. If the predicate is used, then only the rows conforming to the predicate are updated, otherwise all rows of the table are affected.

Syntax:
UPDATE table_name
SET {column_name = {value_expression|NULL}},...
[WHERE predicate];

Example:
UPDATE Planets SET Distance_mmile = 21000000 WHERE Serial = 4;

Warning! Do not use the UPDATE statement to modify system tables, such as sys.users etc. It may prove detrimental to your database! Use the provided Update User instead.

Update User

The Update User statement modifies a database user's password and/or privileges.

Syntax:
UPDATE USER user_name
[WITH 'new password'|NULL]
[LIKE prototype user]
[USING 'attribute=value;attribute=value...'];

where
attribute::= REMARKS | MAILBOX

For explanation of the attributes remarks and mailbox, see the Create User statement in the same section.

Example:
UPDATE USER pandora WITH 'xyzzy';

UPDATE USER friend LIKE pandora;

The first statement changes the password of user pandora, while the second gives the privileges of user pandora to user friend. Of course, the second statement does not affect friend's password.

The following is the statement user admin has to execute to modify his/her initial password, which (in the beginning) is known to all.
UPDATE USER admin WITH 'new password';

Note: This is not a standard SQL statement.

Commit

: The Commit statement terminates a transaction, making the updates permanent to the database.
Syntax:
COMMIT [WORK];

Rollback

: The Rollback statement aborts the current transaction, undoing all updates.
Syntax:
ROLLBACK [WORK];

15.4.3 Aggregate Functions

Aggregate functions take a group of values in the SELECT or HAVING clause of a query or subquery and produce a single value. The group can be defined with a GROUP BY clause or by all the values produced by the query.

The SQL aggregate functions are described by:

COUNT(*)

all-function::={AVG|MAX|MIN| SUM} ([ALL] expression)

distinct-function::={AVG|COUNT|MAX|MIN|SUM}
(DISTINCT column-name)

Return values of the aggregate functions:

The result of COUNT(*) is an integer. COUNT(DISTINCT) is also integer.
NULL values found in the query are counted. When no rows are found complying to the subquery, COUNT returns 0.

MAX and MIN return results of the same type as their input columns. When no rows are found complying to the subquery, MAX and MIN return NULL.

AVG and SUM return values depending on their inputs:

On tinyint and smallint, they return integer.
On unsigned smallint and unsigned tinyint, they return unsigned int.
On decimal and numeric, they return decimal and numeric with precision 15.

For the rest of the input types the aggregate functions have the same types.

When no rows complying to the subquery are found, AVG and SUM return NULL.

15.5 Standard SQL Functions

The functions described below are Standard SQL2 functions.

CAST

Converts a scalar value to a specified scalar data type.

Syntax:
CAST ( scalar-expression AS data-type )

Example:
SELECT CAST (4 AS decimal(3,1)) FROM tempo

(where tempo is a temporary table) returns 4.0

Note: Some data types may not be converted to others.

CASE

Returns one of the specified sets of values, depending on the conditions.

Syntax:
CASE
when-clause-list
ELSE scalar-expression
END

where a 'when-clause' takes the form:
WHEN conditional-expression THEN scalar-expression

Example:
CASE WHEN value<100 THEN 'Cheap' WHEN value<200 THEN 'Medium' ELSE 'Expensive' END

Example:
SELECT user_name, CASE WHEN user_name='ADMIN' THEN 'Administrator' WHEN user_name='PUBLIC' THEN 'Public' ELSE 'Other' END FROM sys.users

This will return all the database users with the classification, 'Administrator', 'Public' or 'Other'.

Similarly, CASE may also be used as follows:

Syntax:
CASE column_name WHEN value THEN result
...
ELSE scalar-expression END
FROM table_name

Example:
SELECT user_name, CASE user_name WHEN 'ADMIN' THEN 'A!' WHEN 'PUBLIC' THEN 'P!' ELSE 'O!' END FROM sys.users

POSITION

Returns the position of a specified character string (string2) within another specified character string (string1). Each of string1 and string2 is specified as an arbitrary character string expression. More precisely, the expression:

POSITION (string2 IN string1)
is defined to return a value as follows:

If the CHARACTER_LENGTH of string2 is zero, the result is 1. Otherwise, if string2 occurs as a substring within string1, the result is one greater than the number of characters in string1 that precede the first such occurrence. Otherwise the result is zero.

Example:
SELECT POSITION ('ha' IN 'Alpha') FROM tempo

returns 4.

SUBSTRING

Extracts a substring of a string.

Example:
SELECT SUBSTRING ('Good Morning' FROM 3 FOR 4) FROM tempo

returns 'od M'.

In general, the string argument can be any character or bit string expression, and the FROM and FOR arguments can be any numeric expression. If FOR is omitted, all the right part from FROM is extracted.

TRIM

Returns a character string that is identical to a specified character string. Leading and/or trailing pad characters are removed.

Syntax:
TRIM (ltb pad FROM string)
ltb = LEADING, TRAILING or both. If omitted, option BOTH is assumed.

<pad> is a character string expression that evaluates to a single character. The <pad> character to be removed is specified. If the <pad> character is not specified, then space is assumed. If <ltb> and <pad> are omitted, FROM must also be omitted.

USER

Returns the user name.

Example:
INSERT INTO tempo VALUES (USER,...),

causes the user name to be inserted into a row in the table tempo.

NULLIF (expression1, expression2 )

The function relates two values. If they are the same, the condition is true and NULLIF returns the value NULL. Otherwise, the first of the two values are returned. The expression NULLIF (x, y) is defined to be equivalent to the expression:

CASE WHEN x = y THEN NULL ELSE x END

Example:
SELECT nullif (2+3, 4+1) FROM tempo

returns NULL,
while
SELECT nullif (4+2, 4+1) FROM tempo

returns 6

COALESCE(expression1, expression2, ...)

Function COALESCE receives a series of values. It traverses the list until it finds the first one that is not NULL. It then assumes the value. If no value other than NULL is found, then COALESCE assumes the NULL value.

The expression COALESCE (x, y)
is defined to be equivalent to the expression
CASE WHEN x is NOT NULL THEN x ELSE y END

In general, COALESCE (x,y, ..., z) returns NULL only if all its operands evaluate to NULL.

15.6 Supplemental SQL Functions

Ovrimos provides you with a variety of functions, that may be used in combination with the above described SQL statements. Some of these functions comply with ODBC requirements, while a few are implemented to allow BLOB insertion and retrieval.

These functions belong to the following general categories: Mathematical functions, Date and Time functions, String manipulation functions, BLOB reference manipulation functions and System functions.

A brief description of the function, together with the arguments and the return values, is provided below. Several functions are overloaded. The user may also note the underscore (_) in the beginning of some function names. This was used to differentiate functions such as DATE and TIME etc. from reserved SQL words.

All the above mentioned expressions may be literals, results of other scalar functions, names of columns etc.

15.6.1 Mathematical functions

For making the function examples below more solid, we assume there is a table tempo, with columns, angle (containing angles in radians) and number (containing numbers).

Table Tempo

Angle Number

ABS(numeric_expression)

Returns the absolute value of a numeric expression.

Example:
SELECT ABS(number) FROM tempo

returns 3.4 if the number is either 3.4 or -3.4.

ACOS(float_expression)

Returns the arccosine of float_expression as an angle, expressed in radians. If the argument is not within the domain range (-1, 1), then a DOMAIN ERROR message is returned.

Example:
SELECT ACOS (number) FROM tempo

on number 0, returns 1.5707963, i.e. $\pi{}/2$ .

ASIN(float_expression)

Returns the arcsine of float_expression as an angle, expressed in radians. If the argument is not within the domain range (-1, 1), then a DOMAIN ERROR message is returned.

Example:
SELECT ASIN (number) FROM tempo

on number 1, returns $\pi{}$ .

ATAN(float_expression)

Returns the arctangent of float_expression as an angle, expressed in radians.

Example:
SELECT ATAN(number) FROM tempo

on number 1, returns 0.785398, i.e. $\pi{}/4$ .

ATAN2(float_expression1, float_expression2)

Returns the arctangent of x and y coordinates, specified by float_expression1 and float_expression2, respectively, as an angle, expressed in radians. When both x and y coordinates are zero (0), a DOMAIN ERROR message is returned.

Example:
SELECT ATAN2 (Xcoord, Ycoord) FROM tabname

on Xcoord = 10 and Ycoord = 10 returns 0.785398. i.e $\pi{}/4$ .

CEILING(numeric_expression)

Returns the smallest integer greater than or equal to numeric_expression.

Example:
SELECT CEILING (number) FROM tempo

on number = 5.4 returns 6.

SELECT CEILING (number) FROM tempo

on number = -5.4 returns -5.

COS(float_expression)

Returns the cosine of float_expression, where float expression is an angle expressed in radians.

Example:
SELECT COS (angle) FROM tempo

on angle 0, returns 1.

COT(float_expression)

Returns the cotangent of float_expression, where float_expression is an angle expressed in radians. If the argument of COT is 0, then a DIVISION BY ZERO ERROR message is issued.

Example:
SELECT COT (angle) FROM tempo

on angle pi/4, returns 1.

DEGREES(numeric_expression)

Returns the number of degrees converted from numeric_expression to radians.

Example:
SELECT DEGREES (pi()) FROM tempo

returns 180.

EXP(float_expression)

Returns the exponential value of float_expression. In case of a result outside the computer's bounds, a BOUND ERROR message is returned.

Example:
SELECT EXP (number) FROM tempo

on number 4, returns 54.59815, i.e. e⁴.

FLOOR(numeric_expression)

Returns the largest integer less than or equal to numeric_expression.

Example:
SELECT FLOOR (number) FROM tempo

on number 3.4, returns 3, and on number -3.4, returns -4.

LOG(float_expression)

Returns the natural logarithm of float_expression. If the argument is negative, then a DOMAIN ERROR message is returned.

Example:
SELECT LOG (number) FROM tempo

on number e³, returns 3.

LOG10(float_expression)

Returns the base 10 logarithm of float_expression. If the argument is negative, then a DOMAIN ERROR message is returned.

Example:
SELECT LOG10 (number) FROM tempo

on number 1000, returns 3.

MOD(integer_expression1, integer_expression2)

Returns the remainder (modulus) of integer_expression1 divided by integer_expression2. If integer_expression2 is 0, then a DIVISION BY ZERO ERROR message is returned.

Example:
SELECT MOD (number,3) FROM temp

on number 20, returns 2.

PI()

Returns the constant value of $\pi{}$ as a floating-point value. The accuracy of the PI() function depends on the computer's accuracy.

Example:
UPDATE tempo SET angle = PI()

sets angle to 3.14159...

POWER(numeric_expression, integer_expression)

Returns the value of numeric_expression to the power of integer_expression. If numeric_expression is zero and integer_expression is less than zero, then a DOMAIN ERROR message is returned. In case of a result outside the computer's bounds, a BOUND ERROR message is returned.

Example:
SELECT POWER (2,8) FROM tempo

returns 256.

RADIANS(numeric_expression)

: Returns the number of radians converted from numeric_expression to degrees.
Example:
SELECT RADIANS (90) FROM tempo returns 1.5707963, i.e. $\pi{}/2$ .

RAND([integer_expression])

Returns a random floating point value, using integer_expression as the optional seed value.

Example:
SELECT RAND() FROM tempo

returns a random value.

ROUND(numeric_expression, integer_expression)

Returns numeric_expression rounded to integer_expression places right of the decimal point. If integer_expression is negative, numeric_expression is rounded |integer_expression| places to the left of the decimal point.

Example:
SELECT ROUND (number, 2) FROM tempo

on number 7.1278, returns 7.13.

SELECT ROUND (number, -2) FROM tempo

on number 7.1278, returns 0.

SIGN(numeric_expression)

Returns an indicator or the sign of numeric_expression. If numeric_expression is less than 0, -1 is returned. If numeric_expression equals 0, then 0 is returned. If numeric_expression is greater than 0, 1 is returned.

Example:
SELECT SIGN (number) FROM tempo

on number 10, returns 1

on number 0, returns 0

on number -10, returns -1

SIN(float_expression)

Returns the sine of float_expression, where float_expression is an angle expressed in radians.

Example:
SELECT SIN (angle) FROM tempo

on number $\pi{}/2$ , returns 1.

SQRT(float_expression)

Returns the square root of float_expression. If the argument is negative, then a DOMAIN ERROR message is returned. In case of a result outside the computer's bounds, a BOUND ERROR message is returned.

Example:
SELECT SQRT (number) FROM tempo

on number 16, returns 4.

TAN(float_expression)

Returns the tangent of float_expression, where float_expression is an angle expressed in radians.

Example:
SELECT TAN (angle) FROM tempo

on number $\pi{}/4$ , returns 1.

TRUNCATE(numeric_expression, integer_expression)

Returns numeric_expression truncated to integer_expression places right of the decimal point. If integer_expression is negative, numeric_expression is truncated to |integer_expression| places to the left of the decimal point.

Example:
SELECT TRUNCATE (number, 2) FROM tempo

on number 7.1278 returns 7.12.

SELECT TRUNCATE (number, -2) FROM tempo

on number 7.1278 returns 0.

IDENTITY(numeric_expression1, numeric_expression2)

Increments numeric_expression1, which is the seed by numeric_expression2, which is the increment.

Example:
CREATE TABLE A (I, int IDENTITY(10,5)

where I is numeric_expression, CHAR or VARCHAR.

IDENTITY(seed, increment)

increments the seed by increment each time it is called.

It is also possible to have IDENTITY with only one argument, A. In this case, IDENTITY returns the last value that was assigned to A.

15.6.2 Date and Time functions

For making the examples of time and date functions more solid, we assume a table tempo, with columns date_field, time_field and tstamp_field of types DATE, TIME and TIMESTAMP respectively.

The following time and date functions do not accept all the types required by ODBC. Specifically, CHAR and VARCHAR are not accepted by these functions.

EXTRACT(keyword FROM value)

Example:
EXTRACT (MINUTE FROM TIME '09:30:00')

returns 30.

_HOUR(time_expression)

Returns the hour in time_expression as an integer value in the range of 0-23.

Example:
SELECT _HOUR (time_field) FROM tempo

on time_field 22:17:21, returns 22.

SELECT _HOUR (TIME '21:18:50') FROM tempo

returns 21.

_MINUTE(time_expression)

Returns the minute in time_expression as an integer value in the range of 0-59.

Example:
SELECT _MINUTE (time_field) FROM tempo

on time_field 22:17:21, returns 17.

_MONTH(date_expression)

Returns the month in date_expression as an integer value in the range of 1-12.

Example:
SELECT _MONTH (date_field) FROM tempo

on date_field 1998-10-12, returns 10.

SELECT _MONTH (DATE '1999-02-15') FROM tempo

returns 2.

_SECOND(time_expression)

Returns the second in time_expression as an integer value in the range of 0-59.

Example:
SELECT _SECOND (time_field) FROM tempo

on time_field 22:17:21, returns 21.

_YEAR(date_expression)

Returns the year in date_expression as an integer value. The range is data source dependent.

Example:
SELECT _YEAR (date_field) FROM tempo

on date_field 1998-10-12, returns 1998.

CURDATE()

Returns the time when the statement has begun as a date value.

If a statement began at 10:20 am on October 15th 1998, then

Example:
SELECT CURDATE () FROM tempo

returns 1998-10-15.

CURTIME()

Returns the local time when the statement has begun as a time value.

If a statement began at 10:20 am on October 15th 1998, then

Example:
SELECT CURTIME () FROM tempo

returns 10:20:00.

CURTIMESTAMP()

Returns the time where the statement has begun as a timestamp value.

If a statement began at 10:20 am on October 15th 1998, then

Example:
SELECT CURTIMESTAMP () FROM tempo

returns 1998-10-12 10:20:00.

DAYNAME(date_expression)

Returns a character string, containing the name of the day (in the Sun through Sat form) for the day portion of date_expression.

Example:
SELECT DAYNAME (DATE '1999-10-12') FROM tempo

returns Mon.

DAYOFMONTH(date_expression)

: Returns the day of the month in date_expression as an integer value in the range of 1-31.
Example:
SELECT DAYOFMONTH (DATE '1998-10-12') FROM tempo returns 12.

DAYOFWEEK(date_expression)

Returns the day of the week in date_expression as an integer value in the range of 1-7, where 1 represents Sunday.

Example:
SELECT DAYOFWEEK (date_field) FROM tempo

on date_field 1998-10-12, returns 2 (since the 12th of October of 1998 was a Monday).

DAYOFYEAR(date_expression)

Returns the day of the year in date_expression as an integer value in the range of 1-366.

Example:
SELECT DAYOFYEAR (DATE '1998-10-12') FROM tempo

returns 285.

MONTHNAME(date_expression)

Returns a character string containing the name of the month (in the Jan through Dec form) for the month portion of the date_expression.

Example:
SELECT MONTHNAME (DATE '1998-10-12') FROM tempo

returns Oct.

NOW()

Returns the current date and time as a timestamp value.

Example:
SELECT NOW() FROM tempo

on December 2, 1998 at 09:30:22,
returns 1998-12-02 09:30:22.

QUARTER(date_expression)

Returns the quarter in date expression as an integer value in the range of 1-4, where 1 represents January 1 through March 31.

Example:
SELECT QUARTER (date_field) FROM tempo

on date_field 1998-10-12, returns 4.

TIMESTAMPADD(interval, integer_expression,
timestamp_expression)

Returns the timestamp calculated by adding integer_expression intervals of type interval to timestamp_expression. Valid values of interval are the following keywords:
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

Example:
SELECT TIMESTAMPADD(SQL_TSI_HOUR, 10, timestamp '1999-12-31 16:00:00') FROM tempo

returns 2000-01-01 02:00:00

TIMESTAMPDIFF(interval, timestamp_expression1,
timestamp_expression2)

Returns the integer number of intervals of type interval by which timestamp_expression2 is greater than timestamp_expression1. Valid values of interval are the following keywords:
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

Example:
SELECT TIMESTAMPDIFF(SQL_TSI_DAY, timestamp '1999-12-01 14:30:00', timestamp '1998-10-05 20:10:00') FROM tempo

returns 421 days.

Note! Some caution is required here, because there is a known minor bug when months are compared, because a time difference of 30 days is considered as a month.

WEEK(date_expression)

Returns the week of the year in date_expression as an integer value in the range of 1-53.

Example:
SELECT WEEK (date_field) FROM tempo

on date_field 1999-06-11, returns 23.

15.6.3 String manipulation functions

For making the function examples below more solid, we assume there is a table tempo with column name defined as a string of characters.

Conversion Note! You should keep in mind that string_expressions are either ascii_expressions or unicode_expressions. Whenever a function requires more than one argument, an attempt of conversion from one to another is made under certain conditions, before the operation is performed.

Only Unicode characters corresponding to 7-bit ASCII characters may be correctly converted to ASCII. Only 7-bit ASCII character (most significant bit 0) may be converted to an equivalent Unicode.

There can be no conversion between ascii_expression and binary_expression or between unicode_expression and binary_expression.

ASCII(string_expression)

If the string is ASCII, it returns the ASCII code value of the leftmost character of string_expression as an integer. If the string is a binary_expression, then the ASCII code value of the first byte is returned. If the string is a unicode_expression, then the Unicode value of the leftmost character string is returned.

Example:
SELECT ASCII (name) FROM tempo

on name 'Alpha', returns 65.

CHAR_LENGTH(string_expression)

Returns the number of characters in string_expression.

Example:
SELECT CHAR_LENGTH (name) FROM tempo

on name 'Good', returns 4.

CHARACTER_LENGTH(string_expression)

Returns the number of characters in string_expression.

Example:
SELECT CHARACTER_LENGTH (name) FROM tempo

on name 'Good', returns 4.

CONCAT(string_expression, string_expression)

Returns a character string that is the result of concatenating string_expression2 to string_expression1. See the note in the beginning of this section for conversion conditions.

Example:
UPDATE tempo SET name = CONCAT ('Alpha',' and Beta')

sets name to 'Alpha and Beta'.

Concatenation Operator

Besides CONCAT, one may also use the concatenation operator, ||.

Example:
INSERT INTO tempo VALUES ('X'|| 'Y')

will insert the string 'XY'.

LCASE(string_expression)

Converts all upper case characters in string expression to lower case. Applies only to ascii_expressions.

Example:
SELECT LCASE (name) FROM tempo

on name 'ZERO', returns 'zero'.

LENGTH(string_expression)

Returns the number of characters in string_expression, excluding trailing blanks.

Example:
Select LENGTH (name) from tempo

on name 'Good', returns 4.

LOCATE(string_expression1, string_expression2 [, start])

Returns the starting position of the first occurrence of string_expression1 within string_expression2. The search for the first occurrence of string_expression1 begins with the first character position in string_expression2 unless the optional argument start is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_expression2 is indicated by the value 1. If string_expression1 is not found within string_expression2, the value 0 is returned. See the note in the beginning of this section for conversion conditions.

Example:
SELECT LOCATE ('ha','Alpha') FROM tempo

returns 4.

LOCATE applies also to BLOBs.

LOWER(string_expression)

Converts all upper case characters in string_expression to lower case. Applies only to ascii_expressions.

Example:
SELECT LOWER (name) FROM tempo

on name 'ZERO', returns 'zero'.

LTRIM(string_expression)

Returns the characters of string_expression, with leading blanks removed.

Example:
SELECT LTRIM (name) FROM tempo

on name ' XX', returns 'XX'

_CHAR(integer_expression)

Returns the corresponding ASCII character to integer_expression. The integer_expression must be in the range 0 - 255.

Example:
SELECT _CHAR (66) FROM tempo

returns 'B'.

REPLACE(string_expression1, string_expression2,
string_expression3)

Replaces all occurrences of string_expression2 in string_expression1 with string_expression3. See the note in the beginning of this section for conversion conditions.

Example:
SELECT REPLACE ('Alpha', 'Alph', 'Bet') FROM tempo

returns 'Beta'.

RTRIM(string_expression)

Returns the characters of string_expression with trailing blanks removed.

Example:
SELECT RTRIM (name) FROM tempo

on name 'Good ', returns 'Good'.

_INSERT(string_expression1, start, length, string_expression2)

: Returns a character string, where length characters have been deleted from string_expression1, beginning at start and where string_expression2 has been inserted into string_expression1, beginning at start. See the note in the beginning of this section for conversion conditions. Suppose column name contains 'Good Morning', then
Example:
UPDATE tempo SET name = _INSERT (name, 6, 3, 'Eve') returns 'Good Evening'.

_LEFT(string_expression, count)

Returns the leftmost count of characters of string_expression.

Example:
SELECT _LEFT (name, 6) FROM tempo

on name 'Good Morning', returns 'Good M'.

_RIGHT(string_expression, count)

Returns the rightmost count of characters of string_expression.

Example:
SELECT _RIGHT (name, 7) FROM tempo

on name 'Good Morning' returns 'Morning'.

_SUBSTRING(string_expression, start, length)

Returns a character string that is derived from string_expression, beginning at the character position specified by start for length characters.

Example:
SELECT _SUBSTRING (name, 6, 7) FROM tempo

on name 'Good Morning Friend', returns 'Morning'.

_SUBSTRING applies also to BLOBs.

UCASE(string_expression)

Converts all lower case characters in string_expression to upper case. Applies only to ascii_expressions.

Example:
SELECT UCASE (name) FROM tempo

on name 'Alpha', returns 'ALPHA'.

UPPER(string_expression)

Converts all lower case characters in string_expression to upper case. Applies only to ascii_expressions.

Example:
SELECT UPPER (name) FROM tempo

on name 'Alpha', returns 'ALPHA'.

15.7 System functions

DATABASE()

Returns the name of the database you work with.

Example:
SELECT DATABASE() from planets

returns the name of the database, e.g. testbase.

IFNUL(expression, value)

If expression is NULL, value is returned. If expression is not NULL, expression is returned. The possible data type(s) of value must be compatible with the data type of expression.

Example:
SELECT IFNUL (cast(null as char(2)), 'xx',4) FROM tempo

returns 'xx'.

SELECT IFNUL(10, 3) FROM tempo

returns 10.

15.8 Regular Expression functions

REGEXP(string, pattern [,position])

Locates the regular expression pattern into the string string.

string can be any of CHARACTER, VARCHAR, or LONG VARCHAR (BLOB).

pattern can be either CHARACTER or VARCHAR.

Optionally, function REGEXP accepts argument position to indicate the position within the string from which the search will be start.

REGEXP is case sensitive.

REGEXPICASE(expression, pattern [,position])

Locates the regular expression pattern into the string string, regardless of case.

string can be any of CHARACTER, VARCHAR, or LONG VARCHAR (BLOB).

pattern can be either CHARACTER or VARCHAR.

Optionally, function REGEXPICASE accepts argument position to indicate the position within the string from which the search will be start.

REGEXP is not case sensitive.

REGEXPCOUNT(expression, pattern [,position])

Returns the number of times, pattern is found into string string. The function returns 0 if pattern is not located into string.

string can be any of CHARACTER, VARCHAR, or LONG VARCHAR (BLOB).

pattern can be either CHARACTER or VARCHAR.

Optionally, function REGEXPCOUNT accepts argument position to indicate the position within the string from which the search will be start.

REGEXPCOUNT is case sensitive.

REGEXPCOUNTICASE(expression, pattern [,potition])

Returns the number of times, pattern is found into string string, regardless of case. The function returns 0 if pattern is not located into string.

string can be any of CHARACTER, VARCHAR, or LONG VARCHAR (BLOB).

pattern can be either CHARACTER or VARCHAR.

Optionally, function REGEXPCOUNTICASE accepts argument position to indicate the position within the string from which the search will be start.

REGEXPCOUNT is case sensitive.

subsection*Notes on Regular Expressions

The following text is provided as it was written by Henry Spencer and his library of functions was used for the implementation of the Regular Expressions functions.

DESCRIPTION
Regular expressions (``RE''s), as defined in POSIX 1003.2, come in two forms: modern REs (roughly those of egrep; 1003.2 calls these ``extended'' REs) and obsolete REs (roughly those of ed; 1003.2 ``basic'' REs). Obsolete REs mostly exist for backward compatibility in some old programs; they will be discussed at the end. 1003.2 leaves some aspects of RE syntax and semantics open; `' marks decisions on these aspects that may not be fully portable to other 1003.2 implementations.

A (modern) RE is one or more non-empty branches, separated by |. It matches anything that matches one of the branches.

A branch is one or more pieces, concatenated. It matches a match for the first, followed by a match for the second, etc.

A piece is an atom possibly followed by a single `*', `+', `?', or bound. An atom followed by `*' matches a sequence of 0 or more matches of the atom. An atom followed by `+' matches a sequence of 1 or more matches of the atom. An atom followed by `?' matches a sequence of 0 or 1 matches of the atom.

A bound is { followed by an unsigned decimal integer, possibly followed by `,' possibly followed by another unsigned decimal integer, always followed by }. The integers must lie between 0 and RE_DUP_MAX (255) inclusive, and if there are two of them, the first may not exceed the second. An atom followed by a bound containing one integer i and no comma matches a sequence of exactly i matches of the atom. An atom followed by a bound containing one integer i and a comma matches a sequence of i or more matches of the atom. An atom followed by a bound containing two integers i and j matches a sequence of i through j (inclusive) matches of the atom.

An atom is a regular expression enclosed in `()' (matching a match for the regular expression), an empty set of `()' (matching the null string), a bracket expression (see below), `.' (matching any single character), `' (matching the null string at the beginning of a line), `$' (matching the null string at the end of a line), a ` $\backslash$ ' followed by one of the characters ^.[$()|*+?} (matching that character taken as an ordinary character), a `followed by any other character (matching that character taken as an ordinary character, as if the `had not been present), or a single character with no other significance (matching that character). A `

Next: 16. Ovrimos SQL Up: Ovrimos version 3.0 On-line Previous: 14. Ovrimos Files and

Name	Planet	Small_pic	Big_pic
Moon	Earth	image/gif	image/jpeg
Phobos	Mars	image/gif	image/jpeg
Deimos	Mars	image/gif	image/jpeg
Callisto	Jupiter	image/gif	image/jpeg
Ganymede	Jupiter	image/gif	image/jpeg
Io	Jupiter	image/gif	image/jpeg
Europa	Jupiter	image/gif	image/jpeg
Enceladus	Saturn	image/gif	image/jpeg
Dione	Saturn	image/gif	image/jpeg
Titan	Saturn	image/gif	image/jpeg
Tethys	Saturn	image/gif	image/jpeg
Rhea	Saturn	image/gif	image/jpeg
Mimas	Saturn	image/gif	image/jpeg
Oberon	Uranus	image/gif	image/jpeg
Titania	Uranus	image/gif	image/jpeg
Umbriel	Uranus	image/gif	image/jpeg
Triton	Neptune	image/gif	image/jpeg
Charon	Pluto	image/gif	image/jpeg

Cid	Cname	City	Rating	Sid
2001	Hanson	Helsinki	100	1001
2002	Gerard	Athens	200	1003
2003	Longman	Rome	200	1002
2004	Gable	London	300	1002
2006	Conrad	Helsinki	100	1001
2007	Carlton	Paris	300	1007
2008	Peterson	New York	100	1004

Sid	Sname	City	Commission
1001	Pelee	Helsinki	0.11
1002	Sarmani	Rome	0.12
1004	Moss	Helsinki	0.13
1007	Randall	Paris	0.09
1003	Anakin	New York	0.12

Ordid	Quantity	Odate	Cid	Sid
3001	25	1998-12-22	2008	1007
3002	38	1998-12-23	2007	1001
3003	129	1998-12-23	2001	1004
3004	72	1999-01-04	2004	1002
3005	18	1999-01-04	2002	1007
3006	31	1999-01-05	2006	1003
3007	55	1999-01-07	2004	1001
3008	62	1999-01-07	2003	1002

ISBN	TITLE	AUTHOR	PRICE	COVER
999-4444-22-1	50 ways to feed a cat	D. H. Hackleberry	12.50	image/gif
999-4434-21-1	Ancient Egypt	D. H. Hackleberry	35.00	image/gif
989-2444-22-9	What I saw in Congo	Apple J. Pie	34.00	image/gif
499-4144-52-8	Teach yourself Cobol in 21 days	Egbert H. Cailloux	34.50	image/gif
499-6447-12-4	Surfing USA	D. Boy	45.00	image/gif