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
Basic data types
The following table shows Derby
support for the SQL-92 basic data types.
Support for
SQL-92 Features: Basic data typesFeatureSourceSMALLINTSQL92EYesINTEGERSQL92EYesDECIMAL(p,s)SQL92EYesNUMERIC(p,s)SQL92EYesREALSQL92EYesFLOAT(p)SQL92EYesDOUBLE PRECISIONSQL92EYesCHAR(n)SQL92EYes
Basic math operations
Support for SQL-92
Features: Basic math operationsFeatureSource+, *, -, /, unary +, unary -SQL92EYes
Basic comparisons
Support for SQL-92
Features: Basic comparisonsFeatureSource<, >, <= ,>=, <>, =SQL92EYes
Basic predicates
Support for SQL-92 Features:
Basic predicatesFeatureSourceBETWEEN, LIKE, NULLSQL92EYes
Quantified predicates
Support for SQL-92
Features: Quantified predicatesFeatureSourceIN, ALL/SOME, EXISTSSQL92EYes
Schema definition
Support for SQL-92
Features: schema definitionFeatureSourceTablesSQL92EYesViewsSQL92EYesPrivilegesSQL92EYes
Column attributes
Support for SQL-92
Features: column attributesFeatureSourceDefault valuesSQL92EYesNullabilitySQL92EYes
Constraints (non-deferrable)
Support
for SQL-92 Features: constraints (non-deferrable)FeatureSourceNOT NULLSQL92EYes (not stored in SYSCONSTRAINTS)UNIQUE/PRIMARY KEYSQL92EYesFOREIGN KEYSQL92EYesCHECKSQL92EYesView WITH CHECK OPTIONSQL92ENo, views cannot be updated
Cursors
Support for SQL-92 Features:
CursorsFeatureSourceDECLARE, OPEN, FETCH, CLOSESQL92EYes, by using JDBC method callsUPDATE, DELETE CURRENTSQL92EYes
Dynamic SQL 1
Support for SQL-92 Features:
Dynamic SQL 1FeatureSourceALLOCATE / DEALLOCATE / GET / SET DESCRIPTORSQL92TYes, by using JDBC method callsPREPARE / EXECUTE / EXECUTE IMMEDIATESQL92TYes, by using JDBC method callsDECLARE, OPEN, FETCH, CLOSE, UPDATE, DELETE dynamic
cursorSQL92TYes, by using JDBC method callsDESCRIBE outputSQL92TYes, by using JDBC method calls
Basic information schema
Support for
SQL-92 Features: Basic information schemaFeatureSourceTABLESSQL92TSYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNSVIEWSSQL92TSYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNSCOLUMNSSQL92TSYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS
Basic schema manipulation
Support for
SQL-92 Features: Basic schema manipulationFeatureSourceCREATE / DROP TABLESQL92TYesCREATE / DROP VIEWSQL92TYesGRANT / REVOKESQL92TYesALTER TABLE ADD COLUMNSQL92TYesALTER TABLE DROP COLUMNSQL92TYes
Joined table
Support for SQL-92 Features:
Joined tableFeatureSourceINNER JOINSQL92TYesnatural joinSQL92TNoLEFT, RIGHT OUTER JOINSQL92TYesjoin conditionSQL92TYesnamed columns joinSQL92TYes
Date and time data types
Support for
SQL-92 Features: Date and time data typesFeatureSourcesimple DATE, TIME, TIMESTAMP, INTERVALSQL92TYes, not INTERVALdatetime constantsSQL92TYesdatetime mathSQL92TYes, with Java methodsdatetime comparisonsSQL92TYespredicates: OVERLAPSSQL92TYes, with Java methods
VARCHAR data type
Support for SQL-92
Features: VARCHARFeatureSourceLENGTHSQL92TYesconcatenation (||)SQL92TYes
Transaction isolation
Support for SQL-92
Features: Transaction isolationFeatureSourceREAD WRITE / READ ONLYSQL92TBy using JDBC, database properties, and storage mediaRU, RC, RR, SERSQL92TYes
Multiple schemas per user
Support for
SQL-92 Features: Multiple schemas per userFeatureSourceSCHEMATA viewSQL92TSYS.SYSSCHEMAS
Privilege tables
Support for SQL-92 Features:
Privilege tablesFeatureSourceTABLE_PRIVILEGESSQL92TNoCOLUMNS_PRIVILEGESSQL92TNoUSAGE_PRIVILEGESSQL92TNo
Table operations
Support for SQL-92 Features:
Table operationsFeatureSourceUNION relaxationsSQL92IYesEXCEPTSQL92IYesINTERSECTSQL92IYesCORRESPONDINGSQL92INo
Schema definition statement
Support for
SQL-92 Features: Schema definition statementFeatureSourceCREATE SCHEMASQL92IPartial support
User authorization
Support for SQL-92
Features: User authorizationFeatureSourceSET SESSION AUTHORIZATIONSQL92IUse SET SCHEMACURRENT_USERSQL92IYesSESSION_USERSQL92IYesSYSTEM_USERSQL92INo
Constraint tables
Support for SQL-92
Features: Constraint tablesFeatureSourceTABLE CONSTRAINTSSQL92ISYS.SYSCONSTRAINTSREFERENTIAL CONSTRAINTSSQL92ISYS.SYSFOREIGNKEYSCHECK CONSTRAINTSSQL92ISYS.SYSCHECKS
Documentation schema
Support for SQL-92
Features: Documentation schemaFeatureSourceSQL_FEATURESSQL92I/FIPS 127-2Use JDBC DatabaseMetaDataSQL_SIZINGSQL92I/FIPS 127-2Use JDBC DatabaseMetaData
Full DATETIME
Support for SQL-92 Features:
Full DATETIMEFeatureSourceprecision for TIME and TIMESTAMPSQL92FYes
Full character functions
Support for
SQL-92 Features: Full character functionsFeatureSourcePOSITION expressionSQL92FUse Java methods or LOCATEUPPER/LOWER functionsSQL92FYes
Miscellaneous features
Support for SQL-92
Features: MiscellaneousFeatureSourceDelimited identifiersSQL92EYesCorrelated subqueriesSQL92EYesInsert, Update, Delete statementsSQL92EYesJoinsSQL92EYesWhere qualificationsSQL92EYesGroup bySQL92EYesHavingSQL92EYesAggregate functionsSQL92EYesOrder bySQL92EYesSelect expressionsSQL92EYesSelect *SQL92EYesSQLCODESQL92ENo, deprecated in SQL-92SQLSTATESQL92EYesUNION, INTERSECT, and EXCEPT in viewsSQL92TYesImplicit numeric castingSQL92TYesImplicit character castingSQL92TYesGet diagnosticsSQL92TUse JDBC SQLExceptionsGrouped operationsSQL92TYesQualified * in select listSQL92TYesLowercase identifiersSQL92TYesnullable PRIMARY KEYsSQL92TNoMultiple module supportSQL92TNo (not required and not part of JDBC)Referential delete actionsSQL92TCASCADE, SET NULL, RESTRICT, and NO ACTIONCAST functionsSQL92TYesINSERT expressionsSQL92TYesExplicit defaultsSQL92TYesKeyword relaxationsSQL92TYesDomain definitionSQL92INoCASE expressionSQL92IPartial supportCompound character string constantsSQL92IUse concatenationLIKE enhancementsSQL92IYesUNIQUE predicateSQL92INoUsage tablesSQL92ISYS.SYSDEPENDSIntermediate information schemaSQL92IUse JDBC DatabaseMetaData and system
tablesSubprogram supportSQL92INot relevant to JDBC, which is much richerIntermediate SQL FlaggingSQL92INoSchema manipulationSQL92IYesLong identifiersSQL92IYesFull outer joinSQL92INoTime zone specificationSQL92INoScrolled cursorsSQL92IPartial support (scrollable insensitive result sets
through JDBC 2.0)Intermediate set function supportSQL92IPartial supportCharacter set definitionSQL92ISupport for Java localesNamed character setsSQL92ISupport for Java localesScalar subquery valuesSQL92IYesExpanded null predicateSQL92IYesConstraint managementSQL92IYes (ADD/DROP CONSTRAINT)FOR BIT DATA typesSQL92FYes Assertion constraintsSQL92FNoTemporary tablesSQL92FPartial support, with DECLARE GLOBAL TEMPORARY TABLE Full dynamic SQLSQL92FNoFull value expressionsSQL92FYesTruth value testsSQL92FYesDerived tables in FROMSQL92FYesTrailing underscoreSQL92FYesIndicator data typesSQL92FNot relevant to JDBCReferential name orderSQL92FNoFull SQL FlaggingSQL92FNoRow and table constructorsSQL92FYesCatalog name qualifiersSQL92FNoSimple tablesSQL92FNoSubqueries in CHECKSQL92FNo, but can with Java methodsUnion joinSQL92FNoCollation and translationSQL92FJava locales supportedReferential update actionsSQL92FRESTRICT and NO ACTION. Can do others with triggers.ALTER domainSQL92FnNoINSERT column privilegesSQL92FNoReferential MATCH typesSQL92FNoView CHECK enhancementsSQL92FNo, views cannot be updatedSession managementSQL92FUse JDBCConnection managementSQL92FUse JDBCSelf-referencing operationsSQL92FYesInsensitive cursorsSQL92FYes through JDBC 2.0Full set functionSQL92FPartial supportCatalog flaggingSQL92FNoLocal table referencesSQL92FNoFull cursor updateSQL92FNo