HP c-tree-SQL ISQL and Tools manual Values Insert Into

ISQL and Tools

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.3Manage

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');

2-6 FairCom Corporation