Filter
Top Products
ISQL and Tools
2-6 FairCom Corporation
ISQL> CREATE INDEX itemnum ON itemmast (im_itemnum);
ISQL> CREATE TABLE custmast (
cm_custnum VARCHAR(5),
cm_zip VARCHAR(10),
cm_state VARCHAR(3),
cm_rating VARCHAR(2),
cm_name VARCHAR(48),
cm_address VARCHAR(48),
cm_city VARCHAR(48));
ISQL> CREATE INDEX custnum ON custmast (cm_custnum);
ISQL> COMMIT WORK;
2.2.3 Manage
This step provides data management functionality for the applica-
tion. In this example we will simply add records to the tables, pro-
cess records in the form of a query, and finally display the results
of the query.
This process involves fetching from 'OrderList' table, and for each
record fetch related records from the 'OrderItems' table based on
the index ordernum. The result is a recordset that amounts to a
list of items associated with an order. The output of the manage
step is a list of items that comprise an order showing name, quantity and price.
The following SQL syntax provides the functionality for the manage phase:
• INSERT INTO - This will add a record by inserting it into the table
• SELECT - Fetch records according to select criteria
• DELETE FROM - Delete records from a table.
• COMMIT WORK - Make changes permanent.
The following SQL statements populate all the tables with data.
INSERT INTO orderlist VALUES ('9/1/2002', '9/5/2002', '1', '1001');
INSERT INTO orderlist VALUES ('9/2/2002', '9/6/2002', '2', '1002');
INSERT INTO orderitems VALUES ('1', 1, 2, '1');
INSERT INTO orderitems VALUES ('1', 2, 1, '2');
INSERT INTO orderitems VALUES ('1', 3, 1, '3');
INSERT INTO orderitems VALUES ('2', 1, 3, '3');
INSERT INTO itemmast VALUES (10, 19.95, '1', 'Hammer');
INSERT INTO itemmast VALUES (3, 9.99, '2', 'Wrench');
INSERT INTO itemmast VALUES (4, 16.59, '3', 'Saw');
INSERT INTO itemmast VALUES (1, 3.98, '4', 'Plyers');