Oracle7 Server SQL Reference Manual | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Oracle7 SQL is a superset of the American National Standards Institute (ANSI) and the International Standards Organization (ISO) SQL92 standard at entry level conformance.
This manual notes any features that require the distributed option, Parallel Server option, Parallel Query option, or PL/SQL to be installed. Also noted are parts of Oracle7 SQL that are only used with the Trusted Oracle7 Server. For information on PL/SQL, Oracle's procedural language extension to SQL, see PL/SQL User's Guide and Reference.
Brief descriptions of Oracle7 embedded SQL are included in this manual. Detailed descriptions of Oracle7 embedded SQL can be found in Programmer's Guide to the Oracle Precompilers.
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.
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.
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.
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:
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:
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.
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.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |