Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

1 Using SQL

1.1 SQL Overview

1.1.1 Examples

1.1.2 Oracle SQL and SQL-92

1.1.2.1 Running SQL-92 on Oracle Lite

1.2 Oracle Lite SQL and Oracle SQL Comparison

1.2.1 Objects

1.2.2 Operators

1.2.3 Functions

1.2.4 Commands

1.2.5 Miscellaneous Data Definition Language (DDL)

1.2.6 Datatypes

1.2.7 Indicator Variables

1.2.8 Data Precision During Arithmetic Operations

1.2.9 Data Dictionaries

1.2.10 Tables Not Installed with Oracle Lite

1.2.11 Messages

1.2.12 Sequences

1.2.13 PL/SQL

1.2.14 SQL Functions

1.2.15 Locking and Transactions

1.3 Oracle Lite SQL Conventions

1.3.1 SQL Statement Syntax

1.3.1.1 Capital Letters

1.3.1.2 Lowercase

1.3.1.3 Bracket Delimited

1.3.1.4 Braces

1.3.1.5 Vertical Bars

1.3.1.6 Ellipsis

1.3.1.7 Underline

1.3.1.8 Block Letters

1.3.1.9 Initial Colon

1.3.2 SQL Tables

1.3.3 SQL Object Names

1.3.4 SQL Operator Precedence

1.3.5 SQL Sessions

1.3.6 SQL Transactions

1.3.7 Issuing SQL Statements From a Program

1.3.8 SQL and ODBC

1.4 ODBC SQL Syntax Conventions

1.5 Oracle Lite Database Object Naming Conventions

1.6 Formats

1.6.1 Number Format Elements

1.6.2 Date Format Elements

1.7 Specifying SQL Conditions

1.7.1 Simple Comparison Conditions

1.7.2 Group Comparison Conditions

1.7.2.1 A Row_Value_Constructor in a Subquery Comparison

1.7.2.2 Subquery in Place of a Column

1.7.3 Membership Conditions

1.7.4 Range Conditions

1.7.5 NULL Conditions

1.7.6 EXISTS Conditions

1.7.7 LIKE Conditions

1.7.8 Compound Conditions

1.8 Specifying Expressions

1.8.1 Form I, Simple Expression

1.8.2 Form II, Function Expression

1.8.3 Form III, Java Function Expression

1.8.4 Form IV, Compound Expression

1.8.5 Form V, DECODE Expression

1.8.6 Form VI, Expression List

1.8.7 Form VII, Variable Expression

1.8.8 Form VIII, CAST Expression

1.9 Oracle Lite SQL Datatypes and Literals

1.9.1 Character String Comparison Rules

1.9.1.1 Blank-Padded Comparison Semantics

1.9.1.2 Nonpadded Comparison Semantics

1.10 Comments Within SQL Statements

2 SQL Operators

2.1 SQL Operators Overview

2.1.1 Unary Operators

2.1.2 Binary Operators

2.1.3 Set Operators

2.1.4 Other Operators

2.2 Arithmetic Operators

2.3 Character Operators

2.3.1 Concatenating Character Strings

2.4 Comparison Operators

2.5 Logical Operators

2.6 Set Operators

2.7 Other Operators

3 SQL Functions

3.1 SQL Function Types

3.2 SQL Functions Overview

3.2.1 Number Functions

3.2.2 Character Functions

3.2.3 Character Functions Returning Number Values

3.2.4 Date Functions

3.2.5 Conversion Functions

3.3 SQL Functions Alphabetical Listing

3.3.1 ADD_MONTHS

3.3.2 ASCII

3.3.3 AVG

3.3.4 CASE

3.3.5 CAST

3.3.6 CEIL

3.3.7 CHR

3.3.8 CONCAT

3.3.9 CONVERT

3.3.10 COUNT

3.3.11 CURDATE

3.3.12 CURRENT_DATE

3.3.13 CURRENT_TIME

3.3.14 CURRENT_TIMESTAMP

3.3.15 CURTIME

3.3.16 DATABASE

3.3.17 DAYNAME

3.3.18 DAYOFMONTH

3.3.19 DAYOFWEEK

3.3.20 DAYOFYEAR

3.3.21 DECODE

3.3.22 EXTRACT

3.3.23 FLOOR

3.3.24 GREATEST

3.3.25 HOUR

3.3.26 INITCAP

3.3.27 INSTR

3.3.28 INSTRB

3.3.29 INTERVAL

3.3.30 LAST_DAY

3.3.31 LEAST

3.3.32 LENGTH

3.3.33 LENGTHB

3.3.34 LOCATE

3.3.35 LOWER

3.3.36 LPAD

3.3.37 LTRIM

3.3.38 MAX

3.3.39 MIN

3.3.40 MINUTE

3.3.41 MOD

3.3.42 MONTH

3.3.43 MONTHNAME

3.3.44 MONTHS_BETWEEN

3.3.45 NEXT_DAY

3.3.46 NOW

3.3.47 NVL

3.3.48 POSITION

3.3.49 QUARTER

3.3.50 REPLACE

3.3.51 ROUND - Date Function

3.3.52 ROUND - Number Function

3.3.53 RPAD

3.3.54 RTRIM

3.3.55 SECOND

3.3.56 STDDEV

3.3.57 SUBSTR

3.3.58 SUBSTRB

3.3.59 SUM

3.3.60 SYSDATE

3.3.61 TIMESTAMPADD

3.3.62 TIMESTAMPDIFF

3.3.63 TO_CHAR

3.3.64 TO_DATE

3.3.65 TO_NUMBER

3.3.66 TRANSLATE

3.3.67 TRIM

3.3.68 TRUNC

3.3.69 UPPER

3.3.70 USER

3.3.71 VARIANCE

3.3.72 WEEK

3.3.73 YEAR

4 SQL Commands

4.1 SQL Command Types

4.2 SQL Commands Overview

4.2.1 Data Definition Language (DDL) Commands

4.2.2 Data Manipulation Language (DML) Commands

4.2.3 Transaction Control Commands

4.2.4 Clauses

4.2.5 Pseudocolumns

4.2.6 BNF Notation Conventions

4.3 SQL Commands Alphabetical Listing

4.3.1 ALTER SEQUENCE

4.3.2 ALTER SESSION

4.3.3 ALTER TABLE

4.3.4 ALTER TRIGGER

4.3.5 ALTER USER

4.3.6 ALTER VIEW

4.3.7 COMMIT

4.3.8 CONSTRAINT clause

4.3.9 CREATE DATABASE

4.3.10 CREATE FUNCTION

4.3.11 CREATE GLOBAL TEMPORARY TABLE

4.3.12 CREATE INDEX

4.3.13 CREATE JAVA

4.3.14 CREATE PROCEDURE

4.3.15 CREATE SCHEMA

4.3.16 CREATE SEQUENCE

4.3.17 CREATE SYNONYM

4.3.18 CREATE TABLE

4.3.19 CREATE TRIGGER

4.3.20 CREATE USER

4.3.21 CREATE VIEW

4.3.22 CURRVAL and NEXTVAL pseudocolumns

4.3.23 DELETE

4.3.24 DROP clause

4.3.25 DROP FUNCTION

4.3.26 DROP INDEX

4.3.27 DROP JAVA

4.3.28 DROP PROCEDURE

4.3.29 DROP SCHEMA

4.3.30 DROP SEQUENCE

4.3.31 DROP SYNONYM

4.3.32 DROP TABLE

4.3.33 DROP TRIGGER

4.3.34 DROP USER

4.3.35 DROP VIEW

4.3.36 EXPLAIN PLAN

4.3.37 GRANT

4.3.38 INSERT

4.3.39 LEVEL pseudocolumn

4.3.40 OL_ROW_STATUS pseudocolumn

4.3.41 REVOKE

4.3.42 ROLLBACK

4.3.43 ROWID pseudocolumn

4.3.44 ROWNUM pseudocolumn

4.3.45 SAVEPOINT

4.3.46 SELECT

4.3.47 SET TRANSACTION

4.3.48 TRUNCATE TABLE

4.3.49 UPDATE

A Oracle Lite Keywords and Reserved Words

A.1 Oracle Lite Keywords

A.2 Oracle Lite Reserved Words

B Oracle Lite Datatypes

B.1 BIGINT

B.2 BINARY

B.3 BIT

B.4 BLOB

B.5 CHAR

B.6 CLOB

B.7 DATE

B.8 DECIMAL

B.9 DOUBLE PRECISION

B.10 FLOAT

B.11 INTEGER

B.12 LONG

B.13 LONG RAW

B.14 LONG VARBINARY

B.15 LONG VARCHAR

B.16 NUMBER

B.17 NUMERIC

B.18 RAW

B.19 REAL

B.20 ROWID

B.21 SMALLINT

B.22 TIME

B.23 TIMESTAMP

B.24 TINYINT

B.25 VARBINARY

B.26 VARCHAR

B.27 VARCHAR2

C Oracle Lite Literals

C.1 CHAR, VARCHAR

C.2 DATE

C.3 DECIMAL, NUMERIC, NUMBER

C.4 REAL, FLOAT, DOUBLE PRECISION

C.5 SMALLINT, INTEGER, BIGINT, TINYINT

C.6 TIME

C.7 TIMESTAMP

D Index Creation Options

D.1 Uniqueness Constraint in Oracle Lite

D.1.1 The Address Table Example

D.1.2 Using Uniqueness Constraints

D.1.3 Specifying the Number of Columns in an Index

D.1.3.1 The POLITE.INI File

D.1.3.2 The CREATE UNIQUE INDEX Statement

D.1.3.3 The CREATE TABLE and ALTER TABLE Statements

D.1.3.4 Usage Notes

E Syntax Diagram Conventions

E.1 Introduction

E.2 Required Keywords and Parameters

E.3 Optional Keywords and Parameters

E.4 Syntax Loops

E.5 Multipart Diagrams

E.6 Database Objects

E.7 BNF Notation

Glossary

Index