Structured Query Language, also known as SQL (and frequently pronounced "sequel"), is a powerful tool for data manipulation and has become the standard query language for relational database management systems.

SQL is a high-level language that provides more abstraction than third generation languages such as COBOL. For example, COBOL requires the programmer to code the program logic to traverse through the data structures a record at a time.

With SQL, the programmer specifies what data is needed but does not and cannot specify how to retrieve it. It is the job of the DBMS to analyze the SQL and formulate the access path necessary to retrieve the required information thus relieving the programmer of this duty.

SQL is more than a query language. It is used to define data structures, control access to data, and perform insertions, modifications and deletions of data.

The most important feature of SQL is its use of English-like syntax. This syntax is much easier to understand than pages of COBOL. Because SQL instructions are easier to understand and are smaller in size, they are also easier to learn, code and maintain.

These factors have made SQL very popular with many commercial relational DBMS products such ORACLE, DB2, INGRES, SYBASE and others. SQL has been endorsed by the American National Standards Institute (ANSI) as the ideal language for database manipulation.

Every SQL statement operates on a table and stores results in another table. One query can retrieve numerous rows into a results table. One update can modify multiple rows. These operations are therefore performed at set level. This set-at-a-time processing in combination with the knowledge that SQL specifies what data to retrieve while leaving the how to up to the DBMS defines SQL as a nonprocedural language.

A procedural language is based on nothing other than, procedures. Recalling our experience with COBOL; one procedure is coded to retrieve data a record-at-a-time. Another procedure would modify it, while still another writes it out. A controlling procedure (main routine of the program) then calls each of these sub-routines in a specified order.

Since COBOL is procedural while SQL is not, it is possible that one or two SQL statements can do what an entire COBOL program is required to do. For example, employee number 214 of XYZ Corp. has just received a raise. The new rate of pay must then be recorded in the database. Consider the following SQL statement:

The above SQL statement goes into the database table called 'Employee' and changes the value of the attribute 'salary' to 1000 for each row where the employee number is equal to 214. This statement achieves what the following COBOL program does.

IDENTIFICATION DIVISION.

PROGRAM-ID. PGMUPDATE.

AUTHOR. JANE DOE.

INSTALLATION. KSC.

DATE-WRITTEN. 1-10-94.

DATE-COMPILED.

ENVIRONMENT DIVISION.

CONFIGURATION SECTION.

SOURCE-COMPUTER. IBM-4341.

OBJECT-COMPUTER. IBM-4341.

INPUT-OUTPUT SECTION.

FILE-CONTROL.

SELECT EMPLOYEE-INPUT-FILE

ASSIGN TO SYS005-UR-2540R-S.

SELECT EMPLOYEE-REPORT-FILE

ASSIGN TO SYS005-UR-1403-S.

DATA DIVISION.

FILE SECTION.

FD EMPLOYEE-INPUT-FILE

RECORD CONTAINS 37 CHARACTERS

LABEL RECORDS ARE OMITTED

DATA RECORD IS EMPLOYEE-INPUT-RECORD.

01 EMPLOYEE-INPUT-RECORD.

05 EMPNO-IN PIC X(3).

05 NAME-IN PIC X(25).

05 SALARY-IN PIC S9(7)V99 COMP-3.

FD EMPLOYEE-OUTPUT-FILE

RECORD CONTAINS 37 CHARACTERS

LABEL RECORDS ARE OMITTED

DATA RECORD IS EMPLOYEE-OUTPUT-RECORD.

01 EMPLOYEE-OUTPUT-RECORD.

05 EMPNO-OUT PIC X(3).

05 NAME-OUT PIC X(25).

05 SALARY-OUT PIC S9(7)V99 COMP-3.

WORKING-STORAGE SECTION.

01 EMPLOYEE-RECORD-LAYOUT.

05 EMPNO PIC X(3).

05 NAME PIC X(25).

05 SALARY PIC S9(7)V99 COMP-3.

01 FLAGS.

05 ARE-THERE-MORE-RECORDS PIC X VALUE 'Y'.

PROCEDURE DIVISION.

A000-MAIN-ROUTINE.

PERFORM A001-INIT-ROUTINE THRU A001-EXIT.

PERFORM A002-READ-ROUTINE THRU A002-EXIT.

PERFORM A003-PROCESS-UPDATE-ROUTINE THRU A003-EXIT

UNTIL ARE-THERE-MORE-RECORDS = 'N'.

PERFORM A004-END-OF-JOB-ROUTINE THRU A004-EXIT.

STOP RUN.

A000-EXIT.

A001-INIT-ROUTINE.

OPEN INPUT EMPLOYEE-INPUT-FILE

OPEN OUTPUT EMPLOYEE-OUTPUT-FILE.

A001-EXIT.

A002-READ-ROUTINE.

READ EMPLOYEE-INPUT-FILE

INTO EMPLOYEE-RECORD-LAYOUT

AT END MOVE 'N' TO ARE-THERE-MORE-RECORDS.

A002-EXIT.

A003-PROCESS-UPDATE-ROUTINE.

IF EMPNO = '214'

MOVE 1000.00 TO SALARY.

WRITE EMPLOYEE-OUTPUT-RECORD

FROM EMPLOYEE-RECORD-LAYOUT.

PERFORM A002-READ-ROUTINE THRU A002-EXIT.

A003-EXIT.

A004-END-OF-JOB-ROUTINE.

CLOSE EMPLOYEE-INPUT-FILE

EMPLOYEE-OUTPUT-FILE.

A004-EXIT.

Whew! The SQL version sure is shorter but both the SQL statement and the COBOL program perform the same action; that of updating the salary field. Although the COBOL example is simple to understand for someone knowledgeable about COBOL, it is more difficult for beginning users to learn, code and maintain.

Last revised 7/31/1998 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems

Return to Oracle/SQL Tutorial Contents Page | Go to Next Section - ORACLE/SQL Information