Derby Reference Manual
Version 10.9
Derby Document build:
May 31, 2012, 12:23:59 PM (PDT)
Version 10.9 Derby Reference Manual
i
Contents
Copyright..............................................................................................................................11
License................................................................................................................................. 12
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....................................... 52
DELETE statement..............................................................................................54
DROP statements................................................................................................55
GRANT statement ..............................................................................................58
INSERT statement...............................................................................................61
LOCK TABLE statement..................................................................................... 63
RENAME statements...........................................................................................64
REVOKE statement ............................................................................................65
SELECT statement..............................................................................................70
SET statements...................................................................................................72
TRUNCATE TABLE statement............................................................................74
UPDATE statement............................................................................................. 74
SQL clauses............................................................................................................. 75
CONSTRAINT clause..........................................................................................76
FOR UPDATE clause..........................................................................................82
FROM clause.......................................................................................................82
GROUP BY clause..............................................................................................83
HAVING clause................................................................................................... 84
ORDER BY clause..............................................................................................84
The result offset and fetch first clauses.............................................................. 86
Version 10.9 Derby Reference Manual
ii
USING clause......................................................................................................87
WHERE clause....................................................................................................88
WHERE CURRENT OF clause...........................................................................88
SQL expressions......................................................................................................89
SelectExpression................................................................................................. 92
TableExpression.................................................................................................. 94
NEXT VALUE FOR expression...........................................................................95
VALUES expression............................................................................................96
Expression precedence.......................................................................................97
Boolean expressions........................................................................................... 98
CASE expression.............................................................................................. 100
Dynamic parameters......................................................................................... 101
JOIN operations..................................................................................................... 103
INNER JOIN operation......................................................................................104
LEFT OUTER JOIN operation...........................................................................105
RIGHT OUTER JOIN operation........................................................................ 106
CROSS JOIN operation.................................................................................... 107
NATURAL JOIN operation................................................................................ 107
SQL queries............................................................................................................108
Query................................................................................................................. 108
ScalarSubquery................................................................................................. 110
TableSubquery...................................................................................................111
Built-in functions....................................................................................................112
Standard built-in functions.................................................................................112
Aggregates (set functions)................................................................................ 113
ABS or ABSVAL function..................................................................................114
ACOS function...................................................................................................114
ASIN function.....................................................................................................114
ATAN function................................................................................................... 115
ATAN2 function................................................................................................. 115
AVG function..................................................................................................... 115
BIGINT function.................................................................................................116
CAST function................................................................................................... 117
CEIL or CEILING function.................................................................................120
CHAR function...................................................................................................121
COALESCE function......................................................................................... 122
Concatenation operator.....................................................................................123
COS function..................................................................................................... 124
COSH function...................................................................................................124
COT function..................................................................................................... 124
COUNT function................................................................................................ 124
COUNT(*) function............................................................................................ 125
CURRENT DATE function.................................................................................125
CURRENT_DATE function................................................................................125
CURRENT ISOLATION function.......................................................................126
CURRENT_ROLE function................................................................................126
CURRENT SCHEMA function...........................................................................126
CURRENT TIME function..................................................................................126
CURRENT_TIME function.................................................................................126
CURRENT TIMESTAMP function..................................................................... 127
CURRENT_TIMESTAMP function.....................................................................127
CURRENT_USER function................................................................................127
DATE function................................................................................................... 128
DAY function......................................................................................................128
DEGREES function........................................................................................... 129
DOUBLE function.............................................................................................. 129
Version 10.9 Derby Reference Manual
iii
EXP function......................................................................................................129
FLOOR function.................................................................................................130
HOUR function.................................................................................................. 130
IDENTITY_VAL_LOCAL function...................................................................... 130
INTEGER function.............................................................................................132
LCASE or LOWER function.............................................................................. 132
LENGTH function.............................................................................................. 133
LN or LOG function...........................................................................................133
LOG10 function................................................................................................. 133
LOCATE function...............................................................................................134
LTRIM function.................................................................................................. 134
MAX function..................................................................................................... 135
MIN function...................................................................................................... 135
MINUTE function............................................................................................... 136
MOD function.....................................................................................................136
MONTH function................................................................................................137
NULLIF function.................................................................................................137
PI function..........................................................................................................137
RADIANS function.............................................................................................137
RANDOM function.............................................................................................138
RAND function...................................................................................................138
ROW_NUMBER function...................................................................................138
RTRIM function..................................................................................................139
SECOND function..............................................................................................139
SESSION_USER function.................................................................................139
SIGN function.................................................................................................... 140
SIN function.......................................................................................................140
SINH function.................................................................................................... 140
SMALLINT function........................................................................................... 140
SQRT function...................................................................................................141
SUBSTR function.............................................................................................. 141
SUM function.....................................................................................................142
TAN function......................................................................................................143
TANH function................................................................................................... 143
TIME function.................................................................................................... 143
TIMESTAMP function........................................................................................144
TRIM function.................................................................................................... 144
UCASE or UPPER function...............................................................................145
USER function...................................................................................................146
VARCHAR function........................................................................................... 146
XMLEXISTS operator........................................................................................146
XMLPARSE operator.........................................................................................148
XMLQUERY operator........................................................................................149
XMLSERIALIZE operator.................................................................................. 151
YEAR function................................................................................................... 152
Built-in system functions......................................................................................152
SYSCS_UTIL.SYSCS_CHECK_TABLE system function..................................152
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY system function......... 153
SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS system function............. 153
SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function....................... 154
SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function.........................154
SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function.................... 155
SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE system function..................... 155
Built-in system procedures.................................................................................. 156
SYSCS_UTIL.SYSCS_BACKUP_DATABASE system procedure.................... 156
