|
|
|
|
|
|
|
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.
SQL statements act on databases, so the database file is passed first:
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 queries are used to make new tables in a database.
Section Contents
Use a select-statement to specify what data should be present in the view, and how to present it.
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.
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.
Section Contents
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))
These are the building blocks of the search condition. Each predicate tests one condition of a column in the selected table.
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.
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.
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.
Test whether or not a column is Null. This predicate can be applied to all column types.
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.
|
|
|
|
|
|
|