Derby support for SQL-92 features SQL-92features supported by Derbyfeatures not supported by Derby

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

Support for SQL-92 Features: Basic 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
Support for SQL-92 Features: Basic math operations Feature Source +, *, -, /, unary +, unary - SQL92E yes
Support for SQL-92 Features: Basic comparisons Feature Source <, >, <= ,>=, <>, = SQL92E yes
Support for SQL-92 Features: Basic predicates Feature Source BETWEEN, LIKE, NULL SQL92E yes
Support for SQL-92 Features: Quantified predicates Feature Source IN, ALL/SOME, EXISTS SQL92E yes
Support for SQL-92 Features: schema definition Feature Source tables SQL92E yes views SQL92E yes privileges SQL92E yes
Support for SQL-92 Features: column attributes Feature Source default values SQL92E yes nullability SQL92E yes
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, since views are not updatable
Support for SQL-92 Features: Cursors Feature Source DECLARE, OPEN, FETCH, CLOSE SQL92E done through JDBC UPDATE, DELETE CURRENT SQL92E yes
Support for SQL-92 Features: Dynamic SQL 1 Feature Source 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
Support 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
Support for SQL-92 Features: Basic schema manipulation Feature Source 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
Support 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
Support for SQL-92 Features: Joined table Feature Source 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
Support for SQL-92 Features: VARCHAR Feature Source LENGTH SQL92T yes concatenation (||) SQL92T yes
Support for SQL-92 Features: Transaction isolation Feature Source READ WRITE / READ ONLY SQL92T through JDBC, database properties, and storage media. RU, RC, RR, SER SQL92T yes
Support for SQL-92 Features: Multiple schemas per user Feature Source SCHEMATA view SQL92T SYS.SYSSCHEMAS
Support for SQL-92 Features: Privilege tables Feature Source TABLE_PRIVILEGES SQL92T no COLUMNS_PRIVILEGES SQL92T no USAGE_PRIVILEGES SQL92T no
Support for SQL-92 Features: Table operations Feature Source UNION relaxations SQL92I yes EXCEPT SQL92I yes INTERSECT SQL92I yes CORRESPONDING SQL92I no
Support for SQL-92 Features: Schema definition statement Feature Source CREATE SCHEMA SQL92I yes, partially
Support 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
Support for SQL-92 Features: Constraint tables Feature Source TABLE CONSTRAINTS SQL92I SYS.SYSCONSTRAINTS REFERENTIAL CONSTRAINTS SQL92I SYS.SYSFOREIGNKEYS CHECK CONSTRAINTS SQL92I SYS.SYSCHECKS
Support 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
Support for SQL-92 Features: Full DATETIME Feature Source precision for TIME and TIMESTAMP SQL92F yes
Support for SQL-92 Features: Full character functions Feature Source POSITION expression SQL92F use Java methods or LOCATE UPPER/LOWER functions SQL92F yes
Support 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 (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