# Derby support for SQL-92 features

There are four levels of SQL-92 support:

- SQL92E

  Entry

- SQL92T

  Transitional, a level defined by NIST in a publication called FIPS 127-2

- SQL92I

  Intermediate

- SQL92F

  Full

Table 1. Support for SQL-92 Features: Basic types
Feature | Source | Derby
--- | --- | ---
SMALLINT | SQL92E | yes
INTEGER | SQL92E | yes
DECIMAL(p,s) | SQL92E | yes
NUMERIC(p,s) | SQL92E | yes
REAL | SQL92E | yes
FLOAT(p) | SQL92E | yes
DOUBLE PRECISION | SQL92E | yes
CHAR(n) | SQL92E | yes

Table 2. Support for SQL-92 Features: Basic math operations
Feature | Source | Derby
--- | --- | ---
+, *, -, /, unary +, unary - | SQL92E | yes

Table 3. Support for SQL-92 Features: Basic comparisons
Feature | Source | Derby
--- | --- | ---
<, >, <= ,>=, <>, = | SQL92E | yes

Table 4. Support for SQL-92 Features: Basic predicates
Feature | Source | Derby
--- | --- | ---
BETWEEN, LIKE, NULL | SQL92E | yes

Table 5. Support for SQL-92 Features: Quantified predicates
Feature | Source | Derby
--- | --- | ---
IN, ALL/SOME, EXISTS | SQL92E | yes

Table 6. Support for SQL-92 Features: schema definition
Feature | Source | Derby
--- | --- | ---
tables | SQL92E | yes
views | SQL92E | yes
privileges | SQL92E | yes

Table 7. Support for SQL-92 Features: column attributes
Feature | Source | Derby
--- | --- | ---
default values | SQL92E | yes
nullability | SQL92E | yes

Table 8. Support for SQL-92 Features: constraints (non-deferrable)
Feature | Source | Derby
--- | --- | ---
NOT NULL | SQL92E | yes (not stored in SYSCONSTRAINTS)
UNIQUE/PRIMARY KEY | SQL92E | yes
FOREIGN KEY | SQL92E | yes
CHECK | SQL92E | yes
View WITH CHECK OPTION | SQL92E | no, since views are not updatable

Table 9. Support for SQL-92 Features: Cursors
Feature | Source | Derby
--- | --- | ---
DECLARE, OPEN, FETCH, CLOSE | SQL92E | done through JDBC
UPDATE, DELETE CURRENT | SQL92E | yes

Table 10. Support for SQL-92 Features: Dynamic SQL 1
Feature | Source | Derby
--- | --- | ---
ALLOCATE / DEALLOCATE / GET / SET DESCRIPTOR | SQL92T | done through JDBC
PREPARE / EXECUTE / EXECUTE IMMEDIATE | SQL92T | done through JDBC
DECLARE, OPEN, FETCH, CLOSE, UPDATE, DELETE dynamic cursor | SQL92T | done through JDBC
DESCRIBE output | SQL92T | done through JDBC

Table 11. Support for SQL-92 Features: Basic information schema
Feature | Source | Derby
--- | --- | ---
TABLES | SQL92T | SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS
VIEWS | SQL92T | SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS
COLUMNS | SQL92T | SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS

Table 12. Support for SQL-92 Features: Basic schema manipulation
Feature | Source | Derby
--- | --- | ---
CREATE / DROP TABLE | SQL92T | yes
CREATE / DROP VIEW | SQL92T | yes
GRANT / REVOKE | SQL92T | no
ALTER TABLE ADD COLUMN | SQL92T | yes
ALTER TABLE DROP COLUMN | SQL92T | no

Table 13. Support for SQL-92 Features: Joined table
Feature | Source | Derby
--- | --- | ---
INNER JOIN | SQL92T | yes
natural join | SQL92T | no
LEFT, RIGHT OUTER JOIN | SQL92T | yes
join condition | SQL92T | yes
named columns join | SQL92T | yes

Table 14. Support for SQL-92 Features: Joined table
Feature | Source | Derby
--- | --- | ---
simple DATE, TIME, TIMESTAMP, INTERVAL | SQL92T | yes, not INTERVAL
datetime constants | SQL92T | yes
datetime math | SQL92T | can do with Java methods
datetime comparisons | SQL92T | yes
predicates: OVERLAPS | SQL92T | can do with Java methods

Table 15. Support for SQL-92 Features: VARCHAR
Feature | Source | Derby
--- | --- | ---
LENGTH | SQL92T | yes
concatenation (||) | SQL92T | yes

Table 16. Support for SQL-92 Features: Transaction isolation
Feature | Source | Derby
--- | --- | ---
READ WRITE / READ ONLY | SQL92T | through JDBC, database properties, and storage media.
RU, RC, RR, SER | SQL92T | yes

Table 17. Support for SQL-92 Features: Multiple schemas per user
Feature | Source | Derby
--- | --- | ---
SCHEMATA view | SQL92T | SYS.SYSSCHEMAS

Table 18. Support for SQL-92 Features: Privilege tables
Feature | Source | Derby
--- | --- | ---
TABLE_PRIVILEGES | SQL92T | no
COLUMNS_PRIVILEGES | SQL92T | no
USAGE_PRIVILEGES | SQL92T | no

Table 19. Support for SQL-92 Features: Table operations
Feature | Source | Derby
--- | --- | ---
UNION relaxations | SQL92I | yes
EXCEPT | SQL92I | yes
INTERSECT | SQL92I | yes
CORRESPONDING | SQL92I | no

Table 20. Support for SQL-92 Features: Schema definition statement
Feature | Source | Derby
--- | --- | ---
CREATE SCHEMA | SQL92I | yes, partially

Table 21. Support for SQL-92 Features: User authorization
Feature | Source | Derby
--- | --- | ---
SET SESSION AUTHORIZATION | SQL92I | use set schema
CURRENT_USER | SQL92I | yes
SESSION_USER | SQL92I | yes
SYSTEM_USER | SQL92I | no

Table 22. Support for SQL-92 Features: Constraint tables
Feature | Source | Derby
--- | --- | ---
TABLE CONSTRAINTS | SQL92I | SYS.SYSCONSTRAINTS
REFERENTIAL CONSTRAINTS | SQL92I | SYS.SYSFOREIGNKEYS
CHECK CONSTRAINTS | SQL92I | SYS.SYSCHECKS

Table 23. Support for SQL-92 Features: Documentation schema
Feature | Source | Derby
--- | --- | ---
SQL_FEATURES | SQL92I/FIPS 127-2 | use JDBC DatabaseMetaData
SQL_SIZING | SQL92I/FIPS 127-2 | use JDBC DatabaseMetaData

Table 24. Support for SQL-92 Features: Full DATETIME
Feature | Source | Derby
--- | --- | ---
precision for TIME and TIMESTAMP | SQL92F | yes

Table 25. Support for SQL-92 Features: Full character functions
Feature | Source | Derby
--- | --- | ---
POSITION expression | SQL92F | use Java methods or LOCATE
UPPER/LOWER functions | SQL92F | yes

Table 26. Support for SQL-92 Features: Miscellaneous
Feature | Source | Derby
--- | --- | ---
Delimited identifiers | SQL92E | yes
Correlated subqueries | SQL92E | yes
Insert, Update, Delete statements | SQL92E | yes
Joins | SQL92E | yes
Where qualifications | SQL92E | yes
Group by | SQL92E | yes
Having | SQL92E | yes
Aggregate functions | SQL92E | yes
Order by | SQL92E | yes
Select expressions | SQL92E | yes
Select * | SQL92E | yes
SQLCODE | SQL92E | no, deprecated in SQL-92
SQLSTATE | SQL92E | yes
UNION, INTERSECT, and EXCEPT in views | SQL92T | yes
Implicit numeric casting | SQL92T | yes
Implicit character casting | SQL92T | yes
Get diagnostics | SQL92T | use JDBC SQLExceptions
Grouped operations | SQL92T | yes
Qualified * in select list | SQL92T | yes
Lowercase identifiers | SQL92T | yes
nullable PRIMARY KEYs | SQL92T | no
Multiple module support | SQL92T | no (not required and not part of JDBC)
Referential delete actions | SQL92T | CASCADE, SET NULL, RESTRICT, and NO ACTION.
CAST functions | SQL92T | yes
INSERT expressions | SQL92T | yes
Explicit defaults | SQL92T | yes
Keyword relaxations | SQL92T | yes
Domain definition | SQL92I | no
CASE expression | SQL92I | partial support
Compound character string constants | SQL92I | use concatenation
LIKE enhancements | SQL92I | yes
UNIQUE predicate | SQL92I | no
Usage tables | SQL92I | SYS.SYSDEPENDS
Intermediate information schema | SQL92I | use JDBC DatabaseMetaData and Derby system tables
Subprogram support | SQL92I | not relevant to JDBC, which is much richer
Intermediate SQL Flagging | SQL92I | no
Schema manipulation | SQL92I | yes
Long identifiers | SQL92I | yes
Full outer join | SQL92I | no
Time zone specification | SQL92I | no
Scrolled cursors | SQL92I | partial (scrollable insensitive result sets through JDBC 2.0)
Intermediate set function support | SQL92I | partial
Character set definition | SQL92I | supports Java locales
Named character sets | SQL92I | supports Java locales
Scalar subquery values | SQL92I | yes
Expanded null predicate | SQL92I | yes
Constraint management | SQL92I | yes (ADD/DROP CONSTRAINT)
FOR BIT DATA types | SQL92F | yes
Assertion constraints | SQL92F | no
Temporary tables | SQL92F | IBM specific syntax only
Full dynamic SQL | SQL92F | no
Full value expressions | SQL92F | yes
Truth value tests | SQL92F | yes
Derived tables in FROM | SQL92F | yes
Trailing underscore | SQL92F | yes
Indicator data types | SQL92F | not relevant to JDBC
Referential name order | SQL92F | no
Full SQL Flagging | SQL92F | no
Row and table constructors | SQL92F | yes
Catalog name qualifiers | SQL92F | no
Simple tables | SQL92F | no
Subqueries in CHECK | SQL92F | no, but can do with Java methods
Union join | SQL92F | no
Collation and translation | SQL92F | Java locales supported
Referential update actions | SQL92F | RESTRICT and NO ACTION. Can do others with triggers.
ALTER domain | SQL92F | no
INSERT column privileges | SQL92F | no
Referential MATCH types | SQL92F | no
View CHECK enhancements | SQL92F | no, views not updateable
Session management | SQL92F | use JDBC
Connection management | SQL92F | use JDBC
Self-referencing operations | SQL92F | yes
Insensitive cursors | SQL92F | Yes through JDBC 2.0
Full set function | SQL92F | partially
Catalog flagging | SQL92F | no
Local table references | SQL92F | no
Full cursor update | SQL92F | no