database.sql

IBM RSA下的JSF开发示例

ALTER TABLE TEMPERATURE.AVERAGEHIGHS DROP CONSTRAINT AVERGEHIGHSKEY;

ALTER TABLE STUDENT.GRADES DROP CONSTRAINT GRADESKEY;

ALTER TABLE CANDYFACTORY.CUSTOMERS DROP CONSTRAINT CUSTOMERSKEY;

ALTER TABLE PRODUCT.CATALOGDATA DROP CONSTRAINT CATALOGDATAKEY;

ALTER TABLE CANDYFACTORY.ORDERS DROP CONSTRAINT ORDERSKEY;

ALTER TABLE CANDYFACTORY.INVENTORY DROP CONSTRAINT SQL060908103300280;

ALTER TABLE CANDYFACTORY.INVENTORYSORT DROP CONSTRAINT SORTKEY;

ALTER TABLE CANDYFACTORY.CUSTOMERS2 DROP CONSTRAINT CUSTOMERSKEY2;

DROP TABLE CANDYFACTORY.INVENTORY;

DROP TABLE STUDENT.GRADES;

DROP TABLE CANDYFACTORY.INVENTORYSORT;

DROP TABLE CANDYFACTORY.ORDERS;

DROP TABLE CANDYFACTORY.CUSTOMERS2;

DROP TABLE PRODUCT.CATALOGDATA;

DROP TABLE STUDENT.KEYVALUE;

DROP TABLE CANDYFACTORY.CUSTOMERS;

DROP TABLE TEMPERATURE.AVERAGEHIGHS;

CREATE TABLE CANDYFACTORY.INVENTORY (
		ID INTEGER NOT NULL,
		CATEGORY CHAR(50),
		TITLE CHAR(25),
		DESCRIPTION VARCHAR(200),
		PRICE DECIMAL(7 , 2),
		QUANTITY INTEGER
	);

CREATE TABLE STUDENT.GRADES (
		STUDENT_ID INTEGER NOT NULL,
		STUDENT_NAME CHAR(50),
		MATH INTEGER,
		SCIENCE INTEGER,
		HISTORY INTEGER,
		WRITING INTEGER,
		ART INTEGER,
		UPDATE_KEY BIGINT NOT NULL
	);

CREATE TABLE CANDYFACTORY.INVENTORYSORT (
		ID INTEGER NOT NULL,
		CATEGORY CHAR(50),
		TITLE CHAR(25),
		DESCRIPTION VARCHAR(200),
		PRICE DECIMAL(7 , 2),
		QUANTITY INTEGER
	);

CREATE TABLE CANDYFACTORY.ORDERS (
		ID INTEGER NOT NULL,
		CUSTOMERID INTEGER,
		PRODUCTID INTEGER,
		DATEPLACED TIMESTAMP,
		STATUS CHAR(50)
	);

CREATE TABLE CANDYFACTORY.CUSTOMERS2 (
		ID INTEGER NOT NULL,
		NAME CHAR(50),
		ADDRESS CHAR(50),
		CITY CHAR(50),
		STATE CHAR(2)
	);

CREATE TABLE PRODUCT.CATALOGDATA (
		PRODUCT_ID INTEGER NOT NULL,
		PRODUCT_TYPE CHAR(50),
		PRODUCT_NAME CHAR(50),
		PRODUCT_DESCRIPTION VARCHAR(200),
		PRODUCT_PRICE INTEGER
	);

CREATE TABLE STUDENT.KEYVALUE (
		APPKEY INTEGER NOT NULL,
		KEYCOUNTER INTEGER NOT NULL
	);

CREATE TABLE CANDYFACTORY.CUSTOMERS (
		ID INTEGER NOT NULL,
		NAME CHAR(50),
		ADDRESS CHAR(50),
		CITY CHAR(50),
		STATE CHAR(2)
	);

CREATE TABLE TEMPERATURE.AVERAGEHIGHS (
		CITY CHAR(50) NOT NULL,
		JANUARY INTEGER,
		FEBRUARY INTEGER,
		MARCH INTEGER,
		APRIL INTEGER,
		MAY INTEGER,
		JUNE INTEGER,
		JULY INTEGER,
		AUGUST INTEGER,
		SEPTEMBER INTEGER,
		OCTOBER INTEGER,
		NOVEMBER INTEGER,
		DECEMBER INTEGER,
		RECORDHIGH INTEGER,
		RECORDLOW INTEGER
	);

ALTER TABLE TEMPERATURE.AVERAGEHIGHS ADD CONSTRAINT AVERGEHIGHSKEY PRIMARY KEY (CITY);

ALTER TABLE STUDENT.GRADES ADD CONSTRAINT GRADESKEY PRIMARY KEY (STUDENT_ID);

ALTER TABLE CANDYFACTORY.CUSTOMERS ADD CONSTRAINT CUSTOMERSKEY PRIMARY KEY (ID);

ALTER TABLE PRODUCT.CATALOGDATA ADD CONSTRAINT CATALOGDATAKEY PRIMARY KEY (PRODUCT_ID);

ALTER TABLE CANDYFACTORY.ORDERS ADD CONSTRAINT ORDERSKEY PRIMARY KEY (ID);

ALTER TABLE CANDYFACTORY.INVENTORY ADD CONSTRAINT SQL060908103300280 PRIMARY KEY (ID);

ALTER TABLE CANDYFACTORY.INVENTORYSORT ADD CONSTRAINT SORTKEY PRIMARY KEY (ID);

ALTER TABLE CANDYFACTORY.CUSTOMERS2 ADD CONSTRAINT CUSTOMERSKEY2 PRIMARY KEY (ID);


INSERT INTO CANDYFACTORY.CUSTOMERS
  VALUES(101, 'Bob', '111 Main Street', 'New York City', 'NY');
INSERT INTO CANDYFACTORY.CUSTOMERS
  VALUES(102, 'Sue', '111 North Road', 'Columbus', 'OH');
INSERT INTO CANDYFACTORY.CUSTOMERS
  VALUES(103, 'Fred', '17 Candy Lane', 'Atlanta', 'GA');
INSERT INTO CANDYFACTORY.CUSTOMERS
  VALUES(104, 'Polly', '111 Main Street', 'Boston', 'MA');
INSERT INTO CANDYFACTORY.CUSTOMERS
  VALUES(105, 'Howard', '447 South Avenue', 'Durham', 'NC');
  
INSERT INTO CANDYFACTORY.CUSTOMERS2
  VALUES(101, 'Bob', '111 Main Street', 'New York City', 'NY');
INSERT INTO CANDYFACTORY.CUSTOMERS2
  VALUES(102, 'Sue', '8 North Road', 'Columbus', 'OH');
INSERT INTO CANDYFACTORY.CUSTOMERS2
  VALUES(103, 'Fred', '17 Candy Lane', 'Atlanta', 'GA');
INSERT INTO CANDYFACTORY.CUSTOMERS2
  VALUES(104, 'Polly', '362 Broad Street', 'Boston', 'MA');
INSERT INTO CANDYFACTORY.CUSTOMERS2
  VALUES(105, 'Howard', '447 South Avenue', 'Durham', 'NC');
  
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(1, 'Candy Bar', 'The Nut Bar', 'A Chocolate Bar filled with a variety of nuts.', 0.75, 1500);
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(2, 'Candy Bar', 'The Raspberry Bar', 'A Chocolate Bar filled with raspberry syrup.', 1.00, 750);
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(3, 'Candy Bar', 'The Crunchy Bar', 'A Chocolate Bar filled with crisp rice.', 0.75, 2000);
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(4, 'Gum', 'Bubble Gum', 'Bubble gum flavored gum.', 0.35, 2500);
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(5, 'Gum', 'Cherry Gum', 'Cherry flavored gum.', 0.35, 2000);
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(6, 'Gum', 'Mint Gum', 'Mint flavored gum.', 0.35, 2000);
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(7, 'Hard Candy', 'Lollipops', 'A bag of lollipops - 25 counts - Various Flavors', 3.15, 750);
INSERT INTO CANDYFACTORY.INVENTORY
  VALUES(8, 'Hard Candy', 'Fruit Candies', 'A bag of hard fruit flavored candies - 20 count', 3.10, 500);
  
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(1, 'Candy Bar', 'The Nut Bar', 'A Chocolate Bar filled with a variety of nuts.', 0.75, 1500);
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(2, 'Candy Bar', 'The Raspberry Bar', 'A Chocolate Bar filled with raspberry syrup.', 1.00, 750);
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(3, 'Candy Bar', 'The Crunchy Bar', 'A Chocolate Bar filled with crisp rice.', 0.75, 2000);
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(4, 'Gum', 'Bubble Gum', 'Bubble gum flavored gum.', 0.35, 2500);
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(5, 'Gum', 'Cherry Gum', 'Cherry flavored gum.', 0.35, 2000);
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(6, 'Gum', 'Mint Gum', 'Mint flavored gum.', 0.35, 2000);
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(7, 'Hard Candy', 'Lollipops', 'A bag of lollipops - 25 counts - Various Flavors', 3.15, 750);
INSERT INTO CANDYFACTORY.INVENTORYSORT
  VALUES(8, 'Hard Candy', 'Fruit Candies', 'A bag of hard fruit flavored candies - 20 count', 3.10, 500);

INSERT INTO CANDYFACTORY.ORDERS
  VALUES(1001, 101, 6, '2004-12-23 00:00:00', 'Closed');
INSERT INTO CANDYFACTORY.ORDERS
  VALUES(1002, 104, 8, '2004-12-24 00:00:00', 'Open');
INSERT INTO CANDYFACTORY.ORDERS
  VALUES(1003, 102, 1, '2005-01-03 00:00:00', 'Open');
INSERT INTO CANDYFACTORY.ORDERS
  VALUES(1004, 105, 2, '2005-02-20 00:00:00', 'Open');
  
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(1,  'Television', 'The TV-z14', 'A 14 inch color television set.', 89);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(2, 'Television', 'The TV-z17', 'A 17 inch color television set.', 109);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(3, 'Television', 'The TV-z20', 'A 20 inch color television set.', 159);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(4, 'Television', 'The TV-z27', 'A 27 inch color television set.', 189);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(5, 'Television', 'The TV-hd31', 'A 31 inch high definition television set.', 369);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(6, 'Television', 'The TV-hd37', 'A 37 inch high definition television set.', 499);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(7, 'DVD Player', 'The DVD-x1', 'A single DVD player', 49);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(8, 'DVD Player', 'The DVD-x3', 'A DVD player with a three DVD changer.', 99);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(9, 'DVD Player', 'The DVD-x6', 'A DVD player with a six DVD changer.', 159);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(10, 'Digital Camera', 'DigCam-25', 'A 2.5 megapixel digital camera.', 150);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(11, 'Digital Camera', 'DigCam-35', 'A 3.5 megapixel digital camera.', 200);
INSERT INTO PRODUCT.CATALOGDATA
  VALUES(12, 'Digital Camera', 'DigCam-50', 'A 5 megapixel digital camera.', 300);

INSERT INTO STUDENT.GRADES
  VALUES(1, 'Ethan', 89, 88, 73, 78, 89, 0);
INSERT INTO STUDENT.GRADES
  VALUES(2, 'Sean', 91, 96, 83, 88, 95, 0);
INSERT INTO STUDENT.GRADES
  VALUES(3, 'Thelma', 71, 68, 73, 77, 80, 0);
INSERT INTO STUDENT.GRADES
  VALUES(4, 'Jane', 87, 84, 89, 81, 95, 0);
INSERT INTO STUDENT.GRADES
  VALUES(5, 'Jared', 94, 95, 81, 79, 89, 0);
  
INSERT INTO STUDENT.KEYVALUE VALUES(1,5);

INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Juneau, AK', 31, 34, 39, 48, 56, 62, 64, 63, 56, 47, 38, 33, 90, -22);
INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Sun City, AZ', 68, 73, 78, 86, 95, 105, 108, 106, 101, 90, 77, 68, 122, 20);
INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Twin Falls, ID', 37, 43, 52, 61, 70, 79, 88, 87, 77, 65, 48, 38, 110, -23);
INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Worcester, MA', 31, 34, 43, 54, 66, 74, 79, 77, 69, 58, 47, 36, 99, -13);
INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Unionville, MO', 32, 38, 50, 62, 73, 82, 87, 85, 77, 65, 49, 36, 114, -22);
INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Morrisville, NC', 50, 54, 62, 72, 79, 86, 89, 87, 81, 72, 62, 53, 105, -9);
INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Halfway, OR', 33, 40, 52, 62, 71, 79, 88, 87, 78, 65, 46, 34, 108, -34);
INSERT INTO TEMPERATURE.AVERAGEHIGHS
  VALUES('Kempster, WI', 20, 26, 37, 52, 67, 75, 79, 76, 67, 55, 38, 25, 98, -39);