Quick Guide to SQL and COBOL with DB2
on WINDOWS NT
by
Timothy Paul Cronan, Jennifer Kreie, and Lori Komp Leonard
Computer Information Systems and Quantitative Analysis
University of Arkansas
(Fall 1998)
Quick Guide to SQL and COBOL with DB2/2
Table of Contents
Embedding SQL statements in a COBOL program
DATA DIVISION (WORKING-STORAGE SECTION)
PROCEDURE DIVISION
SQL in a PC Network Environment
Using Micro Focus COBOL with DB/2 - SQLPREP and SQLDEMO
SQLDEMO2
SQLPREP.CBL program code
SQLDEMO.CBL program code
SQLDEMO2.CBL program code
Tables in the database used in these exercise
Quick Guide To SQL and COBOL with DB2/2
In addition to using SQL statements for direct and interactive access, SQL statements can be embedded in application programs written in various higher level languages, such as COBOL.
Working Storage Section
All variables that need to be brought into the COBOL program from the tables need to be defined in the Working Storage Section of the host COBOL program in its DECLARE SECTION. All of these variables must be defined as 01 level data items, so that they are forced to align on full word boundaries. These data items are the interface area between the SQL tables and the COBOL program.
If the database table ENROLL was defined as CREATE TABLE ENROLL (STUNUM SMALLINT, CLASSNAME CHAR[5], POSNUM CHAR[1]), the corresponding WORKING-STORAGE definition should be:
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 STUDENTNUMBER PIC S9(3) COMP-3. 01 CLASS PIC X(5). 01 POSITION PIC X(1). EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
NOTE 1: For each table that you want to access through the application program, you need to have COBOL variable-names defined as illustrated above for the table columns. These COBOL variable-names need to be enclosed within the statements EXEC SQL BEGIN DECLARE SECTION END-EXEC, and the EXEC SQL END DECLARE SECTION END-EXEC.
NOTE 2: You need one EXEC SQL INCLUDE SQLCA END-EXEC statement at the end.
NOTE 3: The INCLUDE SQLCA creates a communication area between SQL and COBOL, and defines any SQL variable. Numerous Statements are inserted as a result.
Procedure Division
SQL can be used in a COBOL to extract either a single row from a relation (table) or to extract multiple rows from a table. The way in which the selected rows are processed depends on whether one or more rows need to be extracted from one or more tables.
This is illustrated below in the case of the table STUDENT from which all attributes of the particular student (whose ID is entered through the console) need be extracted. The working storage section would be :
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 STUID PIC S9(3) COMP-3. 01 STUNAME PIC X(5). 01 STUMAJOR PIC X(1). 01 STUGRADELVL PIC X(2). 01 STUAGE PIC X(2).
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
The PROCEDURE DIVISION would contain a paragraph, in the usual COBOL fashion, and would look like :
PARAGRAPH-1.
DISPLAY 'ENTER THE ID NUMBER OF THE STUDENT'.
ACCEPT SIDIN FROM CONSOLE.
EXEC SQLSELECT SID, NAME, MAJOR, GRADELVL, AGE
INTO :STUID, :STUNAME, :STUMAJOR, :STUGRADELVL, :STUAGE
FROM STUDENT
WHERE SID = :SIDINEND-EXEC.
The variables in the SELECT statement (SID, NAME, MAJOR, GRADELVL, AGE) are the column names in the STUDENT table. The selected variables STUID, STUNAME, STUMAJOR, STUGRADELVL, AND STUAGE may be processed as any other variable defined in COBOL. The colons (:) differentiate between SQL field names and COBOL data names, in case there are any duplications between the two.
If more than one row is to be extracted from a table, the way COBOL source code is written differs from the earlier illustration. One difference is that the data retrieved from the SQL query returns a table and, therefore, a "CURSOR" is needed. The "CURSOR" is declared by using the following statement:
EXEC SQL
DECLARE CURSORNAME CURSOR FOR
SELECT ...................
FROM ..................... Usual SQL statements.
WHERE ....................
END-EXEC.
The cursorname could be any name such as X, or CURRENT, etc.
In the following example, the names of all students enrolled in a specific class (entered by a user at the console) are extracted.
PARAGRAPH-1.
DISPLAY 'ENTER DESIRED COURSE NAME'.
ACCEPT CLASSNAMEIN FROM CONSOLE.
EXEC SQLDECLARE RESULT CURSOR FOR
SELECT NAME FROM STUDENT
WHERE SID IN (SELECT STUNUM FROM ENROLL
WHERE CLASSNAME = :CLASSNAMEIN)
END-EXEC.
PERFORM PARAGRAPH-2.
This SELECT extracts all rows that meet the condition to a table and assigns a cursor named RESULT to that extracted table. NOTE: RESULT does not need to be defined in the WORKING-STORAGE SECTION. Processing the extracted rows in RESULT is similar to the processing of a sequential data file. However, instead of opening and closing a data file, the cursor defined in the above SELECT statement is opened and closed. Also, instead of using the usual READ statement, a FETCH statement is used. The general form of a FETCH is as shown below.
EXEC SQL OPEN CURSORNAME END-EXEC.
EXEC SQL FETCH CURSORNAME INTO :COBOL-VARIABLE END-EXEC.
Since the FETCH substitutes a READ, this FETCH statement has to be executed until SQLCODE = 100 is returned by SQL. This return code can be viewed as equivalent to the usual END-OF-FILE marker.
PARAGRAPH-2.
EXEC SQL OPEN RESULT END-EXEC.
PERFORM FETCH-PARAGRAPH.
PERFORM PROCESS-LOOP UNTIL SQLCODE = 100.
EXEC SQL CLOSE RESULT END-EXEC.FETCH-PARAGRAPH.
EXEC SQL
FETCH RESULT INTO :CLASSNAMEIN
END-EXEC.
PROCESS-LOOP.
:
: Statements to process the names of students
: who are enrolled in the specified class.
:
DISPLAY CLASSNAMEIN
PERFORM FETCH-PARAGRAPH.
Using Micro Focus Net Express 2.0 with DB/2
The first exercise which uses SQLPREP.CBL and SQLDEMO.CBL illustrates creating a VIEW, a TABLE, and using INSERT and SELECT. It also describes the compilation, linking and running of a COBOL program.
SQLPREP.CBL, SQLDEMO.CBL and SQLDEMO2.CBL can be compiled, linked and run using the same process. SQLDEMO2.CBL illustrates the use of a cursor in a transaction processing program and it produces a report that is displayed on screen.
Before proceeding you need to copy these files to your diskette. These files will be on each computer system in lab 242, under the CRONAN subdirectory. They will also be available on the regular student network at T:\PCronan.
SAMPLE.TXT
SQLPREP.CBL
SQLCA.CPY
SQLDEMO.CBL
SQLINIT.OBJ
SQLDEMO2.CBL
Creating tables in your database for use with the example COBOL programs.
You=ll use the sample.txt file to create tables and insert data in your database.
1) Select IBM DB2 from the Startup/Programs menu then select DB2 Command Line Processor.
2) At the DB2 prompt, connect to your database.
3) Type QUIT; to leave the DB2 command line.
4) Set a few DB2 options with the following command: (type the command exactly as indicated) Don't type the angle brackets <>, but do enter a name for the output file on the A drive.
SET DB2OPTIONS=-t -v -za:<output file name>
5) Run the file called SAMPLE.TXT to create the tables and data you will need to run the example programs in COBOL with embedded SQL. (Type the command exactly as indicated.)
DB2 -fa:sample.txt
6) Check the output file. The first time you run the sample.txt file, there will be an error for two DROP commands because the tables don't exist. Everything else should run successfully, however. Tables STAFF and ORG will be created and data inserted.
7) Leave the command line and return to Windows NT.
Using Micro Focus Net Express 2.0 with DB2 - SQLPREP and SQLDEMO
(Exercises 1 and 2)
1) From Startup/Programs, move to MF Net Express 2.0, then select Net Express.
2) Select File in the menu bar, then select Open. Open sqlprep.cbl from your diskette. First, you will need to edit the program so that it refers to your specific database using your ID and password. See the instructions at the beginning of the program.
3) The program code must be checked for syntax errors first. The contents of this program a shown at the end of this handout. Click on the Compile Program button. The check process should return no errors.
4) Now you can select Run. Sqlprep.cbl will create a VIEW and a table called MF_TABLE. (You can verify this at the DB2 command line by connecting to your database, then typing list tables.) As the name of this program indicates, it should be run first to prepare the database for the next SQL/COBOL example.
Using the same general steps listed above for Sqlprep.cbl, you can now run the other example programs. The programs used in this handout are listed later in this handout. Comments are included throughout the programs to explain what is being done with the SQL code.
Open each program in Net Express 2.0. Edit the code so that it contains the name of your database, your ID, and your password. Then you can check it and run it.
*Remember to logoff whenever you leave lab 242!
Modify an example program (Exercise 3)
Modify SQLDEMO2 to write to a file. Also, put a report heading and column headings in the output. Compile and run the program using the same process described above. Print the modified program and its resulting report.
NOTE: You must write the report to a file. You cannot write to the printer over the network one line at a time.
SQLPREP.CBL
$set ans85 mf SQL(DBMAN=ODBC, INIT, DB=DBNAME PASS=ID.PASSWORD)
***********************************************************
* In the line above put your database name after db= *
* and put your login ID and password after pass= *
* Put your ID and PASSWORD in CAPITAL LETTERS. *
* Example: $set ans85 mf SQL(DBMAN=ODBC, INIT, *
* DB=DBASE40 PASS=DEBUG40.XYZNM) *
* *
* Look for the CONNECT TO and DISCONNECT statements *
* below and insert specific database, ID, and password *
* information, too. *
***************************************************************************************************************************
* Copyright Micro Focus Limited 1989-95. All Rights Reserved. *
* This demonstration program is provided for use by users of *
* Micro Focus products and may be used, modified and *
* distributed as part of your application provided that you *
* properly acknowledge the copyright of Micro Focus in this *
* material. *
****************************************************************working-storage section.
01 y-or-n pic x value "n".
78 object-exists value -601.*SQL error code for database object exists
01 created-var pic x value "n".
88 created value "y".exec sql include sqlca end-exec
procedure division.
exec sql
connect to DBNAME user USER_ID using PASSWORD
end-exec.
perform until created
exec sql
create view people_loc as
select name,location
from staff,org
where dept=deptnumb
end-exec
if not (sqlcode = object-exists or zero)
perform sql-error
end-if
if sqlcode = object-exists
display
"View PEOPLE_LOC exists in DB, Delete it and re-create Y/[N]"
accept y-or-n
if y-or-n = "y" or "Y"
exec sql
drop view people_loc
end-exec
if sqlcode = zero
move "n" to created-var
else
perform sql-error
end-if
else
move "y" to created-var
end-if
else
move "y" to created-var
end-if
end-perform
*Now create table mf_table
move "n" to created-var
perform until created
exec sql
create table mf_table
(name char(9),
car char(20),
nto60 decimal(3,1))
end-exec
if not (sqlcode = object-exists or zero)
perform sql-error
end-if
if sqlcode = object-exists
display
"Table MF_TABLE exists in DB, Delete it and re-create Y/[N]"
accept y-or-n
if y-or-n = "y" or "Y"
exec sql
drop table mf_table
end-exec
if sqlcode = zero
move "n" to created-var
else
perform sql-error
end-if
else
move "y" to created-var
end-if
else
move "y" to created-var
end-if
end-perform
exec sql
commit
end-exec.
exec sql
disconnect DBNAME
end-exec.
stop run.
sql-error.
display "SQL error SQLCODE="sqlcode
stop run.
SQLDEMO.CBL
$set ans85 mf SQL(DBMAN=ODBC, INIT, DB=DBNAME PASS=ID.PASSWORD)
***********************************************************
* In the line above put your database name after db= *
* and use enter your login ID and password after pass= *
* Put your ID and PASSWORD in CAPITAL LETTERS. *
* Example: $set ans85 mf SQL(DBMAN=ODBC, INIT, *
* DB=DBASE40 PASS=DEBUG40.XYZNM) *
* *
* Look for the CONNECT TO and DISCONNECT statements *
* below and insert specific database, ID, and password *
* information, too. *
***************************************************************************************************************************
* Copyright Micro Focus Limited 1989-93. All Rights Reserved. *
* This demonstration program is provided for use by users of *
* Micro Focus products and may be used, modified and *
* distributed as part of your application provided that you *
* properly acknowledge the copyright of Micro Focus in this *
* material. *
****************************************************************************************************************************
* *
* SQLDEMO.CBL *
* *
* This sample program demonstrates the use of CREATE *
* TABLE, INSERT and SELECT commands with IBM's Database *
* Manager. The program, SQLPREP, should be compiled and *
* run before SQLDEMO to prepare the system for SQLDEMO. *
* *
* Before compiling the program you should ensure that: *
* *
* o your machine has access to Database services *
* o the sample database exists *
* o SQLCA.CPY is present in the current directory *
* o SQL_DYN.LIB is available either through the LIB *
* environment variable or in the current directory *
* o SQLINIT.OBJ is available in the current directory *
* *
* Running SQLDEMO will access a database, insert *
* some data, read it back and then create a couple of *
* tables. *
* *
************************************************************working-storage section.
78 no-data value 100.*SQLCODE for no data available
01 y-or-n pic x.
01 display-line.
03 disp-id pic z(5).
03 filler pic x.
03 disp-name pic x(9).
03 filler pic x.
03 disp-dept pic z(5).
03 filler pic x.
03 disp-job pic x(5).
03 filler pic x.
03 disp-years pic z(5).
03 yrs-nul-val redefines disp-years pic x(5).
03 filler pic x.
03 disp-salary pic z(7).9(2).
03 filler pic x.
03 disp-comm pic z(7).9(2).
03 com-nul-val redefines disp-comm pic x(10).
01 disp-n60 pic zz9.9.
* An SQLCA is needed to communicate with database manager
exec sql include sqlca end-exec* Host variables for database interrogation
exec sql begin declare section end-exec
* You may use comp-3, comp-5 or packed-decimal for host variables
01 nme pic x(9).01 wsid pic s9(4) packed-decimal.
01 dept pic s9(4) packed-decimal.
01 job pic x(5).
01 years pic s9(4) packed-decimal.
01 salary pic s9(5)v9(2) packed-decimal.
01 comm pic s9(5)v9(2) packed-decimal.
01 location pic x(13).
01 deptname pic x(14).
01 car pic x(20).
01 n60 pic s9(3)v9 packed-decimal.
01 avalue pic s9(4) packed-decimal.
* Now two indicator variables are needed because years and comm
* may have null values. Indicator variables must be comp-5.
01 yrsnul pic s9(4) comp-5.
01 commnul pic s9(4) comp-5.
* Now the base string for the prepare example
01 prep pic x(34).
exec sql end declare section end-execprocedure division.
perform connect-db
perform sub-select
perform select-with-cursor
perform full-select
perform view-example
perform insert-example
perform prepare-example
perform end-run
perform disconnect-db
stop run.Connect-DB.
exec sql
connect to DBNAME user USER_ID using PASSWORD
end-exec.End-run.
exec sql
commit
end-exec.Disconnect-db.
exec sql
disconnect DBNAME
end-exec.sub-select.
* This example is a straight forward select statement
* Note the use of indicator variables yrsnul and commnul they
* are negative if the relevant value from the database is null
*
display
"This demo will select from table STAFF of the sample SQL"
display
"database. The selection will be based on the value of the"
display
"column 'ID', the entry with ID equal to the value you enter"
display
"will be displayed"
display "Enter value (table values go from 10 - 350)"
accept avalueexec sql
select id, name, dept, job, years, salary, comm
into :wsid, :nme, :dept, :job, :years:yrsnul,
:salary, :comm:commnul
from staff
where id = :avalue
end-execif sqlcode = zero
perform make-line
display display-line
else
if sqlcode = no-data
display "No row with that ID"
else
perform sql-err
end-if
end-if.select-with-cursor.
display spaces
display
"This demo will select from table STAFF of the sample SQL"
display
"database. The selection will be based on the value of the"
display
"column ID, all entries with a value greater than the value"
display "you enter will be displayed."
display "Enter cutoff value (table values go from 10 - 350)"
accept avalue* Must use a cursor as many values are expected
exec sql
declare c1 cursor for
select id, name, dept, job, years, salary, comm
from staff
where id > :avalue
end-exec* Open the cursor to process the database entries
exec sql
open c1
end-execperform until sqlcode not = zero
* SQLCODE will be zero as long as it has successfully fetched data
exec sql
fetch c1 into :wsid , :nme, :dept, :job,
:years:yrsnul, :salary, :comm:commnul
end-exec
if sqlcode = zero
perform make-line
display display-line
end-if
end-perform.full-select.
* This example uses a cursor to handle the data extracted by two
* select statements joined by an intersect statement, other set
* operations may be substituted
*
display spaces
display
"This demo shows the usage of intersect across two tables"
display
"in the same database, the data extracted is the DEPT from"
display "STAFF and the DEPTNUMB from ORG"
perform wait-acceptexec sql
declare c2 cursor for
select dept from staff
intersect
select deptnumb from org
end-execexec sql
open c2
end-execperform until sqlcode not = zero
exec sql
fetch c2 into :dept
end-exec
if sqlcode = zero
move dept to disp-dept
display disp-dept
end-if
end-perform.view-example.
* This example uses the view PEOPLE_LOC created by SQLPREP
display spaces
display
"This demo will create a view over the two tables ORG and"
display
"STAFF then will extract all data from the view. The result"
display
"of the view is a list of all employees (from STAFF) and"
display "their place of work (from ORG)"
perform wait-accept* Once the view is created it may be treated just like a table
exec sql
declare c3 cursor for
select name,location from people_loc
end-execexec sql
open c3
end-execperform until sqlcode not = zero
exec sql
fetch c3 into :nme,:location
end-exec
if sqlcode = zero
display nme" "location
end-if
end-perform.insert-example.
* This example inserts a row into MF_TABLE which is created by
* SQLPREP. The row is then queried and deleted to prevent any
* problems which could be caused if the program was run a second
* time with identical rows in the table. The select would then
* fail as the resultant data would comprise more than one row
* which would require a cursor. Note the use of apostrophe (')
* instead of quotes (") to delimit the SQL character data.
display spaces
display
"This demo will insert a row into the table MF_TABLE which"
display
"is created by DEMO1 and then will query the row. The values"
display "inserted are: Roger, Ferrari 328 GTB, 6.4"
perform wait-acceptexec sql
insert into mf_table (name, car, nto60)
values ('Roger','Ferrari 328 GTB',6.4)
end-execexec sql
select name,car,nto60
into :nme,:car,:n60
from mf_table
where name='Roger'
end-execif sqlcode = zero
move n60 to disp-n60
display nme" "car" "disp-n60
else
perform sql-err
end-if* Now to delete the row
exec sql
delete from mf_table
where name='Roger'
end-exec.prepare-example.
* This example inserts data into MF-TABLE (created by SQLPREP)
* by use of the SQL PREPARE and EXECUTE statements. Note the use
* of the parameter markers '?' which are replaced by the actual
* data during the EXECUTE statement
*
display spaces
display
"This example inserts one row into MF_TABLE using a prepared"
display
"SQL statement, then reads it back. The row inserted is:-"
display "Elaine, Lamborghini, 4.9"
perform wait-accept
move "insert into mf_table values(?,?,?)" to prep
exec sql
prepare prep_stat from :prep
end-exec
if sqlcode not = zero
perform sql-err
else
move "Elaine" to nme
move "Lamborghini" to car
move 4.9 to n60
exec sql
execute prep_stat using :nme, :car, :n60
end-exec
end-ifexec sql
select name,car,nto60
into :nme, :car, :n60
from mf_table
where name='Elaine'
end-exec
if sqlcode = zero
move n60 to disp-n60
display nme" "car" "disp-n60
else
perform sql-err
end-if
* Now to delete row
exec sql
delete from mf_table
where name='Elaine'
end-exec.sql-err.
display "SQL error SQLCODE="sqlcode.make-line.
move spaces to display-line
move wsid to disp-id
move nme to disp-name
move dept to disp-dept
move job to disp-job
move salary to disp-salary
* Now check for null values and handle accordingly
if yrsnul < 0
move "NULL" to yrs-nul-val
else
move years to disp-years
end-if
if commnul < 0
move "NULL" to com-nul-val
else
move comm to disp-comm
end-if.wait-accept.
display "Press return to run demo"
accept y-or-n.
SQLDEMO2 PROGRAM
$set ans85 mf SQL(DBMAN=ODBC, INIT, DB=DBNAME PASS=ID.PASSWORD)
***********************************************************
* In the line above put your database name after db= *
* and use enter your login ID and password after pass= *
* Put your ID and PASSWORD in CAPITAL LETTERS. *
* Example: $set ans85 mf SQL(DBMAN=ODBC, INIT, *
* DB=DBASE40 PASS=DEBUG40.XYZNM) *
* *
* Look for the CONNECT TO and DISCONNECT statements *
* below and insert specific database, ID, and password *
* information, too. *
***********************************************************************************************************************
* This SQL demo program was written by Roger McHaney and *
* modified by Jennifer Kreie to demonstrate the use of *
* the cursor in transaction processing database *
* applications. Data in a table called STAFF is *
* queried for NAME, SALARY, and COMMISSION. The salary *
* and commission amounts are summed to give total pay *
* in a final report line. Intermediate values are *
* printed as the cursor moves through the database to *
* demonstrate its function. *
************************************************************************************************************************
* *
* SQLDEMO2.CBL *
* *
* Before compiling the program you should ensure that: *
* *
* o your machine has access to Database services *
* o the sample database exists *
* o SQLCA.CPY is present in the current directory *
* o SQL_DYN.LIB is available either through the LIB *
* environment variable or in the current directory *
* o SQLINIT.OBJ is available in the current directory *
* *
************************************************************working-storage section.
78 no-data value 100.
*SQLCODE for no data available
01 y-or-n pic x.
01 display-line.
03 disp-name pic x(9).
03 filler pic x(3).
03 disp-salary pic z(7).9(2).
03 filler pic x.
03 disp-comm pic z(7).9(2).
03 com-nul-val redefines disp-comm pic x(10).
03 disp-salaryt pic z(10).9(2).
03 disp-commt pic z(9).9(2).
01 salaryt pic s9(6)v9(2) value zeroes.
01 commt pic s9(6)v9(2) value zeroes.
01 waget pic s9(6)v9(2) value zeroes.
* An SQLCA is needed to communicate with database manager
exec sql include sqlca end-exec
01 final-line.
03 filler pic x(13) value "Tot. Salary= ".
03 salaryt-final pic $$$$,zzz.9(2).
03 filler pic x(15) value " Tot. Commis.= ".
03 commt-final pic $$$$,zzz.9(2).
03 filler pic x(13) value " Tot. Pay = ".
03 waget-final pic $$$$,zzz.9(2).* Host variables for database interrogation
exec sql begin declare section end-exec
01 empname pic x(9).
01 salary pic s9(5)v9(2) packed-decimal.
01 comm pic s9(5)v9(2) packed-decimal.
* Now an indicator variable is needed because comm
* may have null values. Indicator variables must be comp-5.
01 commnul pic s9(4) comp-5.
exec sql end declare section end-execprocedure division.
perform connect-db
perform select-with-cursor
perform total-line
perform end-run
perform disconnect-db
stop run.Connect-DB.
exec sql
connect to DBNAME user USER_ID using PASSWORD
end-exec.End-run.
exec sql
commit
end-exec.Disconnect-db.
exec sql
disconnect DBNAME
end-exec.select-with-cursor.
* Must use a cursor as many values are expected
exec sql
declare payt cursor for
select name, salary, comm
from staff
end-exec* Open the cursor to process the database entries
exec sql
open payt
end-execperform until sqlcode not = zero
* SQLCODE will be zero as long as it has successfully fetched data
exec sql
fetch payt into :empname, :salary, :comm:commnul
end-exec
if sqlcode = zero
perform make-line
display display-line
end-if
end-perform.* Move values to display line and print
* Also keep running tab of totalsmake-line.
move empname to disp-name
move salary to disp-salary
add salary to salaryt
add salary to waget
move salaryt to disp-salaryt
if commnul < 0
move " NULL" to com-nul-val
else
move comm to disp-comm
add comm to commt
add comm to waget
move commt to disp-commt
end-if.* Print total commission, total salary and total pay
* dispensed by the companytotal-line.
move salaryt to salaryt-final.
move commt to commt-final.
move waget to waget-final.
display final-line.
Table: STAFF
ID NAME DEPT JOB YEARS SALARY COMM ------ --------- ------ ----- ------ ---------- ---------- 10 Sanders 20 Mgr 7 18357.50 - 20 Pernal 20 Sales 8 18171.25 612.45 30 Marenghi 38 Mgr 5 17506.75 40 O'Brien 38 Sales 6 18006.00 846.55 50 Hanes 15 Mgr 10 20659.80 - 60 Quigley 38 Sales - 16808.30 650.25 70 Rothman 15 Sales 7 16502.83 1152.00 80 James 20 Clerk - 13504.60 128.20 90 Koonitz 42 Sales 6 18001.75 1386.70 100 Plotz 42 Mgr 7 18352.80 - 110 Ngan 15 Clerk 5 12508.20 206.60 120 Naughton 38 Clerk - 12954.75 180.00 130 Yamaguchi 42 Clerk 6 10505.90 75.60 140 Fraye 51 Mgr 6 21150.00 - 150 Williams 51 Sales 6 19456.50 637.65 160 Molinare 10 Mgr 7 22959.20 - 170 Kermisch 15 Clerk 4 12258.50 110.10 180 Abrahams 38 Clerk 3 12009.75 236.50 190 Sneider 20 Clerk 8 14252.75 126.50 200 Scoutten 42 Clerk - 11508.60 84.20 210 Lu 10 Mgr 10 20010.00 - 220 Smith 51 Sales 7 17654.50 992.80 230 Lundquist 51 Clerk 3 13369.80 189.65 240 Daniels 10 Mgr 5 19260.25 - 250 Wheeler 51 Clerk 6 14460.00 513.30 260 Jones 10 Mgr 12 21234.00 - 270 Lea 66 Mgr 9 18555.50 - 280 Wilson 66 Sales 9 18674.50 811.50 290 Quill 84 Mgr 10 19818.00 - 300 Davis 84 Sales 5 15454.50 806.10 310 Graham 66 Sales 13 21000.00 200.30 320 Gonzales 66 Sales 4 16858.20 844.00 330 Burke 66 Clerk 1 10988.00 55.50 340 Edwards 84 Sales 7 17844.00 1285.00 350 Gafney 84 Clerk 5 13030.50 188.00
Table: ORG
DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION -------- -------------- ------- ---------- ------------- 10 Head Office 160 Corporate New York 15 New England 50 Eastern Boston 20 Mid Atlantic 10 Eastern Washington 38 South Atlantic 30 Eastern Atlanta 42 Great Lakes 100 Midwest Chicago 51 Plains 140 Midwest Dallas 66 Pacific 270 Western San Francisco 84 Mountain 290 Western Denver