NexusDB V3 Manual

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:

      Jim Melton, Alan R. Simon

      ISBN 1-55860-456-1

      http://www.iso.org

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:

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

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:

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.