apache > db
Apache DB Project
 
Font size:      

Derby Support for SQL-92 Features

Derby Support for SQL-92 Features

Table 9 shows the SQL-92 features that Derby 10 supports. 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 9. Support for SQL-92 Features

FeatureSourceDerby 10
Basic types SQL92E

SMALLINTyes

INTEGERyes

DECIMAL(p,s)yes

NUMERIC(p,s)yes

REALyes

FLOAT(p)yes

DOUBLE PRECISIONyes

CHAR(n)yes
Basic math operations SQL92E

+, *, -, /, unary +, unary -yes
Basic comparisons SQL92E

<, >, <= ,>=, <>, =yes
Basic predicates SQL92E

BETWEEN, LIKE, NULLyes
Quantified predicates SQL92E

IN, ALL/SOME, EXISTSyes
schema definition SQL92E

tablesyes

viewsyes (not updatable)

privilegesno, but you can configure databases for user authentication and restriction to read-only access
column attributes SQL92E

default valuesyes

nullabilityyes
constraints (non-deferrable) SQL92E

NOT NULLyes (not stored in SYSCONSTRAINTS)

UNIQUE/PRIMARY KEYyes

FOREIGN KEYyes

CHECKyes

View WITH CHECK OPTIONno, since views are not updatable
Delimited identifiersSQL92Eyes
Correlated subqueriesSQL92Eyes
Cursors SQL92E

DECLARE, OPEN, FETCH, CLOSEdone through JDBC

UPDATE, DELETE CURRENTyes
Insert, Update, Delete statementsSQL92Eyes
JoinsSQL92Eyes
Where qualificationsSQL92Eyes
Group bySQL92Eyes
HavingSQL92Eyes
Aggregate functionsSQL92Eyes
Order bySQL92Eyes
Select expressionsSQL92Eyes
Select *SQL92Eyes
SQLCODESQL92Eno, deprecated in SQL-92
SQLSTATESQL92Eyes
Dynamic SQL 1 SQL92Tdone through JDBC

ALLOCATE/DEALLOCATE/ GET/SET DESCRIPTOR

PREPARE/EXECUTE/ EXECUTE IMMEDIATE

DECLARE, OPEN, FETCH, CLOSE, UPDATE, DELETE dynamic cursor

DESCRIBE output
Basic information schema SQL92TSYS.SYSTABLES, SYS.SYSVIEWS, SYS.SYSCOLUMNS

TABLES

VIEWS

COLUMNS
Basic schema manipulation SQL92T

CREATE/DROP TABLEyes

CREATE/DROP VIEWyes

GRANT/REVOKEno

ALTER TABLE ADD COLUMNyes

ALTER TABLE DROP COLUMNyes
Joined table SQL92T

INNER JOINyes

natural joinno

LEFT, RIGHT OUTER JOINyes

join conditionyes

named columns joinyes
DATETIME data types 1 SQL92T

simple DATE, TIME, TIMESTAMP, INTERVALyes, not INTERVAL

datetime constantsyes

datetime mathcan do with Java methods

datetime comparisonsyes

predicates: OVERLAPScan do with Java methods
VARCHAR SQL92Tyes

LENGTH
yes

concatenation (||)yes
UNION in viewsSQL92Tyes
Implicit numeric castingSQL92Tyes
Implicit character castingSQL92Tyes
Transaction isolation SQL92T

READ WRITE/READ ONLYthrough JDBC, database properties, and storage media.

RU, RC, RR, SERyes
Get diagnosticsSQL92Tuse JDBC SQLExceptions
Grouped operationsSQL92Tyes
Qualified * in select listSQL92Tyes
Lowercase identifiersSQL92Tyes
nullable PRIMARY KEYsSQL92Tno
Multiple schemas per user SQL92Tyes

SCHEMATA viewSYS.SYSSCHEMAS
Multiple module supportSQL92Tno (not required and not part of JDBC)
Referential delete actionsSQL92TCASCADE, SET NULL, RESTRICT, and NO ACTION.
CAST functionsSQL92Tyes
INSERT expressionsSQL92Tyes
Explicit defaultsSQL92Tyes
Privilege tables SQL92Tno

TABLE_PRIVILEGES

COLUMNS_PRIVILEGES

USAGE_PRIVILEGES
Keyword relaxationsSQL92Tyes
Domain definitionSQL92Ino
CASE expressionSQL92Ipartial support
Compound character string constantssSQL92Iuse concatenation
LIKE enhancementsSQL92Iyes
UNIQUE predicateSQL92Ino
Table operations SQL92I

UNION relaxationsyes

EXCEPTno

INTERSECTno

CORRESPONDINGno
Schema definition statement SQL92I

CREATE SCHEMAyes, partially
User authorization SQL92I

SET SESSION AUTHORIZATIONuse set schema

CURRENT_USERyes

SESSION_USERyes

SYSTEM_USERno
Constraint tables SQL92I

TABLE_CONSTRAINTSSYS.SYSCONSTRAINTS

REFERENTIAL_CONSTRAINTSSYS.SYSFOREIGNKEYS

CHECK_CONSTRAINTSSYS.SYSCHECKS
Usage tablesSQL92ISYS.SYSDEPENDS
Intermediate information schemaSQL92Iuse JDBC DatabaseMetaData and Derby system tables
Subprogram supportSQL92Inot relevant to JDBC, which is much richer
Intermediate SQL FlaggingSQL92Ino
Schema manipulationSQL92Iyes, to drop a default, set it to NULL.
Long identifiersSQL92Iyes
Full outer joinSQL92Ino
Time zone specificationSQL92Ino
Scrolled cursorsSQL92Ipartial (scrolling insensitive result sets through JDBC 2.0)
Intermediate set function supportSQL92Ipartial
Character set definitionSQL92Isupports Java locales
Named character setsSQL92Isupports Java locales
Scalar subquery valuesSQL92Iyes
Expanded null predicateSQL92Iyes
Constraint managementSQL92Iyes (ADD/DROP CONSTRAINT)
Documentation schema SQL92I/FIPS 127-2

SQL_FEATURESuse JDBC DatabaseMetaData

SQL_SIZINGuse JDBC DatabaseMetaData
FOR BIT DATA typesSQL92Fyes
Assertion constraintsSQL92Fno
Temporary tablesSQL92FIBM specific syntax only
Full dynamic SQLSQL92Fno
Full DATETIME SQL92F

precision for TIME and TIMESTAMPno
Full value expressionsSQL92Fyes
Truth value testsSQL92Fyes
Full character functions SQL92F

POSITION expressionuse Java methods or LOCATE

UPPER/LOWER functionsyes
Derived tables in FROMSQL92Fyes
Trailing underscoreSQL92Fyes
Indicator data typesSQL92Fnot relevant to JDBC
Referential name orderSQL92Fno
Full SQL FlaggingSQL92Fno
Row and table constructorsSQL92Fyes
Catalog name qualifiersSQL92Fno
Simple tablesSQL92Fno
Subqueries in CHECKSQL92Fno, but can do with Java methods
Union joinSQL92Fno
Collation and translationSQL92FJava locales supported
Referential update actionsSQL92FRESTRICT and NO ACTION. Can do others with triggers.
ALTER domainSQL92Fno
INSERT column privilegesSQL92Fno
Referential MATCH typesSQL92Fno
View CHECK enhancementsSQL92Fno, views not updateable
Session managementSQL92Fuse JDBC
Connection managementSQL92Fuse JDBC
Self-referencing operationsSQL92Fyes
Insensitive cursorsSQL92FYes through JDBC 2.0
Full set functionSQL92Fpartially
Catalog flaggingSQL92Fno
Local table referencesSQL92Fno
Full cursor updateSQL92Fno
Previous Page
Next Page
Table of Contents
Index