Oracle7 Server SQL Reference

How this Manual is Organized

This Manual is divided into the following parts:

Chapter 1: Introduction This chapter defines SQL and describes its history as well as the advantages of using it to access relational databases.

Chapter 2: Elements of Oracle7 SQL This chapter describes the basic building blocks of an Oracle7 database and the Oracle7 SQL.

Chapter 3: Operators, Functions, Expressions, Conditions This chapter describes how to use SQL operators and functions to combine data into expressions and conditions.

Chapter 4: Commands This chapter lists and describes all of the SQL commands in alphabetical order.

Appendix A: Differences From Previous Versions This appendix lists differences in Release 7.2 and previous releases of Oracle7 SQL.

Appendix B: Oracle7 and Standard SQL This appendix describes Oracle7 compliance with ANSI and ISO standards and lists Oracle7 extensions beyond the standards.

Appendix C: Operating System-Specific Dependencies This appendix notes places in this manual referring to operating system-specific documentation.

Conventions Used in this Manual

This section explains the conventions used in this Manual including:

icons

text

syntax diagrams and notation

examples

example data

Icons

This manual uses the following icons:

Additional Information: This icon indicates information that is contained within Oracle operating system-specific documentation. Such references are noted in Appendix C.

Warning: This icon warns you of a possible danger when using a feature.

Text

The text in this manual adheres to the following conventions:

UPPERCASE

Uppercase text is used to call attention to names of Oracle7 tools commands, keywords, filenames, and initialization parameters.

italics

Italicized text is used call to attention to definitions of terms and parameters of SQL commands.

Syntax Diagrams and Notation

The syntax diagrams and notation in this manual show the complete syntax for SQL commands, functions, and other elements. This section describes syntax diagrams and gives examples of how to write SQL statements. Syntax diagrams are made up of these items:

Keywords Keywords are words that have special meanings in the SQL language. In the syntax diagrams in this manual, keywords appear in uppercase. You must use keywords in your SQL statements exactly as they appear in the syntax diagram, except that they can be either uppercase or lowercase. For example, you must use the CREATE keyword to begin your CREATE TABLE statements just as it appears in the CREATE TABLE syntax diagram.

Parameters Parameters act as place holders in syntax diagrams. They appear in lowercase. Parameters are usually names of database objects, Oracle7 datatype names, or expressions. When you see a parameter in a syntax diagram, substitute an object or expression of the appropriate type in your SQL statement. For example, to write a CREATE TABLE statement, use the name of the table you want to create, such as EMP, in place of the table parameter in the syntax diagram. Note that parameter names appear in italics in the text.

This lists shows parameters that appear in the syntax diagrams in this manual and examples of the values you might substitute for them in your statements:

Parameter

Description

Examples

table

The substitution value must be the name of an object of the type specified by the parameter. For a list of all types of objects, see the section, "Schema Objects" .

emp

The substitution value must be a single character from your database character set.

T s

'text'

The substitution value must be a text string in single quotes. See the syntax description of 'text' .

'Employee records'

char

The substitution value must be an expression of datatype CHAR or VARCHAR2 or a character literal in single quotes.

ename 'Smith'

condition

The substitution value must be a condition that evaluates to TRUE or FALSE. See the syntax description of condition .

ename > 'A'

date d

The substitution value must be a date constant or an expression of DATE datatype.

TO_DATE( '01-Jan-1994', 'DD-MON-YYYY')

expr

The substitution value can be an expression of any datatype as defined in the syntax description of expr .

sal + 1000

integer

The substitution value must be an integer as defined by the syntax description of integer .

72

label

The substitution value must be an expression of datatype MLSLABEL. For information on such expressions, see the Trusted Oracle7 Server Administration guide.

TO_LABEL( 'SENSITIVE:ALPHA')

number m n

The substitution value must be an expression of NUMBER datatype or a number constant as defined in the syntax description of number .

AVG(sal) 15 * 7

raw

The substitution value must be an expression of datatype RAW.

HEXTORAW('7D')

rowid

The substitution value must be an expression of datatype ROWID.

00000462.0001.0001

subquery

The substitution value must be a SELECT statement, which will be used in another SQL statement. See the syntax description of subquery .

SELECT ename FROM emp

:host_variable

The substitution value must be the name of a variable declared in an embedded SQL program. This manual also uses :host_integer and :host_string to indicate specific datatypes.

:employee_number

cursor

The substitution value must be the name of a cursor in an embedded SQL program.

curs1

db_name

The substitution value must be the name of a non-default database in an embedded SQL program.

sales_db

db_string

The substitution value must be the database identification string for a SQL*Net database connection. For details, see the user's guide for your specific SQL*Net protocol.

statement_name block_name

The substitution value must be an identifier for a SQL statement or PL/SQL block.

s1 b1

Syntax Diagrams This manual uses syntax diagrams to show SQL commands, "Commands," and to show other elements of the SQL language, "Elements of Oracle7 SQL," and Chapter 3, "Operators, Functions, Expressions, Conditions." These syntax diagrams use lines and arrows to show syntactic structure. The following list shows the lines and arrows used and their syntactical meaning.

Examples

This manual also contains many examples of SQL statements. These examples show you how to use elements of SQL. The following example shows a CREATE TABLE statement:

CREATE TABLE accounts

	(accno    NUMBER,

	 owner    VARCHAR2(10),

	 balance  NUMBER(7,2) )

Note that examples appear in a different font than the text.

Examples follow these case conventions:

Keywords, such as CREATE and NUMBER, appear in uppercase.

Names of database objects and their parts, such as ACCOUNTS and ACCNO, appear in lowercase, although they appear in uppercase in the text.

SQL is not case-sensitive (except for quoted identifiers), so you need not follow these conventions when writing your own SQL statements, although your statements may be easier for you to read if you do.

Some Oracle7 tools require you to terminate SQL statements with a special character. For example, SQL statements issued through SQL*Plus may be terminated with a semicolon (;). If you issue these examples statements to Oracle7, you must terminate them with the special character expected by the Oracle7 tool you are using.

Example Data

Many of the examples in this manual operate on sample tables. The definitions of some of these tables appear in a SQL script available on your distribution media. On most operating systems the name of this script is UTLSAMPL.SQL , although its exact name and location may vary depending on your operating system. This script creates sample users and creates these sample tables in the schema of the user SCOTT:

CREATE TABLE dept

	(deptno    NUMBER(2)      CONSTRAINT pk_dept PRIMARY KEY,

	 dname     VARCHAR2(14),

	 loc       VARCHAR2(13) )

CREATE TABLE emp

	(empno     NUMBER(4)      CONSTRAINT pk_emp PRIMARY KEY,

	 ename     VARCHAR2(10),

	 job       VARCHAR2(9),

	 mgr       NUMBER(4),

	 hiredate  DATE,

	 sal       NUMBER(7,2),

	 comm      NUMBER(7,2),

	 deptno    NUMBER(2)    CONSTRAINT fk_deptno REFERENCES emp )

CREATE TABLE bonus

	(ename     VARCHAR2(10),

	 job       VARCHAR2(9),

	 sal       NUMBER,

	 comm      NUMBER )

CREATE TABLE salgrade

	(grade     NUMBER,

	 losal     NUMBER,

	 hisal     NUMBER )

The script also fills the sample tables with this data:

SELECT * FROM dept

DEPTNO  DNAME      LOC

------- ---------- ---------

10      ACCOUNTING NEW YORK

20      RESEARCH   DALLAS

30      SALES      CHICAGO 40      OPERATIONS BOSTON

SELECT * FROM emp

EMPNO ENAME   JOB          MGR  HIREDATE    SAL   COMM  DEPTNO

----- ------- --------- ------ --------- ------ ------ -------

7369 SMITH   CLERK       7902 17-DEC-80    800             20

7499 ALLEN   SALESMAN    7698 20-FEB-81   1600    300      30

7521 WARD    SALESMAN    7698 22-FEB-81   1250    500      30

7566 JONES   MANAGER     7839 02-APR-81   2975             20

7654 MARTIN  SALESMAN    7698 28-SEP-81   1250   1400      30

7698 BLAKE   MANAGER     7839 01-MAY-81   2850             30

7782 CLARK   MANAGER     7839 09-JUN-81   2450             10

7788 SCOTT   ANALYST     7566 19-APR-87   3000             20

7839 KING    PRESIDENT        17-NOV-81   5000             10

7844 TURNER  SALESMAN    7698 08-SEP-81   1500             30

7876 ADAMS   CLERK       7788 23-MAY-87   1100             20

7900 JAMES   CLERK       7698 03-DEC-81    950             30

7902 FORD    ANALYST     7566 03-DEC-81   3000             20

7934 MILLER  CLERK       7782 23-JAN-82   1300             10

SELECT * FROM salgrade

GRADE LOSAL HISAL

----- ----- -----

1     700   1200

2     1201  1400

3     1401  2000

4     2001  3000

5     3001  9999

To perform all the operations of the script, run it when you are logged into Oracle7 as the user SYSTEM.

Your Comments Are Welcome

We value and appreciate your comments as an Oracle7 user and reader of the manuals. As we write, revise, and evaluate, your opinions are the most important input we receive. At the back of this manual is a Reader's Comment Form that we encourage you to use to tell us both what you like and what you dislike about this (or other) Oracle7 manuals. If the form has been used, or you would like to contact us, please use the following address or fax number: