SQL reference

JDataStore is a feature of JBuilder Professional and Enterprise, and the Inprise Application Server.

This is a description of the SQL dialect supported by the query engine for the JDataStore JDBC driver. The methods in the JDBC API that take an SQL statement are:

int Statement.executeUpdate(String query)
ResultSet Statement.executeQuery(String query)
PreparedStatement Connection.prepare(String query)

Each query string must contain exactly one SQL statement. Use executeQuery for SELECT statements, and updateQuery for all other queries. If there are parameter markers in the query, a PreparedStatement must be used to pass the actual parameters.


Lists in syntax notation

A number of syntax element names ending with the word "list"; for example,
<select item list>

aren't defined. These are to be read as a comma-separated list with at least one element; in this case,

<select item list> ::= <select item> [ , <select item> ]* 


Data types

The following data types are supported in the JDataStore:

In SQL you can be specify these types by using those names or by using synonym type names, which are more portable to other SQL engines. The possible types are listed in the following table:

Table C.1 Data types supported by JDataStore 

JDataStore type

Synonyms

Attributes

Bytes of storage

Precision

Range

SHORT

SMALLINT

 

1-3

exact

-32768...32767

INT

INTEGER

 

1-5

exact

-2147483648...
2147483647

LONG

BIGINT

 

1-9

exact

-9223372036854775808...
9223372036854775807

BIGDECIMAL

DECIMAL,
NUMERIC

prec,dec

1-32

exact

-10^72...10^72

FLOAT

REAL

 

1-5

approximate

{+/-}1.4E-45...3.4E38

DOUBLE

DOUBLE PRECISION

 

1-9

approximate

{+/-}4.9E-324...1.8E308

STRING

VARCHAR,
CHAR

prec,inline

1-MAX_INT

n/a

 

BOOLEAN

BIT

 

1

n/a

 

INPUTSTREAM

BINARY

 

1-MAX_INT

n/a

 

OBJECT

 

JavaType

1-MAX_INT

n/a

 

DATE

DATE

 

1-9

exact

 

TIME

TIME

 

1-9

exact

 

TIMESTAMP

TIMESTAMP

 

1-14

exact

 

You can specify additional attributes for some types. For strings, you can specify a precision and an inline length. The precision is the maximum number of bytes allocated to such a string, and the inline length is the maximum number of bytes a string can take before the string is treated as a large object (which yields slower performance) similar to the BINARY types. The default inline value is 64.

Example

A string with a maximum size of 30 bytes and any string over 10 bytes is stored in a separate stream for large objects:

CHAR(30,10)

A string with a maximum size of 30 bytes, never inlined (precision is less than default inline value of 64):

CHAR(30)

A string with no length limit, use default inline size:

CHAR

A BigDecimal with 2 decimals and space for at least 5 significant digits:

DECIMAL(5,2)

A BigDecimal with 0 decimals and space for at least 4 significant digits:

NUMERIC(4)

A BigDecimal with 0 decimals and space for at least 72 significant digits:

NUMERIC

Any Java object that is serializable:

OBJECT

Only Java strings using Java serialization:

OBJECT(java.lang.String)


Literals

The following table lists the types of scalar literal values supported:

Table C.2   JDataStore SQL literal values 

Type

Details

Examples

String

Strings are enclosed in single quotes. The single quote character is represented by two consecutive single quotes.

'Hello'
'don''t'

Binary number

A binary or hexadecimal seqence enclosed in single quotes and preceded by the letter B or X

B'1011001'
X'F08A'
X'f777'

Exact numeric

A number that may contain a decimal point

8
2.
15.7
.9233

Approximate numeric

A number followed by the letter E, followed with an optionally signed integer

8E0
4E3
0.3E2
6.2E-72

Date

Written in the format:
DATE 'yyyy-mm-dd'

DATE '1999-03-23'

Time

24-hour time written in the format:
TIME 'hh:mm:ss'

TIME '00:46:55'

Timestamp

Written in the format:
TIMESTAMP 'yyyy-mm-dd hh:mm:ss'

TIMESTAMP '2001-12-31 13:15:45'

There is no literal syntax for the OBJECT data type.


JDBC Escape sequences

JDataStore supports JDBC Escape sequences for specifying date and time literals, the escape character for a LIKE clause, and OUTER JOINs, and for inserting the results of string or date and time functions into your SQL statement. JDBC Escapes are always enclosed in braces {}. They are used to extend the functionality of SQL.

Supported Escapes

Date and time literals:

OUTER JOINs:

Escape character for LIKE:

Functions

Functions are written in the format:

FN indicates that the function following it should be performed.

String functions:

Date and time functions:

Examples


Keywords

This list contains all identifiers reserved for keywords in this version of the JDataStore SQL engine. All keywords are case-insensitive. For example, select, SELECT, and SeLeCT are all considered to be the keyword SELECT.

Note that not all SQL-92 keywords are treated as a keyword by the JDataStore SQL engine. For maximum portability, don't use identifiers that are treated as keywords in any SQL dialect.

Table C.3   Keywords for JDataStore SQL engine 

ALL

ALTER

AND

AVG

AS

ASC

BETWEEN

BINARY

BIT

BOTH

BY

CASE

CAST

CHAR

CHAR_LENGTH

CHARACTER

CHARACTER_LENGTH

COLUMN

COUNT

CREATE

CROSS

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DATE

DAY

DEC

DECIMAL

DEFAULT

DELETE

DESC

DISTINCT

DOUBLE

DROP

ELSE

END

ESCAPE

EXTRACT

FALSE

FLOAT

FOR

FROM

FULL

GROUP

HAVING

HOUR

IN

INDEX

INNER

INSERT

INTO

INT

INTEGER

IS

JOIN

KEY

LEFT

LEADING

NOT

LIKE

LOWER

MAX

MIN

MINUTE

MONTH

NATURAL

NOT

NULL

OR

NUMERIC

ON

ORDER

OUTER

POSITION

PRECISION

PRIMARY

REAL

RIGHT

SECOND

SELECT

SET

SMALLINT

SUBSTRING

SUM

TABLE

THEN

TIME

TIMESTAMP

TIMEZONEHOUR

TIMEZONEMINUTE

TRAILING

TRIM

TRUE

UNIQUE

UNKNOWN

UPDATE

UPPER

USING

VALUES

VARCHAR

VARYING

WHEN

WHERE

YEAR


Identifiers

Unquoted SQL identifiers are case-insensitive and treated as uppercase. An identifier can be enclosed in double quotes, which then is treated as case-sensitive. An unquoted identifier must follow these rules:

Quoted identifiers can contain any character string including spaces, symbols, keywords, etc.

Example

Valid identifiers:

customer    // treated as CUSTOMER
Help_me     // treated as HELP_ME
"Hansen"    // treated as Hansen
"DATE"      // treated as DATE
" "         // treated as a single space

Invalid identifiers:

_order      // must start with a character
date        // date is a keyword
borland.com // dots are not allowed

This list denotes the very same identifier; they are all treated as "ORDER":

order
Order
oRDeR
"ORDER"


Expressions

Expression are used throughout the SQL language. They contain several infix operators and a few prefix operators. This is the operator precedence from strongest to weakest:

Syntax

<expression> ::=
      <scalar expression>
    | <conditional expression>

<conditional expression> ::=
      <expression> OR <expression>
    | <expression> AND <expression>
    | NOT <expression>
    | <scalar expression> <compare operator> <scalar expression>
    | <scalar expression> [NOT] BETWEEN <scalar expression>
    | <scalar expression> [NOT] LIKE <scalar expression> [ ESCAPE <scalar expression> ]
    | <scalar expression> [NOT] IS { NULL | TRUE | FALSE | UNKNOWN }

<compare operator> ::=
      = | <> | < | > | <= | >=

<scalar expression> ::=
      <scalar expression> {+ | - | * | / | <concat> } <scalar expression>
    | {+ | -} <scalar expression>
    | ( <expression> )
    | <column reference>
    | <literal>
    | <aggregator function>
    | <function>
    | <parameter marker>

<concat> ::=   | |

<function> ::=
      <substring function>
    | <position function>
    | <trim function>
    | <extract function>
    | <lower function>
    | <upper function>
    | <char length function>
    | <current date function>

<current date function> ::=
      CURRENT_DATE
    | CURRENT_TIME
    | CURRENT_TIMESTAMP

Example

Select the calculated value of amount times price from the orders table for a to-be-provided customer for orders in January:

SELECT Amount * Price FROM Orders 
        WHERE CustId = ? AND EXTRACT(MONTH FROM Ordered) = 1


Predicates

The following predicates, used in condition expressions, are supported.

BETWEEN

The BETWEEN predicate defines an inclusive range of values. The result of:
expr BETWEEN leftExpr AND rightExpr

is equivalent to the expression:

leftExpr <= expr AND expr <= rightExpr

Syntax

<between expression> ::=
      <scalar expression> [NOT] BETWEEN <scalar expression> AND <scalar expression>

Example

Select all the orders where a customer has orders between 3 and 7 items of the same kind:

SELECT * from Orders WHERE Amount BETWEEN 3 AND 7

IS

The IS predicate is defined to test expressions. Any expression can evaluate to the value NULL, but conditional expressions can evaluate to one of the three the values: TRUE, FALSE, UNKNOWN. UNKNOWN is equivalent with NULL for conditional expressions. Note that for a SELECT query with a WHERE clause, only rows that evaluate to TRUE are included. If the expression evaluates to FALSE or UNKNOWN it isn't included. The output of the IS predicate can have two results: TRUE or FALSE.

Syntax

<is expression> ::=
      <scalar expression> IS [NOT] { NULL | TRUE | FALSE | UNKNOWN }

Example

TRUE IS TRUE     // evaluates to TRUE
FALSE IS NULL    // evaluates to FALSE

LIKE

The LIKE predicate provides SQL with simple string pattern matching. The search item, pattern, and escape character (if given) must all evaluate to strings. The pattern can include the special wildcard characters _ and % where:

The escape character, if given, allows the two special wildcard characters to be included in the search pattern. The pattern match is case-sensitive. Use the LOWER or UPPER functions on the search item for a case-insensitive match.

Syntax

<like expression> ::=
      <search item> [NOT] LIKE <pattern> [ ESCAPE <escape char> ]

<search item>   ::= <scalar expression>
<pattern>            ::= <scalar expression>
<escape char>  ::= <scalar expression>

Example

Item LIKE '%shoe%'

evaluates to true if Item contains the string "shoe" anywhere inside it.

Item LIKE 'S__'

evaluates to true if Item is exactly 3 characters long, starting with the letter "S".

Item Like '%*%' ESCAPE '*'

evaluates to true if Item ends with the percent character. The * is defined to escape the two special characters. If it precedes a special character, it's treated as a normal character in the pattern.

IN

The IN clause indicates a list of values to be matched. Any one of the values in the list will be considered a match for the SELECT statement containing the IN clause.

Syntax

<in expression> ::=
      IN ( <scalar expression commalist> )

Example

SELECT * FROM zodiac WHERE name IN ('leo', 'aquarius')
// Returns all records where the name column matches
// either "leo" or "aquarius".


Functions

Note that functions that act on strings work for strings of any length. Large strings are stored as blobs, so you might want to define large text fields as CHAR to enable searches.

CHAR_LENGTH and CHARACTER_LENGTH

The SQL CHAR_LENGTH and CHARACTER_LENGTH functions yield the length of a given string.

Syntax

<char length function> ::=
      CHAR_LENGTH ( <scalar expression> )
      CHARACTER_LENGTH ( <scalar expression> )

CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP

These SQL functions yield the current date and/or time. If the functions are placed more than once in a statement, they all yield the same result when the statement is executed.

Example

SELECT * from Returns where ReturnDate <= CURRENT_DATE

EXTRACT

The SQL EXTRACT function is able to extract parts of date and time values. The expression can be a DATE, TIME or TIMESTAMP value.

Syntax

<extract function> ::=
      EXTRACT ( <extract field> FROM <scalar expression> )

<extract field> ::=
      YEAR
    | MONTH
    | DAY
    | HOUR
    | MINUTE
    | SECOND

Example

EXTRACT(MONTH FROM DATE '1999-05-17')     // yields 5
EXTRACT(HOUR FROM TIME '18:00:00')        // yields 18
EXTRACT(HOUR FROM DATE '1999-05-17')      // yields an exception

LOWER and UPPER

The SQL LOWER and UPPER functions simply yield the given string, converted to all-lowercase or all-uppercase.

Syntax

<lower function> ::=
      LOWER ( <scalar expression> )

<upper function> ::=
      UPPER ( <scalar expression> )

POSITION

The SQL POSITION function returns the position of a string within another string. If any of the arguments evaluates to NULL, the result is NULL.

Syntax

<position function> ::=
      POSITION ( <string> IN <another> )

Example

POSITION('BCD' IN 'ABCDEFG')     // yields 2
POSITION('' IN 'ABCDEFG')        // yields 1
POSITION('TAG' IN 'ABCDEFG')     // yields 0

SUBSTRING

The SQL SUBSTRING function extracts a substring from a given string. If any of the operands are NULL, the result is NULL. The start position indicates the first character position where the substring is taken, where 1 indicates the first character. If the FOR part is present, it indicates the length of the resulting string.

Syntax

<substring function> ::=
      SUBSTRING ( <string expression> FROM <start pos> [ FOR <length> ] )

Example

SUBSTRING('ABCDEFG' FROM 2 FOR 3)  // yields 'BCD'
SUBSTRING('ABCDEFG' FROM 4)        // yields 'DEFG'
SUBSTRING('ABCDEFG' FROM 10)       // yields ''
SUBSTRING('ABCDEFG' FROM -6 FOR 3) // yields 'ABC'
SUBSTRING('ABCEDFG' FROM 2 FOR -1) // raises an exception

TRIM

The SQL TRIM function is able to remove leading and/or trailing padding characters from a given string. The <padding> must be a string of length 1, which is the character that is removed from the string.

Syntax

<trim function> ::=
      TRIM ( [<trim spec>] [<padding>] [FROM] <scalar expression> )

<trim spec> ::=
      LEADING
    | TRAILING
    | BOTH

<padding> ::= <scalar expression>

Example

TRIM('  Hello world   ')              // yields 'Hello world'
TRIM(LEADING '0' FROM '00000789.75')  // yields '789.75'

CAST

The CAST function permits casting one data type to another data type.

Syntax

<cast function> ::=
      CAST ( <column name> AS <data type> )

Example

SELECT * FROM employee WHERE CAST ( salary AS long ) = 86293
// yields row where salary = 86,292.94


Statements

The JDataStore JDBC driver supports a subset of the ANSI/ISO SQL-92 standard. In general, it provides:

Syntax

<SQL statement> ::= 
      <data definition statement>
    | <data manipulation statement>

<data definition statement> ::=
      <create table statement>
    | <alter table statement>
    | <drop table statement>
    | <create index statement>
    | <drop index statement>

<data manipulation statement> ::=
      <select statement>
    | <delete statement>
    | <insert statement>
    | <update statement>

CREATE TABLE

This statement creates a table in the JDataStore. A column name and data type must be defined for each column.

Optionally, you can specify a default value for each column, along with uniqueness constraints.

You can also optionally specify a primary key. JDataStore supports the use of one or more columns as a primary key.

Syntax

<create table statement> ::=
      CREATE TABLE <table name> ( <table element list> )

<table element> ::= 
      <column name> <data type> 
    [ DEFAULT <default value> ]
    [ NOT NULL ]
    [ PRIMARY KEY ]
    [ UNIQUE ]
    | PRIMARY KEY( <column name list> )
    | UNIQUE( <column name list> )

<default value> ::= 
      <literal>
    | <current date function>

<table name> ::=
      <SQL identifier>

<column name> ::=
      <SQL identifier>
	  
<data type> ::=
        BOOLEAN
	  | CHARACTER [ VARYING ]
	  | CHAR [ VARYING ]
	  | SHORT
	  | LONG         
	  | VARCHAR
	  | BIGDECIMAL   
	  | INTEGER
	  | INT          
	  | STRING       
	  | FLOAT
	  | INPUTSTREAM  
	  | DOUBLE [ PRECISION ]
	  | OBJECT
	  | SMALLINT
	  | DECIMAL
	  | DEC
	  | DATE
	  | TIME
	  | TIMESTAMP

Example

CREATE TABLE Orders ( CustId INTEGER PRIMARY KEY, Item VARCHAR(30), 
        Amount INT, OrderDate DATE DEFAULT CURRENT_DATE)

Example of creating a table using two columns for the primary key constraint:

create table t1 (c1 INT, c2 STRING, c3 STRING,
primary key (c1, c2))

ALTER TABLE

This statement adds and removes columns in a table in the JDataStore.

Syntax

<alter table statement> ::=
      ALTER TABLE <table name> <column change list>

<column change> ::= 
      <add column element>
    | <drop column element>
    | <alter column element>

<add column element> ::= 
      ADD [COLUMN] <table element>

<drop column element> ::=
      DROP [COLUMN] <column name>

<alter column element> ::=
      ALTER [COLUMN] <column name> SET       
      <default-definition> | DROP DEFAULT

The optional COLUMN keyword is included for SQL compatibility. It has no effect.

Example

ALTER TABLE Orders Add ShipDate DATE, DROP Amount

CREATE INDEX

This statement creates an index for a table in the JDataStore. Each column can be specified to be ordered in ascending or descending order. The default value is ascending order.

Syntax

<create index statement> ::=
      CREATE [UNIQUE] [CASEINSENSITIVE] INDEX <index name> ON <table name>
                ( <index element list> ) 

<index name> ::=
      <SQL Identifier>

<index element> ::=
      <column name> [DESC | ASC]

Example

This generates a non-unique, case-sensitive, ascending index on the column "ITEM" of the table "ORDERS":

CREATE INDEX OrderIndex ON Orders (Item ASC)

SELECT

SELECT statements are used to retrieve data from one or more tables. The optional keyword DISTINCT eliminates duplicate rows from the result of a SELECT statement. The keyword ALL, which is the default, obtains all rows including duplicates. The data can optionally be grouped or sorted.

Syntax

<select statement> ::=
      SELECT [ ALL | DISTINCT ] * | < item list>
      FROM   <table expression list>
    [ WHERE  <expression> ]
    [ GROUP BY <column reference list> ]
    [ HAVING <expression> ]
    [ ORDER BY <order item list> ]

< item> ::= 
      <expression> [ [AS] <column name> ]

<table expression> ::=
      <table name> [ [AS] <correlation name> ]
      | <join table expression>

<column reference> ::=
      [ <table qualifier> . ] <column name>

<table qualifier> ::=
      <table name> | <correlation name>

<correlation name> ::=
      <SQL identifier>

Example

SELECT * FROM Orders WHERE Item = 'Shorts'

JOIN

JOIN table expressions are used to SELECT a result set that combines results from two tables. JOIN can be modified with other keywords depending on the desired result.

CROSS JOIN produces the same result set as "select a.*, b.* from a,b".

ON allows a conditional expression for row selection to be specified. Only rows for which the condition evaluates to true will be selected.

USING allows the tables to be joined using columns which match in both tables. The columns specified in the column list will only be shown in the result set once, followed by the remaining columns from table a, then the remaining columns from table b.

INNER JOIN has the same result as CROSS JOIN if no ON or USING clause is specified. If an ON or USING clause is added, it means that all rows which match the ON or USING clause in both tables will be selected. If no JOIN type is specified, INNER is assumed.

LEFT JOIN, RIGHT JOIN, and FULL JOIN are all OUTER JOINs. Every OUTER JOIN must have one of the keywords NATURAL, ON, or USING.

LEFT JOIN means form "a inner join b" and then append rows of a that didn't contribute, filling in the spaces corresponding to columns in b with nulls.

RIGHT JOIN means form "a inner join b" and then append rows of b that didn't contribute, filling in the columns corresponding to columns in a with nulls.

FULL JOIN means form "a inner join b", but append rows of a or b that didn't contribute, filling in the new columns corresponding with nulls.

NATURAL JOIN is the same as "a JOIN b USING (all of the columns common to both a and b)". When NATURAL is specified, neither ON nor USING can be used, and when ON or USING is specified, NATURAL cannot be used.

Syntax

<join-table-expression> ::=
          <table-reference> CROSS JOIN
	  <table reference> |
	  <table-reference> [ NATURAL ]
	  [ join-type ] JOIN <table-reference>
	  [ ON <conditional-expression>]
	  [ USING (column-column-list) ]

join-type ::= 
      INNER
      LEFT [  OUTER ]
      RIGHT [ OUTER ]
      FULL  [ OUTER ]
Examples
SELECT * FROM Tinvoice FULL OUTER JOIN Titem USING ("InvoiceNumber")

SELECT * FROM Tinvoice LEFT JOIN Titem ON Tinvoice."InvoiceNumber" =
Titem."InvoiceNumber"

SELECT * FROM Tinvoice NATURAL RIGHT OUTER JOIN Titem

SELECT * FROM Tinvoice INNER JOIN Titem USING ("InvoiceNumber")

SELECT * FROM Tinvoice JOIN Titem ON Tinvoice."InvoiceNumber" = Titem."InvoiceNumber"

GROUP BY and HAVING

You can use aggregate functions to calculate summary values from the data in a table. The WHERE clause (if present) limits the number of rows included in the summary. If no GROUP BY clause is present, a summary for the whole table is calculated. If a GROUP BY clause is present, a summary is computed for each unique set of values for the columns listed in the GROUP BY. Then, if the HAVING clause is present, it filters out complete groups given the conditional expression in the HAVING clause.

Summary queries have additional rules for where columns can appear in expressions:

Syntax

<select statement> ::=
      SELECT [ ALL | DISTINCT ] * | < item list>
      FROM   <table reference list>
    [ WHERE  <expression> ]
    [ GROUP BY <column reference list> ]
    [ HAVING <expression> ]
    [ ORDER BY <order item list> ]

<aggregator function> ::=  
      <aggregator name> ( <scalar expression> )
    | COUNT ( * )

<aggregator name> ::=
      AVG
    | SUM
    | MIN
    | MAX
    | COUNT

Example

SELECT SUM(Amount * Price) FROM Orders

yields a single row with the total value of all orders

SELECT COUNT(Amount) FROM Orders WHERE CustId = 123

yields a single row with the number of orders where Amount is non-NULL for the customer 123

SELECT CustId, SUM(Amount * Price), COUNT(Amount)
        WHERE CustId < 200 GROUP BY CustId

yields a set of rows, with the sum of the value of all orders grouped by customers for the customers with an ID number less than 200.

SELECT CustId, SUM(Amount * Price), COUNT(Amount) 
  GROUP BY CustId HAVING SUM(Amount * Price) > 500000

yields a set of big customers with the value of all their orders

SELECT CustId, COUNT(23 + SUM(Amount)) GROUP BY CustId

Illegal: nested aggregators present

SELECT CustId, SUM(Amount* Price) GROUP BY Amount

Illegal: the column CustId is referenced in the select item list, but it is not present in the GROUP BY reference list.

ORDER BY

The output of a SELECT statement is usually a set of unordered rows of data. The ORDER BY clause can be used to sort the data before it is retrieved. Each ordering factor can be:

Specify DESC to order in descending order. If neither ASC or DESC is specified ASC (ascending) is assumed.

Syntax

<select statement> ::=
      SELECT [ ALL | DISTINCT ] * | < item list>
      FROM   <table reference list>
    [ WHERE  <expression> ]
    [ GROUP BY <column reference list> ]
    [ HAVING <expression> ]
    [ ORDER BY <order item list> ]

<order item> ::= <order part> [ ASC | DESC ]

<order part> ::=
      <integer literal>
    | <column name>
    |  <expression>

Example

SELECT Item FROM Orders ORDER BY 1 DESC

orders by the first column, the Item column.

SELECT CustId, Amount*Price+500.00 AS CALC FROM Orders ORDER BY CALC

orders by the calculated column CALC

SELECT CustId, Amount FROM Orders ORDER BY Amount*Price

orders by the expression given

SELECT CustId FROM Orders ORDER BY 2

Illegal: ordinal outside the range

INSERT

An INSERT statement inserts rows into a table in the JDataStore. A list of columns with associated values are listed in the INSERT statement. Columns that aren't listed in the statement are set to their default values. The values given are either a list of expressions or a SELECT expression. A SELECT expression has the same syntax as a SELECT statement, except that an ORDER BY clause is not accepted. The resulting set of rows from the SELECT statement are inserted in the target table.

Syntax

<insert statement> ::=
      INSERT INTO <table name> ( <column name list> )
                [ <table expression> | DEFAULT VALUES ]

<table expression> ::=
      VALUES ( <scalar expression list> )
    | <select statement>

<select statement> ::=
      SELECT [ ALL | DISTINCT ] * | < item list>
      FROM   <table reference list>
    [ WHERE  <expression> ]
    [ GROUP BY <column reference list> ]
    [ HAVING <expression> ]

Example

This statement should be used in connection with a PreparedStatement in JDBC. It inserts one row each time it is executed. The columns not mentioned are set to their default values. If a column doesn't have a default value, it's set to NULL.

INSERT INTO Orders (CustId, Item) VALUES (?,?)

This statement finds all the orders from the customer with CustId = 123 and inserts the Item of these orders into the table ResTable.

INSERT INTO ResTable SELECT Item from Orders 
        WHERE CustId = 123

UPDATE

An UPDATE statement is used to modify existing data. The columns that are changed by the statement are listed explicitly. All the rows where the WHERE clause evaluates to TRUE are changed. If no WHERE clause is specified, all rows in the table are changed.

Syntax

<update statement> ::=
      UPDATE <table name> SET <update assignment list>
    [ WHERE <expression> ]

<update assignment> ::=
      <column reference> = <update expression>

<update expression> ::=
      <scalar expression>
    | DEFAULT
    | NULL

Example

All orders from the customer 123 are changed to orders from the customer 500:

UPDATE Orders SET CustId = 500 WHERE CustId = 123

Increase the amount of all orders in the table:

UPDATE Orders SET Amount = Amount + 1

Reprice all disposable underwater cameras to 7.25:

UPDATE UWCameras SET Price = 7.25 WHERE Price > 7.25

DELETE

A DELETE statement deletes rows from a table in the JDataStore. If no WHERE clause is specified, all the rows are deleted. Otherwise only the rows that match the WHERE expression are deleted.

Syntax

<delete statement> ::=
      DELETE FROM <table name>
    [ WHERE <expression> ]

Example

DELETE FROM Orders WHERE Item = 'Shorts'

DROP INDEX

This statement deletes an index from a table in the JDataStore.

Syntax

<drop index statement> ::=
      DROP INDEX <index name> ON <table name>

Example

This deletes the index "ORDERINDEX" on the table "ORDERS":

DROP INDEX OrderIndex ON Orders

DROP TABLE

This statement deletes a table and its indexes from the JDataStore.

Syntax

<drop table statement> ::=
      DROP TABLE <table name>

Example

DROP TABLE Orders