Jef's SQL Summary

SQL Summary reference: SQL Instant Reference by Martin Gruber, published by Sybex 1993

Relational database summary:
All data values are of the simple types in the table to the right
All data is represented in the form of 2-dimensional tables (relations). Each table is composed of zero or more rows (tuples) and one or more columns (attibutes).
By matching column values in different tables, more complicated merged tables can be constructed.
All operations are defined by logic; there is no particular order in tables, so row index is meaningless
Therefore, a primary key is necessary: one or more columns that uniquely define each row

SQL Type	C Type
CHAR	char
VARCHAR	char
BIT	char
INTEGER	long
SMALLINT	short
REAL	float
DOUBLE PRECISION	double
type of SQLCODE	long
type of SQLSTATE	char 6
type of indicators	long

COMPLICATIONS

Connection must be established before any database operations can be performed. This is done in SQL with the SET CONNECTION statement.
Privileges: there is a complex set of priviledges associated with Authorization IDs. Sometimes these are owned by an application.
There are various isolation levels applicable to multiple users accessing same data simultaneously: READ COMMITTED, READ UNCOMMITTED, READ REPEATABLE, and SERIALIZABLE.
SQL operations can result in errors. 5 character error strings can be found in SQLSTATE. The deprecated old standard saved as number in SQLCODE.

KEYS

Key Type Description
Primary Set of 1 or more columns, whose combined value uniquely defines each row in a table.
Foreign Set of 1 or more columns, whose combined value references a row in another table.
Parent Set of 1 or more columns in another table corresponding to a match from a foreign key. The match must be unique.
A system has referential integrity when every foreign key row corresponds to a parent key row.

JOIN TYPES
NOTE: joins combine columns in 2 tables, and in most cases match rows to foreign/parent keys.

Type Description
TableA UNION JOIN TableB All unmatched rows of both tables.
TableA INNER JOIN TableB All matched rows of both tables.
TableA [NATURAL] JOIN TableB An inner join, with column name(s) of foreign and parent key the same.
TableA CROSS JOIN TableB All combinations of rows from both tables.
Outer Joins:
TableA LEFT JOIN TableB All rows of TableA (+ NULLed rows corresponding to TableB) + all matches
TableA RIGHT JOIN TableB All rows of TableB(+ NULLed rows corresponding to TableA) + all matches
TableA FULL JOIN TableB All rows of TableB + all rows with TableA + all matches

COMMON ELEMENTS

AGGREGATE FUNCTIONS

Function Returns
AVG Return the average of all values. Supported types: INTERVAL and NUMERIC
COUNT Number of rows produced by a query for a column, disregarding rows with that column NULL.
MAX Maximum of all values, which can be of any type.
MIN Minimum of all values, which can be of any type.
SUM Sum of all values, which can be of type INTERVAL or NUMERIC.
NOTE: each of about are be followed by ([DISTINCT or ALL] value-expression) where DISTINCT eliminates duplicates. ALL is the default.
COUNT(*) Number of rows produced by a query.

IDENTIFIERS

Element Description
Authorization ID Unique identifier associated with: SQL session, a set of owned objects, and a set of priveleges on objects. Up to 3 authorization IDs can be active at any moment in a session: SESSION_USER(defined in CONNECT/USER clause, changed with the SET SESSION AUTHORIZATION statement), SYSTEM_USER(defined by OS - as a means to link DB users to OS users), CURRENT_USER(default is SESSION_USER, but can differ when defining a schema or in a module).

EXPRESSIONS

Function Returns
CASE expressions allow values to be specified conditionally CASE value-expr1 WHEN value-expr2 THEN [value-expr | NULL] ... END This is equivalent to "CASE value-expr1=value-expr2 THEN ..." and the default ELSE value-expr is NULL. NOTE: there can be multiple WHEN..ELSE.. clauses before the final END. or
CASE WHEN predicate THEN value-expr | NULL]... [ELSE [value-expr | NULL] END or
CASE NULLIF ( value-expr1, value-expr2 ) this is equivalent to "CASE WHEN value-expr1=value-expr2 THEN NULL ELSE value-expr1 END" or
CASE COALESCE ( value-expr1, value-expr2, ... ) this is the same as "CASE WHEN value-expr1 IS NOT NULL THEN value-expr1 ELSE COALESCE (value-expr2,...) END"
CAST expressions are used to convert from one data type to another CAST ([ value-expr | NULL] AS [data-type | domain])
SUPPORTED CASTS

TARGET
SOURCE exact
numeric approx
numeric var.
char fixed
char var
bit
str. fixed
bit
str date time time-
stamp year
month.
interval date-
time
interval
exact
numeric yes yes yes yes NO NO NO NO NO maybe maybe
approx.numeric yes yes yes yes NO NO NO NO NO NO NO
fixed charstr yes yes maybe maybe yes yes yes yes yes yes yes
var. charstr yes yes maybe maybe yes yes yes yes yes yes yes
fixed bit-str NO NO yes yes yes yes NO NO NO NO NO
var bit-str NO NO yes yes yes yes NO NO NO NO NO
date NO NO yes yes NO NO yes NO yes NO NO
time NO NO yes yes NO NO NO yes yes NO NO
timestamp NO NO yes yes NO NO yes yes yes NO NO
year-month
interval maybe NO yes yes NO NO NO NO NO yes NO
maybe NO yes yes NO NO NO NO NO NO yes

OBJECTS

Function Returns
COLLATIONS Can be created with the CREATE COLLATION statement, and used in the COLLATE clause. Collations define how comaparisons work when comparing character strings.

CONSTRAINTS

type clause
Table CONSTRAINT constraint-name followed by any of:
PRIMARY KEY (column-name, ...) duplicates and NULLs will be rejected
UNIQUE (column-name, ...) duplicates will be rejected
FOREIGN KEY (column-name, ...) REFERENCES table-name [(column-name,...)] [referential-spec] implies either PRIMARY KEY (default) or just UNIQUE
CHECK PREDICATE predicate when evaluates to FALSE, will be rejected
[[INITIALLY DEFERRED | INITIALLY IMMEDIATE] [NOT] DEFERRABLE]]
Column [CONSTRAINT constraint-name] followed by any of:
NOT NULL
PRIMARY KEY duplicates and NULLs will be rejected
UNIQUE duplicates will be rejected
REFERENCES table-name [(column-name,...)] [referential-spec]
CHECK predicate
[[INITIALLY DEFERRED | INITIALLY IMMEDIATE] [NOT] DEFERRABLE]]
Referential (between columns) [MATCH [FULL | PARTIAL]] [ON UPDATE [CASCADE | SET NULL | SET DEFAULT | NO ACTION]] [ON DELETE [CASCADE | SET NULL | SET DEFAULT | NO ACTION]]

DATA TYPES

Type Description
[NATIONAL] [N]CHAR[ACTER] [(length)] [CHARACTER SET (repertoire-name | form-of-use-name)] Fixed-length character string. Default and minimun length is 1. Strings are padded with blanks should they be shorter than the length.
CHAR[ACTER] VARYING (length) [CHARACTER SET (repertoire-name | form-of-use-name)]
VARCHAR (length) [CHARACTER SET (repertoire-name | form-of-use-name)]
or NATIONAL CHAR[ACTER] VARYING (length)
or NCHAR VARYING (length)
Length is the maximum length, which must be less than the implementation maximum. String lengths can vary from 0 up to this length.
BIT [(length)] Binary numbers composed of length bits. Length cannot exceed the implementation maximum. Numbers can be represented in either binary or hexadecimal notation: B"xxxx" or X"xxxx". Default length: 1.
BIT VARYING (length) Same as BIT, except length can vary.
NUMERIC [(precision [,scale])]
DEC[IMAL] [(precision [,scale])]
[SMALL]INT[EGER]
These are exact numeric types. Numeric specifies a maximum, decimal a minimum precision (non-integer). INT has implementation defined precision. SMALLINT may have less precision than INT.
FLOAT [(precision)]
REAL
DOUBLE PRECISION
Approximate numeric types. REAL and DOUBLE PRECISION only use implementation-defined precision.
DATE Dashes separate fields: yyyy-mm-dd (exactly 10 characters).
TIME [(precision)] [WITH TIME ZONE] Colons separate fields: hh:mm:ss[.s[s...]][time-zone] where time zone is of form +hh+mm (where + could also be -).
TIMESTAMP [(precision)] [WITH TIME ZONE] A blank separates the date from the time.
INTERVAL [interval-qualifier] INTERVAL 'interval-spec'
Interval-spec Could be a range: 'start-timestamp TO end-timestamp'
or just a timestamp
the timestamp could be preceeded by an interval qualifier, such as HOUR or YEAR. When not expressed as a range, it is same as 'time-stamp TO same-time-stamp'.

DATETIME VALUE FUNCTIONS

Function Returns
CURRENT_DATE the date according to the system clock
CURRENT_TIME[(precision)] the time according to the system clock
CURRENT_TIMESTAMP[(precision)] the date-time according to the system clock

DESCRIPTOR AREAS

Used in dynamic SQL to provide info about, and possibly store results, of statement that have been generated at runtime. A descriptor area is composed of descriptions(fields) of input or output parameters.

Name of field Data type
TYPE integer: < 0 is implementation dependent
1 is CHARACTER, 2 NUMERIC, 4 INTEGER, 5 SMALLINT, 6 FLOAT, 7 REAL, 8 DOUBLE PRECISION, 9 DATE (or TIME or TIMESTAMP), 10 INTERVAL, 12 CHARACTER VARYING, 14 BIT, 15 BIT VARYING
LENGTH integer
OCTET_LENGTH integer
RETURNED_LENGTH integer
RETURNED_OCTET_LENGTH integer
PRECISION integer
SCALE integer
DATETIME_INTERVAL_CODE integer: if TYPE is INTERVAL, 1 is YEAR, 2 MONTH, 3 DAY, 4 HOUR, 5 MINUTE, 6 SECOND, 7 YEAR TO MONTH, 8 DAY TO HOUR, 9 DAY TO MINUTE, 10 DAY TO SECOND, 11 HOUR TO MINUTE, 12 HOUR TO SECOND, 13 MINUTE TO SECOND. Otherwise it TYPE is a DATE: 1 is DATE, 2 TIME, 3 TIMESTAMP, 4 TIME WITH TIME ZONE, and 5 TIMESTAMP WITH TIME ZONE.
DATETIME_INTERVAL_PRECISION integer
NULLABLE integer (1 means yes, 0 means no)
INDICATOR integer: negative means parameter is NULL
DATA whatever is specified in TYPE, LENGTH, PRECISION, SCALE, ...
NAME character string using character set SQL_TEXT with length at least 128
UNNAMED integer: 0 means SQL syntax dictates what the name is (i.e. column name). 1 means it is implementation-dependent.
COLLATION_CATALOG character string using character set SQL_TEXT with length at least 128
COLLATION_SCHEMA character string using character set SQL_TEXT with length at least 128
COLLATION_NAME character string using character set SQL_TEXT with length at least 128
CHARACTER_SET_CATALOG character string using character set SQL_TEXT with length at least 128
CHARACTER_SET_SCHEMA character string using character set SQL_TEXT with length at least 128
CHARACTER_SET_NAME character string using character set SQL_TEXT with length at least 128

NUMERIC VALUE FUNCTIONS

Function Returns
POSITION ( character-string IN character-string ) NULL if any parameter is NULL in all of these, otherwise if first string is subset of the 2nd: the character position in the 2nd string where 1st match occurs, otherwise 0.
EXTRACT ( datetime-field FROM datetime-or-interval-value-expr ) corresponding integer for YEAR, MONTH, DAY, HOUR, MINUTE, TIMEZONE_HOUR, TIMEZONE_MINUTE, exact numeric for SECOND.
CHAR[ACTER]_LENGTH (string-val-expr) an integer representing the number of characters in the string, or octets in a bit string.
OCTET_LENGTH (string-val-expr) number of octets in the string (rounded up in non-integer)
BIT_LENGTH (string-val-expr) number of bits in a bit string.

PREDICATES

Predicate Returns
NOT predicate reverse of predicate
( predicate ) predicates can be enclosed in parens
predicate AND/font> predicate ands 2 predicates
predicate OR/font> predicate ors 2 predicates
string-val-expr [NOT] LIKE/font> pattern-string-val-expr [ESCAPE esc-char] First string is searched for 2nd (pattern) string. The 2 must equate for a TRUE result. The pattern can contain the wildcards: '_' represents any character, and '%' represents 0 or more characters in the match value. The ESCAPE char is used when matching a '_' or '%'. When an ESCAPE char is used, it precedes the '_' or '%' to represent these chars in a pattern.
Comparison Predicates: row-val-constructor [=, <, >, <=, >=, <>] row-val-constructor TRUE when both are non-NULL, and comparison is satisfied. Collations are used when comparing character strings. Intervals and date-times are also comparable. Whenever NULL is encountered, result will be UNKNOWN.
row-value-constructor [NOT] BETWEEN row-val-constructor AND row-val-constructor when row-value constructors are comparable (must have same number of values), this is the same as RC1 <= RC <= RC2, that is, an include between. NOT is the opposite. NOTE: first row-value-constuctor must be less than 2nd.
row-value-constructor [NOT] IN table sub-query | (value-expr,...) true when row-value-constructor is found in table, or list of values
string-val-expr [NOT] LIKE string-val-expr [ ESCAPE char ] true if first string (match value) exactly matches the second string (the pattern). The pattern can contain '_', which corresponds to any char, and '%' matches any substring. An escape char can be defined when either of these are supposed to be in the pattern. If either the match string, pattern, or escape is NULL, then result is UNKNOWN.
row-val-constructor IS [NOT] NULL true if all columns are NULL, false otherwise
quantified comparisons:
row-val-contructor [ =, <, >, <=, >=, <> ] [ ANY, SOME, ALL ] table-subquery true when all rows meet the criteria. For example, "> ALL" means every row must be greater than the row-val-constructor. When NULLs are involved answer is UNKNOWN. SOME is equivalent to ANY.
EXISTS table-subquery true if subquery produces at least one row, false otherwise
UNIQUE table-subquery true when no duplicate rows (except those with NULLs) are produced by query; false otherwise
row-value-constructor MATCH [UNIQUE] [PARTIAL | FULL] table-subquery true when row-value-constructor matches any row in subquery table, similar to IN, but allows for partial matches (involving partially NULL rows):
when row-value-constructor is completely NULL, result is true in all cases
UNIQUE yields true when only one non-NULL value matches.
FULL requires row-value-constructor to be devoid of NULLs before any match returns true.
when neither PARTIAL nor FULL is specified, matches must be exact

STRING NUMERIC FUNCTIONS

Function Returns
POSITION( char-string IN char-string ) Integer: 0 if first string not found in 2nd, or index (starting at 1) where first string is found in 2nd. NOTE: if either string is NULL, result is also null.
EXTRACT ( date-time-field FROM [date-time-value-expr or interval-val-expr] ) Number of date-time-field, which can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE
CHAR[ACTER]_LENGTH ( string-val-expr ) Integer representing number of characters in a string
OCTET_LENGTH ( string-val-expr ) The number of bytes needed to fit the string
BIT_LENGTH ( string-val-expr ) The number of bits needed to fit the string

ROW AND TABLE CONSTRUCTORS

Function Returns

STRING VALUE FUNCTIONS

Function Returns
SUBSTRING( charval-expr FROM startpos [FOR strlen] ) String representing substring from startpos. NOTE: pos starts at 1
UPPER( charval-expr ) String converted to all uppercase
LOWER( charval-expr ) String converted to all lowercase
CONVERT( charval-expr USING form-of-use-conversion-name ) String converted to different representation (mapping is all done in same character set). This changes the collation, for ordering strings.
TRANSLATE( charval-expr USING translation-name ) String converted to another character set. The mapping must have been defined with a CREATE TRANSLATION statement
TRIM( [LEADING or TRAILING or BOTH] [charval-expr FROM] charval-expr ) String, such that 1) if first charval-expr is omitted, the trim character is assumed to be the blank character. 2) This trims all trim-characters at either or both ends of the string. 3) if LEADING or TRAILING is not specified, trimming is done at BOTH ends.
NOTE: all of the above can have [COLLATE FROM collation-name] appended. This associates a collation to the resulting string.
charval-expr || charval-expr String that is the concationation of the 2 strings

SUBQUERIES

Function Returns

VALUE EXPRESSIONS

Function Returns

SCHEMA CREATION

CREATE [[GLOBAL | LOCAL] TEMPORARY] TABLE tablename column-def [tab-constraint], .... [ON COMMIT [DELETE | PRESERVE] ROWS]
The ON COMMIT part is for temporary tables only. column-def is of the form column-name [type [size]] | domain-name | column-contraint | DEFAULT-value | COLLATE collation-name]

SQL ERROR HANDLING

SQLCODE variable has been deprecated because the value is implementation dependent. It is 0 after a successful completion, 100 means no error, but no data was found to operate on. Otherwise, a negative number means an error occurred.
SQLSTATE is a 5-character string. The first 2 digits indicate the error class, and the last 3 indicate the subclass.

SQLSTATE VALUES

Class Subclass
00: successful completion 000: no subclass
01: warning 001: cursor operation conflict
002: disconnect error
003: null value eliminated in set fcn
004: string data, right truncation
005: insufficient item descriptor areas
006: privilege not revoked
007: privilege not granted
008: implicit zero-bit padding
009: search condition too long for information schema
00A: query expression too long for information schema
02: no data 000: no subclass
07: dynamic SQL error 001: using clause does not match dynamic params
002: using clause does not match target specs
003: cursor spec cannot be executed
004: using clause required for dynamic params
005: prepared statement not a cursor spec
006: restricted data type attr. violation
007: using clause required for result fields
008: invalid descriptor count
009: invalid descriptor index
08: connection exception 001: SQL-client unable to establish SQL-connection
002: connection name in use
003: connection does not exist
004: SQL-server rejected establishment of SQL-connection
006: connection failure
007: transaction resolution unknown
0A: feature not supported 001: multiple server transactions
21: cardinality violation 000: no subclass
22: data exception 001: string data, right truncation
002: null value, no indicator
003: numeric value out of range
005: error in assignment
007: invalid datetime format
008: datetime field overflow
009: invalid timezone displacement value
011: substring error
012: division by zero
015: interval field overflow
018: invalid char for cast
019: invalid escape char
021: char not in repertoire
022: indicator overflow
023: invalid param value
024: unterminated C string
025: invalid escape sequence
026: string data, length mismatch
027: trim error
23: integrity constrain violation 000: no subclass
24: invalid cursor state 000: no subclass
25: invalid transaction state 000: no subclass
26: invalid SQL statement 000: no subclass
27: triggered data change violation 000: no subclass
28: invalid authorization specification 000: no subclass
2A: syntax error or access rule violation in direct SQL statement 000: no subclass
2B: dependent privilege descriptors still exist 000: no subclass
2C: invalid character set name 000: no subclass
2D: invalid transaction termination 000: no subclass
2E: invalid connection name 000: no subclass
33: invalid SQL descriptor name 000: no subclass
34: invalid cursor name 000: no subclass
35: invalid condition number 000: no subclass
37: syntax error or access rule violation in dynamic SQL statement 000: no subclass
3C: ambiguous cursor name 000: no subclass
3D: invalid catalog name 000: no subclass
3F: invalid schema name 000: no subclass
40: transaction rollback 001: serialization failure
002: integrity constraint violation
003: statement completion unknown
42: syntax error or access rule violation 000: no subclass
44: with check option violation 000: no subclass
HZ: remote database access N/A: see elsewhere for subclasses

GLOSSARY

Word Definition
AGGREGATE FUNCTION Function that returns asingle value from groups of values. Examples: SUM, MIN, COUNT. Synonym: Set Function.
ATTRIBUTE Synonym for: column
Authorization ID Identifier of an issuer of SQL statements with a body of privileges, corresponding to either a user or a module. Also used to specify the owner of a schema.
Base Table An independent table; not derived from any other table.
Binding Style One of 2 approaches for using SQL:
module language groups SQL procedures in modules that are called from other languages, or
embedded SQL where SQL statements are embedded in the source code for another language.
Bit String Sequence of binary data.
BLOB Synonym for: Binary Large OBject. Essentially a long bit string used to represent complex data.
Boolean Expression An expression that evaluates to TRUE, FALSE, or UNKNOWN.
Candidate Row or Tuple A row subject to test by some predicate, for the purpose of selecting or updating that row.
Cardinality The number of columns in a row (or table)
Cartesian Product The set of all combinations of rows from two tables. SQL can limit this JOIN via predicates.
CATALOG A group of schemas treated as an object. In some database products, the set of tables that define the structure of the database (a schema).
Character Repertoire The set of all characters that can be represented by a character set.
Character Set A set of characters used by a CHARACTER type. This incudes the character repertoire, the internal coding for these characters, and the collating sequence(sorting order).
Clause A functional subunit of a statement that is complete enough for it's effects on the statement to be understood.
Client/Server Architecture A configuration of computers in a network with specialized tasks running on different computers:
client computers interface to users and interface via SQL to
Server computers that perform specialized tasks for the clients.
Closure The property of data maintaining it's structure all the way to it's final state.
Cluster A collection of catalogs (SQL92)
Collation A database object that specifies a collating sequence.
Collating Sequence An order imposed on a character set so that characters can be sorted. Usually corresponding to standard alphabets.
Column A set of corresponding values in a table's rows, each having the same data type and constraints. Synonym for: Attribute
Compilation Unit A body of executable code that is treated as a unit in regards to scope and isolating interface between SQL compilation units and the application via parameters. This is a way to emulate SQL modules.
Complete Condition A runtime occurance that produces a warning, possibly an error, but doesn't prevent statement from executing, and doesn't rollback the current transaction.
Composite Key A key consisting of multiple columns. (Keys can be primary, unique, or foreign).
Concatenated Key Synonym for: composite key
Concurrency Situation where multiple users, possibly doing different operations, simultaneously access the same data.
Constraint A test for data failure (when result is FALSE data is rejected). Note: when result is TRUE or UNKNOWN, data will be accepted.
Correlation Name Temporary name for a base or derived table defined in the FROM clause of a query, and usable only with that query. Synonym for: correlation variable, range variable, or alias
Cursor An object used to store the output of a query for row-by-row processing by the application.
DEFINITION_SCHEMA The set of base tables on which the INFORMATION_SCHEMA is based. Not likely to actually exist, as the INFORMATION_SCHEMA usually only simulates there being a DEFINITION_SCHEMA.
Degree The number of rows in a table
Denormalization Opposite of normalization. This tends to simplify the schema by combining tables. Normalization tends to increase the number of tables.
Deprecated Grudgingly supported. Supported by the current ISO standard to retain compatibility with earlier standards, but likely to be dropped in the future.
Derived Table A table produced by extracting, or deriving, values from other tables, such as from a query. Derived tables can also be defined and treated as objects, as views (created with the FROM clause in a query).
DESCRIPTOR A structured description of an object or privilege, used to specify SQL semantics.... or
an abbreviation for descriptor area
DESCRIPTOR AREA An allocated area of memory containing descriptive information about the input/output parameters of dynamically generated statements. The statement's input/output can also be stored in a descriptor area.
DIAGNOSTIC AREA An allocated area of memory where error messages are stored.
DOMAIN An object that can be used as a data type in specifying a column. It is composed of a data type, and may also define a default value, and constraint(s). Hopefully all values in the domain are comparable.
DYADIC function taking 2 parameters.
DYNAMIC SQL SQL code that is generated at runtime.
EMBEDDED SQL SQL code embedded in the source code of another language.
EQUIJOIN A join that compares values in the joined column(s), retrieving row combinations that make the values equal.
EXCEPTION CONDITION A runtime error that will prevent a SQL statement from executing, causing the containing transaction to be rolled back.
FIELD Alternative name for column. from fields of a record in early databases.
FILE SERVER ARCHITECTURE A computer network that holds the data, but does not process the data, nor perform queries. This architecture has mostly been replaced by the client/server architecture.
FLAG Some implementation-defined mark, in the source code of an application, that indicated whether a SQL statement conforms to the SQL92 standard at a specified level.
FLAGGER The program that puts the flags in the source code.
FOREIGN KEY A group of one or more columns in table, whose values must also be present in another group of one or more columns (the parent key), No duplicate rows in parent table can be present. An alternative to foreign/parent keys is referencing/referenced keys, which is the standard.
FORM-OF-USE Encodings used internally for a character repertoire.
FOURTH GENERATION LANGUAGE (4GL) A language that specifies results of operations, rather than procedures to achieve the results. SQL is a 4GL, but is insufficient in itself to describe applications. In the database world, a non-procedural language that use SQL to interface to the database. 4GLs are not standardized.
GENERALLY UNDERLYING TABLES All the tables referenced in the FROM clause of a query PLUS all the tables referenced by those tables, etc.... down to the leaf-underlying tables.
HIERARCHICAL DATABASE A database where data is represented in a hierarchy (or tree). This old approach is problematic in modifying many-to-many relationships.
HOST LANGUAGE An application development language that either uses embedded SQL or calls procedures contained in a module.
HOST VARIABLE Variable in the host language.
IMPLEMENTATION-DEFINED Implementation of the standard is free to behave as it likes, but its behaviour must be consistent and documented.
IMPLEMENTATION-DEPENDENT Describes an area where the implementation of the standard is free to behave as it likes and need not document its behavior or ensure its consistency.
INDEX An ordered list of single or grouped column values that store the disk locations of rows containing these values. Used to make processing more efficient, and sometimes ensure uniqueness.
INDICATOR PARAMETER A numeric parameter appended to another parameter whenever the other parameter contains a NULL, for use in languages that don't recognize the SQL NULL.
Also used to indicate string truncation.
INDICATOR VARIABLE A numeric variable appended to another variable when that other variable contains a NULL, for use in languages that don't recognize the SQL NULL.
Also used to indicate string truncation.
INFORMATION_SCHEMA A set of views that define the objects in the database, and provide fundamental information about them. It can be accessed with the same statements used to access other tables, but it cannot be directly updated.
INTERVAL A period of time between 2 date/times. When capitalized refers to the corresponding SQL data type.
ISO International Standards Organization. (publisher of SQL 92).
ISOLATION LEVEL Extent to which transaction is affected by actions of other transactions on the same data.
JOIN A combination of 2 or more tables achieved by finding every combination of rows possible, usually
LEAF UNDERLYING TABLES Tables that actually contain the data (and are not derived tables). This is based on the analog of a tree, where a view is the root, other derived tables are nodes, and finally the leaves are the real tables.
LITERAL A value expression that represents itself. Not a variable, parameter, or complex value expression. In SQL92 literals are often preceded by datatype specifications, and may be delimited with single quotes.
LOCK Mechanism that prevents concurrent operations from interfering with each other.
METADATA Data that describes the data. This is the content of the INFORMATION_SCHEMA.
MODULE A set of SQL procedures that may be called by another program. In SQL92, a module may have an authorization ID, and have privileges.
MODULE LANGUAGE Implemented superset of the SQL language.
MONADIC Taking one operand.
MULTICOLUMN KEY Synonym for composite key.
NETWORK DATABASE A database based on a graph structure, where items of data point to other items. This older approach evolved from the hierarchical database structure.
NO-DATA CONDITION A completion condition that indicates a statement executed normally, but had no effect. This corresponds to the NOT FOUND condition used in WHENEVER declarations. This is a SQLSTATE of '02xxx' and a SQLCODE of 100.
NORMAL FORM Any of a number of stages in the process of normalization.
NORMALIZATION The process of analyzing the data to be represented, and breaking it down into separate tables in accordance with the principles of relational structure.
NULL A marker in SQL denoting a value is missing or unknown.
NULLABLE May possibly contain NULLs.
OBJECT An entity in the database that has persistent identity, such as tables, domains, schemas, collations, and assertions. NOT the object as specified in object-oriented programming.
OBJECT-ORIENTED The standard following SQL92 will (does) use this approach, where the program and data are modeled as a group of objects that communicate with each other with messages. The internal structure and processes in each object are invisible to other objects.
OCTET Sequence of 8 bits. A BYTE.
OUTER REFERENCE A reference in a correlated subquery to a value in some containing query. It forces the subquery to be performed separately for each distinct referenced value, producing different results for different rows of the containing query.
PARENT KEY The primary or unique key referenced by a foreign key. The referenced key.
PERMANENT BASE TABLE A base table whose data is retained between SQL sessions.
PERSISTENT BASE TABLE Synonym for PERMANENT BASE TABLE.
POSITIONED DELETION A DELETE statement performed on a cursor using a WHERE CURRENT OF clause.
POSITIONED UPDATE An UPDATE statement performed on a cursor using a WHERE CURRENT OF clause.
POSSIBLY NONDETERMINISTIC QUERY A query whose results are implementation-dependent.
POSSIBLY NULLABLE May contain NULLs. Synonym for NULLABLE.
PRECOMPILER A program that examines and processes source code before it is compiled. Used in embedded SQL Used in embedded SQL to process SQL statements in the source code.
PREDICATE An expression that may have a Boolean value of TRUE, FALSE, or UNKNOWN. Used in certain statements for conditional operations.
PREPARABLE STATEMENT A text string stored in a variable that is capable of being dynamically converted to a valid SQL statement by a PREPARE or EXECUTE IMMEDIATE statement.
PREPARED STATEMENT A SQL statement that has been dynamically generated by a PREPARE or EXECUTE IMMEDIATE statement from a text string stored as a variable.
PREPROCESSOR Synonym for PRECOMPILER.
PRIMARY KEY The group of one or more columns used to uniquely identify each row in a base table. Also the constraint used to enforce that these columns are NOT NULLABLE and UNIQUE.
QUERY A statement that extracts information from the database. Usually selecting information based on a predicate.
RECORD Unit of disk storage corresponding to a row in a base table. Synonym: row.
REFERENTIAL INTEGRITY A state a system has when all foreign key references are valid (corresponding parent key values exist).
RELATION Synonym for table.
RELATIONAL DATABASE A database composed of tables, conforming to all of Codd's 12 rules.
ROW A sequence of values in a table or view, one for each column.
SCHEMA A named group of related objects under control of a single authorization ID.
SCHEMATA Synonym for schemas.
SCOPE The visibility of an object name or statement to various processes. This applies to descriptor areas, temporary tables, correlation names, and prepared statements. Global scope implies visibility to all processes in a session. Local scope implies visibility only to the defining module or compilation unit.
SEARCHED DELETION A DELETE clause that affects all rows satisfying some predicate.
SEARCHED UPDATE An UPDATE clause that affects all rows satisfying some predicate
SELF-JOIN A join of a table to itself.
SEMIRELATIONAL A system that represents data in the form of tables, but does not follow all of Codd's 12 rules. The probable rule not followed is when SQL can be bypassed to access the data.
SEPARATOR A delineator in SQL that indicates divisions between parts of statements. Blanks and newlines are considered equivalent separators. Comments are also separators. Consecutive separators are treated the same as a single separator.
SESSION Set of operations performed by a user during a connection. It starts with an implicit or explicit CONNECT statement, and ends with an implicit or explicit DISCONNECT.
SET FUNCTIONS Synonymous with Aggregate Functions
SIMPLE TARGET SPEC A parameter or variable that is to be assigned a value, but cannot take an appended indicator (and so cannot be NULL).
SIMPLE VALUE EXPRESSION A value expression that cannot take an appended indicator.
SIMPLY UNDERLYING TABLES Tables directly referenced in the FROM clause of a query. This includes views or results of explicit joins or subqueries. (NOT the tables referenced by view, joins or subqueries).
STATEMENT TERMINATOR A special character marking the end of a statement. In interactive SQL, it is ';'. In embedded SQL, it is host-language dependent, and in dynamic SQL, it is omitted.
STATUS PARAMETERS OR VARIABLES Parameters or variables that transfer information regarding results of the preceding statement. Specifically: SQLCODE, SQLSTATE.
STRING A sequence of characters or bits.
SUBQUERY A query used in another statement to produce a set of values to be used by the rest of the statement.
TABLE The fundamental data structure of a relational database: A set of values that can be partitioned into rows and columns, such that there is one (or NULL) value at each row/column intersection. All values in a column must be of the same data type. The columns are named and the rows are identified with a column values comprising a unique identifier (the PRIMARY KEY), unless the table is a derived table. The results of all operations are tables, as the description of the database itself (the Metadata).
TARGET SPECIFICATION A variable or parameter that is to be assigned a value by a SQL statement. May include an indicator variable or parameter.
TEMPORARY BASE TABLE A base table that is automatically destroyed at the end of a transaction or session.
THREE-VALUED-LOGIC (3VL) Variation in boolean logic allowing 3 values: TRUE, FALSE, UNKNOWN (which occurs when NULLs are involved in the comparison).
TRANSACTION A sequence of statements that succeed or fail as a group. When success, the effects are retained. When failure, effects are disregarded.
TRIADIC A 3-parameter function, such as SUBSTRING("glarf" FROM 2 TO 5)
TRUNCATION The disregarding of the rightmost character, bits, or digits of a data item.
TUPLE synonym for row.
UNDERLYING TABLES All tables referenced in a query, PLUS all tables any of these tables reference.... all the way to the leaf-underlaying tables that actually contain the data. syn: underlying tables.
UNIQUE IDENTIFIER Any unique value that identifies an entity. In SQL, imprtant unque identifiers are object names and primary keys.
UNIQUE INDEX An index that forces values to be unique by allowing only one entry (row) per distinct value.
UNIQUE KEY A group of one or more columns that should be unique for logical reasons, rather than for the relational structure. Also, the constraint used to enforce such uniqueness. This differs from the PRIMARY KEY constraint because it allows NULLs.
UNKNOWN (BOOLEAN VALUE) The SQL boolean value that derives from referencing NULLs.
VIEWS (VIEW TABLES) Tables whose content is derived from other tables with the use of a query. Or derived tables with definitions that are persistent in the database.

Key Type	Description
Primary	Set of 1 or more columns, whose combined value uniquely defines each row in a table.
Foreign	Set of 1 or more columns, whose combined value references a row in another table.
Parent	Set of 1 or more columns in another table corresponding to a match from a foreign key. The match must be unique. A system has referential integrity when every foreign key row corresponds to a parent key row.

Type	Description
TableA UNION JOIN *TableB*	All unmatched rows of both tables.
TableA INNER JOIN *TableB*	All matched rows of both tables.
TableA [NATURAL] JOIN *TableB*	An inner join, with column name(s) of foreign and parent key the same.
TableA CROSS JOIN *TableB*	All combinations of rows from both tables.
Outer Joins:
TableA LEFT JOIN *TableB*	All rows of TableA (+ NULLed rows corresponding to TableB) + all matches
TableA RIGHT JOIN *TableB*	All rows of TableB(+ NULLed rows corresponding to TableA) + all matches
TableA FULL JOIN *TableB*	All rows of TableB + all rows with TableA + all matches

Function	Returns
AVG	Return the average of all values. Supported types: INTERVAL and NUMERIC
COUNT	Number of rows produced by a query for a column, disregarding rows with that column NULL.
MAX	Maximum of all values, which can be of any type.
MIN	Minimum of all values, which can be of any type.
SUM	Sum of all values, which can be of type INTERVAL or NUMERIC.
NOTE: each of about are be followed by ([DISTINCT or ALL] value-expression) where DISTINCT eliminates duplicates. ALL is the default.
*COUNT()**	Number of rows produced by a query.

Function	Returns
COLLATIONS	Can be created with the CREATE COLLATION statement, and used in the COLLATE clause. Collations define how comaparisons work when comparing character strings.

type	clause
Table	CONSTRAINT constraint-name followed by any of: PRIMARY KEY (column-name, ...) duplicates and NULLs will be rejected UNIQUE (column-name, ...) duplicates will be rejected FOREIGN KEY (column-name, ...) REFERENCES table-name [(column-name,...)] [referential-spec] implies either PRIMARY KEY (default) or just UNIQUE CHECK PREDICATE predicate when evaluates to FALSE, will be rejected [[INITIALLY DEFERRED \| INITIALLY IMMEDIATE] [NOT] DEFERRABLE]]
Column	[CONSTRAINT constraint-name] followed by any of: NOT NULL PRIMARY KEY duplicates and NULLs will be rejected UNIQUE duplicates will be rejected REFERENCES table-name [(column-name,...)] [referential-spec] CHECK predicate [[INITIALLY DEFERRED \| INITIALLY IMMEDIATE] [NOT] DEFERRABLE]]
Referential (between columns)	[MATCH [FULL \| PARTIAL]] [ON UPDATE [CASCADE \| SET NULL \| SET DEFAULT \| NO ACTION]] [ON DELETE [CASCADE \| SET NULL \| SET DEFAULT \| NO ACTION]]

Type	Description
[NATIONAL] [N]CHAR[ACTER] [(length)] [CHARACTER SET (repertoire-name \| form-of-use-name)]	Fixed-length character string. Default and minimun length is 1. Strings are padded with blanks should they be shorter than the length.
CHAR[ACTER] VARYING (length) [CHARACTER SET (repertoire-name \| form-of-use-name)] VARCHAR (length) [CHARACTER SET (repertoire-name \| form-of-use-name)] or NATIONAL CHAR[ACTER] VARYING (length) or NCHAR VARYING (length)	Length is the maximum length, which must be less than the implementation maximum. String lengths can vary from 0 up to this length.
BIT [(length)]	Binary numbers composed of length bits. Length cannot exceed the implementation maximum. Numbers can be represented in either binary or hexadecimal notation: B"xxxx" or X"xxxx". Default length: 1.
BIT VARYING (length)	Same as BIT, except length can vary.
NUMERIC [(precision [,scale])] DEC[IMAL] [(precision [,scale])] [SMALL]INT[EGER]	These are exact numeric types. Numeric specifies a maximum, decimal a minimum precision (non-integer). INT has implementation defined precision. SMALLINT may have less precision than INT.
FLOAT [(precision)] REAL DOUBLE PRECISION	Approximate numeric types. REAL and DOUBLE PRECISION only use implementation-defined precision.
DATE	Dashes separate fields: yyyy-mm-dd (exactly 10 characters).
TIME [(precision)] [WITH TIME ZONE]	Colons separate fields: hh:mm:ss[.s[s...]][time-zone] where time zone is of form +hh+mm (where + could also be -).
TIMESTAMP [(precision)] [WITH TIME ZONE]	A blank separates the date from the time.
INTERVAL [interval-qualifier]	INTERVAL 'interval-spec' Interval-spec Could be a range: 'start-timestamp TO end-timestamp' or just a timestamp the timestamp could be preceeded by an interval qualifier, such as HOUR or YEAR. When not expressed as a range, it is same as 'time-stamp TO same-time-stamp'.

Function	Returns
CURRENT_DATE	the date according to the system clock
CURRENT_TIME[(precision)]	the time according to the system clock
CURRENT_TIMESTAMP[(precision)]	the date-time according to the system clock

Name of field	Data type
TYPE	integer: < 0 is implementation dependent 1 is CHARACTER, 2 NUMERIC, 4 INTEGER, 5 SMALLINT, 6 FLOAT, 7 REAL, 8 DOUBLE PRECISION, 9 DATE (or TIME or TIMESTAMP), 10 INTERVAL, 12 CHARACTER VARYING, 14 BIT, 15 BIT VARYING
LENGTH	integer
OCTET_LENGTH	integer
RETURNED_LENGTH	integer
RETURNED_OCTET_LENGTH	integer
PRECISION	integer
SCALE	integer
DATETIME_INTERVAL_CODE	integer: if TYPE is INTERVAL, 1 is YEAR, 2 MONTH, 3 DAY, 4 HOUR, 5 MINUTE, 6 SECOND, 7 YEAR TO MONTH, 8 DAY TO HOUR, 9 DAY TO MINUTE, 10 DAY TO SECOND, 11 HOUR TO MINUTE, 12 HOUR TO SECOND, 13 MINUTE TO SECOND. Otherwise it TYPE is a DATE: 1 is DATE, 2 TIME, 3 TIMESTAMP, 4 TIME WITH TIME ZONE, and 5 TIMESTAMP WITH TIME ZONE.
DATETIME_INTERVAL_PRECISION	integer
NULLABLE	integer (1 means yes, 0 means no)
INDICATOR	integer: negative means parameter is NULL
DATA	whatever is specified in TYPE, LENGTH, PRECISION, SCALE, ...
NAME	character string using character set SQL_TEXT with length at least 128
UNNAMED	integer: 0 means SQL syntax dictates what the name is (i.e. column name). 1 means it is implementation-dependent.
COLLATION_CATALOG	character string using character set SQL_TEXT with length at least 128
COLLATION_SCHEMA	character string using character set SQL_TEXT with length at least 128
COLLATION_NAME	character string using character set SQL_TEXT with length at least 128
CHARACTER_SET_CATALOG	character string using character set SQL_TEXT with length at least 128
CHARACTER_SET_SCHEMA	character string using character set SQL_TEXT with length at least 128
CHARACTER_SET_NAME	character string using character set SQL_TEXT with length at least 128

Function	Returns
POSITION ( character-string IN character-string )	NULL if any parameter is NULL in all of these, otherwise if first string is subset of the 2nd: the character position in the 2nd string where 1st match occurs, otherwise 0.
EXTRACT ( datetime-field FROM datetime-or-interval-value-expr )	corresponding integer for YEAR, MONTH, DAY, HOUR, MINUTE, TIMEZONE_HOUR, TIMEZONE_MINUTE, exact numeric for SECOND.
CHAR[ACTER]_LENGTH (string-val-expr)	an integer representing the number of characters in the string, or octets in a bit string.
OCTET_LENGTH (string-val-expr)	number of octets in the string (rounded up in non-integer)
BIT_LENGTH (string-val-expr)	number of bits in a bit string.

Predicate	Returns
NOT predicate	reverse of predicate
( predicate )	predicates can be enclosed in parens
predicate AND/font> predicate	ands 2 predicates
predicate OR/font> predicate	ors 2 predicates
string-val-expr [NOT] LIKE/font> pattern-string-val-expr [ESCAPE esc-char]	First string is searched for 2nd (pattern) string. The 2 must equate for a TRUE result. The pattern can contain the wildcards: '_' represents any character, and '%' represents 0 or more characters in the match value. The ESCAPE char is used when matching a '_' or '%'. When an ESCAPE char is used, it precedes the '_' or '%' to represent these chars in a pattern.
Comparison Predicates: row-val-constructor [=, <, >, <=, >=, <>] row-val-constructor	TRUE when both are non-NULL, and comparison is satisfied. Collations are used when comparing character strings. Intervals and date-times are also comparable. Whenever NULL is encountered, result will be UNKNOWN.
row-value-constructor [NOT] BETWEEN row-val-constructor AND row-val-constructor	when row-value constructors are comparable (must have same number of values), this is the same as RC1 <= RC <= RC2, that is, an include between. NOT is the opposite. NOTE: first row-value-constuctor must be less than 2nd.
row-value-constructor [NOT] IN table sub-query \| (value-expr,...)	true when row-value-constructor is found in table, or list of values
string-val-expr [NOT] LIKE string-val-expr [ ESCAPE char ]	true if first string (match value) exactly matches the second string (the pattern). The pattern can contain '_', which corresponds to any char, and '%' matches any substring. An escape char can be defined when either of these are supposed to be in the pattern. If either the match string, pattern, or escape is NULL, then result is UNKNOWN.
row-val-constructor IS [NOT] NULL	true if all columns are NULL, false otherwise
quantified comparisons: row-val-contructor [ =, <, >, <=, >=, <> ] [ ANY, SOME, ALL ] *table-subquery*	true when all rows meet the criteria. For example, "> ALL" means every row must be greater than the row-val-constructor. When NULLs are involved answer is UNKNOWN. SOME is equivalent to ANY.
EXISTS table-subquery	true if subquery produces at least one row, false otherwise
UNIQUE table-subquery	true when no duplicate rows (except those with NULLs) are produced by query; false otherwise
row-value-constructor MATCH [UNIQUE] [PARTIAL \| FULL] table-subquery	true when row-value-constructor matches any row in subquery table, similar to IN, but allows for partial matches (involving partially NULL rows): when row-value-constructor is completely NULL, result is true in all cases UNIQUE yields true when only one non-NULL value matches. FULL requires row-value-constructor to be devoid of NULLs before any match returns true. when neither PARTIAL nor FULL is specified, matches must be exact

Function	Returns
POSITION( char-string IN char-string )	Integer: 0 if first string not found in 2nd, or index (starting at 1) where first string is found in 2nd. NOTE: if either string is NULL, result is also null.
EXTRACT ( date-time-field FROM [date-time-value-expr or interval-val-expr] )	Number of date-time-field, which can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE
CHAR[ACTER]_LENGTH ( string-val-expr )	Integer representing number of characters in a string
OCTET_LENGTH ( string-val-expr )	The number of bytes needed to fit the string
BIT_LENGTH ( string-val-expr )	The number of bits needed to fit the string

Function	Returns
SUBSTRING( charval-expr FROM startpos [FOR strlen] )	String representing substring from startpos. NOTE: pos starts at 1
UPPER( charval-expr )	String converted to all uppercase
LOWER( charval-expr )	String converted to all lowercase
CONVERT( charval-expr USING form-of-use-conversion-name )	String converted to different representation (mapping is all done in same character set). This changes the collation, for ordering strings.
TRANSLATE( charval-expr USING translation-name )	String converted to another character set. The mapping must have been defined with a CREATE TRANSLATION statement
TRIM( [LEADING or TRAILING or BOTH] [charval-expr FROM] charval-expr )	String, such that 1) if first charval-expr is omitted, the trim character is assumed to be the blank character. 2) This trims all trim-characters at either or both ends of the string. 3) if LEADING or TRAILING is not specified, trimming is done at BOTH ends.
NOTE: all of the above can have [COLLATE FROM collation-name] appended. This associates a collation to the resulting string.
charval-expr \|\| charval-expr	String that is the concationation of the 2 strings

Class	Subclass
00: successful completion	000: no subclass
01: warning	001: cursor operation conflict 002: disconnect error 003: null value eliminated in set fcn 004: string data, right truncation 005: insufficient item descriptor areas 006: privilege not revoked 007: privilege not granted 008: implicit zero-bit padding 009: search condition too long for information schema 00A: query expression too long for information schema
02: no data	000: no subclass
07: dynamic SQL error	001: using clause does not match dynamic params 002: using clause does not match target specs 003: cursor spec cannot be executed 004: using clause required for dynamic params 005: prepared statement not a cursor spec 006: restricted data type attr. violation 007: using clause required for result fields 008: invalid descriptor count 009: invalid descriptor index
08: connection exception	001: SQL-client unable to establish SQL-connection 002: connection name in use 003: connection does not exist 004: SQL-server rejected establishment of SQL-connection 006: connection failure 007: transaction resolution unknown
0A: feature not supported	001: multiple server transactions
21: cardinality violation	000: no subclass
22: data exception	001: string data, right truncation 002: null value, no indicator 003: numeric value out of range 005: error in assignment 007: invalid datetime format 008: datetime field overflow 009: invalid timezone displacement value 011: substring error 012: division by zero 015: interval field overflow 018: invalid char for cast 019: invalid escape char 021: char not in repertoire 022: indicator overflow 023: invalid param value 024: unterminated C string 025: invalid escape sequence 026: string data, length mismatch 027: trim error
23: integrity constrain violation	000: no subclass
24: invalid cursor state	000: no subclass
25: invalid transaction state	000: no subclass
26: invalid SQL statement	000: no subclass
27: triggered data change violation	000: no subclass
28: invalid authorization specification	000: no subclass
2A: syntax error or access rule violation in direct SQL statement	000: no subclass
2B: dependent privilege descriptors still exist	000: no subclass
2C: invalid character set name	000: no subclass
2D: invalid transaction termination	000: no subclass
2E: invalid connection name	000: no subclass
33: invalid SQL descriptor name	000: no subclass
34: invalid cursor name	000: no subclass
35: invalid condition number	000: no subclass
37: syntax error or access rule violation in dynamic SQL statement	000: no subclass
3C: ambiguous cursor name	000: no subclass
3D: invalid catalog name	000: no subclass
3F: invalid schema name	000: no subclass
40: transaction rollback	001: serialization failure 002: integrity constraint violation 003: statement completion unknown
42: syntax error or access rule violation	000: no subclass
44: with check option violation	000: no subclass
HZ: remote database access	N/A: see elsewhere for subclasses

Word	Definition
AGGREGATE FUNCTION	Function that returns asingle value from groups of values. Examples: SUM, MIN, COUNT. Synonym: Set Function.
ATTRIBUTE	Synonym for: column
Authorization ID	Identifier of an issuer of SQL statements with a body of privileges, corresponding to either a user or a module. Also used to specify the owner of a schema.
Base Table	An independent table; not derived from any other table.
Binding Style	One of 2 approaches for using SQL: module language groups SQL procedures in modules that are called from other languages, or embedded SQL where SQL statements are embedded in the source code for another language.
Bit String	Sequence of binary data.
BLOB	Synonym for: Binary Large OBject. Essentially a long bit string used to represent complex data.
Boolean Expression	An expression that evaluates to TRUE, FALSE, or UNKNOWN.
Candidate Row or Tuple	A row subject to test by some predicate, for the purpose of selecting or updating that row.
Cardinality	The number of columns in a row (or table)
Cartesian Product	The set of all combinations of rows from two tables. SQL can limit this JOIN via predicates.
CATALOG	A group of schemas treated as an object. In some database products, the set of tables that define the structure of the database (a schema).
Character Repertoire	The set of all characters that can be represented by a character set.
Character Set	A set of characters used by a CHARACTER type. This incudes the character repertoire, the internal coding for these characters, and the collating sequence(sorting order).
Clause	A functional subunit of a statement that is complete enough for it's effects on the statement to be understood.
Client/Server Architecture	A configuration of computers in a network with specialized tasks running on different computers: client computers interface to users and interface via SQL to Server computers that perform specialized tasks for the clients.
Closure	The property of data maintaining it's structure all the way to it's final state.
Cluster	A collection of catalogs (SQL92)
Collation	A database object that specifies a collating sequence.
Collating Sequence	An order imposed on a character set so that characters can be sorted. Usually corresponding to standard alphabets.
Column	A set of corresponding values in a table's rows, each having the same data type and constraints. Synonym for: Attribute
Compilation Unit	A body of executable code that is treated as a unit in regards to scope and isolating interface between SQL compilation units and the application via parameters. This is a way to emulate SQL modules.
Complete Condition	A runtime occurance that produces a warning, possibly an error, but doesn't prevent statement from executing, and doesn't rollback the current transaction.
Composite Key	A key consisting of multiple columns. (Keys can be primary, unique, or foreign).
Concatenated Key	Synonym for: composite key
Concurrency	Situation where multiple users, possibly doing different operations, simultaneously access the same data.
Constraint	A test for data failure (when result is FALSE data is rejected). Note: when result is TRUE or UNKNOWN, data will be accepted.
Correlation Name	Temporary name for a base or derived table defined in the FROM clause of a query, and usable only with that query. Synonym for: correlation variable, range variable, or alias
Cursor	An object used to store the output of a query for row-by-row processing by the application.
DEFINITION_SCHEMA	The set of base tables on which the INFORMATION_SCHEMA is based. Not likely to actually exist, as the INFORMATION_SCHEMA usually only simulates there being a DEFINITION_SCHEMA.
Degree	The number of rows in a table
Denormalization	Opposite of normalization. This tends to simplify the schema by combining tables. Normalization tends to increase the number of tables.
Deprecated	Grudgingly supported. Supported by the current ISO standard to retain compatibility with earlier standards, but likely to be dropped in the future.
Derived Table	A table produced by extracting, or deriving, values from other tables, such as from a query. Derived tables can also be defined and treated as objects, as views (created with the FROM clause in a query).
DESCRIPTOR	A structured description of an object or privilege, used to specify SQL semantics.... or an abbreviation for descriptor area
DESCRIPTOR AREA	An allocated area of memory containing descriptive information about the input/output parameters of dynamically generated statements. The statement's input/output can also be stored in a descriptor area.
DIAGNOSTIC AREA	An allocated area of memory where error messages are stored.
DOMAIN	An object that can be used as a data type in specifying a column. It is composed of a data type, and may also define a default value, and constraint(s). Hopefully all values in the domain are comparable.
DYADIC	function taking 2 parameters.
DYNAMIC SQL	SQL code that is generated at runtime.
EMBEDDED SQL	SQL code embedded in the source code of another language.
EQUIJOIN	A join that compares values in the joined column(s), retrieving row combinations that make the values equal.
EXCEPTION CONDITION	A runtime error that will prevent a SQL statement from executing, causing the containing transaction to be rolled back.
FIELD	Alternative name for column. from fields of a record in early databases.
FILE SERVER ARCHITECTURE	A computer network that holds the data, but does not process the data, nor perform queries. This architecture has mostly been replaced by the client/server architecture.
FLAG	Some implementation-defined mark, in the source code of an application, that indicated whether a SQL statement conforms to the SQL92 standard at a specified level.
FLAGGER	The program that puts the flags in the source code.
FOREIGN KEY	A group of one or more columns in table, whose values must also be present in another group of one or more columns (the parent key), No duplicate rows in parent table can be present. An alternative to foreign/parent keys is referencing/referenced keys, which is the standard.
FORM-OF-USE	Encodings used internally for a character repertoire.
FOURTH GENERATION LANGUAGE (4GL)	A language that specifies results of operations, rather than procedures to achieve the results. SQL is a 4GL, but is insufficient in itself to describe applications. In the database world, a non-procedural language that use SQL to interface to the database. 4GLs are not standardized.
GENERALLY UNDERLYING TABLES	All the tables referenced in the FROM clause of a query PLUS all the tables referenced by those tables, etc.... down to the leaf-underlying tables.
HIERARCHICAL DATABASE	A database where data is represented in a hierarchy (or tree). This old approach is problematic in modifying many-to-many relationships.
HOST LANGUAGE	An application development language that either uses embedded SQL or calls procedures contained in a module.
HOST VARIABLE	Variable in the host language.
IMPLEMENTATION-DEFINED	Implementation of the standard is free to behave as it likes, but its behaviour must be consistent and documented.
IMPLEMENTATION-DEPENDENT	Describes an area where the implementation of the standard is free to behave as it likes and need not document its behavior or ensure its consistency.
INDEX	An ordered list of single or grouped column values that store the disk locations of rows containing these values. Used to make processing more efficient, and sometimes ensure uniqueness.
INDICATOR PARAMETER	A numeric parameter appended to another parameter whenever the other parameter contains a NULL, for use in languages that don't recognize the SQL NULL. Also used to indicate string truncation.
INDICATOR VARIABLE	A numeric variable appended to another variable when that other variable contains a NULL, for use in languages that don't recognize the SQL NULL. Also used to indicate string truncation.
INFORMATION_SCHEMA	A set of views that define the objects in the database, and provide fundamental information about them. It can be accessed with the same statements used to access other tables, but it cannot be directly updated.
INTERVAL	A period of time between 2 date/times. When capitalized refers to the corresponding SQL data type.
ISO	International Standards Organization. (publisher of SQL 92).
ISOLATION LEVEL	Extent to which transaction is affected by actions of other transactions on the same data.
JOIN	A combination of 2 or more tables achieved by finding every combination of rows possible, usually
LEAF UNDERLYING TABLES	Tables that actually contain the data (and are not derived tables). This is based on the analog of a tree, where a view is the root, other derived tables are nodes, and finally the leaves are the real tables.
LITERAL	A value expression that represents itself. Not a variable, parameter, or complex value expression. In SQL92 literals are often preceded by datatype specifications, and may be delimited with single quotes.
LOCK	Mechanism that prevents concurrent operations from interfering with each other.
METADATA	Data that describes the data. This is the content of the INFORMATION_SCHEMA.
MODULE	A set of SQL procedures that may be called by another program. In SQL92, a module may have an authorization ID, and have privileges.
MODULE LANGUAGE	Implemented superset of the SQL language.
MONADIC	Taking one operand.
MULTICOLUMN KEY	Synonym for composite key.
NETWORK DATABASE	A database based on a graph structure, where items of data point to other items. This older approach evolved from the hierarchical database structure.
NO-DATA CONDITION	A completion condition that indicates a statement executed normally, but had no effect. This corresponds to the NOT FOUND condition used in WHENEVER declarations. This is a SQLSTATE of '02xxx' and a SQLCODE of 100.
NORMAL FORM	Any of a number of stages in the process of normalization.
NORMALIZATION	The process of analyzing the data to be represented, and breaking it down into separate tables in accordance with the principles of relational structure.
NULL	A marker in SQL denoting a value is missing or unknown.
NULLABLE	May possibly contain NULLs.
OBJECT	An entity in the database that has persistent identity, such as tables, domains, schemas, collations, and assertions. NOT the object as specified in object-oriented programming.
OBJECT-ORIENTED	The standard following SQL92 will (does) use this approach, where the program and data are modeled as a group of objects that communicate with each other with messages. The internal structure and processes in each object are invisible to other objects.
OCTET	Sequence of 8 bits. A BYTE.
OUTER REFERENCE	A reference in a correlated subquery to a value in some containing query. It forces the subquery to be performed separately for each distinct referenced value, producing different results for different rows of the containing query.
PARENT KEY	The primary or unique key referenced by a foreign key. The referenced key.
PERMANENT BASE TABLE	A base table whose data is retained between SQL sessions.
PERSISTENT BASE TABLE	Synonym for PERMANENT BASE TABLE.
POSITIONED DELETION	A DELETE statement performed on a cursor using a WHERE CURRENT OF clause.
POSITIONED UPDATE	An UPDATE statement performed on a cursor using a WHERE CURRENT OF clause.
POSSIBLY NONDETERMINISTIC QUERY	A query whose results are implementation-dependent.
POSSIBLY NULLABLE	May contain NULLs. Synonym for NULLABLE.
PRECOMPILER	A program that examines and processes source code before it is compiled. Used in embedded SQL Used in embedded SQL to process SQL statements in the source code.
PREDICATE	An expression that may have a Boolean value of TRUE, FALSE, or UNKNOWN. Used in certain statements for conditional operations.
PREPARABLE STATEMENT	A text string stored in a variable that is capable of being dynamically converted to a valid SQL statement by a PREPARE or EXECUTE IMMEDIATE statement.
PREPARED STATEMENT	A SQL statement that has been dynamically generated by a PREPARE or EXECUTE IMMEDIATE statement from a text string stored as a variable.
PREPROCESSOR	Synonym for PRECOMPILER.
PRIMARY KEY	The group of one or more columns used to uniquely identify each row in a base table. Also the constraint used to enforce that these columns are NOT NULLABLE and UNIQUE.
QUERY	A statement that extracts information from the database. Usually selecting information based on a predicate.
RECORD	Unit of disk storage corresponding to a row in a base table. Synonym: row.
REFERENTIAL INTEGRITY	A state a system has when all foreign key references are valid (corresponding parent key values exist).
RELATION	Synonym for table.
RELATIONAL DATABASE	A database composed of tables, conforming to all of Codd's 12 rules.
ROW	A sequence of values in a table or view, one for each column.
SCHEMA	A named group of related objects under control of a single authorization ID.
SCHEMATA	Synonym for schemas.
SCOPE	The visibility of an object name or statement to various processes. This applies to descriptor areas, temporary tables, correlation names, and prepared statements. Global scope implies visibility to all processes in a session. Local scope implies visibility only to the defining module or compilation unit.
SEARCHED DELETION	A DELETE clause that affects all rows satisfying some predicate.
SEARCHED UPDATE	An UPDATE clause that affects all rows satisfying some predicate
SELF-JOIN	A join of a table to itself.
SEMIRELATIONAL	A system that represents data in the form of tables, but does not follow all of Codd's 12 rules. The probable rule not followed is when SQL can be bypassed to access the data.
SEPARATOR	A delineator in SQL that indicates divisions between parts of statements. Blanks and newlines are considered equivalent separators. Comments are also separators. Consecutive separators are treated the same as a single separator.
SESSION	Set of operations performed by a user during a connection. It starts with an implicit or explicit CONNECT statement, and ends with an implicit or explicit DISCONNECT.
SET FUNCTIONS	Synonymous with Aggregate Functions
SIMPLE TARGET SPEC	A parameter or variable that is to be assigned a value, but cannot take an appended indicator (and so cannot be NULL).
SIMPLE VALUE EXPRESSION	A value expression that cannot take an appended indicator.
SIMPLY UNDERLYING TABLES	Tables directly referenced in the FROM clause of a query. This includes views or results of explicit joins or subqueries. (NOT the tables referenced by view, joins or subqueries).
STATEMENT TERMINATOR	A special character marking the end of a statement. In interactive SQL, it is ';'. In embedded SQL, it is host-language dependent, and in dynamic SQL, it is omitted.
STATUS PARAMETERS OR VARIABLES	Parameters or variables that transfer information regarding results of the preceding statement. Specifically: SQLCODE, SQLSTATE.
STRING	A sequence of characters or bits.
SUBQUERY	A query used in another statement to produce a set of values to be used by the rest of the statement.
TABLE	The fundamental data structure of a relational database: A set of values that can be partitioned into rows and columns, such that there is one (or NULL) value at each row/column intersection. All values in a column must be of the same data type. The columns are named and the rows are identified with a column values comprising a unique identifier (the PRIMARY KEY), unless the table is a derived table. The results of all operations are tables, as the description of the database itself (the Metadata).
TARGET SPECIFICATION	A variable or parameter that is to be assigned a value by a SQL statement. May include an indicator variable or parameter.
TEMPORARY BASE TABLE	A base table that is automatically destroyed at the end of a transaction or session.
THREE-VALUED-LOGIC (3VL)	Variation in boolean logic allowing 3 values: TRUE, FALSE, UNKNOWN (which occurs when NULLs are involved in the comparison).
TRANSACTION	A sequence of statements that succeed or fail as a group. When success, the effects are retained. When failure, effects are disregarded.
TRIADIC	A 3-parameter function, such as SUBSTRING("glarf" FROM 2 TO 5)
TRUNCATION	The disregarding of the rightmost character, bits, or digits of a data item.
TUPLE	synonym for row.
UNDERLYING TABLES	All tables referenced in a query, PLUS all tables any of these tables reference.... all the way to the leaf-underlaying tables that actually contain the data. syn: underlying tables.
UNIQUE IDENTIFIER	Any unique value that identifies an entity. In SQL, imprtant unque identifiers are object names and primary keys.
UNIQUE INDEX	An index that forces values to be unique by allowing only one entry (row) per distinct value.
UNIQUE KEY	A group of one or more columns that should be unique for logical reasons, rather than for the relational structure. Also, the constraint used to enforce such uniqueness. This differs from the PRIMARY KEY constraint because it allows NULLs.
UNKNOWN (BOOLEAN VALUE)	The SQL boolean value that derives from referencing NULLs.
VIEWS (VIEW TABLES)	Tables whose content is derived from other tables with the use of a query. Or derived tables with definitions that are persistent in the database.