1
Introduction

Structured Query Language (SQL), is the set of commands that all programs and users must use to access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request. This chapter provides background information on SQL as used by most relational database systems. Topics include:

History of SQL

The paper, "A Relational Model of Data for Large Shared Data Banks," by Dr. E. F. Codd, was published in June 1970 in the Association of Computer Machinery (ACM) journal, Communications of the ACM. Codd's model is now accepted as the definitive model for relational database management systems (RDBMS). The language, Structured English Query Language ("SEQUEL") was developed by IBM Corporation, Inc., to use Codd's model. SEQUEL later became SQL (still pronounced "sequel"). In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language.

SQL Standards

Oracle SQL complies with industry-accepted standards. Oracle Corporation ensures future compliance with evolving SQL standards by actively involving key personnel in SQL standards committees. Industry-accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO), which is affiliated with the International Electrotechnical Commission (IEC). Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of the standards conform to conventions used by the organization, but the standards are technically identical.

The latest SQL standard published by ANSI and ISO is often called SQL92 (and sometimes SQL2). The formal names of the new standard are:

ANSI X3.135-1992, "Database Language SQL"
ISO/IEC 9075:1992, "Database Language SQL"

SQL92 defines four levels of compliance: Entry, Transitional, Intermediate, and Full. A conforming SQL implementation must support at least Entry SQL. Oracle8, Release 8.0, fully supports Entry SQL and has many features that conform to Transitional, Intermediate, or Full SQL.

Oracle8 conformance to Entry-level SQL92 was tested by the National Institute for Standards and Technology (NIST) using the Federal Information Processing Standard (FIPS), FIPS PUB 127-2.

Additional Information:

For more information about Oracle and standard SQL, see Appendix B, "Oracle and Standard SQL".

How SQL Works

The strengths of SQL benefit all types of users, including application programmers, database administrators, management, and end users. Technically speaking, SQL is a data sublanguage: the purpose of SQL is to provide an interface to a relational database such as Oracle, and all SQL statements are instructions to the database. In this it differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:

It processes sets of data as groups rather than as individual units.
It provides automatic navigation to the data.
It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and Oracle's PL/SQL extension to SQL is close to PSM.

Essentially, SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle that determines a fast means of accessing the specified data. Oracle also provides techniques you can use to make the optimizer perform its job better.

SQL provides commands for a variety of tasks, including:

querying data
inserting, updating, and deleting rows in a table
creating, replacing, altering, and dropping objects
controlling access to the database and its objects
guaranteeing database consistency and integrity

SQL unifies all of the above tasks in one consistent language.

Common Language for All Relational Databases

All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable: they can often be moved from one database to another with very little modification.

Embedded SQL

Embedded SQL refers to the use of standard SQL commands embedded within a procedural programming language. The embedded SQL commands are documented in the Oracle precompiler books, SQL*Module for Ada Programmer's Guide, Pro*C/C++ Precompiler Programmer's Guide, and Pro*COBOL Precompiler Programmer's Guide.

Embedded SQL is a collection of these commands:

all SQL commands, such as SELECT and INSERT, available with SQL with interactive tools
dynamic SQL execution commands, such as PREPARE and OPEN, which integrate the standard SQL commands with a procedural programming language

Embedded SQL also includes extensions to some standard SQL commands. Embedded SQL is supported by the Oracle precompilers. The Oracle precompilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers.

Each of these Oracle precompilers translates embedded SQL programs into a different procedural language:

the Pro*C/C++ precompiler
the Pro*COBOL precompiler
the Pro*FORTRAN precompiler

the SQL*Module for ADA

Additional Information:

For a definition of the Oracle precompilers and the embedded SQL commands, see SQL*Module for Ada Programmer's Guide, Pro*C/C++ Precompiler Programmer's Guide, and Pro*COBOL Precompiler Programmer's Guide.

Lexical Conventions

The following lexical conventions for issuing SQL statements apply specifically to Oracle's implementation of SQL, but are generally acceptable in all other SQL implementations.

When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the command. Thus, Oracle evaluates the following two statements in the same manner:

   SELECT ENAME,SAL*12,MONTHS_BETWEEN(HIREDATE,SYSDATE) FROM EMP;

   SELECT ENAME,
        SAL * 12,
             MONTHS_BETWEEN( HIREDATE, SYSDATE )
   FROM EMP;

Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names. See the syntax description in "Text".

Tools Support

Most (but not all) Oracle tools support all features of Oracle's SQL. This reference describes the complete functionality of SQL. If the Oracle tool that you are using does not support this complete functionality, you can find a discussion of the restrictions in the manual describing the tool, such as PL/SQL User's Guide and Reference.

1 Introduction