5. SQL 92
5.1. SQL 92 Primer
Table of Contents
As mentioned earlier, a catalog can contain many SCHEMAs. A SCHEMA is owned by a user and only this user can add or drop objects in the SCHEMA. A SCHEMA object can be a table, a view, a domain, etc. By means of the GRANT/REVOKE statements, other users can be granted a number of privileges pertaining to the objects within a schema, e.g. the INSERT privilege on a table.
Objects in a SCHEMA can be referenced via so-called qualified names:
[[<catalog name>.] <schema name>.] <object name>
SQL 92 offers a rich "default for everything" setup, so whenever you want to reference objects in the current SCHEMA, only the
<object name>needs to be given.
When a new database is created, two SCHEMAs are created as well: DEFINITION_SCHEMA and INFORMATION_SCHEMA. The DEFINITION_SCHEMA holds all the objects used to maintain all other SCHEMAs. The INFORMATION_SCHEMA holds various objects, which offer access to the objects in the DEFINITION_SCHEMA, and a number of convenience objects. For example if you want to see which TABLEs have been defined in a database, the following SQL 92 statement could be executed:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
INFORMATION_SCHEMA.TABLES is actually a VIEW defined like:
CREATE VIEW INFORMATION_SCHEMA.TABLES AS
SELECT * FROM DEFINITION_SCHEMA.TABLES;
The VIEWs in INFORMATION_SCHEMA are all non-updateable, i.e. an INSERT like:
INSERT INTO INFORMATION_SCHEMA.TABLES ....
The DEFINTION_SCHEMA is maintained exclusively by FrontBase and cannot be accessed or manipulated directly by any user.
To create a new SCHEMA (which the current user will then own):
CREATE SCHEMA <schema name>;
-- See the SQL 92 standard for the complete
-- syntax of which the example is only but a
-- tiny fragment.
To make a schema the current schema:
SET SCHEMA '<schema name>';
-- note that the schema name is given using a character string
To see the list of defined SCHEMAs:
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
To see what is the current schema, the CURRENT_SCHEMA string function is available. Please note that CURRENT_SCHEMA is a FrontBase extension to SQL 92.
If you have feedback or questions on this document, please send e-mail to firstname.lastname@example.org. Please reference the section number and topic. Thanks!!
©2000 FrontBase, Inc. All rights reserved.