Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

Lexical Conventions

2 Basic Elements of Oracle SQL

Datatypes

Literals

Format Models

Nulls

Pseudocolumns

Comments

Database Objects

Schema Object Names and Qualifiers

Syntax for Schema Objects and Parts in SQL Statements

3 Operators

Unary and Binary Operators

Precedence

Arithmetic Operators

Concatenation Operator

Comparison Operators

Logical Operators: NOT, AND, OR

Set Operators: UNION [ALL], INTERSECT, MINUS

Other Built-In Operators

User-Defined Operators

4 Functions

SQL Functions

ABS

ACOS

ADD_MONTHS

ASCII

ASIN

ATAN

ATAN2

AVG

BFILENAME

BITAND

CEIL

CHARTOROWID

CHR

CONCAT

CONVERT

CORR

COS

COSH

COUNT

COVAR_POP

COVAR_SAMP

CUME_DIST

DENSE_RANK

DEREF

DUMP

EMPTY_[B | C]LOB

EXP

FIRST_VALUE

FLOOR

GREATEST

GROUPING

HEXTORAW

INITCAP

INSTR

INSTRB

LAG

LAST_DAY

LAST_VALUE

LEAD

LEAST

LENGTH

LENGTHB

LN

LOG

LOWER

LPAD

LTRIM

MAKE_REF

MAX

MIN

MOD

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

NTILE

NUMTODSINTERVAL

NUMTOYMINTERVAL

NVL

NVL2

PERCENT_RANK

POWER

RANK

RATIO_TO_REPORT

RAWTOHEX

REF

REFTOHEX

REGR_ (linear regression) functions

REPLACE

ROUND (number function)

ROUND (date function)

ROW_NUMBER

ROWIDTOCHAR

RPAD

RTRIM

SIGN

SIN

SINH

SOUNDEX

SQRT

STDDEV

STDDEV_POP

STDDEV_SAMP

SUBSTR

SUBSTRB

SUM

SYS_CONTEXT

SYS_GUID

SYSDATE

TAN

TANH

TO_CHAR (date conversion)

TO_CHAR (number conversion)

TO_DATE

TO_LOB

TO_MULTI_BYTE

TO_NUMBER

TO_SINGLE_BYTE

TRANSLATE

TRANSLATE ... USING

TRIM

TRUNC (number function)

TRUNC (date function)

UID

UPPER

USER

USERENV

VALUE

VAR_POP

VAR_SAMP

VARIANCE

VSIZE

ROUND and TRUNC Date Functions

User-Defined Functions

5 Expressions, Conditions, and Queries

Expressions

Conditions

Queries and Subqueries

6 About SQL Statements

Summary of SQL Statements

Finding the SQL Statement for a Database Task

7 SQL Statements:
ALTER CLUSTER to ALTER SYSTEM

ALTER CLUSTER

ALTER DATABASE

ALTER DIMENSION

ALTER FUNCTION

ALTER INDEX

ALTER JAVA

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW LOG

ALTER OUTLINE

ALTER PACKAGE

ALTER PROCEDURE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLE

ALTER ROLLBACK SEGMENT

ALTER SEQUENCE

ALTER SESSION

ALTER SYSTEM

8 SQL Statements:
ALTER TABLE to constraint_clause

ALTER TABLE

ALTER TABLESPACE

ALTER TRIGGER

ALTER TYPE

ALTER USER

ALTER VIEW

ANALYZE

ASSOCIATE STATISTICS

AUDIT

CALL

COMMENT

COMMIT

9 SQL Statements:
CREATE CLUSTER to CREATE SEQUENCE

CREATE CLUSTER

CREATE CONTEXT

CREATE CONTROLFILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE DIMENSION

CREATE DIRECTORY

CREATE FUNCTION

CREATE INDEX

CREATE INDEXTYPE

CREATE JAVA

CREATE LIBRARY

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG

CREATE OPERATOR

CREATE OUTLINE

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PROCEDURE

CREATE PROFILE

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE SCHEMA

CREATE SEQUENCE

10 SQL Statements:
CREATE SYNONYM to DROP ROLLBACK SEGMENT

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TEMPORARY TABLESPACE

CREATE TRIGGER

CREATE TYPE

CREATE TYPE BODY

CREATE USER

CREATE VIEW

DELETE

DISASSOCIATE STATISTICS

DROP CLUSTER

DROP CONTEXT

DROP DATABASE LINK

DROP DIMENSION

DROP DIRECTORY

DROP FUNCTION

DROP INDEX

DROP INDEXTYPE

DROP JAVA

DROP LIBRARY

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW LOG

DROP OPERATOR

DROP OUTLINE

DROP PACKAGE

DROP PROCEDURE

DROP PROFILE

DROP ROLE

DROP ROLLBACK SEGMENT

11 SQL Statements:
DROP SEQUENCE to UPDATE

DROP SEQUENCE

DROP SYNONYM

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP TYPE

DROP TYPE BODY

DROP USER

DROP VIEW

EXPLAIN PLAN

filespec

GRANT

INSERT

LOCK TABLE

NOAUDIT

RENAME

REVOKE

ROLLBACK

SAVEPOINT

SELECT and subquery

SET CONSTRAINT[S]

SET ROLE

SET TRANSACTION

storage_clause

TRUNCATE

UPDATE

A Syntax Diagrams

B Oracle and Standard SQL

Conformance with Standard SQL

Oracle Extensions to Standard SQL

C Oracle Reserved Words