Copyright © "2000" Distributed Management Task Force, Inc. (DMTF). All rights reserved.
DMTF is a not-for-profit association of industry members dedicated to promoting enterprise and systems management and interoperability. DMTF specifications and documents may be reproduced for uses consistent with this purpose by members and non-members, provided that correct attribution is given. As DMTF specifications may be revised from time to time, the particular version and release cited should always be noted."
WBEM Query Language (Draft)
14 June, 2000
Abstract
The DMTF Common Information Model (CIM) is an approach to the management of systems and networks that applies the basic structuring and conceptualization techniques of the object-oriented paradigm. The approach uses a uniform modeling formalism that-together with the basic repertoire of object-oriented constructs-supports the cooperative development of an object-oriented schema across multiple organizations.
This document describes the query language available for manipulating data expressed through a CIM compliant system.
Change History
Change History |
Version 2.00 - 30 April 2000, result of splitting the original document into two parts one for level 1&2 and another (this document) for level 3&4 - this document repeats all the material in level 1&2 document. |
Version 2.01 - 31 May 2000, reflecting comments received from Andrea Westerinen of Cisco. |
Editor
Patrick Thompson
Microsoft Corporation
For the DMTF WQL Working Group
WBEM will provide a query mechanism that WBEM clients can use to specify criteria for selecting a set of objects. Query definitions allow a WBEM client to be very specific in the nature and the number of objects that are selected, resulting in a WBEM managed environment placing less needs on the network infrastructure. The precise mechanics for delivering query requests and receiving query results are specified as a part of the WBEM XML based protocol.
WBEM SQL is a subset of ANSI SQL 92 and was chosen as a query language because of the wide range of tools and environments that support it. WBEM supports an object model (i.e. CIM), not a relational model, and as such it is necessary, as a part of the query language specification, to specify a mapping between the structures of the WBEM CIM object model and the structures of the relational model. This document describes the SQL constructs supported and the structure mapping between the models. Note this document does not explain SQL constructs such as INNER or OUTER JOIN - there are numerous books and articles available from which such an explanation can be had.
WBEM servers implement a Query Engine to parse the query and evaluate its results. The server must parse the query because even if the query is executed by some other process acting as a data provider for the server, the server must be able to understand the query sufficiently to be able to determine where it should be processed. WBEM defines several levels of WBEM SQL implementation, depending on the complexity of the syntax and semantics. These levels of implementation encourage WBEM Server implementations, especially on simple or resource-sensitive installations, to support a query interpreter that best suits the needs of WBEM clients while also taking the capabilities of the server into account.
The language is a subset of SQL and will support queries in the form
SELECT <row set>
FROM <table list>
WHERE <selection expression>
In which a <row set> is a list of table columns, qualified if necessary, related to the tables specified in the FROM clause. A row set item may be a column name, qualified column (required if the name would otherwise be ambiguous) or the special character “*”, meaning all the columns in the table, again qualified if the use would otherwise be ambiguous.
The <table list> is a list of table names. All tables must be joined either by a JOIN clause in the FROM clause or by a join condition in the WHERE clause. In the FROM clause it is possible to specify INNER JOIN or LEFT or RIGHT outer joins. For example:
SELECT Qeg_System.Name, Qeg_Device.Caption
FROM Qeg_Device RIGHT JOIN (Qeg_System LEFT JOIN Qeg_SubComponent ON
Qeg_System.[__Path] = Qeg_SubComponent.Group) ON Qeg_Device.[__Path] =
Qeg_SubComponent.Part
WHERE Qeg_System.Name="Wenna Panga";
The <selection expression> of the WHERE clause is limited to simple variable - constant or single table variable comparison with the exception of joins where primary key - foreign key comparisons are allowed (see the Object Model Mapping section below).
Specific limitations on SQL are detailed in the following sections.
NULL and LIKE are the only supported predicates. Common SQL predicates such as BETWEEN, IN, EXISTS, UNIQUE, SOME, ANY, ALL are not supported.
Standard DDL constructs such as CREATE TABLE are not supported.
Declaration of constraint, assertion or referential integrity is not supported through the query language Equivalents may be supported by the object model.
SQL style security specification is not supported through the query language. GRANT and REVOKE are not supported.
It will not be possible to identify properties by qualifier. For example it will not be possible to include just the key properties of a class in a select clause (for example SELECT keys FROM ..). This would violate the “well-defined table” rule. It should be noted; with respect to key retrieval that retrieval of __Path (see the mapping proposal) will retrieve all key values as a part of the path expression.
Insert, Update and Delete are supported against any table that represents a non-abstract class. See the discussion of object mapping for a detailed description of effects of the various update statements (section 4 Object Model Mapping). The behavior of Insert, Update and Delete statements should be the same as an equivalent batch of statements delivered using the CIM XML protocol. The effects of the update should be the same and where possible the status values returned should be the same.
This verb creates a new instance of a class. The instance (as determined by its key) must not already exist, or the operation fails.
Example:
insert into Qeg_System (__Path,
Name, Manufacturer, Location)
values ("QegSystem.Name='Bosop Wenna'", "Bosop Wenna",
"Mompara Inc", "Gutama")
This verb updates an existing instance. The instance (as determined by its key) must already exist, or the operation fails.
For example, the following query would update the version property of the device driver object identified by its Id property ("2" in this case):
update Qec_System set Manufacturer = "Picanini Kia Inc" where Name = "Bosop Wenna"
Semantically, it is possible to update a wide range of instances if the "where" clause is general enough. If the "where" clause were absent in the above example, the update will be performed on all instances of the class.
The delete verb deletes the set of instances implied by the contents of the "where" clause. The following deletes the instance of the device driver whose Id property is set to 2:
delete from Qeg_System where Name = "Bosop Wenna"
The delete operation is powerful and can have a large number of objects against which it is applied. Therefore, caution is required in specifying such an operation. It would be possible to delete all instances of a class by simply executing the query:
delete from Qeg_System
The language is specified as a series of subsets the smallest being the WBEMSQL1 language, WBEMSQL2, WBEMSQL3 and WBEMSQL4 are successively larger subsets, WBEMSQL4 being the most extensive.
WBEMSQL1 supports simple select operations without joins
WBEMSQL2 Insert, Update and Delete
WBEMSQL3 supports complex expressions
WBEMSQL4 supports joins
Each level of capability of WBEM SQL includes syntax supported at the previous levels, i.e., a WBEMSQL1 query can be submitted to a query engine which syntactically supports WBEMSQL2, but not the reverse.
Two types of relationship, namely inheritance and association, dominate the object model. Mapping each class to a table containing columns corresponding to all the single-valued properties of the class, both inherited and immediate represents inheritance. In the model, associations are classes. Array properties get mapped to separate tables. A table represents each association in the same way as a normal class. Associations contain references and the references contain path values, any joins involving references must be with the __Path column provide by the table mapping.
The meta model represents the classes, properties and other constructs of the CIM. Every object has a name and can have some number of qualifiers as characteristics. Qualifiers themselves may not have characteristics. Classes represent a type and a set of instances. A class can have a superclass. The class - superclass relationship implies the instances of the subclass are a subset of the instances of the superclass. The class subclass relationship implies inheritance as a consequence of the subset relationship. Given a class A with property pa, any instance of a subclass of A must also be an instance of A and therefore support the property pa. Associations are types of classes. Any class may be an Association. Any subclass of an Association is an Association. Properties have a type that can be a primitive type (such as number, string or Boolean). A Property may be a reference in which case it has a user-defined class as its type. References are used to construct relationships between objects. Properties may be arrays. Classes have a set of properties and methods associated with them that together constitute the type of the class. Methods and properties may have an override relationship to an inherited property in which case the property or method of the subclass is substituted for the property or method of the superclass if the given instance is a member of the subclass. Indications are a type of class and are produced as a by-product of a trigger. The various schema elements are declared within the scope of a schema. For a complete discussion of these topics please see the CIM Specification Version 2.3.
The elements of the model are mapped as follows:
Model Element |
Maps to |
Comments |
Class |
Table |
Every class, both abstract and leaf most class, will be mapped to a separate table |
Association |
Table |
Association and class mappings are identical. |
Property |
Column |
Every single-valued property becomes a separate column |
Reference |
Column |
The value of the column is the fully qualified path of the reference target |
__PATH |
Column |
The value of the column is the fully qualified path of the relevant object. |
__SUPERCLASS |
Column |
A table column containing the name of the superclass this class is a subclass of. |
__CLASS |
Column |
A table column containing the name of the leafmost class this instance participates in. |
Array Property |
Table |
The name of the table is the concatenation of the class name and the property name separated by an underscore ("_"). The table has three columns, OwnerPath, Value, Index. The OwnerPath is a copy of the __Path mapping property value for the instance that owns the data value. |
Inherited property |
Column |
Every inherited property becomes a separate column. This means that property sets will be repeated across all tables representing descendents of a given class. |
Namespace |
Database |
Each namespace is mapped to a separate database. Queries that span multiple namespaces will have to be formulated using distributed query execution capabilities available in SQL engines. |
The mapping convention documented above requires a single object to be potentially mapped to many rows, one for each class in the inheritance graph of the object’s creation class. An object must be inserted in the table representing its creation class.
Delete can be applied to any table in which the object is visible and will have the effect of removing the object from all tables that represent aspects of the inheritance graph that instantiates the object.
Update can modify properties in any table the instance participates in. The value the property is modified to should be reflected in the rows in other tables that represent the same instance. Insert must be applied to the table that represents the leafmost class in the inheritance tree. For example given a schema contain classes A, B, C, and D in which B is a subclass of A, C is a subclass of B and D is a subclass of C. To establish an instance of the class C a row should be created in the C table. The corresponding rows in the A and B tables should be created automatically.
Given the schema:
[abstract]
class Qeg_Component
{
STRING Caption;
STRING Description;
STRING Status;
STRING Name;
};
[abstract]
class Qeg_LogicalComponent:Qeg_Component
{
};
class Qeg_Setting
{
[key: DisableOverride
ToSubclass]
STRING ComponentName;
};
[association]
class Qeg_Dependency
{
[key: DisableOverride
ToSubclass]
Qeg_Component ref Antecedent;
[key:DisableOverride
ToSubclass]
Qeg_Component ref Dependent;
};
[aggregation, association]
class Qeg_SubComponent
{
[aggregate, key:
DisableOverride ToSubclass]
Qeg_Component ref Group;
[key: DisableOverride
ToSubclass]
Qeg_Component ref Part;
};
[association]
class Qeg_ComponentSettings
{
[key: DisableOverride
ToInstance ToSubclass]
Qeg_Component ref Element;
[key: DisableOverride
ToSubclass]
Qeg_Setting ref Setting;
};
Note this and all other schema fragments defined in the rest of this document are for illustration purposes only and should not be taken as representing some specific version of the CIM schema.
Figure 1
The path for configurations applicable to a given CIM_ManagedSystemElement are given by the following query:
SELECT Qeg_Setting.[__Path]
FROM Qeg_Component LEFT JOIN (Qeg_Setting RIGHT JOIN Qeg_ComponentSettings ON
Qeg_Setting.[__Path] = Qeg_ComponentSettings.Setting) ON Qeg_Component.[__Path]
= Qeg_ComponentSettings.Element
WHERE Qeg_Component.Name="Funazonki";
Graphically the join tree can be represented as follows:
Note that the relationship, strictly speaking, exists between Qeg_LogicalComponent and Qeg_Setting, not Qeg_Component and Qeg_Setting. The join is possible as a result of the presence of the foreign keys. However the query language does not enforce the constraint implied by the association declaration (i.e. that only CIM_LogicalComponent instances can have an association to to CIM_Setting instances). The constraint does not effect the expression or behavior of the query other than by the possibility that the query may fail to return a result.
The protocol specification defines functions that allow for the expression of association traversal without requiring a full SQL implementation. Association traversal necessarily involves JOIN operations that are only required in CIM SQL level 3. This is essential to allow for small implementations of the language. However it is also essential, at least as a part of the protocol, to be able to express association traversal. Accordingly the following functions are provided as a part of the protocol specification and documented here as they overlap in functionality with the JOIN capabilities in CIM SQL 3. (see section 2.4.14. Associators from the DMTF IMP document) . This operation is used to enumerate CIM Objects (Classes or Instances) that are associated to a particular source CIM Object.
Associators
<objectWithPath>[] Associators (
[IN] <objectName> ObjectName,
[IN,OPTIONAL] string AssocClass,
[IN,OPTIONAL] string ResultClass,
[IN,OPTIONAL] string Role,
[IN,OPTIONAL] string ResultRole
)
The ObjectName input parameter defines the source CIM Object whose associated Objects are to be returned. This may be either a Class name or Instance name (model path).
In which Request identifies a particular type of request and Parameter is a value the type and interpretation of which varies by request type. The ObjectPath may be either an instance or a class. The Requests and associated Parameters are described in the following table:
Request |
Parameter |
|
AssocClass |
Association Class Name |
Return any instance associated with the object by the given association |
ResultClass |
Class Name |
Return any instance associated with the object where the instance is an instance of the given class |
ResultRole |
Property Name |
Return any instance associated with the object where the instance plays the given role |
Role |
PropertyName |
Return any instance associated with the object where the source object plays the given role |
The AssociatorsOf function supports association traversal where the object of interest is the target of the association. The ReferencesOf function supports association traversal where the object of interest is the association not the target class ( see section 2.4.16 References of the IMP specification)
References
<objectWithPath>[] References (
[IN] <objectName> ObjectName,
[IN,OPTIONAL] string ResultClass,
[IN,OPTIONAL] string Role
)
Request |
Parameter |
|
|
ResultClass |
Class Name |
Return any association that contains a reference that references the given object and which refers to the given class |
|
Role |
Property Name |
Return any association which refers to the given using an reference with the given name |
|
The following diagram defines the schema fragment used in illustrating the language (the schema prefix is intended for illustration purposes only):
Figure 2
No system properties are shown as a part of the model. The corresponding table definitions are given below.
Table Qeg_Component |
Table Qeg_Disk |
Table Qeg_StorageDevice |
This section provides a number of sample queries to illustrate the use of the Query language.
Get the Name and Caption for all Qeg_StorageDevices.
SELECT Qeg_StorageDevice.Name,
Qeg_StorageDevice.Caption
FROM Qeg_StorageDevice;
This is a very simple retrieval query and will result in a standard SQL tabular result.
Name |
Caption |
A: |
Local floppy drive |
C: |
Local fixed disk |
D: |
Remote drive |
F: |
Remote drive |
G: |
Remote drive |
H: |
Remote drive |
I: |
Remote drive |
Select Devices that have a Status not equal to “OK”.
SELECT *
FROM Qeg_Device
WHERE Qeg_Device.Status<>"OK";
Get all StorageDevice and ActualStorageDevice instances. Note that two different types of instances are being returned.
SELECT *
FROM Qeg_Device
WHERE __CLASS="Qeg_Disk" OR __CLASS="Qeg_StorageDevice";
Get all Device instances. Device is an abstract class so each instance returned may be of a different type.
SELECT *
FROM Qeg_ Device;
List the Systems and any objects dependent on the system using the Dependency association.
SELECT Qeg_System.Name, Qeg_Component.Name
FROM (Qeg_System LEFT JOIN Qeg_Dependency ON Qeg_System.[__Path] =
Qeg_Dependency.Antecedent) LEFT JOIN Qeg_Component ON Qeg_Dependency.Dependent
= Qeg_Component.[__Path];
Figure 3 Dependency Query
Although the query may appear somewhat complex the expression of the query,
even in such a simple query design tool as Microsoft Access, is not complex at
all. This is illustrated in Figure 3.
List the Systems and their component devices
SELECT Qeg_System.Name,
Qeg_Device.*
FROM (Qeg_System LEFT JOIN Qeg_SubComponent ON Qeg_System.[__Path] =
Qeg_SubComponent.Group) LEFT JOIN Qeg_Device ON Qeg_SubComponent.Part =
Qeg_Device.[__Path];
Both of these queries involve simple inner joins. For example in the system components query any System that has no Component Devices will not be returned in the query result.
LogicalComponent Settings
SELECT Qeg_LogicalComponent.Name,
Qeg_Setting.*
FROM Qeg_LogicalComponent LEFT JOIN (Qeg_Setting RIGHT JOIN
Qeg_ComponentSettings ON Qeg_Setting.[__Path] = Qeg_ComponentSettings.Setting)
ON Qeg_LogicalComponent.[__Path] = Qeg_ComponentSettings.Component;
This, again, involves a simple join. The Select clause in this case includes a column representing the name of the relevant LogicalComponent and the properties of the related configuration.
System Component Dependencies
SELECT Qeg_System.Name, Qeg_Component.Name, Dependent_Component.Name
FROM (((Qeg_System LEFT JOIN Qeg_SubComponent ON Qeg_System.[__Path] =
Qeg_SubComponent.Group) LEFT JOIN Qeg_Component ON Qeg_SubComponent.Part =
Qeg_Component.[__Path]) LEFT JOIN Qeg_Dependency ON Qeg_Component.[__Path] =
Qeg_Dependency.Dependent) LEFT JOIN Qeg_Component AS Dependent_Component ON
Qeg_Dependency.Antecedent = Dependent_Component.[__Path];
The tables that follow this section represent various subsets of SQL 92. The SQL 92 standard is specified in a grammar that consists of approximately 600 productions. The grammar is very complex and there are regions of the syntax tree that are over 40 levels deep, making the tree very difficult to understand. The BNF productions have been kept as close as possible to the form used in the SQL 92 standard. Each of the sublanguages, as far as possible is defined using the same production names and sequence as the language it is derived from.
The WBEMSQL4 subset consists of four root productions <SQL terminal character>, <token>, <query specification> and <SQL data change statement>. The productions that appear in the table are only those that can be traced from these three root productions all others have been excluded. In addition, for the productions that have been included, various items have been omitted mainly on the grounds of estimated complexity (attempting to keep the language and implementation as simple as possible) and in some cases redundancy.
The main language features supported are the SQL SELECT command, and insert, update and delete statements. The query capability includes joins but does not include subqueries.
The supported syntax for queries is.
<query specification> ::= SELECT [<set quantifier>]<select list><table expression>
The table expression includes the where, group by and having clauses.
<table expression> ::= <from clause>[<where clause>] [<group by clause>] [<having clause>]
The data change statements does not include the positioned versions of the statements. These are used for cursor manipulation - cursors are not included in WBEM SQL.
<SQL data change statement> ::= <delete statement: searched>|<insert statement>|<update statement: searched>
<delete statement: searched> ::= DELETE FROM<table name>[ WHERE<search condition>]
<insert statement> ::= INSERT INTO<table name><insert columns and source>
<update statement: searched> ::= UPDATE<table name>SET<set clause list>[ WHERE<search condition>]
All the forms of the joins clause are supported the syntax being:
<qualified join> ::= <table reference>[ NATURAL ] [<join type>] JOIN<table reference>[<join specification>]
<join type> ::= INNER|<outer join type>[ OUTER ]|UNION
In all cases it is assumed that security is defined in terms of the underlying data source. That is it must not be possible to access more data or perform actions that would not be permitted to the same user through the underlying instrumentation or data access interfaces. WBEM at this point (version 2.3) has no mechanism for the specification of security, any security introduced is outside of the WBEM standard and must be expressed in other terms. For example the <general value specification> provides access to USER, CURRENT_USER, SESSION_USER, and SYSTEM_USER. It is assumed that these values would not be returned if the requestor would not have access to them by other means.
In WBEMSQL4 the date and time functions are applicable to CIM properties of type datetime. In WBEMSQL3 and below, these functions are not supported and datetime properties should be dealt with as strings.
The following is a partial list of the constructs that have been excluded:
<delete statement: positioned> ::= DELETE FROM<table name>WHERE CURRENT OF<cursor name>
<update statement: positioned> ::= UPDATE<table name>SET<set clause list>WHERE CURRENT OF<cursor name>
The positioned versions of the delete and update statements are not included as cursors are not included.
<module authorization clause> ::= SCHEMA<schema name>| AUTHORIZATION<module authorization identifier>| SCHEMA<schema name>AUTHORIZATION<module authorization identifier>
None of the schema manipulation features are included, so none of the authorization features are included. Security is expected to be described and applied through the CIM model.
<temporary table declaration> ::= DECLARE LOCAL TEMPORARY TABLE<qualified local table name><table element list>[ ON COMMIT ( PRESERVE| DELETE ) ROWS ]
Temporary tables are not included on the grounds that schema definition is not included and temporary tables are really a form of schema definition.
<preparable statement> ::= <preparable SQL data statement>| <preparable SQL schema statement>| <preparable SQL transaction statement>| <preparable SQL session statement>| <preparable implementation-defined statement>
<embedded SQL host program> ::= <embedded SQL Ada program>| <embedded SQL C program>| <embedded SQL COBOL program>| <embedded SQL Fortran program>| <embedded SQL MUMPS program>| <embedded SQL Pascal program>| <embedded SQL PL/I program>
<embedded SQL declare section> ::= <embedded SQL begin declare>[<embedded character set declaration>] [<host variable definition>... ]<embedded SQL end declare>| <embedded SQL MUMPS declare>
<embedded SQL MUMPS declare> ::= <SQL prefix>BEGIN DECLARE SECTION [<embedded character set declaration>] [<host variable definition>... ] END DECLARE SECTION<SQL terminator>
<embedded SQL statement> ::= <SQL prefix><statement or declaration>[<SQL terminator>]
None of the embedded program features are supported.
<statement or declaration> ::= <declare cursor>| <dynamic declare cursor>| <temporary table declaration>| <embedded exception declaration>| <SQL procedure statement>
No cursor related features are included as these are mainly related to embedded SQL scenarios.
<directly executable statement> ::= <direct SQL data statement>| <SQL schema statement>| <SQL transaction statement>| <SQL connection statement>| <SQL session statement>| <direct implementation-defined statement>
Elements of the <direct SQL data statement> are supported (see the exclusions that follow) None of the other directly executable statements are supported.
<SQL object identifier> ::= <SQL provenance><SQL variant>
<SQL variant> ::= <SQL edition><SQL conformance>
The language identifier and conformance related features are not included.
In SQL a <value expression primary> includes <unsigned value specification>, <column reference> and <set function specification>, these are all included. The elements of this production that are excluded are <scalar subquery>, <case expression> and <cast specification>. The exclusion of the <scalar subquery> is particularly significant as it eliminates much of the complexity, but also much of the power of the language.
The exclusion of the schema declaration portions of SQL, excludes such things as
<column constraint> ::= NOT NULL| <unique specification>| <references specification>| <check constraint definition>
<default clause> ::= DEFAULT<default option>
<data type> ::= <character string type>[ CHARACTER SET<character set specification>]| <national character string type>| <bit string type>| <numeric type>| <datetime type>| <interval type>
<table constraint> ::= <unique constraint definition>| <referential constraint definition>| <check constraint definition>
SQL support is defined at four levels, level 4 being the most complete, level 3, 2 and 1 being smaller subsets suitable for implementation on platforms where memory or other computing resources are constrained in some way.
The WBEMSQL4 language includes most of the major features of SQL apart from subqueries, schema manipulation and the use of cursors and embedded language statements. The representation and manipulation of blobs is also excluded in this level of the language. All productions are either defined by terminating literals in the table or by other material in this document (notably the various reserved words). The exception is this document does not define the production labeled <character set name>. The productions that define the language are as follows:
<nondelimiter token> |
::= |
<regular identifier>| <key word>| <unsigned numeric literal>| <national character string literal>| <bit string literal>| <hex string literal> |
<identifier body> |
::= |
<identifier start>[ (<underscore>| <identifier part>)... ] |
<underscore> |
::= |
_ |
<digit> |
::= |
0| 1| 2| 3| 4| 5| 6| 7| 8| 9 |
<unsigned numeric literal> |
::= |
<exact numeric literal>| <approximate numeric literal> |
<unsigned integer> |
::= |
<digit>... |
<approximate numeric literal> |
::= |
<mantissa>E<exponent> |
<exponent> |
::= |
<signed integer> |
<sign> |
::= |
<plus sign>| <minus sign> |
<minus sign> |
::= |
- |
<quote> |
::= |
' |
<comment> |
::= |
<comment introducer>[<comment character>... ]<newline> |
<comment character> |
::= |
<nonquote character>| <quote> |
<newline> |
::= |
!!<EMPHASIS>(implementation-defined end-of-line indicator) |
<character representation> |
::= |
<nonquote character>| <quote symbol> |
<quote symbol> |
::= |
<quote><quote> |
<bit> |
::= |
0| 1 |
<hexit> |
::= |
<digit>| A| B| C| D| E| F| a| b| c| d| e| f |
<delimiter token> |
::= |
<character string literal>| <date string>| <time string>| <timestamp string>| <interval string>| <delimited identifier>| <SQL special character>| <not equals operator>| <greater than or equals operator>| <less than or equals operator>| <concatenation operator>| <left bracket>| <right bracket> |
<character string literal> |
::= |
[<introducer><character set specification>]<quote>[<character representation>... ]<quote>[ (<separator>...<quote>[<character representation>... ]<quote>)... ] |
<character set specification> |
::= |
<standard character repertoire name>| <standard universal character form-of-use name> |
<date string> |
::= |
<quote><date value><quote> |
<years value> |
::= |
<datetime value> |
<months value> |
::= |
<datetime value> |
<time string> |
::= |
<quote><time value>[<time zone interval>]<quote> |
<hours value> |
::= |
<datetime value> |
<minutes value> |
::= |
<datetime value> |
<seconds integer value> |
::= |
<unsigned integer> |
<time zone interval> |
::= |
<sign><hours value><colon><minutes value> |
<interval string> |
::= |
<quote>(<year-month literal>| <day-time literal>)<quote> |
<day-time literal> |
::= |
<day-time interval>| <time interval> |
<delimited identifier> |
::= |
<double quote><delimited identifier body><double quote> |
<delimited identifier body> |
::= |
<delimited identifier part>... |
<nondoublequote character> |
::= |
<EMPHASIS>(!! See the Syntax Rules) |
<percent> |
::= |
% |
<left paren> |
::= |
( |
<asterisk> |
::= |
* |
<solidus> |
::= |
/ |
<less than operator> |
::= |
< |
<greater than operator> |
::= |
> |
<vertical bar> |
::= |
| |
<greater than or equals operator> |
::= |
>= |
<concatenation operator> |
::= |
| | |
<right bracket> |
::= |
] |
<delete statement: searched> |
::= |
DELETE FROM<table name>[ WHERE<search condition>] |
<qualified name> |
::= |
[<schema name><period>]<qualified identifier> |
<catalog name> |
::= |
<identifier> |
<actual identifier> |
::= |
<regular identifier>| <delimited identifier> |
<qualified identifier> |
::= |
<identifier> |
<boolean term> |
::= |
<boolean factor>| <boolean term>AND<boolean factor> |
<boolean test> |
::= |
<boolean primary>[ IS [ NOT ]<truth value>] |
<predicate> |
::= |
<comparison predicate>| <null predicate> | <like predicate> |
<like predicate> |
::= |
<match value>[ NOT ] LIKE<pattern>[ ESCAPE<escape character>] |
<pattern> |
::= |
<character value expression> |
<row value constructor> |
::= |
<row value constructor element>| <left paren><row value constructor list><right paren> |
<value expression> |
::= |
<numeric value expression>| <string value expression>| <datetime value expression>| <interval value expression> |
<term> |
::= |
<factor>| <term><asterisk><factor>| <term><solidus><factor> |
<numeric primary> |
::= |
<value expression primary>| <numeric value function> |
<unsigned value specification> |
::= |
<unsigned literal>| <general value specification> |
<datetime literal> |
::= |
<date literal>| <time literal>| <timestamp literal> |
<time literal> |
::= |
TIME<time string> |
<interval literal> |
::= |
INTERVAL [<sign>]<interval string><interval qualifier> |
<start field> |
::= |
<non-second datetime field>[<left paren><interval leading field precision><right paren>] |
<general value specification> |
::= |
USER| CURRENT_USER| SESSION_USER| SYSTEM_USER |
<qualifier> |
::= |
<table name> |
<set function specification> |
::= |
COUNT<left paren><asterisk><right paren>| <general set function> |
<set function type> |
::= |
AVG| MAX| MIN| SUM| COUNT |
<numeric value function> |
::= |
<position expression>| <extract expression>| <length expression> |
<extract expression> |
::= |
EXTRACT <left paren> <extract field> FROM <extract source> <right paren> |
<extract field> |
::= |
<datetime field> | <time zone field> |
<datetime field> |
::= |
<non-second datetime field> | SECOND |
<time zone field> |
::= |
TIMEZONE_HOUR | TIMEZONE_MINUTE |
<extract source> |
::= |
<datetime value expression> | <interval value expression> |
<position expression> |
::= |
<string position expression> |
<string position expression> |
::= |
POSITION <left paren> <string value expression> IN <string value expression> <right paren> |
<char length expression> |
::= |
( CHAR_LENGTH| CHARACTER_LENGTH )<left paren><string value expression><right paren> |
<character value expression> |
::= |
<concatenation>| <character factor> |
<character factor> |
::= |
<character primary> |
<string value function> |
::= |
<character value function>| <bit value function> |
<character substring function> |
::= |
SUBSTRING<left paren><character value expression>FROM<start position>[ FOR<string length>]<right paren> |
<string length> |
::= |
<numeric value expression> |
<bit substring function> |
::= |
SUBSTRING<left paren><bit value expression>FROM<start position>[ FOR<string length>]<right paren> |
<bit concatenation> |
::= |
<bit value expression> <concatenation operator> <bit factor> |
<bit factor> |
::= |
<bit primary> |
<bit primary> |
::= |
<value expression primary> | <string value function> |
<bit length expression> |
::= |
BIT_LENGTH<left paren><string value expression><right paren> |
<datetime value expression> |
::= |
<datetime term>| <interval value expression><plus sign><datetime term>| <datetime value expression><plus sign><interval term>| <datetime value expression><minus sign><interval term> |
<datetime factor> |
::= |
<datetime primary>[<time zone>] |
<datetime value function> |
::= |
<current date value function>| <current time value function>| <current timestamp value function> |
<current time value function> |
::= |
CURRENT_TIME [<left paren><time precision><right paren>] |
<current timestamp value function> |
::= |
CURRENT_TIMESTAMP [<left paren><timestamp precision><right paren>] |
<time zone> |
::= |
AT<time zone specifier> |
<interval factor> |
::= |
[<sign>]<interval primary> |
<interval term 2> |
::= |
<interval term> |
<interval term 1> |
::= |
<interval term> |
<default specification> |
::= |
DEFAULT |
<null predicate> |
::= |
<row value constructor>IS [ NOT ] NULL |
<insert statement> |
::= |
INSERT INTO<table name><insert columns and source> |
<insert column list> |
::= |
<column name list> |
<query expression> |
::= |
<non-join query expression>| <joined table> |
<non-join query expression> |
::= |
<non-join query term>| <query expression>UNION [ ALL ] [<corresponding spec>]<query term>| <query expression>EXCEPT [ ALL ] [<corresponding spec>]<query term> |
<non-join query primary> |
::= |
<simple table>| <left paren><non-join query expression><right paren> |
<query specification> |
::= |
SELECT [<set quantifier>]<select list><table expression> |
<select sublist> |
::= |
<derived column>| <qualifier><period><asterisk> |
<as clause> |
::= |
[ AS ]<column name> |
<from clause> |
::= |
FROM<table reference>[ (<comma><table reference>)... ] |
<table reference> |
::= |
<table name>[ [ AS ]<correlation name>[<left paren><derived column list><right paren>] ]| <joined table> |
<derived column list> |
::= |
<column name list> |
<correlation name> |
::= |
<identifier> |
<cross join> |
::= |
<table reference>CROSS JOIN<table reference> |
<join type> |
::= |
INNER| <outer join type>[ OUTER ]| UNION |
<join specification> |
::= |
<join condition>| <named columns join> |
<named columns join> |
::= |
USING<left paren><join column list><right paren> |
<where clause> |
::= |
WHERE<search condition> |
<grouping column reference list> |
::= |
<grouping column reference>[ (<comma><grouping column reference>)... ] |
<collate clause> |
::= |
COLLATE <collation name> |
<collation name> |
::= |
<regular identifier> |
<table value constructor> |
::= |
VALUES<table value constructor list> |
<explicit table> |
::= |
TABLE<table name> |
<corresponding spec> |
::= |
CORRESPONDING [ BY<left paren><corresponding column list><right paren>] |
<query primary> |
::= |
<non-join query primary>| <joined table> |
<set clause list> |
::= |
<set clause>[ (<comma><set clause>)... ] |
<object column> |
::= |
<column name> |
<SQL terminal character> |
::= |
<SQL language character> |
<simple Latin letter> |
::= |
<simple Latin upper case letter>| <simple Latin lower case letter> |
<simple Latin lower case letter> |
::= |
a| b| c| d| e| f| g| h| i| j| k| l| m| n| o| p| q| r| s| t| u| v| w| x| y| z |
<order by clause> |
::= |
ORDER BY<sort specification list> |
<sort specification> |
::= |
<sort key> [<ordering specification>] |
<ordering specification> |
::= |
ASC| DESC |
The WBEMSQL3, WBEMSQL2 and WBEMSQL1 all share the following definitions:
<token> |
::= |
<nondelimiter token>| <delimiter token> |
<nondelimiter token> |
::= |
<regular identifier>| <key word>| <unsigned numeric literal>|<bit string literal>| <hex string literal> |
<regular identifier> |
::= |
<identifier body> |
<identifier body> |
::= |
<identifier start>[ (<underscore>| <identifier part>)... ] |
<identifier start> |
::= |
<EMPHASIS>(!! See the Syntax Rules) |
<underscore> |
::= |
_ |
<identifier part> |
::= |
<identifier start>| <digit> |
<key word> |
::= |
<reserved word>| <non-reserved word> |
<unsigned numeric literal> |
::= |
<exact numeric literal>| <approximate numeric literal> |
<exact numeric literal> |
::= |
<unsigned integer>[<period>[<unsigned integer>] ]| <period><unsigned integer> |
<unsigned integer> |
::= |
<digit>... |
<digit> |
::= |
0| 1| 2| 3| 4| 5| 6| 7| 8| 9 |
<period> |
::= |
. |
<approximate numeric literal> |
::= |
<mantissa>E<exponent> |
<mantissa> |
::= |
<exact numeric literal> |
<exponent> |
::= |
<signed integer> |
<signed integer> |
::= |
[<sign>]<unsigned integer> |
<sign> |
::= |
<plus sign>| <minus sign> |
<plus sign> |
::= |
+ |
<minus sign> |
::= |
- |
<quote> |
::= |
' |
<separator> |
::= |
(<comment>| <space>| <newline>)... |
<comment> |
::= |
<comment introducer>[<comment character>... ]<newline> |
<comment introducer> |
::= |
<minus sign><minus sign>[<minus sign>...] |
<comment character> |
::= |
<nonquote character>| <quote> |
<nonquote character> |
::= |
!!<EMPHASIS>(See the Syntax Rules.) |
<space> |
::= |
!!<EMPHASIS>(space character in character set in use) |
<newline> |
::= |
!!<EMPHASIS>(implementation-defined end-of-line indicator) |
<character representation> |
::= |
<nonquote character>| <quote symbol> |
<bit string literal> |
::= |
B<quote>[<bit>... ]<quote>[ (<separator>...<quote>[<bit>... ]<quote>)... ] |
<bit> |
::= |
0| 1 |
<hex string literal> |
::= |
X<quote>[<hexit>... ]<quote>[ (<separator>...<quote>[<hexit>... ]<quote>)... ] |
<hexit> |
::= |
<digit>| A| B| C| D| E| F| a| b| c| d| e| f |
<delimiter token> |
::= |
<character string literal>| <SQL special character>| <not equals operator>| <greater than or equals operator>| <less than or equals operator>| <concatenation operator>| <double period>| <left bracket>| <right bracket> |
<character string literal> |
::= |
<quote>[<character representation>... ]<quote>[ (<separator>...<quote>[<character representation>... ]<quote>)... ] |
<quote symbol> |
::= |
"" |
<SQL special character> |
::= |
<space>| <double quote>| <percent>| <ampersand>| <quote>| <left paren>| <right paren>| <asterisk>| <plus sign>| <comma>| <minus sign>| <period>| <solidus>| <colon>| <semicolon>| <less than operator>| <equals operator>| <greater than operator>| <question mark>| <vertical bar> |
<double quote> |
::= |
" |
<percent> |
::= |
% |
<ampersand> |
::= |
& |
<left paren> |
::= |
( |
<right paren> |
::= |
) |
<asterisk> |
::= |
* |
<comma> |
::= |
, |
<solidus> |
::= |
/ |
<colon> |
::= |
: |
<semicolon> |
::= |
; |
<less than operator> |
::= |
< |
<equals operator> |
::= |
= |
<greater than operator> |
::= |
> |
<question mark> |
::= |
? |
<vertical bar> |
::= |
| |
<not equals operator> |
::= |
<> |
<greater than or equals operator> |
::= |
>= |
<less than or equals operator> |
::= |
<= |
<concatenation operator> |
::= |
| | |
<double period> |
::= |
.. |
<left bracket> |
::= |
[ |
<right bracket> |
::= |
] |
<SQL terminal character> |
::= |
<SQL language character> |
<SQL language character> |
::= |
<simple Latin letter>| <digit>| <SQL special character> |
<simple Latin letter> |
::= |
<simple Latin upper case letter>| <simple Latin lower case letter> |
<simple Latin upper case letter> |
::= |
A| B| C| D| E| F| G| H| I| J| K| L| M| N| O| P| Q| R| S| T| U| V| W| X| Y| Z |
<simple Latin lower case letter> |
::= |
a| b| c| d| e| f| g| h| i| j| k| l| m| n| o| p| q| r| s| t| u| v| w| x| y| z |
The following are the productions specific to WBEMSQL3. This subset differs from WBEMSQL4 mainly in the elimination of language features such as joins, and the GROUP BY and HAVING clauses.
<query specification> |
::= |
SELECT <select list><table expression> |
<select list> |
::= |
<asterisk>| <select sublist>[ (<comma><select sublist>)... ] |
<select sublist> |
::= |
<derived column> |
<derived column> |
::= |
<column name> [<as clause>] |
<table expression> |
::= |
<from clause>[<where clause>] |
<where clause> |
::= |
WHERE<search condition> |
<from clause> |
::= |
FROM<table name> |
<column name> |
::= |
<regular identifier> |
<table name> |
::= |
<regular identifier> |
<SQL data change statement> |
::= |
<delete statement: searched>| <insert statement>| <update statement: searched> |
<delete statement: searched> |
::= |
DELETE FROM<table name>[ WHERE<search condition>] |
<insert statement> |
::= |
INSERT INTO<table name><insert columns and source> |
<insert columns and source> |
::= |
<left paren><insert column list><right paren><insert sublist>| DEFAULT VALUES |
<insert column list> |
::= |
<column name list> |
<column name list> |
::= |
<column name>[ (<comma><column name>)... ] |
<insert sublist> |
::= |
<general literal>|<insert sublist>[ (<comma><insert sublist>)... |
<update statement: searched> |
::= |
UPDATE<table name>SET<set clause list>[ WHERE<search condition>] |
<set clause list> |
::= |
<set clause>[ (<comma><set clause>)... ] |
<set clause> |
::= |
<object column><equals operator><update source> |
<object column> |
::= |
<column name> |
<update source> |
::= |
<general literal>| <null specification>| DEFAULT |
<value expression> |
::= |
<numeric value expression>| <string value expression> |
<numeric value expression> |
::= |
<term>| <numeric value expression><plus sign><term>| <numeric value expression><minus sign><term> |
<term> |
::= |
<factor>| <term><asterisk><factor>| <term><solidus><factor> |
<factor> |
::= |
[<sign>]<numeric primary> |
<numeric primary> |
::= |
<value expression primary>| <numeric value function> |
<value expression primary> |
::= |
<unsigned value specification>| <column reference>| <left paren><value expression><right paren> |
<unsigned value specification> |
::= |
<unsigned literal> |
<unsigned literal> |
::= |
<unsigned numeric literal>| <general literal> |
<general literal> |
::= |
<character string literal>| <bit string literal>| <hex string literal> |
<column reference> |
::= |
<column name> |
<as clause> |
::= |
[ AS ]<column name> |
<search condition> |
::= |
<boolean term>| <search condition>OR<boolean term> |
<boolean term> |
::= |
<boolean factor>| <boolean term>AND<boolean factor> |
<boolean factor> |
::= |
[ NOT ]<boolean test> |
<boolean test> |
::= |
<boolean primary>[ IS [ NOT ]<truth value>] |
<boolean primary> |
::= |
<predicate>| <left paren><search condition><right paren> |
<predicate> |
::= |
<comparison predicate>| <null predicate> | <like predicate> |
<like predicate> |
::= |
<match value>[ NOT ] LIKE<pattern>[ ESCAPE<escape character>] |
<match value> |
::= |
<character value expression> |
<pattern> |
::= |
<character value expression> |
<escape character> |
::= |
<character value expression> |
<comparison predicate> |
::= |
<row value constructor><comp op><row value constructor> |
<row value constructor> |
::= |
<row value constructor element>| <left paren><row value constructor list><right paren> |
<row value constructor element> |
::= |
<value expression>| <null specification>| <default specification> |
<null specification> |
::= |
NULL |
<row value constructor list> |
::= |
<row value constructor element>[ (<comma><row value constructor element>)... ] |
<comp op> |
::= |
<equals operator>| <not equals operator>| <less than operator>| <greater than operator>| <less than or equals operator>| <greater than or equals operator> |
<null predicate> |
::= |
<row value constructor>IS [ NOT ] NULL |
<truth value> |
::= |
TRUE| FALSE| UNKNOWN |
<numeric value function> |
::= |
<position expression>| <extract expression>| <length expression> |
<position expression> |
::= |
<string position expression> |
<string position expression> |
::= |
POSITION <left paren> <string value expression> IN <string value expression> <right paren> |
<length expression> |
::= |
<char length expression>| <octet length expression>| <bit length expression> |
<char length expression> |
::= |
( CHAR_LENGTH| CHARACTER_LENGTH )<left paren><string value expression><right paren> |
<string value expression> |
::= |
<character value expression>| <bit value expression> |
<character value expression> |
::= |
<concatenation>| <character factor> |
<concatenation> |
::= |
<character value expression><concatenation operator><character factor> |
<character factor> |
::= |
<character primary> |
<character primary> |
::= |
<value expression primary>| <string value function> |
<string value function> |
::= |
<character value function>| <bit value function> |
<character value function> |
::= |
<character substring function> |
<character substring function> |
::= |
SUBSTRING<left paren><character value expression>FROM<start position>[ FOR<string length>]<right paren> |
<start position> |
::= |
<numeric value expression> |
<string length> |
::= |
<numeric value expression> |
<numeric value expression> |
::= |
[sign] <unsigned numeric literal> |
<bit value function> |
::= |
<bit substring function> |
<bit substring function> |
::= |
SUBSTRING<left paren><bit value expression>FROM<start position>[ FOR<string length>]<right paren> |
<bit value expression> |
::= |
<bit concatenation>| <bit factor> |
<bit concatenation> |
::= |
<bit value expression> <concatenation operator> <bit factor> |
<bit factor> |
::= |
<bit primary> |
<bit primary> |
::= |
<value expression primary> | <string value function> |
<octet length expression> |
::= |
OCTET_LENGTH<left paren><string value expression><right paren> |
<bit length expression> |
::= |
BIT_LENGTH<left paren><string value expression><right paren> |
<extract expression> |
::= |
EXTRACT <left paren> <extract field> FROM <extract source> <right paren> |
<extract field> |
::= |
<datetime field> | <time zone field> |
<datetime field> |
::= |
<non-second datetime field> | SECOND |
<time zone field> |
::= |
TIMEZONE_HOUR | TIMEZONE_MINUTE |
<extract source> |
::= |
<datetime value expression> | <interval value expression> |
<datetime value expression> |
::= |
<datetime term>| <interval value expression><plus sign><datetime term>| <datetime value expression><plus sign><interval term>| <datetime value expression><minus sign><interval term> |
<datetime term> |
::= |
<datetime factor> |
<datetime factor> |
::= |
<datetime primary>[<time zone>] |
<datetime primary> |
::= |
<value expression primary>| <datetime value function> |
<datetime value function> |
::= |
<current date value function>| <current time value function>| <current timestamp value function> |
<current date value function> |
::= |
CURRENT_DATE |
<current time value function> |
::= |
CURRENT_TIME [<left paren><time precision><right paren>] |
<time precision> |
::= |
<time fractional seconds precision> |
<current timestamp value function> |
::= |
CURRENT_TIMESTAMP [<left paren><timestamp precision><right paren>] |
<timestamp precision> |
::= |
<time fractional seconds precision> |
<time fractional seconds precision> |
::= |
<unsigned integer> |
<time zone> |
::= |
AT<time zone specifier> |
<time zone specifier> |
::= |
LOCAL| TIME ZONE<interval value expression> |
<interval value expression> |
::= |
<interval term>| <interval value expression 1><plus sign><interval term 1>| <interval value expression 1><minus sign><interval term 1>| <left paren><datetime value expression><minus sign><datetime term><right paren><interval qualifier> |
<interval term> |
::= |
<interval factor>| <interval term 2><asterisk><factor>| <interval term 2><solidus><factor>| <term><asterisk><interval factor> |
<interval factor> |
::= |
[<sign>]<interval primary> |
<interval primary> |
::= |
<value expression primary>[<interval qualifier>] |
<interval term 2> |
::= |
<interval term> |
<interval value expression 1> |
::= |
<interval value expression> |
<interval term 1> |
::= |
<interval term> |
<default specification> |
::= |
DEFAULT |
<interval qualifier> |
::= |
<start field> TO <end field> | <single datetime field> |
<start field> |
::= |
<non-second datetime field> [ <left paren> <interval leading field precision> <right paren> ] |
<non-second datetime field> |
::= |
YEAR | MONTH | DAY | HOUR | MINUTE |
<interval leading field precision> |
::= |
<unsigned integer> |
<end field> |
::= |
<non-second datetime field> | SECOND [ <left paren> <interval fractional seconds precision> <right paren> ] |
<interval fractional seconds precision> |
::= |
<unsigned integer> |
In this level of the standard expressions used in the SELECT WHERE clause are reduced to simple variable constant comparisons and updates are limited to simple literal assignments to variables. SQL2 consists of SQL1 plus the following productions.
<query specification> |
::= |
SELECT <select list><table expression> |
<select list> |
::= |
<asterisk>| <select sublist>[ (<comma><select sublist>)... ] |
<select sublist> |
::= |
<derived column> |
<derived column> |
::= |
<column name> |
<table expression> |
::= |
<from clause>[<where clause>] |
<from clause> |
::= |
FROM<table name> |
<where clause> |
::= |
WHERE<search condition> |
<search condition> |
::= |
<boolean term>| <search condition>OR<boolean term> |
<boolean term> |
::= |
<boolean factor>| <boolean term>AND<Boolean factor> |
<boolean factor> |
::= |
[ NOT ]<boolean test> |
<boolean test> |
::= |
<boolean primary>[ IS [ NOT ]<truth value>] |
<boolean primary> |
::= |
<predicate>| <left paren><search condition><right paren> |
<predicate> |
::= |
<comparison predicate>| <null predicate> |
<comparison predicate> |
::= |
<row value constructor><comp op><row value constructor> |
<comp op> |
::= |
<equals operator>| <not equals operator>| <less than operator>| <greater than operator>| <less than or equals operator>| <greater than or equals operator> |
<null predicate> |
::= |
<row value constructor>IS [ NOT ] NULL |
<truth value> |
::= |
TRUE| FALSE |
<column name> |
::= |
<regular identifier> |
<table name> |
::= |
<regular identifier> |
<row value constructor> |
::= |
<column name>|<numeric value expression>| <string value expression>| <truth value> |
<numeric value expression> |
::= |
[sign] <unsigned numeric literal> |
<string value expression> |
|
<character string literal>|<hex string literal>|<bit string literal> |
<SQL data change statement> |
::= |
<delete statement: searched>| <insert statement>| <update statement: searched> |
<delete statement: searched> |
::= |
DELETE FROM<table name>[ WHERE<search condition>] |
<insert statement> |
::= |
INSERT INTO<table name><insert columns and source> |
<insert columns and source> |
::= |
<left paren><insert column list><right paren><insert sublist>| DEFAULT VALUES |
<insert column list> |
::= |
<column name list> |
<column name list> |
::= |
<column name>[ (<comma><column name>)... ] |
<insert sublist> |
::= |
<general literal>|<insert sublist>[ (<comma><insert sublist>)... |
<general literal> |
::= |
<numeric value expression>| <string value expression>| <truth value> |
<update statement: searched> |
::= |
UPDATE<table name>SET<set clause list>[ WHERE<search condition>] |
<set clause list> |
::= |
<set clause>[ (<comma><set clause>)... ] |
<set clause> |
::= |
<object column><equals operator><update source> |
<object column> |
::= |
<column name> |
<update source> |
::= |
<general literal>| <null specification>| DEFAULT |
<null specification> |
::= |
NULL |
This level of the query language allows simple retrieval of information from single tables. Specific columns may be selected using the SELECT clause, specific rows may be selected using the WHERE clause. Joins and updates are not supported.
<query specification> |
::= |
SELECT <select list><table expression> |
<select list> |
::= |
<asterisk>| <select sublist>[ (<comma><select sublist>)... ] |
<select sublist> |
::= |
<derived column> |
<derived column> |
::= |
<column name> |
<table expression> |
::= |
<from clause>[<where clause>] |
<from clause> |
::= |
FROM<table name> |
<where clause> |
::= |
WHERE<search condition> |
<search condition> |
::= |
<boolean term>| <search condition>OR<boolean term> |
<boolean term> |
::= |
<boolean factor>| <boolean term>AND<Boolean factor> |
<boolean factor> |
::= |
[ NOT ]<boolean test> |
<boolean test> |
::= |
<boolean primary>[ IS [ NOT ]<truth value>] |
<boolean primary> |
::= |
<predicate>| <left paren><search condition><right paren> |
<predicate> |
::= |
<comparison predicate>| <null predicate> |
<comparison predicate> |
::= |
<row value constructor><comp op><row value constructor> |
<comp op> |
::= |
<equals operator>| <not equals operator>| <less than operator>| <greater than operator>| <less than or equals operator>| <greater than or equals operator> |
<null predicate> |
::= |
<row value constructor>IS [ NOT ] NULL |
<truth value> |
::= |
TRUE| FALSE |
<column name> |
::= |
<regular identifier> |
<table name> |
::= |
<regular identifier> |
<row value constructor> |
::= |
<column name>|<numeric value expression>| <string value expression>| <truth value> |
<numeric value expression> |
::= |
[sign] <unsigned numeric literal> |
<string value expression> |
|
<character string literal>|<hex string literal>|<bit string literal> |
<reserved word>
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 |
CONNECT |
CONNECTION |
CONSTRAINT |
CONSTRAINTS |
CONTINUE |
CONVERT |
CORRESPONDING |
COUNT |
CREATE |
CROSS |
CURRENT |
CURRENT_DATE |
CURRENT_TIME |
CURRENT_TIMESTAMP |
CURRENT_USER |
CURSOR |
DATE |
DAY |
DEALLOCATE |
DEC |
DECIMAL |
DECLARE |
DEFAULT |
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 |
PAD |
PARTIAL |
POSITION |
PRECISION |
PREPARE |
PRESERVE |
PRIMARY |
PRIOR |
PRIVILEGES |
PROCEDURE |
PUBLIC |
READ |
REAL |
REFERENCES |
RELATIVE |
RESTRICT |
REVOKE |
RIGHT |
ROLLBACK |
ROWS |
SCHEMA |
SCROLL |
SECOND |
SECTION |
SELECT |
SESSION |
SESSION_USER |
SET |
SIZE |
SMALLINT |
SOME |
SPACE |
SQL |
SQLCODE |
SQLERROR |
SQLSTATE |
SUBSTRING |
SUM |
SYSTEM_USER |
TABLE |
TEMPORARY |
THEN |
TIME |
TIMESTAMP |
TIMEZONE_HOUR |
TIMEZONE_MINUTE |
TO |
TRAILING |
TRANSACTION |
TRANSLATE |
TRANSLATION |
TRIM |
TRUE |
UNION |
UNIQUE |
UNKNOWN |
UPDATE |
UPPER |
USAGE |
USER |
USING |
VALUE |
VALUES |
VARCHAR |
VARYING |
VIEW |
WHEN |
WHENEVER |
WHERE |
WITH |
WORK |
WRITE |
YEAR |
ZONE |
|
<non-reserved word>
ADA |
C |
CATALOG_NAME |
CHARACTER_SET_CATALOG |
CHARACTER_SET_NAME |
CHARACTER_SET_SCHEMA |
CLASS_ORIGIN |
COBOL |
COLLATION_CATALOG |
COLLATION_NAME |
COLLATION_SCHEMA |
COLUMN_NAME |
COMMAND_FUNCTION |
COMMITTED |
CONDITION_NUMBER |
CONNECTION_NAME |
CONSTRAINT_CATALOG |
CONSTRAINT_NAME |
CONSTRAINT_SCHEMA |
CURSOR_NAME |
DATA |
DATETIME_INTERVAL_CODE |
DATETIME_INTERVAL_PRECISION |
DYNAMIC_FUNCTION |
FORTRAN |
LENGTH |
MESSAGE_LENGTH |
MESSAGE_OCTET_LENGTH |
MESSAGE_TEXT |
MORE |
MUMPS |
NAME |
NULLABLE |
NUMBER |
PASCAL |
PLI |
REPEATABLE |
RETURNED_LENGTH |
RETURNED_OCTET_LENGTH |
RETURNED_SQLSTATE |
ROW_COUNT |
SCALE |
SCHEMA_NAME |
SERIALIZABLE |
SERVER_NAME |
SUBCLASS_ORIGIN |
TABLE_NAME |
TYPE |
UNCOMMITTED |
UNNAMED |
|
[1] ANSI [1986] Americal National Standards Institute: The Database Language SQL, Document ANSI X3.135, 1986
[2] DMTF [1999] Distributed Management Taskforce: Specification for Internet Management Protocol (IMP), Version 1.0c, May 11th, 1999.