jpetstore.script

Java/J2EE application framework based on [Expert One-on-One J2EE Design and Development] by Rod John

CREATE USER SA PASSWORD "" ADMIN
/*C1*/CONNECT USER sa PASSWORD ""
create table supplier (\u000a    suppid int not null,\u000a    name varchar(80) null,\u000a    status varchar(2) not null,\u000a    addr1 varchar(80) null,\u000a    addr2 varchar(80) null,\u000a    city varchar(80) null,\u000a    state varchar(80) null,\u000a    zip varchar(5) null,\u000a    phone varchar(80) null,\u000a    constraint pk_supplier primary key (suppid)\u000a)
\u000a\u000acreate table signon (\u000a    username varchar(25) not null,\u000a    password varchar(25)  not null,\u000a    constraint pk_signon primary key (username)\u000a)
\u000a\u000acreate table account (\u000a    userid varchar(80) not null,\u000a    email varchar(80) not null,\u000a    firstname varchar(80) not null,\u000a    lastname varchar(80) not null,\u000a    status varchar(2)  null,\u000a    addr1 varchar(80) not null,\u000a    addr2 varchar(40) null,\u000a    city varchar(80) not  null,\u000a    state varchar(80) not null,\u000a    zip varchar(20) not null,\u000a    country varchar(20) not null,\u000a    phone varchar(80) not null,\u000a    constraint pk_account primary key (userid)\u000a)
\u000a\u000acreate table profile (\u000a    userid varchar(80) not null,\u000a    langpref varchar(80) not null,\u000a    favcategory varchar(30),\u000a    mylistopt int,\u000a    banneropt int,\u000a    constraint pk_profile primary key (userid)\u000a)
\u000a\u000acreate table bannerdata (\u000a    favcategory varchar(80) not null,\u000a    bannername varchar(255)  null,\u000a    constraint pk_bannerdata primary key (favcategory)\u000a)
\u000a\u000acreate table orders (\u000a      orderid int not null,\u000a      userid varchar(80) not null,\u000a      orderdate date not null,\u000a      shipaddr1 varchar(80) not null,\u000a      shipaddr2 varchar(80) null,\u000a      shipcity varchar(80) not null,\u000a      shipstate varchar(80) not null,\u000a      shipzip varchar(20) not null,\u000a      shipcountry varchar(20) not null,\u000a      billaddr1 varchar(80) not null,\u000a      billaddr2 varchar(80)  null,\u000a      billcity varchar(80) not null,\u000a      billstate varchar(80) not null,\u000a      billzip varchar(20) not null,\u000a      billcountry varchar(20) not null,\u000a      courier varchar(80) not null,\u000a      totalprice decimal(10,2) not null,\u000a      billtofirstname varchar(80) not null,\u000a      billtolastname varchar(80) not null,\u000a      shiptofirstname varchar(80) not null,\u000a      shiptolastname varchar(80) not null,\u000a      creditcard varchar(80) not null,\u000a      exprdate varchar(7) not null,\u000a      cardtype varchar(80) not null,\u000a      locale varchar(80) not null,\u000a      constraint pk_orders primary key (orderid)\u000a)
\u000a\u000acreate table orderstatus (\u000a      orderid int not null,\u000a      linenum int not null,\u000a      timestamp date not null,\u000a      status varchar(2) not null,\u000a      constraint pk_orderstatus primary key (orderid, linenum)\u000a)
\u000a\u000acreate table lineitem (\u000a      orderid int not null,\u000a      linenum int not null,\u000a      itemid varchar(10) not null,\u000a      quantity int not null,\u000a      unitprice decimal(10,2) not null,\u000a      constraint pk_lineitem primary key (orderid, linenum)\u000a)
\u000a\u000acreate table category (\u000a\u0009catid varchar(10) not null,\u000a\u0009name varchar(80) null,\u000a\u0009descn varchar(255) null,\u000a\u0009constraint pk_category primary key (catid)\u000a)
\u000a\u000acreate table product (\u000a    productid varchar(10) not null,\u000a    category varchar(10) not null,\u000a    name varchar(80) null,\u000a    descn varchar(255) null,\u000a    constraint pk_product primary key (productid),\u000a        constraint fk_product_1 foreign key (category)\u000a        references category (catid)\u000a)
\u000a\u000acreate index productCat on product (category)
\u000acreate index productName on product (name)
\u000a\u000acreate table item (\u000a    itemid varchar(10) not null,\u000a    productid varchar(10) not null,\u000a    listprice decimal(10,2) null,\u000a    unitcost decimal(10,2) null,\u000a    supplier int null,\u000a    status varchar(2) null,\u000a    attr1 varchar(80) null,\u000a    attr2 varchar(80) null,\u000a    attr3 varchar(80) null,\u000a    attr4 varchar(80) null,\u000a    attr5 varchar(80) null,\u000a    constraint pk_item primary key (itemid),\u000a        constraint fk_item_1 foreign key (productid)\u000a        references product (productid),\u000a        constraint fk_item_2 foreign key (supplier)\u000a        references supplier (suppid)\u000a)
\u000a\u000acreate index itemProd on item (productid)
\u000a\u000acreate table inventory (\u000a    itemid varchar(10) not null,\u000a    qty int not null,\u000a    constraint pk_inventory primary key (itemid)\u000a)
\u000a\u000aCREATE TABLE sequence\u000a(\u000a    name               varchar(30)  not null,\u000a    nextid             int          not null,\u000a    constraint pk_sequence primary key (name)\u000a)
INSERT INTO SEQUENCE VALUES('ordernum',1000)
INSERT INTO SEQUENCE VALUES('linenum',1000)
INSERT INTO SIGNON VALUES('j2ee','j2ee')
INSERT INTO SIGNON VALUES('ACID','ACID')
INSERT INTO ACCOUNT VALUES('j2ee','[e-mail]','ABC','XYX','OK','901 San Antonio Road','MS UCUP02-206','Palo Alto','CA','94303','USA','555-555-5555')
INSERT INTO ACCOUNT VALUES('ACID','[e-mail]','ABC','XYX','OK','901 San Antonio Road','MS UCUP02-206','Palo Alto','CA','94303','USA','555-555-5555')
INSERT INTO PROFILE VALUES('j2ee','english','DOGS',1,1)
INSERT INTO PROFILE VALUES('ACID','english','CATS',1,1)
INSERT INTO BANNERDATA VALUES('FISH','<image src="../images/banner_fish.gif">')
INSERT INTO BANNERDATA VALUES('CATS','<image src="../images/banner_cats.gif">')
INSERT INTO BANNERDATA VALUES('DOGS','<image src="../images/banner_dogs.gif">')
INSERT INTO BANNERDATA VALUES('REPTILES','<image src="../images/banner_reptiles.gif">')
INSERT INTO BANNERDATA VALUES('BIRDS','<image src="../images/banner_birds.gif">')
INSERT INTO CATEGORY VALUES('FISH','Fish','<image src="../images/fish_icon.gif"><font size="5" color="blue"> Fish</font>')
INSERT INTO CATEGORY VALUES('DOGS','Dogs','<image src="../images/dogs_icon.gif"><font size="5" color="blue"> Dogs</font>')
INSERT INTO CATEGORY VALUES('REPTILES','Reptiles','<image src="../images/reptiles_icon.gif"><font size="5" color="blue"> Reptiles</font>')
INSERT INTO CATEGORY VALUES('CATS','Cats','<image src="../images/cats_icon.gif"><font size="5" color="blue"> Cats</font>')
INSERT INTO CATEGORY VALUES('BIRDS','Birds','<image src="../images/birds_icon.gif"><font size="5" color="blue"> Birds</font>')
INSERT INTO PRODUCT VALUES('FI-SW-01','FISH','Angelfish','<image src="../images/fish1.jpg">Salt Water fish from Australia')
INSERT INTO PRODUCT VALUES('FI-SW-02','FISH','Tiger Shark','<image src="../images/fish4.gif">Salt Water fish from Australia')
INSERT INTO PRODUCT VALUES('FI-FW-01','FISH','Koi','<image src="../images/fish3.gif">Fresh Water fish from Japan')
INSERT INTO PRODUCT VALUES('FI-FW-02','FISH','Goldfish','<image src="../images/fish2.gif">Fresh Water fish from China')
INSERT INTO PRODUCT VALUES('K9-BD-01','DOGS','Bulldog','<image src="../images/dog2.gif">Friendly dog from England')
INSERT INTO PRODUCT VALUES('K9-PO-02','DOGS','Poodle','<image src="../images/dog6.gif">Cute dog from France')
INSERT INTO PRODUCT VALUES('K9-DL-01','DOGS','Dalmation','<image src="../images/dog5.gif">Great dog for a Fire Station')
INSERT INTO PRODUCT VALUES('K9-RT-01','DOGS','Golden Retriever','<image src="../images/dog1.gif">Great family dog')
INSERT INTO PRODUCT VALUES('K9-RT-02','DOGS','Labrador Retriever','<image src="../images/dog5.gif">Great hunting dog')
INSERT INTO PRODUCT VALUES('K9-CW-01','DOGS','Chihuahua','<image src="../images/dog4.gif">Great companion dog')
INSERT INTO PRODUCT VALUES('RP-SN-01','REPTILES','Rattlesnake','<image src="../images/lizard3.gif">Doubles as a watch dog')
INSERT INTO PRODUCT VALUES('RP-LI-02','REPTILES','Iguana','<image src="../images/lizard2.gif">Friendly green friend')
INSERT INTO PRODUCT VALUES('FL-DSH-01','CATS','Manx','<image src="../images/cat3.gif">Great for reducing mouse populations')
INSERT INTO PRODUCT VALUES('FL-DLH-02','CATS','Persian','<image src="../images/cat1.gif">Friendly house cat, doubles as a princess')
INSERT INTO PRODUCT VALUES('AV-CB-01','BIRDS','Amazon Parrot','<image src="../images/bird4.gif">Great companion for up to 75 years')
INSERT INTO PRODUCT VALUES('AV-SB-02','BIRDS','Finch','<image src="../images/bird1.gif">Great stress reliever')
INSERT INTO SUPPLIER VALUES(1,'XYZ Pets','AC','600 Avon Way','','Los Angeles','CA','94024','212-947-0797')
INSERT INTO SUPPLIER VALUES(2,'ABC Pets','AC','700 Abalone Way','','San Francisco ','CA','94024','415-947-0797')
INSERT INTO ITEM VALUES('EST-1','FI-SW-01',16.50,10.00,1,'P','Large',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-2','FI-SW-01',16.50,10.00,1,'P','Small',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-19','AV-SB-02',15.50,2.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-20','FI-FW-02',5.50,2.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-21','FI-FW-02',5.29,1.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-22','K9-RT-02',135.50,100.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-23','K9-RT-02',145.49,100.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-24','K9-RT-02',255.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-25','K9-RT-02',325.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-26','K9-CW-01',125.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-27','K9-CW-01',155.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-28','K9-RT-01',155.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO INVENTORY VALUES('EST-1',10000)
INSERT INTO INVENTORY VALUES('EST-2',10000)
INSERT INTO INVENTORY VALUES('EST-3',10000)
INSERT INTO INVENTORY VALUES('EST-4',10000)
INSERT INTO INVENTORY VALUES('EST-5',10000)
INSERT INTO INVENTORY VALUES('EST-6',10000)
INSERT INTO INVENTORY VALUES('EST-7',10000)
INSERT INTO INVENTORY VALUES('EST-8',10000)
INSERT INTO INVENTORY VALUES('EST-9',10000)
INSERT INTO INVENTORY VALUES('EST-10',10000)
INSERT INTO INVENTORY VALUES('EST-11',10000)
INSERT INTO INVENTORY VALUES('EST-12',10000)
INSERT INTO INVENTORY VALUES('EST-13',10000)
INSERT INTO INVENTORY VALUES('EST-14',10000)
INSERT INTO INVENTORY VALUES('EST-15',10000)
INSERT INTO INVENTORY VALUES('EST-16',10000)
INSERT INTO INVENTORY VALUES('EST-17',10000)
INSERT INTO INVENTORY VALUES('EST-18',10000)
INSERT INTO INVENTORY VALUES('EST-19',10000)
INSERT INTO INVENTORY VALUES('EST-20',10000)
INSERT INTO INVENTORY VALUES('EST-21',10000)
INSERT INTO INVENTORY VALUES('EST-22',10000)
INSERT INTO INVENTORY VALUES('EST-23',10000)
INSERT INTO INVENTORY VALUES('EST-24',10000)
INSERT INTO INVENTORY VALUES('EST-25',10000)
INSERT INTO INVENTORY VALUES('EST-26',10000)
INSERT INTO INVENTORY VALUES('EST-27',10000)
INSERT INTO INVENTORY VALUES('EST-28',10000)