The following conventions are used to distinguish between different types of symbols:
The usual BNF notation is used. Optional arguments are enclosed in square brace ([]). If an object is followed by elipses (...), it is repeated 0 or more times. The vertical bar (|) is used to indicate a choice of objects among a group. Curly braces ({}) are used to group a set of choices.
UserDefinedName ::= letter [ digit | letter | _ ]... DataType ::= CharacterStringType | ExactNumericType | ApproximateNumericType | BinaryType | DateType | TimeType | TimestampType CharacterStringType ::= CHARACTER( length ) | CHAR( length ) | CHARACTER VARYING( length ) | VARCHAR( length ) | LONG VARCHAR ExactNumericType ::= DECIMAL( precision, scale ) | NUMERIC( precision, scale ) | BIT | TINYINT | SMALLINT | INTEGER | BIGINT ApproximateNumericType ::= FLOAT | DOUBLE PRECISION | REAL BinaryType ::= BINARY( length ) | VARBINARY( length ) | LONG VARBINARY DateType ::= DATE TimeType ::= TIME TimestampType ::= TIMESTAMP
ALTER TABLE TableName
{
ADD ColumnIdentifier DataType
| ADD ( UserDefinedName DataType [ , UserDefinedName DataType ]... )
}
Description
CREATE [ UNIQUE ] INDEX IndexName ON BaseTableName ( ColumnIdentifier [ ASC | DSC ] [ , ColumnIdentifier [ ASC | DSC ] ]... )Description
CREATE TABLE BaseTableName ( ColumnElement [ , ColumnElement ]... ) ColumnElement ::= ColumnDefinition | TableConstraintDefinition ColumnDefinition ::= ColumnIdentifier DataType [ DEFAULT DefaultValue ] [ ColumnConstraintDefinition [ , ColumnConstraintDefinition ]... ] ColumnConstraintDefinition ::= NOT NULL | UNIQUE | INDEX | PRIMARY KEY | REFERENCES BaseTableName ReferencedColumns | CHECK SearchCondition DefaultValue ::= Literal | NULL | USER TableConstraintDefinition ::= UNIQUE ( ColumnIdentifier [ , ColumnIdentifier ]... ) | INDEX ( ColumnIdentifier [ , ColumnIdentifier ]... ) | PRIMARY KEY ( ColumnIdentifier [ , ColumnIdentifier ]... ) | CHECK ( SearchCondition ) | FOREIGN KEY ReferencingColumns REFERENCES BaseTableName ReferencedColumns ReferencingColumns ::= ( ColumnIdentifier [ , ColumnIdentifier ]... ) ReferencedColumns ::= ( ColumnIdentifier [ , ColumnIdentifier ]... )Description
The column constraint options are:
CREATE TABLE CUSTOMER ( NAME CHAR(100), CUSTOMER_NUMBER INTEGER PRIMARY KEY ) CREATE TABLE PURCHASES ( PURCHASE_DATE DATE, CUSTOMER_NUMBER INTEGER REFERENCES CUSTOMER CUSTOMER_NUMBER ITEMNUMBER INTEGER, ... )
The table constraint definition options are:
CREATE VIEW ViewedTableName [ ( ColumnIdentifier [ , ColumnIdentifier ]... ) ] AS QuerySpecification [ WITH CHECK OPTION ]Description
Depending on the SELECT statement, a view may or may not be
updatable
(that is, usable in an INSERT, UPDATE, or DELETE statement). A view is updatable if the
following conditions are met:
If
View insertions and updates are subject to the table and column
constraints of the underlying base table(s). An insert/update on a view may fail because
the value of a UNIQUE field is duplicated, even though
this value is not displayed when doing SELECTs on the
view (due to the WHERE clause of the CREATE VIEW statement).
If the view definition
does not include a base table column which has the NOT
NULL attribute, then all insertions fail for that view.
If the table owner grants ALL privileges to a user, the user
can do everything the table owner can do, except DROP TABLE, DROP INDEX, CREATE INDEX, and ALTER TABLE.
However, is the user granting the privileges is not the table owner, that user can only grant
privileges which the user has and for which the user has authorization to grant. That is, if
user X creates a table A, and user X grants to user Y SELECT privileges on A, then is user Y
grants ALL privileges on A to user Z, user Z has only SELECT privileges.
If the user has INSERT privilege, he can insert rows into the table. If the user has SELECT
privileges, he can use the table in SELECT statements. If the user has UPDATE privileges, he
can update rows in the table. If UPDATE is followed by the option list of column names, then he
can only update those columns; if the column name list is omitted, he may update all columns.
The WITH GRANT OPTION clause specifies whether or not the user may grant privileges to other users.
Note that WITH GRANT OPTION is part of ANSI SQL but is not part of ODBC SQL.
For example, user X owns table A and grants ALL privileges to user Y WITH GRANT OPTION. User Y can
then grant all privileges to other users. On the other hand, suppose that X grants SELECT
privilege to Y WITH GRANT OPTION, and grants INSERT privilege to Y (without the grant option).
User Y can then grant SELECT to user Z, but user Y cannot grant INSERT to user Z.
There is a special user name, PUBLIC, which is used to grant privileges to every user. For example,
user X owns table A and grants SELECT privilege to PUBLIC. Any user can then use table A in a
SELECT statement. Note, however, that any user other than X must use the syntax
X.A for the table. Granting privileges to PUBLIC allows every user to access the table, but does
not make every user an owner of the table.
Note that one GRANT statement can be used to grant privileges to more than one user. GRANT does
not allow granting of privileges to PUBLIC and specific users in the same statement.
If user X has REFERENCES privilege on table A, user X can create a table Y with references to table
A. REFERENCES is followed by an optional column list. If the column list is omitted, X can reference
any keys in A. If the column list is present, X can only reference the named columns in A.
CQL implements extensions to the SelectList, SearchCondition,
and OrderByClause constructs. These contructs will appear twice in this
section. The first time that they appear, only the ANSI features are
described. Subsequently, the CQL extensions for these constructs are
explained. This is intended to make the distinction between ANSI features and
extensions easy to determine. The second appearance of the construct includes
a complete syntax expression, including both ANSI features and extensions.
The following sections begin with the simplest SELECT
statement, and illustrate SELECT capabilities in
progressively complex examples. These examples use the
sample files S, P, and SP which are created and populated
by the CQL++ script newf supplied with CQL++. The CREATE TABLE
and INSERT statements for S, P, and SP are:
Instead of using *, one or more field names can be used. For example:
Note also that if JOHN does not own a table S, then
Suppose, however, that the current authorization identifier is JOHN, and the
SELECT statement is
The condition SP.SNO = S.SNO tells CQL how to join tables
SP and S. The results of the query are identical if it is written
The following example joins table S to itself. The operation
can be expressed in words as "Get all pairs of supplier
numbers such that the two suppliers are located in the same
city."
Joins are not limited to two tables. The FIPS SQL test suite includes a
SELECT where 10 tables are joined.
The format here is
The result of the SubQuery is effectively a result set.
CQL then tests whether SNO is equal to ANY
of the values retrieved by the SubQuery. If SNO is
equal to at least one of the values retrieved by the subquery,
then = ANY is true.
Here is another example, using < ANY.
The significance of the interblock reference is that the SubQuery
must be performed for each row in the outer
query. Since the value from the outer query changes,
the inner query results are different for each outer
query row.
Examples
The following list provides each function name, its arguments, and a brief description.
The following list provides each function name, its arguments, and a brief description.
The following list provides each function name, its arguments, and a brief description.
The following list provides each function name, its arguments, and a brief description.
To advance the report to the next page, use BREAK PAGE, for example:
COMPUTE can be combined with BREAK or BREAK PAGE. For example:
CQL allows the default pattern string to be replaced by a pattern string explicitly included in
a SELECT statement. The syntax is:
CQL allows the default column heading to be replaced by a
column heading explicitly included in a SELECT statement.
The syntax is:
The value types must match the types of the columns
associated with the table. CHAR fields must be initialized
with quoted strings. Numeric columns must be initialized
with appropriate numeric data (the expected automatic conversions are
provided). DATE columns must be initialized with valid DATE values. TIME columns
must be initialized with valid TIME values. TIMESTAMP columns must be initialized
with a valid DATE, TIME, or TIMESTAMP value.
The ValueExpression types must match the table column
types. CHAR fields must be initialized with CHAR
ValueExpressions. Numeric columns must be initialized with
appropriate numeric data (the expected conversions are made
automatically). DATE columns must be initialized with valid DATE values.
Positioned UPDATE is used only in C programs.
The referenced cursor must be open and not at beginning or end of file.
DROP INDEX
Syntax
DROP INDEX IndexName
Description
Drop index drops an index from a base table. Only the owner of the table can
drop the index. After dropping the index, CQL++ does not rebuild the index file.
To reclaim the space occupied by the index, an off line index rebuild is required.
DROP TABLE
Syntax
DROP TABLE BaseTableName
[ CASCADE | RESTRICT ]
Description
DROP TABLE is used to delete a table. If the RESTRICT modifier is used, then DROP TABLE
fails if the table is referenced by any view or if any integrity constraints exist which reference
the table.
If the CASCADE modifier is used, then any
tables which have constraints which become invalid are also dropped. CASCADE should be used
with care. If neither CASCADE nor RESTRICT is used, the table is dropped unconditionally and
no other tables are affected, which may result in INSERT or UPDATE failures (if any invalid
references exist).
DROP VIEW
Syntax
DROP VIEW BaseTableName
[ CASCADE | RESTRICT ]
Description
DROP VIEW is used to delete a view. If the RESTRICT modifier is used, then DROP VIEW
fails if the view is referenced by any other view or is used in an integrity constraint for any
other view or table.
If the CASCADE modifier is used, then any
views and tables which have constraints which become invalid are also dropped. CASCADE should be used
with care. If neither CASCADE nor RESTRICT is used, the view is dropped unconditionally and
no other tables or views are affected, which may result in INSERT or UPDATE failures (if any invalid
references exist).
GRANT
Syntax
GRANT { ALL | GrantPrivilege [ , GrantPrivilege ]... }
ON TableName
TO { PUBLIC | UserName [ , UserName ]... }
GrantPrivilege ::=
DELETE
| INSERT
| SELECT
| UPDATE [ ( ColumnIdentifier [ , ColumnIdentifier ]... ) ]
| REFERENCES [ ( ColumnIdentifier [ , ColumnIdentifier ]... ) ]
[ WITH GRANT OPTION ]
Description
GRANT is used to allow other users access to a table.
REVOKE
Syntax
REVOKE { ALL | RevokePrivilege [ , RevokePrivilege ]... }
ON TableName
FROM { PUBLIC | UserName [ , UserName ]... }
[ CASCADE | RESTRICT]
RevokePrivilege ::=
DELETE
| INSERT
| SELECT
| UPDATE
| REFERENCES
REVOKE reverses the effects of GRANT. It removes privileges from one or more users (or PUBLIC).
If RESTRICT is used, the REVOKE fails if it creates invalid references. If CASCADE is used, any
tables whose references become invalid are dropped. If neither CASCADE no RESTRICT is used,
the privileges are revoked and any "illegal" references continue to exist.
Data Manipulation Language
The SQL term DML (data manipulation language) refers to statements which select, insert, update, or
delete data.
SELECT Statement
Syntax
SELECT [ ALL | DISTINCT ] SelectList
FROM TableReferenceList
[ WHERE SearchCondition ]
[ GROUP BY ColumnName [ , ColumnName ]... ]
[ HAVING SearchCondition ]
[UNION SelectStatement ]
[ OrderByClause ]
Description
The SELECT statement is the heart of the SQL language, and it has many
variations and forms. The ANSI features are described first, followed by
the CQL extensions. Readers who are not familiar with the SQL language
may wish to consult a user level SQL book for a more detailed description of
the standard features.
CREATE TABLE S (SNO CHAR(5) NOT NULL,
SNAME CHAR(20),
STATUS DECIMAL(3),
CITY CHAR(15),
UNIQUE (SNO));
CREATE TABLE P (PNO CHAR(6) NOT NULL,
PNAME CHAR(20),
COLOR CHAR(6),
WEIGHT DECIMAL(3),
CITY CHAR(15),
UNIQUE (PNO));
CREATE TABLE SP (SNO CHAR(5) NOT NULL,
PNO CHAR(6) NOT NULL,
QTY DECIMAL(5),
UNIQUE (SNO,PNO));
INSERT INTO S (SNO,SNAME,STATUS,CITY)
VALUES ('S1','Smith',20,'London');
INSERT INTO S (SNO,SNAME,STATUS,CITY)
VALUES ('S2','Jones',10,'Paris');
INSERT INTO S (SNO,SNAME,STATUS,CITY)
VALUES ('S3','Blake',30,'Paris');
INSERT INTO S (SNO,SNAME,STATUS,CITY)
VALUES ('S4','Clark',20,'London');
INSERT INTO S (SNO,SNAME,STATUS,CITY)
VALUES ('S5','Adams',30,'Athens');
INSERT INTO P (PNO,PNAME,COLOR,WEIGHT,CITY)
VALUES ('P1','Nut','Red',12,'London');
INSERT INTO P (PNO,PNAME,COLOR,WEIGHT,CITY)
VALUES ('P2','Bolt','Green',17,'Paris');
INSERT INTO P (PNO,PNAME,COLOR,WEIGHT,CITY)
VALUES ('P3','Screw','Blue',17,'Rome');
INSERT INTO P (PNO,PNAME,COLOR,WEIGHT,CITY)
VALUES ('P4','Screw','Red',14,'London');
INSERT INTO P (PNO,PNAME,COLOR,WEIGHT,CITY)
VALUES ('P5','Cam','Blue',12,'Paris');
INSERT INTO P (PNO,PNAME,COLOR,WEIGHT,CITY)
VALUES ('P6','Cog','Red',19,'London');
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S1','P1',300);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S1','P2',200);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S1','P3',400);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S1','P4',200);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S1','P5',100);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S1','P6',100);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S2','P1',300);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S2','P2',400);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S3','P2',200);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S4','P2',200);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S4','P4',300);
INSERT INTO SP (SNO,PNO,QTY) VALUES ('S4','P5',400);
COMMIT WORK;
Simple SELECT
The simplest SELECT statement is:
SELECT * FROM S
The asterisk (*) means all fields. This statement prints all
rows in S.
SELECT PNO FROM SP
SELECT PNO,QTY FROM SP
The Authorization Identifier Prefix
In a SELECT statement, you may use an expression of the form
Authorization.TableName in place of TableName.
For example, instead of
SELECT * FROM S
you can use
SELECT * FROM CQL.S
The authorization identifier in this type of expression is used with
the current authorization identifier to determine whether SELECT access is
allowed. The form ... FROM S... is only allowable when the current
authorization identifier owns the table. For example, if the current
authorization identifier is CQL, then the statement
SELECT * FROM S
is equivalent to
SELECT * FROM CQL.S
and access is allowed. If the current authorization identifier is JOHN,
then the statement
SELECT * FROM S
is equivalent to
SELECT * FROM JOHN.S
Note that JOHN.S is a different table than CQL.S. It is perfectly
legal to have two tables with the same "last" name.
SELECT * FROM JOHN.S
is an error. It does not matter whether JOHN has SELECT privileges for
table CQL.S. Finally, note that
SELECT * FROM S
does not default to CQL.S, regardless of any privileges JOHN may have for
table CQL.S.
SELECT * FROM CQL.S
This is allowed if JOHN has SELECT privilege for table CQL.S. This is true
if one of the following is true:
SELECT with Condition
To limit the retrieved data to certain rows a SearchCondition or
WHERE clause is used. For example:
SELECT SNO FROM S WHERE CITY = 'Paris'
The equals operator (=) means "is equal to". The comparison operators are:
More than one comparison operator can be used with the AND and OR operators.
For example:
SELECT SNO FROM S
WHERE CITY='Paris' AND STATUS > 20
Parentheses can be used to specify the order of evaluation. Without
parentheses, AND is stronger than OR. The keyword NOT can be used to
reverse the sense of any operation, for example:
SELECT SNO FROM S
WHERE ( CITY = 'Paris' OR CITY = 'London' )
AND NOT ( STATUS > 20 )
The second set of parentheses are not required. As in any programming
language, it is best to use parentheses to be explicit about what you
want to machine to do, if only to enhance program readability.
SELECT with DISTINCT - Eliminating Duplicate Rows
The keyword DISTINCT is used to eliminate duplicate rows
in the retrieved data. For example:
SELECT DISTINCT PNO FROM SP
DISTINCT can be used with any query, regardless of complexity, to remove
duplicate logical rows from the output.
SELECT with Two Tables
More than one table can be accessed with a SELECT
statement. This is sometimes referred to as the ability to
"join" tables. In CQL, two (or more) tables may be joined, and
a table may also be joined to itself. For
example, the following query can be described in words as
"for each part supplied, get part number and names of all
cities supplying the part." (See Introduction to Database Systems,
C.J. Date, Addison-Wesley (third edition).)
SELECT DISTINCT PNO,CITY FROM SP,S WHERE
SP.SNO=S.SNO
Two tables are used, S and SP. Since both tables have a
field named SNO, the table name is used as a prefix to specify
which SNO we are referring to. Thus, SP.SNO refers to the
SNO field associated with file SP, while S.SNO refers to the
SNO field associated with file S. The table name prefix can
be used even if the column name is unambiguous.
SELECT DISTINCT PNO,CITY FROM S,SP WHERE S.SNO=SP.SNO
or
SELECT DISTINCT PNO,CITY FROM SP,S WHERE SP.SNO=S.SNO
The query results are not sensitive to the sequence of table names or
to the order in which the columns are mentioned in the WHERE clause. However,
while the same rows are retrieved, they may be retrieved in a different order.
The order of the returned rows is undefined (although the ANSI specification
requires that it always be the same for a given query). To force the logical
rows to be returned in a known sequence, use the ORDER BY clause.
SELECT FIRST.SNO, SECOND.SNO
FROM S FIRST, S SECOND
WHERE FIRST.CITY = SECOND.CITY AND
FIRST.SNO < SECOND.SNO
The files are specified as S FIRST and S SECOND. FIRST
and SECOND are aliases (or correlation names) for S. The
first "incarnation" of S is subsequently referred to as FIRST,
and the second "incarnation" is subsequently referred to as
SECOND. The second comparison clause, FIRST.SNO < SECOND.SNO, eliminates
pairs where both rows are the same, and eliminates duplicates. For example,
the row where FIRST.SNO='S1' AND SECOND.SNO='S2' is selected, but the
row where FIRST.SNO='S4' and SECOND.SNO='S1' is not selected.
SELECT with ANY
Consider the following two queries, which both produce the
same result set. The operation is "Get supplier names for
suppliers who supply part P2."
SELECT DISTINCT SNAME FROM S,SP
WHERE S.SNO=SP.SNO AND SP.PNO='P2'
SELECT SNAME FROM S WHERE SNO = ANY
SELECT SNO FROM SP WHERE PNO='P2'
The second form introduces the ANY keyword, which can
be used after all the comparison operators.
SELECT SNAME FROM S WHERE SNO = ANY ( SubQuery )
where SubQuery is an entire additional select
statement (actually, a SelectExpression which is similar to
a select statement but cannot include the ORDER BY, GROUP BY, or HAVING
clauses).
SELECT SNO FROM S WHERE STATUS < ANY
(SELECT STATUS FROM S)
This finds values of SNO for suppliers whose status is less
than the status of at least one other supplier.
SELECT with IN
IN is used with sub-queries and is the same as = ANY. For example:
SELECT SNAME FROM S WHERE SNO IN
(SELECT SNO FROM SP WHERE PNO='P2')
SELECT with Three Levels
A SubQuery can reference another SubQuery. For example:
SELECT SNAME FROM S WHERE SNO IN
(SELECT SNO FROM SP WHERE PNO IN
(SELECT PNO FROM P WHERE COLOR='Red'))
The number of levels allowed in a SELECT statement
is 3. This can be increased, if desired, using a compile time switch.
SELECT with Interblock Reference
An interblock reference occurs when a SubQuery uses a
value (in its SearchCondition) of a column from an
outer query. For example, this query is a variation on a
previous example:
SELECT SNAME FROM S WHERE 'P2' IN
(SELECT PNO FROM SP WHERE SNO=S.SNO)
Incidentally, this form is less efficient because of
the interblock reference. However, there are many
queries which cannot be performed without an
interblock reference. An query with an interblock reference is sometimes
referred to as a "correlated subquery".
Interblock Reference - Same Table in Both SELECTs
In this query, the interblock reference is to two incarnations
of the same table.
SELECT DISTINCT PNO FROM SP SPX WHERE PNO IN
(SELECT PNO FROM SP WHERE SNO <> SPX.SNO)
This may be phrased "Gets part numbers for all parts
supplied by more than one supplier". For each part (each
outer query row), is there another supplier of that part (is
the SubQuery empty)? This type of query cannot be
performed without the interblock reference.
SELECT with ALL
The ALL keyword is used like the ANY keyword, and can
follow any of the comparison operators. The
following query "gets supplier names for suppliers who do
not supply part P2".
SELECT SNAME FROM S WHERE 'P2' <> ALL
(SELECT PNO FROM SP WHERE SNO=S.SNO)
Note again the use of the interblock reference.
SELECT with Comparison Operator and SubQuery
The comparison operators can
reference a SubQuery without ALL or ANY. For example,
the following query "gets supplier numbers for suppliers who
are located in the same city as supplier S1".
SELECT SNO FROM S WHERE CITY =
(SELECT CITY FROM S WHERE SNO='S1')
Note that subqueries in this context must return exactly
one value. If zero values or two or more values are returned,
an error occurs.
SELECT with EXISTS
EXISTS is used with subqueries to determine whether any
values exist for a particular condition. The following query
is another example of "get supplier names for suppliers who
supply part P2".
SELECT SNAME FROM S WHERE EXISTS
(SELECT * FROM SP WHERE SNO=S.SNO AND PNO='P2')
Subqueries associated with EXISTS usually use SELECT *,
since all that matters is whether any rows are found by the
SubQuery. The meaning of EXISTS can be reversed with
the keyword NOT. For example, the following query "gets
supplier names for suppliers who do not supply part P2".
SELECT SNAME FROM S WHERE NOT EXISTS
(SELECT * FROM SP WHERE SNO=S.SNO AND PNO='P2')
Here is a three level query using NOT EXISTS. The query
"gets supplier names for suppliers who supply all parts".
SELECT SNAME FROM S WHERE NOT EXISTS
(SELECT * FROM P WHERE NOT EXISTS
(SELECT * FROM SP WHERE SNO=S.SNO AND PNO=P.PNO))
To understand how this works, the following is an alternate
word expression of the query: "Select supplier names for
suppliers such that there does not exist a part that they do
not supply". Note that the innermost SubQuery is correlated
to both the outermost query and the middle query.
SELECT with Computed Values
The FieldExpression in a SELECT statement may be an
expression rather than a simple field name. For example:
SELECT PNO, WEIGHT*454 FROM P
Supported operations are addition, subtraction, multiplication, division,
and parentheses. Some other mathematical capabilities are provided by
SQL functions, described in the next section.
SELECT with ANSI Functions
There are five ANSI functions: SUM, AVG, MIN, MAX, and
COUNT. These functions are called
"aggregate" functions because they make sense only when applied to more than
one row. Aggregate functions can be used in the list of
FieldExpressions or in a HAVING clause (described later).
Aggregate functions cannot be used in a WHERE clause.
SELECT with COUNT Function
Syntax
COUNT ( expression ) | COUNT (*)
Counts the number of rows produced by a SELECT. COUNT
is the only function which may be used with *. DISTINCT
can be used to count the number of non-identical rows.
SELECT COUNT(*) FROM S
SELECT COUNT(*) FROM SP WHERE PNO='P2'
SELECT with SUM Function
Syntax
SUM ( NumericExpression )
ResultTypes
InputTypes
Description
SUM accumulates the total of its argument over the rows selected the query.
For example,
SELECT SUM(QTY) FROM SP WHERE PNO = 'P2'
Computes the sum of the QTYs for rows in SP whose part number is 'P2'.
SELECT with AVG Function
Syntax
AVG ( NumericExpression )
Result Types
Input Types
AVG is the average function. For example:
SELECT AVG(QTY) FROM SP
Prints the average of all the QTY values in SP.
SELECT AVG(QTY) FROM SP WHERE PNO = 'P2'
Prints the average of QTY values whose PNO value is 'P2'.
SELECT with MIN Function
Syntax
MIN ( NumericExpression )
Result Types
Input Types
Description
MIN is the minimum value function. For example:
SELECT MIN(QTY) FROM SP
Prints the lowest QTY value in SP.
SELECT MIN(QTY) FROM SP WHERE PNO = 'P2'
Prints the lowest QTY value from rows in SP whose PNO
value is 'P2'.
SELECT with MAX Function
Syntax
MAX ( NumericExpression )
Input Types
Input Types
MAX is the maximum value function. For example:
SELECT MAX(QTY) FROM SP
prints the highest QTY value in SP.
SELECT MAX(QTY) FROM SP WHERE PNO = 'P1'
prints the highest QTY value from rows in SP whose PNO value is 'P1'.
Select with String Functions
This section describes the string scalar functions. These functions are ODBC extensions. In the
descriptions which follow, sexp means string expression.
Numeric Functions
This section describes the numeric scalar functions. These functions are ODBC extensions. In the
descriptions which follow, iexp means integer expression, nexp means numeric expression,
and fexp means float expression. For trignometric functions, angles are expressed in radians.
Time and Date Functions
This section describes functions acting on DATE, TIME, and TIMESTAMP columns. First, the
ODBC time and date functions are described. Second, the CQL++ extension time and date functions are
described. In these descriptions, dexp means date expression, texp means
time expression, and tsexp means timestamp expression.
ODBC Time and Date Functions
CQL++ Extension Time and Date Functions
SELECT with BETWEEN
The BETWEEN operator provides a natural syntax for a condition which is greater than one value
and less than another. For example,
SELECT * FROM S WHERE STATUS BETWEEN 15 AND 25
This is identical in meaning to
SELECT * FROM S
WHERE STATUS >= 15 AND STATUS <= 25
SELECT with LIKE
The LIKE operator provides pattern matching capability for strings. The pattern is specified in a
manner similar to Unix regular expressions, except that the symbols are different.
For those familiar with Unix regular expressions, SQL uses % instead of *, and _ instead of the period.
For those unfamiliar with Unix regular expressions, the following list describes each special symbol.
Thus, '%s' matches any string which ends is s.
'abc' matches only the string abc. 'a_c' matches any
three character string which begins with a
and ends with c, such as abc, azc, etc.
'a%c' matches any string (of whatever
length) which begins with a and ends
with c, for example, abc,
abdefghijklmnopqrc, ac, etc.
Here is an example of an SQL statement using LIKE:
SELECT * FROM S WHERE SNAME LIKE '%k'
In sample table S, this retrieves data for Clark, which ends in k.
SELECT with GROUP BY
GROUP BY allows records in a file to be grouped, usually for
subtotals but also for display purposes. For example, "for each part supplied, get
the part number and the total quantity supplied of that part".
SELECT PNO,SUM(QTY) FROM SP GROUP BY PNO
The GROUP BY clause causes a subtotal to be taken and a line printed whenever the value of PNO
changes.
If there is no explicit ORDER BY clause, then the query is ordered by
the GROUP BY fields. If an explicit ORDER BY clause is used, the results
may not be what you were expecting.
SELECT with GROUP BY and HAVING
The HAVING clause is a conditional expression used to eliminate some
groups from a report. For example, "get part numbers for all parts
supplied by more than one supplier".
SELECT PNO FROM SP
GROUP BY PNO HAVING
COUNT(*) > 1
The function within the HAVING clause applies to the group. Thus
within a HAVING clause "SUM(QTY) > 100" may be true for a group
although the individual values of QTY may be less than 100.
SELECT with UNION
The UNION operator is used to combine the results of two SELECT statements.
The columns selected by each statement must match with respect to type. For
example (from A Guide to the SQL Standard, C.J. Date, First Edition, P84):
SELECT P.PNO FROM P WHERE P.WEIGHT > 16
UNION
SELECT SP.PNO FROM SP WHERE SP.SNO = 'S2'
The UNION operator is different from other SQL operators with respect to
its behavior with duplicate rows. For SELECT, duplicate rows are selected
unless the DISTINCT qualifier is used. For UNION, duplicate rows are
rejected unless the ALL qualifier is used. For example:
SELECT P.PNO FROM P WHERE P.WEIGHT > 16
UNION ALL
SELECT SP.PNO FROM SP WHERE SP.SNO = 'S2'
SELECT with INTERSECTION
The INTERSECTION operator finds the intersecting rows from two complete SELECT statements.
That is, only rows which are retrieved by both statements are retrieved.
INTERSECTION is a CQL extension and is not part of ANSI or ODBC SQL.
SELECT with MINUS
The MINUS operator finds the rows in one SELECT statement, and
then removes from the result any of those rows which are also rows from
the second SELECT statement. MINUS is a CQL extension and is not part
of ANSI or ODBC SQL.
SELECT with ORDER BY
This section describes the various features of the ORDER BY clause.
The standard ANSI features are described first, followed by the CQL
extensions.
ANSI ORDER BY Features
In combination with the features described previously, the order of the resulting table may
be specified with an ORDER BY clause. The format is either
ORDER BY ColumnName [ , ColumnName ]...
or
ORDER BY ColumnName [ , ColumnName ]... DESC
DESC means descending, and reverses the order of the output. Examples are:
SELECT * FROM S ORDER BY STATUS
SELECT SNO,STATUS FROM S
WHERE CITY='Paris'
ORDER BY STATUS DESC
ORDER BY with NODUP
NODUP prints blanks in a column if the value to be printed is the same
as the value for the preceeding line. This is often desirable when
ordering by a column for which there are multiple report lines for
each column value. The syntax is:
ORDER BY ColumnName NODUP
For example,
SELECT * FROM S ORDER BY CITY NODUP
ORDER BY with BREAK
The BREAK keyword allows the user to skip lines between groups of
a report. It is similar to GROUP BY, except that it allows the user to
control how many lines are skipped. The syntax is:
ORDER BY ColumnName BREAK NumericExpression
where NumericExpression is the number of lines. For example,
SELECT * FROM S ORDER BY CITY BREAK
or
SELECT SNO, SNAME, CITY FROM S
ORDER BY CITY DESC BREAK 2
SELECT * FROM S ORDER BY CITY BREAK PAGE
ORDER BY with COMPUTE
COMPUTE is used to print some value or computation when an ordering field changes.
This is most frequently used for printing subtotals. The syntax is:
ORDER BY ColumnName
COMPUTE ValueExpression
AT ColumnNumber
For example,
SELECT SNO, SNAME, STATUS, CITY
FROM S
ORDER BY CITY COMPUTE SUM(STATUS) AT 3
Note that the aggregate functions (SUM, AVG, MIN, and MAX) are
allowed in the COMPUTE statement.
SELECT SNO, SNAME, STATUS, CITY
FROM S
ORDER BY CITY BREAK 2
COMPUTE SUM(STATUS) AT 3
BREAK PAGE
SELECT with AT END REPORT
The AT END REPORT clause is used to print values or calculations at the
end of a report. The syntax is similar to the COMPUTE clause. AT
END REPORT is often used to print totals (or grand totals). Aggregate
functions are allowed in the AT END REPORT clause. The syntax is:
SELECT ... AT END REPORT
[ BREAK [ NumericExpression | PAGE ] ]
COMPUTE ValueExpression
AT ColumnNumber
For example,
SELECT * FROM S
AT END REPORT
COMPUTE SUM(STATUS) AT 3
Some variations:
SELECT * FROM S
AT END REPORT BREAK 2
COMPUTE SUM(STATUS) AT 3
SELECT * FROM S
WHERE STATUS > 10
AT END REPORT BREAK PAGE
COMPUTE SUM(STATUS) AT 3
Report Generation Enhancements
This section describes CQL features which allow control of the format of
a report produced by a SELECT statement. All the features
described are extensions to ANSI and ODBC SQL and are not available on
other SQL systems.
NODUP Modifier
The user can follow a ValueExpression with the
keyword NODUP, which prints a blank if the value of a column is the
same as the value of that column on the preceeding line. The syntax is:
SELECT ValueExpression NODUP ...
For example,
SELECT SNO, SNAME, CITY NODUP FROM S
Pattern String Override
In CQL, each column has a pattern string associated with it.
This pattern string, produced from the column attributes length, scale, and precision, controls
the format of that column in select statements.
SELECT ValueExpression % PatternString ...
For example,
SELECT SNAME % 'XXXXXXXX' FROM S
X allows any character, and the width is 8 because there
are 8 X's. This could have been written as '10X'.
For complete pattern string details, see PatternStrings.
Column Heading Override
In CQL, each column has a column heading associated with it.
This column heading is usually the column name, or the expression
being displayed (e.g., "STATUS + 2").
SELECT ValueExpression ColumnHeading ...
For example,
SELECT SNAME 'Supplier Name' FROM S
The symbol | has a special meaning within column
headings. It is used to split a heading onto several lines.
The preceeding example with a two line heading is:
SELECT SNAME 'Supplier|Name' FROM S
Column Justification
By default, all columns are left justified in a SELECT statement.
CQL allows the user to specify centering or right justification as part of the SELECT.
The syntax is:
SELECT ValueExpression JustificationSpecifier ...
JustificationSpecifier:
CENTER | RIGHT
For example,
SELECT SNAME CENTER, STATUS RIGHT FROM S
Wrapping
For CHAR columns or expressions, CQL normally truncates the output if the width is greater than
the column width (as specified by the pattern string). The user can specify wrapping or word wrapping
in the SELECT statement. The syntax is:
SELECT ValueExpression WrapSpecifier...
WrapSpecifier ::=
WRAPPED | WORD_WRAPPED
For example,
SELECT CITY WRAPPED % 'XXX' FROM S
Note the use of the pattern string override. Since the
default pattern string for CITY specifies the maximum width
allowed for CITY, wrapping won't occur unless the default
pattern string is overridden (except for VARCHAR columns).
Summary of Column Specification Features
The syntax of the SELECT column specification is:
ValueExpression
[ NODUP ]
[ ColumnHeading ]
[ LEFT | CENTER | RIGHT ]
[ WRAPPED | WORD_WRAPPED ]
[ % PatternOverride ]
INSERT, UPDATE, and DELETE
This section describes the form of the statements used to insert data into
tables, update table data, and delete data from tables. For all the
statements in this section, changes are not permanent until a
COMMIT WORK statement is executed. Unwanted changes may be removed using
the ROLLBACK WORK statement.
The INSERT INTO Statement
There are two forms of the INSERT INTO statement. The first form uses a
list of inserted values. The second form uses a SELECT statement to specify
values to be retrieved from another table and inserted.
INSERT INTO with Values
Syntax
INSERT INTO TableName
[ ( ColumnName [ , ColumnName ]... ) ]
VALUES ( value, [ , value ]... )
INSERT INTO is used to insert data into a table. The list of
column names following INSERT INTO TableName is
optional. If the list is included, the number of values must
be the same as the number of column names listed. If the
list is omitted, then the number of values must be the same
as the number of columns in the table.
INSERT INTO with SELECT
Syntax
INSERT INTO TableName1
[ ( ColumnName [ , ColumnName ]... ) ]
SELECT ValueExpression [ , ValueExpression ]...
FROM TableName2
WHERE SearchCondition
This statement inserts the results of the SELECT from TableName2 into
TableName1. The list of column names following INSERT INTO
TableName is optional. If the list is included, the number of
ValueExpressions in the SELECT statement must be the
same as the number of column names listed. If the list is
omitted, then the number of ValueExpressions in the
SELECT statement must be the same as the number of
columns in the table.
The UPDATE Statements
There are two forms of the UPDATE statement. The first form, called
"searched" update, uses a WHERE clause to specify the rows to be updated,
and can be used in C programs and in the interpreter. The second form,
called "positioned" update, can only be used in C programs to update
the "current logical row" of an open cursor.
The Searched UPDATE Statement
Syntax
UPDATE TableName
SET ColumnName = ValueExpression [ , ColumnName = ValueExpression ]...
WHERE SearchCondition
The searched UPDATE modifies one or more rows satisfying a WHERE clause.
The values provided must match the type of the table columns involved.
CHAR columns must be SET with CHAR expressions, numeric columns must be initialized with
numeric data (the expected conversions are made automatically),
and DATE fields must be initialized with valid date values.
Positioned UPDATE
Syntax
UPDATE TableName
SET ColumnName = ValueExpression [ , ValueExpression ]...
WHERE CURRENT OF CursorName
The "current logical row" of cursor CursorName is updated.
The expressions must match the types of the column names.
Character string fields must be assigned character string values,
and numeric fields must be assigned numeric values (the expected conversions are made automatically).
Only floating point fields can be assigned values expressed in exponential notation.
The DELETE Statements
There are two forms of the DELETE statement, known as "searched" delete
and "positioned" delete. Searched delete removes records satisfying
a WHERE clause. Positioned delete deletes the "current logical row" of
an open cursor.
Searched DELETE
Syntax
DELETE FROM TableName WHERE SearchCondition
The searched DELETE can delete 0 rows (if none match the search condition), or one or more rows.
In the interpreter, CQL displays the number of records deleted.
Positioned DELETE
Syntax
DELETE FROM TableName
WHERE CURRENT OF CursorName
Cursor CursorName must be opened and must not be at beginning of
file or end of file.
Statements Specific to Embedded SQL
This section describes statements used in embedded SQL files.
Embedded SQL files are C++ (or C) files containing EXEC SQL statements.
The SQL preprocessor pp translates these files into ordinary C++ (or C) files.
Embedded SQL statements follow the SQL syntax described in this chapter, surrounded by
EXEC SQL and ending with a semicolon. Embedded SQL statements can also reference host variables,
which are C++ or C variables defined in the source file. To make these variables known to the SQL
preprocessor, their declarations are contained within an SQL declare section.
A declare section begins with the statement:
EXEC SQL BEGIN DECLARE SECTION;
and ends with the statement
EXEC SQL END DECLARE SECTION;
For example,
EXEC SQL BEGIN DECLARE SECTION;
char x[10];
short y;
EXEC SQL END DECLARE SECTION;
Host variables x and y are then referred to within SQL statements as
:x and :y. For example,
EXEC SQL SELECT A,B INTO :x, :y FROM XYZ WHERE C='constant1';
After this statement is executed, the values of columns A and B are in x and y,
respectively.