Contents

Title and Copyright Information

List of Figures

List of Tables

Send Us Your Comments

Preface

1 Using SQL

SQL Overview

Examples

Oracle SQL and SQL-92

Running SQL-92 on Oracle Lite

Oracle Lite SQL and Oracle SQL Comparison

Objects

Operators

Functions

Commands

Miscellaneous Data Definition Language (DDL)

Datatypes

Indicator Variables

Data Precision During Arithmetic Operations

Data Dictionaries

Tables Not Installed with Oracle Lite

Messages

Sequences

PL/SQL

SQL Functions

Locking and Transactions

Oracle Lite SQL Conventions

SQL Statement Syntax

Capital Letters

Lowercase Italics

Bracket Delimited

Braces

Vertical Bars

Ellipsis

Underline

Block Letters

Initial Colon

SQL Tables

SQL Object Names

SQL Operator Precedence

SQL Sessions

SQL Transactions

Issuing SQL Statements From a Program

SQL and ODBC

ODBC SQL Syntax Conventions

Oracle Lite Database Object Naming Conventions

Formats

Number Format Elements

Date Format Elements

Specifying SQL Conditions

Simple Comparison Conditions

Group Comparison Conditions

A Row_Value_Constructor in a Subquery Comparison

Subquery in Place of a Column

Membership Conditions

Range Conditions

NULL Conditions

EXISTS Conditions

LIKE Conditions

Compound Conditions

Specifying Expressions

Form I, Simple Expression

Form II, Function Expression

Form III, Java Function Expressio

Form IV, Compound Expression

Form V, DECODE Expression

Form VI, Expression List

Form VII, Variable Expression

Form VIII, CAST Expression

Oracle Lite SQL Datatypes and Literals

Character String Comparison Rules

Blank-Padded Comparison Semantics

Nonpadded Comparison Semantics

Comments Within SQL Statements

2 SQL Operators

SQL Operators Overview

Unary Operators

Binary Operators

Set Operators

Other Operators

Arithmetic Operators

Character Operators

Concatenating Character Strings

Comparison Operators

Logical Operators

Set Operators

Other Operators

3 SQL Functions

SQL Function Types

SQL Functions Overview

Number Functions

Character Functions

Character Functions Returning Number Values

Date Functions

Conversion Functions

SQL Functions Alphabetical Listing

ADD_MONTHS

ASCII

AVG

CASE

CAST

CEIL

CHR

CONCAT

CONVERT

COUNT

CURDATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURTIME

DATABASE

DAYNAME

DAYOFMONTH

DAYOFWEEK

DAYOFYEAR

DECODE

EXTRACT

FLOOR

GREATEST

HOUR

INITCAP

INSTR

INSTRB

INTERVAL

LAST_DAY

LEAST

LENGTH

LENGTHB

LOCATE

LOWER

LPAD

LTRIM

MAX

MIN

MINUTE

MOD

MONTH

MONTHNAME

MONTHS_BETWEEN

NEXT_DAY

NOW

NVL

POSITION

QUARTER

REPLACE

ROUND - Date Function

ROUND - Number Function

RPAD

RTRIM

SECOND

STDDEV

SUBSTR

SUBSTRB

SUM

SYSDATE

TIMESTAMPADD

TIMESTAMPDIFF

TO_CHAR

TO_DATE

TO_NUMBER

TRANSLATE

TRIM

TRUNC

UPPER

USER

VARIANCE

WEEK

YEAR

4 SQL Commands

SQL Command Types

SQL Commands Overview

Data Definition Language (DDL) Commands

Data Manipulation Language (DML) Commands

Transaction Control Commands

Clauses

Pseudocolumns

SQL Commands Alphabetical Listing

ALTER SEQUENCE

ALTER SESSION

ALTER TABLE

ALTER TRIGGER

ALTER USER

ALTER VIEW

COMMIT

CONSTRAINT clause

CREATE DATABASE

CREATE FUNCTION

CREATE GLOBAL TEMPORARY TABLE

CREATE INDEX

CREATE JAVA

CREATE PROCEDURE

CREATE SCHEMA

CREATE SEQUENCE

CREATE SYNONYM

CREATE TABLE

CREATE TRIGGER

CREATE USER

CREATE VIEW

DELETE

DROP clause

DROP FUNCTION

DROP INDEX

DROP JAVA

DROP PROCEDURE

DROP SCHEMA

DROP SEQUENCE

DROP SYNONYM

DROP TABLE

DROP TRIGGER

DROP USER

DROP VIEW

EXPLAIN PLAN

GRANT

INSERT

LEVEL pseudocolumn

REVOKE

ROLLBACK

ROWNUM pseudocolumn

SAVEPOINT

SELECT

SET TRANSACTION

TRUNCATE TABLE

UPDATE

A Oracle Lite Keywords and Reserved Words

Oracle Lite Keywords

Oracle Lite Reserved Words

B Oracle Lite Datatypes

BIGINT

BINARY

BIT

BLOB

CHAR

CLOB

DATE

DECIMAL

DOUBLE PRECISION

FLOAT

INTEGER

LONG

LONG RAW

LONG VARBINARY

LONG VARCHAR

NUMBER

NUMERIC

RAW

REAL

ROWID

SMALLINT

TIME

TIMESTAMP

TINYINT

VARBINARY

VARCHAR

VARCHAR2

C Oracle Lite Literals

CHAR, VARCHAR

DATE

DECIMAL, NUMERIC, NUMBER

REAL, FLOAT, DOUBLE PRECISION

SMALLINT, INTEGER, BIGINT, TINYINT

TIME

TIMESTAMP

D Index Creation Options

Uniqueness Constraint in Oracle Lite

The Address Table Example

Using Uniqueness Constraints

Specifying the Number of Columns in an Index

The POLITE.INI File

The CREATE UNIQUE INDEX Statement

The CREATE TABLE and ALTER TABLE Statements

Usage Notes

E Syntax Diagram Conventions

Introduction

Required Keywords and Parameters

Optional Keywords and Parameters

Syntax Loops

Multipart Diagrams

Database Objects

Glossary

Index