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

Basic data types

The following table shows Derby support for the SQL-92 basic data types.Support for SQL-92 Features: Basic data types Feature Source 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

Basic math operationsSupport for SQL-92 Features: Basic math operations Feature Source +, *, -, /, unary +, unary - SQL92E Yes

Basic comparisonsSupport for SQL-92 Features: Basic comparisons Feature Source <, >, <= ,>=, <>, = SQL92E Yes

Basic predicatesSupport for SQL-92 Features: Basic predicates Feature Source BETWEEN, LIKE, NULL SQL92E Yes

Quantified predicatesSupport for SQL-92 Features: Quantified predicates Feature Source IN, ALL/SOME, EXISTS SQL92E Yes

Schema definitionSupport for SQL-92 Features: schema definition Feature Source Tables SQL92E Yes Views SQL92E Yes Privileges SQL92E Yes

Column attributesSupport for SQL-92 Features: column attributes Feature Source Default values SQL92E Yes Nullability SQL92E Yes

Constraints (non-deferrable)Support for SQL-92 Features: constraints (non-deferrable) Feature Source 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, views cannot be updated

CursorsSupport for SQL-92 Features: Cursors Feature Source DECLARE, OPEN, FETCH, CLOSE SQL92E Yes, by using JDBC method calls UPDATE, DELETE CURRENT SQL92E Yes

Dynamic SQL 1Support for SQL-92 Features: Dynamic SQL 1 Feature Source ALLOCATE / DEALLOCATE / GET / SET DESCRIPTOR SQL92T Yes, by using JDBC method calls PREPARE / EXECUTE / EXECUTE IMMEDIATE SQL92T Yes, by using JDBC method calls DECLARE, OPEN, FETCH, CLOSE, UPDATE, DELETE dynamic cursor SQL92T Yes, by using JDBC method calls DESCRIBE output SQL92T Yes, by using JDBC method calls

Basic information schemaSupport for SQL-92 Features: Basic information schema Feature Source TABLES SQL92T SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS VIEWS SQL92T SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS COLUMNS SQL92T SYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS

Basic schema manipulationSupport for SQL-92 Features: Basic schema manipulation Feature Source CREATE / DROP TABLE SQL92T Yes CREATE / DROP VIEW SQL92T Yes GRANT / REVOKE SQL92T Yes ALTER TABLE ADD COLUMN SQL92T Yes ALTER TABLE DROP COLUMN SQL92T Yes

Joined tableSupport for SQL-92 Features: Joined table Feature Source INNER JOIN SQL92T Yes natural join SQL92T No LEFT, RIGHT OUTER JOIN SQL92T Yes join condition SQL92T Yes named columns join SQL92T Yes

Date and time data typesSupport for SQL-92 Features: Date and time data types Feature Source simple DATE, TIME, TIMESTAMP, INTERVAL SQL92T Yes, not INTERVAL datetime constants SQL92T Yes datetime math SQL92T Yes, with Java methods datetime comparisons SQL92T Yes predicates: OVERLAPS SQL92T Yes, with Java methods

VARCHAR data typeSupport for SQL-92 Features: VARCHAR Feature Source LENGTH SQL92T Yes concatenation (||) SQL92T Yes

Transaction isolationSupport for SQL-92 Features: Transaction isolation Feature Source READ WRITE / READ ONLY SQL92T By using JDBC, database properties, and storage media RU, RC, RR, SER SQL92T Yes

Multiple schemas per userSupport for SQL-92 Features: Multiple schemas per user Feature Source SCHEMATA view SQL92T SYS.SYSSCHEMAS

Privilege tablesSupport for SQL-92 Features: Privilege tables Feature Source TABLE_PRIVILEGES SQL92T No COLUMNS_PRIVILEGES SQL92T No USAGE_PRIVILEGES SQL92T No

Table operationsSupport for SQL-92 Features: Table operations Feature Source UNION relaxations SQL92I Yes EXCEPT SQL92I Yes INTERSECT SQL92I Yes CORRESPONDING SQL92I No

Schema definition statementSupport for SQL-92 Features: Schema definition statement Feature Source CREATE SCHEMA SQL92I Partial support

User authorizationSupport for SQL-92 Features: User authorization Feature Source SET SESSION AUTHORIZATION SQL92I Use SET SCHEMA CURRENT_USER SQL92I Yes SESSION_USER SQL92I Yes SYSTEM_USER SQL92I No

Constraint tablesSupport for SQL-92 Features: Constraint tables Feature Source TABLE CONSTRAINTS SQL92I SYS.SYSCONSTRAINTS REFERENTIAL CONSTRAINTS SQL92I SYS.SYSFOREIGNKEYS CHECK CONSTRAINTS SQL92I SYS.SYSCHECKS

Documentation schemaSupport for SQL-92 Features: Documentation schema Feature Source SQL_FEATURES SQL92I/FIPS 127-2 Use JDBC DatabaseMetaData SQL_SIZING SQL92I/FIPS 127-2 Use JDBC DatabaseMetaData

Full DATETIMESupport for SQL-92 Features: Full DATETIME Feature Source precision for TIME and TIMESTAMP SQL92F Yes

Full character functionsSupport for SQL-92 Features: Full character functions Feature Source POSITION expression SQL92F Use Java methods or LOCATE UPPER/LOWER functions SQL92F Yes

Miscellaneous featuresSupport for SQL-92 Features: Miscellaneous Feature Source 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 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 support (scrollable insensitive result sets through JDBC 2.0) Intermediate set function support SQL92I Partial support Character set definition SQL92I Support for Java locales Named character sets SQL92I Support for 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 Partial support, with DECLARE GLOBAL TEMPORARY TABLE 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 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 nNo INSERT column privileges SQL92F No Referential MATCH types SQL92F No View CHECK enhancements SQL92F No, views cannot be updated 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 Partial support Catalog flagging SQL92F No Local table references SQL92F No Full cursor update SQL92F No