Java DB Reference Manual
Version 10.6
Derby Document build:
September 22, 2010, 4:14:15 PM (EDT)
Version 10.6 Java DB Reference Manual
i
Contents
Copyright..............................................................................................................................10
License................................................................................................................................. 11
Relationship between Java DB and Derby.......................................................................15
About this guide..................................................................................................................16
Purpose of this document...................................................................................... 16
Audience................................................................................................................... 16
How this guide is organized...................................................................................16
SQL language reference.....................................................................................................18
Capitalization and special characters....................................................................18
SQL identifiers ........................................................................................................ 18
Rules for SQL92 identifiers................................................................................. 19
SQL92Identifier....................................................................................................19
column-Name.......................................................................................................20
correlation-Name................................................................................................. 20
new-table-Name...................................................................................................21
schemaName.......................................................................................................21
Simple-column-Name.......................................................................................... 21
synonym-Name....................................................................................................21
table-Name.......................................................................................................... 22
view-Name...........................................................................................................22
index-Name..........................................................................................................22
constraint-Name...................................................................................................22
cursor-Name........................................................................................................ 23
TriggerName........................................................................................................23
AuthorizationIdentifier.......................................................................................... 23
RoleName............................................................................................................23
Statements................................................................................................................ 24
Interaction with the dependency system.............................................................24
ALTER TABLE statement....................................................................................25
CALL (PROCEDURE) statement........................................................................ 29
CREATE statements........................................................................................... 30
DECLARE GLOBAL TEMPORARY TABLE statement....................................... 50
DELETE statement..............................................................................................53
DROP statements................................................................................................54
GRANT statement ..............................................................................................57
INSERT statement...............................................................................................60
LOCK TABLE statement..................................................................................... 61
RENAME statements...........................................................................................62
REVOKE statement ............................................................................................64
SET statements................................................................................................... 68
SELECT statement..............................................................................................71
UPDATE statement............................................................................................. 72
SQL clauses............................................................................................................. 74
CONSTRAINT clause..........................................................................................74
FOR UPDATE clause..........................................................................................80
FROM clause.......................................................................................................80
GROUP BY clause..............................................................................................81
HAVING clause................................................................................................... 82
ORDER BY clause..............................................................................................82
The result offset and fetch first clauses.............................................................. 84
Version 10.6 Java DB Reference Manual
ii
USING clause......................................................................................................85
WHERE clause....................................................................................................85
WHERE CURRENT OF clause...........................................................................86
SQL expressions......................................................................................................86
SelectExpression................................................................................................. 89
TableExpression.................................................................................................. 92
NEXT VALUE FOR expression...........................................................................93
VALUES expression............................................................................................ 94
Expression precedence....................................................................................... 95
Boolean expressions........................................................................................... 95
Dynamic parameters........................................................................................... 98
JOIN operations..................................................................................................... 100
INNER JOIN operation......................................................................................101
LEFT OUTER JOIN operation...........................................................................102
RIGHT OUTER JOIN operation........................................................................ 103
CROSS JOIN operation.................................................................................... 104
NATURAL JOIN operation................................................................................ 105
SQL queries............................................................................................................105
Query................................................................................................................. 105
ScalarSubquery................................................................................................. 107
TableSubquery...................................................................................................108
Built-in functions....................................................................................................109
Standard built-in functions.................................................................................109
Aggregates (set functions)................................................................................ 110
ABS or ABSVAL function.................................................................................. 111
ACOS function...................................................................................................111
ASIN function.....................................................................................................111
ATAN function................................................................................................... 112
ATAN2 function................................................................................................. 112
AVG function..................................................................................................... 112
BIGINT function................................................................................................. 113
CASE expressions.............................................................................................114
CAST function................................................................................................... 114
CEIL or CEILING function.................................................................................117
CHAR function...................................................................................................117
COALESCE function......................................................................................... 119
Concatenation operator..................................................................................... 120
COS function..................................................................................................... 121
COSH function...................................................................................................121
COT function..................................................................................................... 121
COUNT function................................................................................................ 121
COUNT(*) function............................................................................................ 122
CURRENT DATE function.................................................................................122
CURRENT_DATE function................................................................................122
CURRENT ISOLATION function....................................................................... 122
CURRENT_ROLE function................................................................................122
CURRENT SCHEMA function...........................................................................123
CURRENT TIME function..................................................................................123
CURRENT_TIME function.................................................................................123
CURRENT TIMESTAMP function..................................................................... 123
CURRENT_TIMESTAMP function.....................................................................124
CURRENT_USER function................................................................................124
DATE function................................................................................................... 124
DAY function......................................................................................................125
DEGREES function........................................................................................... 125
DOUBLE function.............................................................................................. 125
Version 10.6 Java DB Reference Manual
iii
EXP function......................................................................................................126
FLOOR function.................................................................................................126
HOUR function.................................................................................................. 126
IDENTITY_VAL_LOCAL function...................................................................... 127
INTEGER function............................................................................................. 128
LCASE or LOWER function.............................................................................. 129
LENGTH function.............................................................................................. 129
LN or LOG function...........................................................................................129
LOG10 function................................................................................................. 130
LOCATE function...............................................................................................130
LTRIM function.................................................................................................. 131
MAX function..................................................................................................... 131
MIN function...................................................................................................... 132
MINUTE function............................................................................................... 132
MOD function.....................................................................................................132
MONTH function................................................................................................133
NULLIF expressions.......................................................................................... 133
PI function..........................................................................................................133
RADIANS function............................................................................................. 134
RANDOM function............................................................................................. 134
RAND function...................................................................................................134
ROW_NUMBER function...................................................................................134
RTRIM function..................................................................................................135
SECOND function..............................................................................................135
SESSION_USER function................................................................................. 135
SIGN function.................................................................................................... 136
SIN function....................................................................................................... 136
SINH function.................................................................................................... 136
SMALLINT function........................................................................................... 136
SQRT function................................................................................................... 137
SUBSTR function.............................................................................................. 137
SUM function..................................................................................................... 138
TAN function......................................................................................................139
TANH function................................................................................................... 139
TIME function.................................................................................................... 139
TIMESTAMP function........................................................................................ 139
TRIM function.................................................................................................... 140
UCASE or UPPER function...............................................................................141
USER function................................................................................................... 142
VARCHAR function........................................................................................... 142
XMLEXISTS operator........................................................................................ 142
XMLPARSE operator.........................................................................................144
XMLQUERY operator........................................................................................ 145
XMLSERIALIZE operator.................................................................................. 146
YEAR function................................................................................................... 148
Built-in system functions......................................................................................148
SYSCS_UTIL.SYSCS_CHECK_TABLE system function..................................148
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function......... 148
SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function............. 149
SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function....................... 149
SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function.........................150
SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function.................... 150
Built-in system procedures.................................................................................. 150
SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure.................... 150
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_NOWAIT system procedure....151
