SQL Language Guide
1. Introducing SQL
ANSI Compliance
Interactive SQL
Line-Based Terminal Monitor
Object Naming Rules
User Names
Regular and Delimited Identifiers
Case Sensitivity of Identifiers
Restrictions on Identifiers
Comment Delimiters
Statement Terminators
Correlation Names
Correlation Name Rules
Security Levels
2. SQL Data Types
Data Types
Char Data Type
Varchar Data Type
Nchar Data Type
Nvarchar Data Type
Integer Data Types
Decimal Data Type
Floating Point Data Types
Float Point Limitations
Money Data Type
Ansidate Data Type
Time Data Types
Timestamp Data Types
Interval Data Types
Boolean Data Type
IP Network Address Data Types
Universal Unique Identifier (UUID)
Storage Formats of Data Types
Literals
String Literals
Hexadecimal Representation
Quotes within Strings
Unicode Literals
Numeric Literals
Integer Literals
Decimal Literals
Floating Point Literals
Date and Time Literals
Date Literals
Time Literals
Timestamp Literals
Interval Literals
Boolean Literals
Constants
Nulls
Nulls and Comparisons
Nulls and Aggregate Functions
3. Elements of SQL Statements
SQL Operators
Arithmetic Operators
Comparison Operators
Logical Operators
SQL Operations
String Concatenation Operations
Assignment Operations
Character String Assignments
String Truncation
Numeric Assignments
Date and Time Assignments
Null Value Assignments
Arithmetic Operations
Default Type Conversion
Arithmetic Operations on Decimal Data Types
Date and Time Arithmetic
ANSI Date and Time Comparisons
Operator Coercion Rules
SQL Functions
Aggregate Functions
Basic Aggregate Functions
Regression and Correlation Analysis Aggregate Functions
String Aggregate Functions
Ordering Aggregate Functions
Aggregate Functions and Decimal Data
GROUP BY Clause with Aggregate Functions
Restrictions on the Use of Aggregate Functions
Window Functions
Window Function Syntax
Windowing Aggregate Functions
Analytical Functions
Numeric Functions
String Functions
String Functions and the UTF8 Character Set
Date and Time Functions
Conversion Functions
Length of Results for Data Type Conversions
Bitwise Functions
Hashing Functions
Random Number Functions
Null Handling Functions
IP Network Address Functions
UUID Functions
UUID_CREATE( ) Function
UUID_COMPARE(uuid1, uuid2) Function
UUID_TO_CHAR(u) Function
UUID_FROM_CHAR(c) Function
DBMSINFO Function
Dbmsinfo Examples
Request Names for DBMSINFO Function
Expressions in SQL
CASE Expressions
DECODE Function
IF, NULLIF, and COALESCE Functions
GREATEST, GREATER, LEAST, LESSER Functions
CAST Expressions
Predicates in SQL
Comparison Predicate
Pattern-matching Predicates
LIKE Predicate
BEGINNING, CONTAINING, and ENDING Predicates
BETWEEN Predicate
IN Predicate
Any-or-All Predicate
EXISTS Predicate
IS NULL Predicate
IS INTEGER Predicate
IS DECIMAL Predicate
IS FLOAT Predicate
IS TRUE, IS FALSE, IS UNKNOWN Predicates
Search Conditions in SQL Statements
Subqueries
Subqueries in the WHERE Clause
Subqueries in the FROM Clause (Derived Tables)
Derived Table Syntax
Subqueries in the Target List, SET and VALUE Clauses (Scalar Subqueries)
Scalar Subqueries
Common Table Expressions (CTEs)
Common Table Expression Format
Components of a CTE
How CTEs Are Used
Examples of Using CTEs: The Person and Relative Tables
Referring to a Defined WITH Element
Using Nested WITH Element References
WITH Elements in CREATE VIEW
4. SQL Statements
Statement Contexts
ALTER GROUP
ALTER GROUP Examples
ALTER LOCATION
ALTER LOCATION Examples
ALTER PROFILE
ALTER PROFILE Examples
ALTER ROLE
ALTER ROLE Examples
ALTER SECURITY_AUDIT
ALTER SECURITY_AUDIT Examples
ALTER TABLE
Constraint Specifications
Named Constraints
Rules and Restrictions on Renaming Tables
Rules and Restrictions on Renaming Columns
ALTER TABLE Examples
ALTER USER
ALTER USER Examples
CALL VECTORWISE
CLEANUP_UNUSED_FILES Command
CONDENSE_LOG Command
FORCE_TRANSACTION_DISALLOW_COMMIT Command
SETCONF Command
TERMINATE Command
TOTAL_DB_SIZE Command
VWLOG_RELOAD Command
VWLOG_ROTATE Command
COMMENT ON
COMMENT ON Examples
COMMIT
CONNECT
COPY
Unformatted Copying
Formatted Copying
Column Formats for COPY
Character (Text) Formats
Counted Character Formats
Dummy Format
Binary Formats
COPY Format Details
Delimiters in the Data File
WITH NULL Clause for COPY
Filename Specification for COPY
Windows File Types for COPY
With Clause Options for COPY
Filename Specification for COPY
COPY Examples
COPY VWLOAD
COPY VWLOAD Example
CREATE DBEVENT
CREATE GROUP
CREATE GROUP Examples
CREATE INDEX
CREATE INDEX Examples
CREATE LOCATION
CREATE LOCATION Example
CREATE PROFILE
CREATE PROFILE Examples
CREATE ROLE
CREATE ROLE Examples
CREATE SECURITY_ALARM
CREATE SECURITY_ALARM Examples
CREATE STATISTICS
CREATE STATISTICS Example
CREATE SYNONYM
CREATE TABLE
Column Specification
DEFAULT Clause
Restrictions on the Default Value for a Column
IDENTITY Columns
Constraints
Table-level and Column-level Constraints
WITH Clause
Security_audit Option
Partitioned Tables
Partitioning Schemes
Partitioning Syntax
Guidelines for Partitioned Tables
CREATE TABLE Examples
CREATE USER
CREATE USER Examples
CREATE VIEW
CREATE VIEW Example
DECLARE GLOBAL TEMPORARY TABLE
DECLARE GLOBAL TEMPORARY TABLE Examples
DELETE
DELETE Examples
DISABLE SECURITY_AUDIT
DISABLE SECURITY_AUDIT Example
DROP
DROP Examples
DROP DBEVENT
DROP DBEVENT Example
DROP GROUP
DROP GROUP Examples
DROP LOCATION
DROP LOCATION Example
DROP PROFILE
DROP PROFILE Example
DROP ROLE
DROP ROLE Example
DROP SECURITY_ALARM
DROP SECURITY_ALARM Examples
DROP STATISTICS
DROP STATISTICS Examples
DROP SYNONYM
DROP USER
ENABLE SECURITY_AUDIT
ENABLE SECURITY_AUDIT Example
GET DBEVENT
GRANT (privilege)
Object Privileges
TABLE Privileges
TABLE Privileges for Views
DATABASE Privileges
PROCEDURE Privileges
DBEVENT Privileges
SEQUENCE Privileges
Privilege Defaults
GRANT ALL PRIVILEGES Option
Installation and Database Privileges
Other Privileges
Granting All Privileges on Views
GRANT OPTION Clause
GRANT (PRIVILEGE) Examples
GRANT (role)
GRANT (role) Example
HELP
HELP Examples
INQUIRE_SQL
INQUIRE_SQL Example
INSERT
INSERT Examples
INSERT INTO EXTERNAL CSV
INSERT INTO EXTERNAL CSV Examples
MERGE
MERGE Examples
MODIFY
MODIFY...TO COMBINE Statement
MODIFY...TO COMBINE Examples
MODIFY...TO RECONSTRUCT Statement
MODIFY...TO RECONSTRUCT Examples
MODIFY Examples
RAISE DBEVENT
Description
REGISTER DBEVENT
REGISTER TABLE
Security Log File Format
REGISTER TABLE Example
REMOVE DBEVENT
REMOVE TABLE
REMOVE TABLE Example
RENAME TABLE
RENAME TABLE Example
REVOKE
Revoking Grant Option
Restrict versus Cascade
REVOKE Examples
ROLLBACK
SELECT
SELECT Statement Clauses
SELECT Clause
FROM Clause
WHERE Clause
GROUP BY Clause
HAVING Clause
ORDER BY Clause
UNION Clause
INTERSECT Clause
EXCEPT Clause
WITH Clause for SELECT
Query Evaluation
Syntax for Specifying Tables and Views
Joins
Join Relationships
ANSI/ISO Join Syntax
SELECT Examples
SET
SET Options
UPDATE
UPDATE Examples
WITH (common_table_expression)
Guidelines for Using CTEs
WITH (common_table_expression) Embedded Usage
WITH (common_table_expression) Examples
A. Terminal Monitor
Terminal Monitor
Start the Terminal Monitor
Terminal Monitor Query Buffering
Terminal Monitor Commands
Terminal Monitor Character Input and Output
The HELP Statement
Terminal Monitor Macros
Macro Concepts
System Macros
Macro Evaluation
Macro Parameters
Parameter Prescan
Using Tab or Newline
Use of Quotes
Use of Backslashes
Special {define} Processing
Terminal Monitor Macro Examples
Actian Vector 5.0
Terminal Monitor Macro Examples