# SQL specification

## Contents

- Overview
- Specifying the database to operate on
- Table creation
- Select statements

- Selecting columns
- Table and column names

- Search conditions

- Predicates
- Specifying a sort order

## Overview

The Structured Query Language is a standardised language for querying relational databases. It is abbreviated SQL, pronounced Sequel.

EPOC’s database management system uses a subset of SQL and permits SQL queries of databases; in OPL, SQL queries are used as string parameters to the `CREATE` and `OPEN` OPL commands (see Database manipulation). This document gives a BNF specification for these query strings.

## Specifying the database to operate on

SQL statements act on databases, so the database file is passed first:

  opl-create-string :
  `"` database-file-name [ create-statement ] `"`
  opl-open-string :
  `"` database-file-name [ select-statement ] `"`

Table creation is handled by OPL’s `CREATE` command, and view selection and searching are handled by `OPEN`. `CREATE` and `OPEN` are described in the Database manipulation and Keyword Reference sections, and the syntax of the SQL queries create-statement and select-statement are described below.

## Table creation

  create-statement :
  file-name [ field-restriction ] [ table-spec ]
  field-restriction :
  `FIELDS` field-name [ `,` field-name ]*
  table-spec :
  `TO` table-name

Table creation queries are used to make new tables in a database.

## Select statements

Section Contents

- Selecting columns
- Table and column names

  select-statement :
  `SELECT` select-list `FROM` table-name [ where-clause ] [ ordering ]
  where-clause :
  `WHERE` search-condition
  ordering :
  `ORDER BY` sort-order

Use a select-statement to specify what data should be present in the view, and how to present it.

### Selecting columns

  select-list :
  column-name-comma-list
  `*`
  column-name-comma-list :
  column-name [ `,` column-name-comma-list ]

Specify `*` to request that all columns for the table be returned in the view, in an undefined order; otherwise a comma separated list specifies which columns to return, and the order that the columns appear in the view.

### Table and column names

A table-name should be a table which exists in the database. column-names should refer to columns which exist in the specified table. Table and column names cannot contain spaces.

## Search conditions

Section Contents

- Predicates

- Comparison predicate
- Literals
- Like predicate
- Null predicate

- Specifying a sort order

  search-condition :
  boolean-term [ `OR` search-condition ]
  boolean-term :
  boolean-factor [ `AND` boolean-term ]
  boolean-factor :
  [ `NOT` ] boolean-primary
  boolean-primary :
  predicate
  `(` search-condition `)`

This specifies a condition which a row must meet to be present in the generated view. A trivial search condition is just a single predicate, more complex search conditions are constructed by combining predicates using the keywords `AND`, `OR` and `NOT`, and using parentheses to override the standard precedence of these operators. Without brackets, the order of precedence is `NOT`, `AND` then `OR`. e.g.

        a=1 or not b=2 and c=3

is equivalent to

        (a=1 or ((not b=2) and c=3))

### Predicates

  predicate :
  comparison-predicate
  like-predicate
  null-predicate

These are the building blocks of the search condition. Each predicate tests one condition of a column in the selected table.

#### Comparison predicate

  comparison-predicate :
  column-name comparison-operator literal
  comparison-operator :
  `<` | `>` | `<=` | `>=` | `=` | `<>`

Compare a column value with a supplied literal value. Numeric columns (including bit columns) are compared numerically, text columns are compared lexically and date columns are compared historically. Binary columns cannot be compared. The literal must be of the same type (numeric, string, date) as the column.

#### Literals

  literal :
  string-literal
  numeric-literal
  date-literal

A string-literal is a character string enclosed in single quote characters (`'`). To include a single literal quote character (`'`) in a string-literal, use two literal quote characters: (`''`).

A numeric-literal is any sequence of characters which can be interpreted as a valid decimal integral or floating point number.

A date-literal is a character string enclosed by the # character, which can be interpreted as a valid date.

#### Like predicate

  like-predicate :
  column-name [ `NOT` ] `LIKE` pattern-value
  pattern-value :
  string-literal

Test whether or not a text column matches a pattern string. The wildcard characters used in the pattern-value are not standard SQL, instead the EPOC wildcard characters are used: `?` for matching any single character and `*` for matching zero or more characters.

#### Null predicate

  null-predicate :
  column-name `IS` [ `NOT` ] `NULL`

Test whether or not a column is Null. This predicate can be applied to all column types.

### Specifying a sort order

  sort-order :
  sort-specification-comma-list
  sort-specification :
  column-name [ `ASC` | `DESC` ]

Without an `ORDER BY` clause in the select statement the order that rows are presented is undefined. The columns specified in the sort-order can be ordered in ascending (the default) or descending order, and should appear in the sort-order in decreasing order of precedence. e.g.

        surname, first_name

will order the rows by the column `surname`, and any rows with identical surnames will then be ordered by the column `first_name`.