• Home
• Exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Reference 1
  • Reference 2
• Skill builders
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
• Support
  • Ask Rick
  • Cd case
  • Errata
  • Glossary
  • INSERT macro
  • Java
  • Labs
  • More SQL problems
  • Slides
  • SQL divide
  • SQL Playbook
  • Tables
  • Teradata
  • XML
• Instructor
  • Contact
  • Contents
  • Manual
  • Preface

Section 1 The Managerial Perspective

Chapter 1 Organizational Memory

Chapter 2 Information

Section 2 Data Modeling and SQL

Chapter 3 The Single Entity

1. Visit the NASA Web site that has a history of shuttle flights . Draw a data model, enter details for flights during 2001, and use SQL to solve the following queries:
   1. Which flights lasted more than 12 days?
   2. What was the first flight in 2001?
   3. What was the purpose of mission STS-100?
   4. Which flights had something to do with logistics?
2. Draw a data model for a video collection. Each video has a title, rating (G, PG, PG-13, or R), length in minutes, language, nationality, and number of stars (1-poor to 5-excellent). Half stars can be awarded (e.g., a moving gets 3.5 stars).
   Create a database and populate it with the data in the following table. If your DBMS supports lookup, use that feature for rating, language, and nationality.

Chapter 4 The One-to-Many Relationship

Chapter 5 The Many-to-Many Relationship

Chapter 6 One-to-One and Recursive Relationships

Chapter 7 Data Modeling

Reference 1 Basic Structures

Chapter 8 Normalization and Other Data Modeling Methods

Chapter 9 The Relational Model

Chapter 10 SQL

Embedded SQL

FILE SECTION.
******************************************************************
* Non-database files are defined here
******************************************************************
WORKING-STORAGE SECTION.
******************************************************************
* COBOL variables are defined here
******************************************************************
01 VALUE PIC S9(8)V99 COMP-3.
01 TOTAL-VALUE PIC S9(8)V99 COMP-3.
******************************************************************
* Define the SQL communication area
******************************************************************
EXEC SQL INCLUDE SQLCA END-EXEC.
******************************************************************
* Define variables used to transfer data between the
* application and DB2.
******************************************************************
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 STOCK-TABLE.
05 STOCK-CODE PIC X(3).
05 STOCK-FIRM PIC X(20).
05 STOCK-PRICE PIC S9(4)V99 COMP-3.
05 STOCK-QTY PIC S9(8) COMP-3.
05 STOCK-DIV PIC S9(3)V99 COMP-3.
05 STOCK-PE PIC S99 COMP-3.
EXEC SQL END DECLARE SECTION END-EXEC.
******************************************************************
PROCEDURE DIVISION.
PERFORM INITIALIZE.
PERFORM READ-ROW
UNTIL SQLCODE NOT = 0.
PERFORM FINISH.
STOP RUN.
INITIALIZE.
* Declare cursor
EXEC SQL
DECLARE STOCK-CURSOR CURSOR FOR
SELECT * FROM STOCK
END-EXEC.
* Set variables
MOVE ZERO TO TOTAL-VALUE.
* Open the cursor
EXEC SQL
OPEN STOCK-CURSOR
END-EXEC.
* Issue an initial FETCH
EXEC SQL
FETCH STOCK-CURSOR INTO :STOCK-TABLE
END-EXEC.
* Check SQLCODE
IF SQLCODE = 100
THEN
DISPLAY “Table is empty”
STOP RUN
END-IF.
READ-ROW.
* Calculate and accumulate value
VALUE = STOCK-PRICE*STOCK-QTY.
TOTAL-VALUE = TOTAL-VALUE + VALUE.
* Fetch the next row.
EXEC SQL
FETCH STOCK-CURSOR INTO :STOCK-TABLE
END-EXEC.
FINISH.
DISPLAY “Total value of stock is” TOTAL-VALUE.
EXEC SQL
CLOSE STOCK-CURSOR
END-EXEC.

There are several things to notice about this COBOL program.

1. Every SQL statement is surrounded by the keywords EXEC SQL and END-EXEC. This enables the compiler to identify SQL statements and process them differently from COBOL statements.
   
2. Only one SQL statement can be included between the EXEC SQL and END-EXEC statements.
   
3. The DATA DIVISION describes variables that are used to pass data between the application program and DB2 and systems data shared by the application program and DB2.
   
4. The DECLARE SECTION defines variables that are used to transfer data between the application program and DB2.
   
5. The SQL table STOCK is defined in the COBOL program as STOCK-TABLE. The columns have been renamed; STKCODE is now STOCK-CODE and so on. The SQL data definitions have been converted to COBOL style. For example, CHAR(3) becomes X(3), and DECIMAL(6,2) becomes S9(4)V99 COMP-3.
   
6. Systems data are shared through the SQLCA (SQL communications area). SQLCA is defined by the INCLUDE SQLCA statement. SQLCA contains many data items. The most interesting is SQLCODE, which is set by DB2 after each SQL statement is executed. This code has three types of values, as shown in the accompanying table:
   

   SQLCODE	Condition	Example
   zero	Normal execution	Successful execution of SQL statement
   positive	Unusual, but normal condition	End of data (SQLCODE = 100)
   negative	Abnormal, unexpected condition	Insufficient file space

7. There is one difference between embedded SQL and interactive SQL statements. The difference is an INTO clause that tells DB2 the name of the variable into which to place the value(s) retrieved from the database.
   
8. Because it is possible for a DB2 database and application program to both define a variable with the same name, application variables in an SQL statement are distinguished by preceding them with a colon (:).

There is a fundamental difference between application programming languages, such as COBOL and SQL. COBOL processes one record at a time; SQL processes tables. Thus, an SQL statement will return a table that the COBOL application program then has to process a row or record at a time. This incompatibility is handled by defining a cursor within the application program.

Think of a cursor as a pointer that indicates the row to be processed. In the preceding example, a cursor is defined for processing the STOCK table. The DECLARE statement defines the cursor associated with a particular query

Opening the cursor in the INITIALIZE paragraph is like opening a file; it gets the table ready for processing. The FETCH statement in the INITIALIZE paragraph retrieves the first row of the table. FETCH is like the READ statement of COBOL, which retrieves one row. A looping structure, based on the READ-ROWS paragraph, is then used to fetch subsequent rows, one at a time, until there are no more rows. Then the cursor is closed, again like a file, and the application program stops.

Reference 2 The SQL Playbook

Section 3 Database Architectures and Implementations

Chapter 11 Data Structure and Storage

Chapter 12 Data Storage and Data Processing Architectures

Chapter 13 Object-Oriented Database Management Systems

• Object Design

Chapter 14 Spatial and Temporal Data Management

Section 4 Organizational Memory Technologies

Chapter 15 Organizational Intelligence Technologies

Chapter 16 The Web and Data Management

Chapter 17 SQL and Java

Chapter 18 XML: Managing Data Exchange

Section 5 Managing Organizational Memory

Chapter 19 Data Integrity

• Generating test data

Chapter 20 Data Administration

• Data integration

Chapter 21 U-Commerce and Data Management

Date revised: October 24, 2009