Favourites

Search

About NexusDB SQL Reference

The intention of this manual is to provide a complete reference of the SQL language used in NexusDB V3, with detailed documentation of the syntax and proper explanation of the usage.

It is assumed that readers of the manual have at least basic knowledge of SQL. While the many examples throughout the manual are well suited as small tutorials, illustrating how the grammar and syntactic elements are used in SQL statements, we recommend that users who want to learn SQL well, read a good text book about standard SQL.

Since NexusDB V3 has a modern SQL implementation based on the latest SQL:2003 standard, books describing SQL:1999 or SQL:2003 would be an excellent source. Application programmers and database administrators who want a more comprehensive presentation and documentation of standard SQL, might want to acquire a copy of the excellent SQL:1999 book by Jim Melton (the editor of SQL-92, SQL:1999 and SQL:2003), or even the SQL:2003 standard itself:

Organization and layout of topics

The table of contents (TOC) has been organized in chapters and related sub-chapters following the structure of the main parts of the SQL language.

The topic pages have generally been structured as follows:

♦

Syntax

♦

Usage

♦

Notes

♦

Examples

♦

Conformance summary

In some of the major topic pages, like the CREATE TABLE statement and the SELECT statement, the Notes section has been split into several parts to detail the documentation of complex syntax and functionality.

The Conformance summary at the bottom of each topic page documents the level of conformance to SQL:2003, differences between the standard and NexusDB SQL, and vendor-specific extensions.

BNF conventions

The syntax of the SQL grammar is documented by using a variation of BNF (Backus Normal Form). A BNF production consists of three parts, a non-terminal symbol enclosed in angle brackets, a definition operator (::=) and a production rule defining the syntactic elements on the left side. The production rule may include one or more non-terminal symbols that are resolved in separate BNF productions. The following example shows a simple BNF production:

<current time value function> ::= CURRENT_TIME | LOCALTIME

Since NexusDB SQL is an implementation of SQL:2003, most of the BNF productions are based on standard SQL definitions, but simplified when symbols are self-explanatory. For example, instead of defining the non-terminal symbol <table name> in a separate BNF production, we are using the terminal symbol table-name.

Symbols used in BNF

Symbol

Description

< >

A character string enclosed in angle brackets is the name of a non-terminal symbol.

::=

The definition operator is used in a production rule to separate the element defined by the rule from its definition. The element being defined appears to the left of the operator and the formula that defines the element appears to the right.

[ ]

Square brackets indicate optional elements in a formula. The portion of the formula within the brackets may be explicitly specified or may be omitted.

{ }

Braces group elements in a formula. The portion of the formula within the braces shall be explicitly specified.

|

The alternative operator. The vertical bar indicates that the portion of the formula following the bar is an alternative to the portion preceding the bar. If the vertical bar appears at a position where it is not enclosed in braces or square brackets, it specifies a complete alternative for the element defined by the production rule. If the vertical bar appears in a portion of a formula enclosed in braces or square brackets, it specifies alternatives for the contents of the innermost pair of such braces or brackets.

...

The ellipsis indicates that the element to which it applies in a formula may be repeated any number of times. If the ellipsis appears immediately after a closing brace, then it applies to the portion of the formula enclosed between that closing brace and the corresponding opening brace . If an ellipsis appears after any other element, then it applies only to that element.

While the BNF productions may look rather complex at first, they provide a very precise documentation of the SQL grammar. We believe that users who are unfamiliar with BNF, will gain a better understanding of the flexibility and power of NexusDB SQL by spending some time on studying the BNF for frequently used SQL statements and other syntactic elements.

In the online version of this manual, all BNF non-terminal symbols that are part of a production rule, appear as green colored hot-links. Just click the indexed word with the mouse to jump directly to the topic page where the symbol is resolved.

Examples

A large part of the examples provided in the manual are referencing the "Students Database", a small demo database that ships with the NexusDB V3 installation. The "Students Database" is a collection of the following tables:

♦

students

♦

courses

♦

enrolls

♦

sections

♦

teachers

Before executing example statements that are making changes to these tables, it is recommended to backup the "Students Database" first. Another way of working with the sample data without persisting any changes, is to make temporary versions of the tables using either a CREATE TABLE statement with the AS subquery clause, or a SELECT INTO statement.

In the examples throughout the manual, we have chosen the notation of writing keywords in upper-case and identifiers in lower-case or mixed case to clearly distinguish between two in the text. However, the SQL language is case-insensitive to keywords and regular identifiers, and in NexusDB SQL also to delimited identifiers, meaning that SQL statements can be written in any case.