REM 以下是創建Toyz/Toyz用戶方案的實現腳本
prompt
------------------------------------------------------------------------------------------
prompt 連接管理員用戶
connect sys/sys as sysdba;
prompt 如果有Toyz用戶則刪除
drop user Toyz cascade;
prompt 創建用戶Toyz
create user Toyz identified by Toyz;
prompt 向用戶Toyz授予connect,resource角色權限
grant connect,resource to Toyz;
prompt 修改Toyz在system中的表空間的配額
alter user Toyz quota unlimited on system;
prompt 連接用戶Toyz
connect Toyz/Toyz;
prompt
***************************************************************************************
prompt 以下是創建Toyz/Toyz用戶表的實現腳本
prompt
***************************************************************************************
prompt 玩具表(Toys)
CREATE TABLE Toys
(
cToyId
char(6),
vToyName
varchar2(20),
vToyDescription varchar2(250),
cCategoryId
char(3),
mToyPrice
number(5,2),
cBrandId
char(3),
siToyQty
smallint,
siLowerAge
smallint,
siUpperAge
smallint,
siToyWeight
smallint,
vToyImgPath varchar2(50)
);
prompt 玩具類別表(Category)
CREATE TABLE Category
(
cCategoryId
char(3),
cCategory
char(20),
vDescription varchar2(100)
);
prompt 包裝表(Wrapper)
CREATE TABLE Wrapper
(
cWrapperId
char(3),
vDescription varchar2(20),
mWrapperPrice number(5,2),
vWrapperImgPath varchar2(50)
);
prompt 玩具品牌表(ToyBrand)
CREATE TABLE ToyBrand
(
cBrandId char(3) ,
cBrandName char(20)
);
prompt 國家表(Country)
CREATE TABLE Country
(
cCountryId char(3) ,
cCountry char(25)
);
prompt 運輸方式表(ShippingMode)
CREATE TABLE ShippingMode
(
cModeId char(2) ,
cMode
char(25),
iMaxDelDays int
);
prompt 運輸價格表(ShippingRate)
CREATE TABLE ShippingRate
(
cCountryID char(3) ,
cModeId
char(2),
mRatePerPound number(5,2) not null
);
prompt 購物車表(ShippingRate)
CREATE TABLE ShoppingCart
(
cCartId
char(6),
cToyId char(6),
siQty smallint
);
prompt 購物者表(Shopper)
CREATE TABLE Shopper
(
cShopperId
char(6),
vFirstName
varchar2(20) ,
vEmailId varchar2(40) ,
vAddress
varchar2(40) ,
cState
char(15) ,
cCountryId
char(3),
cZipCode char(10),
cPhone
char(15),
cCreditCardNo
char(16),
vCreditCardType
varchar2(15),
dExpiryDate
date
);
prompt 訂單表(Orders)
CREATE TABLE Orders
(
cOrderNo
char(6),
dOrderDate
date,
cCartId char(6),
cShopperId char(6),
cShippingModeId char(2),
mShippingCharges
number(5,2),
mGiftWrapChargesnumber(5,2),
cOrderProcessed char(1),
mTotalCost number(8,2),
dExpDelDate Date
);
prompt 訂單詳情表(OrderDetail)
CREATE TABLE OrderDetail
(
cOrderNo
char(6),
cToyId
char(6),
siQty
smallint,
cGiftWrap char(1),
cWrapperId char(3),
vMessage varchar2(256),
mToyCost number(8,2)
);
prompt 運輸情況表(Shipment)
CREATE TABLE Shipment
(
cOrderNo char(6),
dShipmentDate date,
cDeliveryStatus char(1),
dActualDeliveryDate
date
);
prompt 接收者表(Recipient)
CREATE TABLE Recipient
(
cOrderNo char(6) ,
vFirstName
varchar2(20),
vAddress varchar2(20),
cCity
char(15),
cState char(15),
cCountryId char(3),
cZipCode char(10) ,
cPhone char(15)
);
prompt 月銷售情況表(PickOfMonth)
CREATE TABLE PickOfMonth
(
cToyId
char(6),
siMonth smallint,
iYear int,
iTotalSold int
);
prompt ***************************************************************************************
prompt 以下是創建Toyz/Toyz用戶表數據的腳本
prompt
***************************************************************************************
prompt 對玩具表(Toys)插入數據
INSERT INTO toys VALUES('000001','捕鯨','一個巨大的藍鯨，帶有兩個手柄，可以讓小孩騎在它的背上。','001', 8.99 ,'001',50,3,9,1,null);
INSERT INTO toys VALUES('000002','水管道系統','小孩喜歡玩水。水管道系統有22塊可互換的板片，這些板片可以組裝一個帶起重機、水輪和四搜船的碼頭，這只水輪產生水流。','001', 33.99,'001',60,5,9,2,null);
INSERT INTO toys VALUES('000003','降落傘與火箭','站在發射臺上發射火箭，隨后一個降落傘從火箭上慢慢降落在地面上。
','001', 6.99,'003',90,7,9,1,null);
INSERT INTO toys VALUES('000004','大暴雨','用這個大暴雨裝置可以在你們家花園里制造人工降雨。','001', 35.99,'005',74,8,9,1,null);
INSERT INTO toys VALUES('000005','發光的展示燈','用一組彩色場景共同建立一個旋轉的、能表示不同心情的情緒燈。','002',
15.99,'001',58,7,9,1,null);
INSERT INTO toys VALUES('000006','玻璃裝飾','？使你變得漂亮,象書上精美的圖片一樣。這一套包括八色的顏料，一個漆刷，樣品和完整的使用說明書。','002', 12.99,'004',99,8,9,2,null);
INSERT INTO toys VALUES('000007','tie dye kit？工具箱','檢查原始的玩具列表','002',
19.99,'002',76,7,9,1,null);
INSERT INTO toys VALUES('000008','愛麗絲奇境記','一本大的彩色書籍','002',
14.99,'001',82,4,8,1,null);
INSERT INTO toys VALUES('000009','迷人的洋娃娃','穿著藍棉布衣服和夾克的她，已經為試演做好拍攝準備。她有一個攝像機和紅的大手提袋，以攜帶膠片傳動裝置。','002', 18.99,'001',39,6,9,2,null);
INSERT INTO toys VALUES('000010','泡泡仙女娃娃','你的制作夢幻小女孩的夢想可以通過這套玩具成真。 你只需將魔棒蘸在有魔力的溶液里，然后按她腳上的按鈕。緊接著，她就開始旋轉，并吹出很多泡泡。這套玩具包括能產生泡沫的溶液，裝溶液的盤，三個魔棒。這些魔棒能很容易地吸附在仙女娃娃的手腕上。','002',9.99,'002',78,4,8,1,null);
INSERT INTO toys VALUES('000011','睡美人','讓你的孩子用睡美人多爾扮演奇妙的神話故事。 ','002', 18.99,'005',65,4,8,1,null);
INSERT INTO toys VALUES('000012','可愛的洋娃娃','漂亮的洋娃娃牽著小寵物。','002',
10.99,'001',82,4,8,1,null);
INSERT INTO toys VALUES('000013','有漂亮頭發的洋娃娃','小女孩們能改變這個洋娃娃頭發的式樣和顏色。','002',
14.99,'003',55,4,8,1,null);
INSERT INTO toys VALUES('000015','愛花的洋娃娃','洋娃娃手上拿著一束花。','002',
49.99,'004',43,8,9,1,null);
INSERT INTO toys VALUES('000016','維多利亞玩具房','這個漂亮的玩具房，會讓每一個女孩子都喜歡它。',
'002',43.25,'003',36,5,9,1,null);
INSERT INTO toys VALUES('000017','廚房用具','一整套廚房用具，包括爐子和所有其他的廚房小用具。','006',
23.99,'002',76,5,9,2,null);
INSERT INTO toys VALUES('000018','兒童臥室','兒童臥室放置著帶梯子和壁櫥的床鋪。 ','006',
16.99,'005',15,5,9,2,null);
INSERT INTO toys VALUES('000019','托兒所','這個托兒所有嬰兒床和嬰兒。','006', 8.99,'001',35,4,9,2,null);
INSERT INTO toys VALUES('000020','維多利亞式家庭？','當你的小孩帶著維多利亞式家庭去戶外玩耍，它能讓你的孩子按時回家。','006',
8.99,'001',45,4,9,2,null);
INSERT INTO toys VALUES('000021','生日宴會','這個孩子們的聚會有玩魔術的魔術師，大游戲輪，樹，各種形狀和大小的氣球，禮物和游戲。 ','006', 25.99,'004',56,4,9,1,null);
INSERT INTO toys VALUES('000023','錫鼓','這個錫鼓適于鄰里間表演，它附帶有用于掛在身上的皮帶和鼓槌。','012', 15.99,'001',88,3,8,1,null);
INSERT INTO toys VALUES('000024','大音量的吉他','這個吉他有雙重音量控制和放大電路，附帶有護肩的襯墊和電池。','012',
25.99,'001',75,5,8,1,null);
INSERT INTO toys VALUES('000025','我的第一個手電筒 ','本產品使得手電筒使用簡單并有娛樂性。這個耐用的手電筒有一個大按鈕方便開關。 需要一個AA電池（不包含在本產品之內）。','013', 7.99,'003',65,3,5,1,null);
INSERT INTO toys VALUES('000026','電子保險箱','大尺寸的保險箱。只需按一個按鈕就打開保險箱并聽到警報聲。把錢通過后面的秘密狹通道放入保險箱，并使用電子號碼鎖進行開啟。','013', 22.99,'005',66,5,9,1,null);
INSERT INTO toys VALUES('000027','X-90賽車套件','快速變化的賽車軌道是X-90賽車的最終挑戰。套件還有帶發動機的小汽車，這些小汽車的速度很快并可以做出刺激的賽車動作。','005', 19.99,'001',77,5,9,1,null);
INSERT INTO toys VALUES('000028','沙丘接力賽','一套帶有賽車道的沙丘童車。','005',
9.99,'004',78,4,9,1,null);
INSERT INTO toys VALUES('000029','螺旋上升的道路','讓孩子用這個易于組裝的軌道，充分享受賽車的刺激和樂趣。','005',
14.99,'002',88,2,7,2,null);
INSERT INTO toys VALUES('000030','卡車賽跑 ','可以用這個有超大輪胎的塑料卡車進行比賽。這個卡車很耐用','005',
35.99,'005',78,3,7,2,null);
INSERT INTO toys VALUES('000031','大鴨子','這只大鴨子大而可愛，摸上去柔軟。它用耐用的棉花制成，填充著可機洗的防火材料。','009', 17.99,'001',88,1,2,1,null);
INSERT INTO toys VALUES('000032','嬰兒明妮','你的孩子只要一接觸明妮，她就發出嘎嘎的聲音，像真的一樣。
','009', 14.99,'002',66,1,3,1,null);
COMMIT;
prompt 對玩具類別表(Category)插入數據
INSERT INTO Category VALUES('001','活動性類','活動性玩具能培養孩子的社會技能并他們對周圍的世界感興趣。');
INSERT INTO Category VALUES('002','洋娃娃類','廣泛來自所有領先品牌的洋娃娃玩具');
INSERT INTO Category VALUES('003','藝術與雕塑','鼓勵孩子們用這些難以至信的雕塑工具箱去創造杰作');
INSERT INTO Category VALUES('004','游戲','新的和經典游戲大全');
INSERT INTO Category VALUES('005','汽車接力賽','所有當今汽車的模型');
INSERT INTO Category VALUES('006','洋裝玩耍','這些游戲對孩子的智力開發起著重要的作用');
INSERT INTO Category VALUES('007','模型工具箱類','用于構造飛機、汽車、輪船等許多玩具的模型');
INSERT INTO Category VALUES('008','嬰兒類','為嬰兒設計的彩色的、交互式玩具');
INSERT INTO Category VALUES('009','塑料的玩具','熊、猴子以及許多軟玩具');
INSERT INTO Category VALUES('010','學習類','把玩具設計得如此有趣，以致孩子們忘了同時是在學習');
INSERT INTO Category VALUES('011','科學與自然類','激發孩子探索周圍世界的玩具');
INSERT INTO Category VALUES('012','音樂玩具類','玩具可發出音樂、聲音和歌曲');
INSERT INTO Category VALUES('013','電動類','需用電池的電動玩具');
INSERT INTO Category VALUES('014','木偶類','這些是木偶類玩具');
INSERT INTO Category VALUES('015','火車類','這些火車是任何鐵路系統的驕傲');
INSERT INTO Category VALUES('016','建筑玩具類','鼓勵孩子們建筑模型房和建筑物的磚、建筑材料和其他玩具');
COMMIT;
prompt 對包裝表(Wrapper)插入數據
INSERT INTO Wrapper VALUES('001','壁虎',1,null);
INSERT INTO Wrapper VALUES('002','可愛',1.25,null);
INSERT INTO Wrapper VALUES('003','星星',1.50,null);
INSERT INTO Wrapper VALUES('004','氣泡',2,null);
INSERT INTO Wrapper VALUES('005','芝麻街',1.5,null);
INSERT INTO Wrapper VALUES('006','月亮',2.25,null);
INSERT INTO Wrapper VALUES('007','海洋',1,null);
INSERT INTO Wrapper VALUES('008','天空',1,null);
COMMIT;
prompt 對玩具品牌表(ToyBrand)插入數據
INSERT INTO ToyBrand VALUES('001','機器貓');
INSERT INTO ToyBrand VALUES('002','藍貓');
INSERT INTO ToyBrand VALUES('003','柏林小子');
INSERT INTO ToyBrand VALUES('004','西游記');
INSERT INTO ToyBrand VALUES('005','百雪公主');
INSERT INTO ToyBrand VALUES('006','瘋狂世界');
INSERT INTO ToyBrand VALUES('007','藍精靈');
INSERT INTO ToyBrand VALUES('008','三國演義');
COMMIT;
prompt 對國家表(Country)插入數據
INSERT INTO Country VALUES('001','美國');
INSERT INTO Country VALUES('002','阿爾巴利亞');
INSERT INTO Country VALUES('003','安道爾');
INSERT INTO Country VALUES('004','阿根廷');
INSERT INTO Country VALUES('005','澳大利亞');
INSERT INTO Country VALUES('006','孟加拉');
INSERT INTO Country VALUES('007','比利時');
INSERT INTO Country VALUES('008','波斯尼亞');
INSERT INTO Country VALUES('009','保加利亞');
INSERT INTO Country VALUES('010','捷克斯羅伐克');
INSERT INTO Country VALUES('011','丹麥');
INSERT INTO Country VALUES('012','南斯拉夫 ');
INSERT INTO Country VALUES('013','法國');
INSERT INTO Country VALUES('014','直布羅陀');
INSERT INTO Country VALUES('015','希臘');
INSERT INTO Country VALUES('016','格魯吉亞');
INSERT INTO Country VALUES('017','克羅地亞');
INSERT INTO Country VALUES('018','愛沙尼亞');
INSERT INTO Country VALUES('019','冰島');
INSERT INTO Country VALUES('020','意大利');
INSERT INTO Country VALUES('021','以色列');
INSERT INTO Country VALUES('022','印度');
INSERT INTO Country VALUES('023','塞浦路斯');
INSERT INTO Country VALUES('024','俄羅斯');
INSERT INTO Country VALUES('025','列支敦士登');
INSERT INTO Country VALUES('027','立陶宛');
INSERT INTO Country VALUES('028','盧森堡');
INSERT INTO Country VALUES('030','馬爾他');
INSERT INTO Country VALUES('031','Moldova');
INSERT INTO Country VALUES('032','摩納哥');
INSERT INTO Country VALUES('033','荷蘭');
INSERT INTO Country VALUES('034','挪威');
INSERT INTO Country VALUES('035','波蘭');
INSERT INTO Country VALUES('036','葡萄牙');
INSERT INTO Country VALUES('037','羅馬尼亞');
INSERT INTO Country VALUES('038','San
Marino');
INSERT INTO Country VALUES('039','斯洛伐克共和國');
INSERT INTO Country VALUES('040','斯洛文尼亞');
INSERT INTO Country VALUES('041','芬蘭');
INSERT INTO Country VALUES('042','葡萄牙');
INSERT INTO Country VALUES('043','瑞士');
INSERT INTO Country VALUES('044','土爾其');
INSERT INTO Country VALUES('045','烏克蘭');
INSERT INTO Country VALUES('046','匈牙利');
INSERT INTO Country VALUES('047','德國');
INSERT INTO Country VALUES('048','瑞典');
INSERT INTO Country VALUES('049','拉托維亞');
COMMIT;
prompt 對運輸方式表(ShippingMode)插入數據
INSERT INTO ShippingMode VALUES('01','標準航運',4);
INSERT INTO ShippingMode VALUES('02','國際郵件',3);
INSERT INTO ShippingMode VALUES('03','國際航運',1);
COMMIT;
prompt 對運輸價格表(ShippingRate)插入數據
INSERT INTO ShippingRate VALUES('001','01',2);
INSERT INTO ShippingRate VALUES('001','02',4);
INSERT INTO ShippingRate VALUES('001','03',10);
INSERT INTO ShippingRate VALUES('002','01',03);
INSERT INTO ShippingRate VALUES('002','02',06);
INSERT INTO ShippingRate VALUES('002','03',10);
INSERT INTO ShippingRate VALUES('003','01',04);
INSERT INTO ShippingRate VALUES('003','02',06);
INSERT INTO ShippingRate VALUES('003','03',12);
INSERT INTO ShippingRate VALUES('004','01',04);
INSERT INTO ShippingRate VALUES('004','02',08);
INSERT INTO ShippingRate VALUES('004','03',12);
INSERT INTO ShippingRate VALUES('005','01',04);
INSERT INTO ShippingRate VALUES('005','02',08);
INSERT INTO ShippingRate VALUES('005','03',12);
INSERT INTO ShippingRate VALUES('006','01',04);
INSERT INTO ShippingRate VALUES('006','02',08);
INSERT INTO ShippingRate VALUES('006','03',12);
INSERT INTO ShippingRate VALUES('007','01',04);
INSERT INTO ShippingRate VALUES('007','02',06);
INSERT INTO ShippingRate VALUES('007','03',10);
INSERT INTO ShippingRate VALUES('008','01',04);
INSERT INTO ShippingRate VALUES('008','02',05);
INSERT INTO ShippingRate VALUES('008','03',08);
INSERT INTO ShippingRate VALUES('009','01',10);
INSERT INTO ShippingRate VALUES('009','02',12);
INSERT INTO ShippingRate VALUES('009','03',15);
INSERT INTO ShippingRate VALUES('010','01',10);
INSERT INTO ShippingRate VALUES('010','02',12);
INSERT INTO ShippingRate VALUES('010','03',15);
COMMIT;
prompt 對購物者表(Shopper)插入數據
INSERT INTO Shopper VALUES('000001','史密斯','angelas@qmail.com','海濱大道227號','弗吉尼亞州','001','22191','227-2344','6947343412896785','MASTER',to_date('08/09/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000002','拉爾森','barbaraj@speedmail.com','海濱大道227號','加利福尼亞州','001','94087-1147','123-5673','5345146765854356','MASTER',to_date('04/10/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000003','威廉','bettyw@dpeedmil.cm','線索路1號','弗吉尼亞州','001','23455','458-3299','4747343412896785','Visa卡',to_date('12/12/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000004','卡爾','carolj@qmail.com','765 - 萊卡大道 ','北卡羅萊納州','001','28607','678-4544','6344676854335436','Visa
Card',to_date('10/12/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000005','羅伯特','catheriner@qmail.com','5508 來克大街','加利福尼亞州','001','95123','445-2256','3756784562869963','MASTER',to_date('09/10/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000006','布朗','charlesb@speedmail.com','7822
S. 樹蔭大道 ','弗羅里達州','001','32751','225-6678','3454678545443344','Visa
Card',to_date('10/09/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000007','戴維斯','Christopherd@qmail.com','4896
11th 大濱道 ','猶他州','001','84056-5410','556-9087','7899887675443322','Visa
Card',to_date('10/02/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000008','米勒','cynthiam@qmailcom','98066 華爾大道','加利福尼亞州','001','93021-2930','422-5688','2345566576879900','MASTER',to_date('08/09/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000009','丹尼爾','danielw@speedmail.com','4642 人民大街 ','俄亥俄州','001','44141','454-2246','2345467890986745','Visa
Card',to_date('11/02/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000010','摩爾','davidm@qmail.com','8808 快樂大道 ','加利福尼亞州','001','94583','982-5577','2343556678799674','Visa Card',to_date('11/12/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000011','泰勒','deboraht@qmail.com','2199- 真理大道','明尼蘇達州','001','60048','889-2235','2345468798078563','MASTER',to_date('11/23/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000012','安得森','Donnaa@speedmail.com ','7930 奧良大道. ','內華達州','001','89117','845-2323','2314345676568766','Visa
Card',to_date('11/24/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000013','托馬斯','dorthyt@speedmail.com','678 東道 56號
正明大道- #12','紐約州','001','10009','696-2278','8765435456678754','Visa
Card',to_date('12/03/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000014','杰克遜','elizabethj@qmail.com','598 頂點街 #2','明尼蘇達州','001','55102','545-9078','4576544354567542','Visa
Card',to_date('10/06/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000015','特拉','francest@speedmail.com','2562 東道 ','德克薩斯州','001','76205-5922','878-6670','5676879007565452','Master
Card',to_date('08/30/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000016','海倫','helenw@spedmail.com',null,'賓夕凡尼亞州','001','18950
','585-7796','4564564564564565','Visa Card',to_date('10/10/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000017','瞻姆斯',' helenw@spedmail.com',Null,'弗吉尼亞州','001','22303-2541','335-6678','4657567545344544','Master
Card',to_date('10/31/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000018','馬丁','jenniferm@qmail.com',null,'馬里蘭州','001','21225','569-7789','9775445343233443','Visa
Card',to_date('09/01/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000019','湯普森','jessicat@speedmail.com','565 圣石大道. ','德克薩斯州','001','76014','445-6797','9766545343233455','Master
Card',to_date('09/19/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000020','約翰遜','johng@qmail.com','2234 B 國王大道','賓夕凡尼亞州','001','17109','521-9095','8764554334534567','Master
Card',to_date('09/13/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000021','愛德華','josephm@qmail.com','995 福尼大道','加利福尼亞州','001','92354','456-9032','5687567567435344','Visa
Card',to_date('05/31/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000022','飛利普','joycep@speedmail.com','535 達爾文街 ','賓夕凡尼亞州','001','15017','789-6905','2345345345645656','Master
Card',to_date('09/15/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000023','魯濱遜','karenr@qmail.com','2343-A 花道
','喬治亞州','001','30067','334-5568','4546575675687689','Visa
Card',to_date('02/26/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000024','克拉克','kimberlyc@speedmail.com','79 英雄大道 ','阿肯阿州','001','72204','645-9023','2345653757898999','Master
Card',to_date('05/31/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000025','勞拉','laurar@speedmail.com',Null,'喬治亞州','001','30062','567-3345','2345345676786543','Visa
Card',to_date('03/03/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000026','劉易斯','lindal@qmail.com','1524 巴塔尼亞大道 ','德克薩斯州','001','75075','459-4563','8765434523543366','Visa
Card',to_date('03/14/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000027','琳莎','lisal@speedmail.com','18927 沙巖街 ','華圣頓州','001','98133','897-3345','7653534745756567','Master
Card',to_date('08/30/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000028','湯姆','margaretw@speedmail.com','405 亞丁道#101 ','華圣頓州','001','99163','567-9083','9867865434534467','Master
Card',to_date('07/09/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000029','瑪利婭','Mariah@speedmail.com','936 米德爾','紐約州','001','14609','345-8764','9786434564564567','Visa
Card',to_date('04/06/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000030','瑪麗','marya@qmail.com','1202 總統大街','明尼蘇達州','001','61801-5304','749-3096','8674564574574356','Master
Card',to_date('04/23/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000031','米切爾','michaely@speedmail.com',Null
,'阿拉巴馬州','001','36117','560-9004','3478786786785677','Master
Card',to_date('02/21/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000032','米切爾','michelleh@speedmail.com','1353
國王街','明尼蘇達州','001','60563','294-5385','6965753564534554','Visa
Card',to_date('01/02/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000033','南希','nancyk@qmail.com','429 華林大道 ','馬薩州','001','01845','563-2298','3464276587468846','Master
Card',to_date('01/01/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000034','布什','patreciaw@speedmail.com',Null,'加利福尼亞州','001','94517-1440','345-8765','3743567985785344','Master
Card',to_date('02/21/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000035','克靈頓','paull@qmail.com','79 英雄大道 ','加利福尼亞州','001','91401','912-7905','4436465768677778','Master
Card',to_date('03/03/2000','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000036','希爾','richardh@speedmail.com','405 亞丁道#101 ','加利福尼亞州','001','60022','459-8749','9568765745645666','Visa
Card',to_date('10/06/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000037','羅伯特','Roberts@speedmail.com','405 亞丁道#101 ','加利福尼亞州','001','94618','709-5565','8678457546556555','Master
Card',to_date('10/02/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000038','露絲','ruthg@speedmail.com','459 橋道','新澤西','001','07945','347-9082','9887654445423443','Visa Card',to_date('12/02/2001','mm/dd/yyyy'));
INSERT INTO Shopper VALUES('000039','亞當斯','Sandra@qmail.com','1524 巴塔尼亞大道 ','弗羅里達州','001','32824','982-9503','7455463534636555','Master
Card',to_date('04/18/2000','mm/dd/yyyy'));
COMMIT;
prompt 對接受者表(Recipient)插入數據
INSERT INTO Recipient VALUES('000001','約翰遜','227 海濱大道.','桑德蘭','加利福尼亞州','001','94087-1147','123-5673');
INSERT INTO Recipient VALUES('000002','羅伯特','5508 國王大道','圣何塞','加利福尼亞州','001','95233-4123','445-2256');
INSERT INTO Recipient VALUES('000003','戴維斯','4896 卡路樂大道','圣城','猶他州','001','84056-5410','556-9087');
INSERT INTO Recipient VALUES('000005','約翰遜','227 海島嶼道.','桑德蘭','加利福尼亞州','001','94087-1147','123-5673');
INSERT INTO Recipient VALUES('000006','安得森','7930奧爾街.','拉斯韋加期','內華達州','001','89328-2517','845-2323');
INSERT INTO Recipient VALUES('000009','摩爾','8808 快樂道','雷勞','加利福尼亞州','001','94453-5849','982-5577');
INSERT INTO Recipient VALUES('000010','威廉姆','1 步行街','弗吉尼亞','弗吉尼亞州','001','23455-2456','458-3299');
INSERT INTO Recipient VALUES('000004','馬丁','9812 76th 正道','Brooklyn','馬里蘭州','001','21254-0025','569-7789');
INSERT INTO Recipient VALUES('000007','勞拉','3242 達爾文 ','治亞','喬治亞州','001','30062-5423','567-3345');
INSERT INTO Recipient VALUES('000008','米切爾','1353 域湖大道','內珀維爾','明尼蘇達州','001','60563-1256','294-5385');
COMMIT;
prompt 對購物車表(ShoppingCart)插入數據
INSERT INTO ShoppingCart VALUES('000001','000001',1);
INSERT INTO ShoppingCart VALUES('000001','000007',1);
INSERT INTO ShoppingCart VALUES('000001','000008',1);
INSERT INTO ShoppingCart VALUES('000002','000016',1);
INSERT INTO ShoppingCart VALUES('000002','000009',1);
INSERT INTO ShoppingCart VALUES('000003','000017',1);
INSERT INTO ShoppingCart VALUES('000004','000030',1);
INSERT INTO ShoppingCart VALUES('000004','000004',1);
INSERT INTO ShoppingCart VALUES('000005','000001',1);
INSERT INTO ShoppingCart VALUES('000005','000024',1);
INSERT INTO ShoppingCart VALUES('000005','000030',1);
INSERT INTO ShoppingCart VALUES('000005','000018',1);
INSERT INTO ShoppingCart VALUES('000006','000017',1);
INSERT INTO ShoppingCart VALUES('000006','000013',1);
INSERT INTO ShoppingCart VALUES('000007','000006',1);
INSERT INTO ShoppingCart VALUES('000008','000023',1);
INSERT INTO ShoppingCart VALUES('000009','000018',1);
COMMIT;
prompt 對訂單表(Orders)插入數據
INSERT INTO Orders VALUES('000001',to_date('05/20/2001','mm/dd/yyyy')
,'000002','000002','01',6,1.2500,'Y',62.2200,to_date('05/24/2001','mm/dd/yyyy')
);
INSERT INTO Orders VALUES('000002',to_date('05/20/2001','mm/dd/yyyy')
,'000001','000005','02',8,2.0000,'Y',96.5000,to_date('05/23/2001','mm/dd/yyyy')
);
INSERT INTO Orders VALUES('000003',to_date('05/20/2001','mm/dd/yyyy')
,'000003','000007','01',12,0,'Y',83.9700,to_date('05/24/2001','mm/dd/yyyy') );
INSERT INTO Orders VALUES('000004',to_date('05/20/2001','mm/dd/yyyy')
,'000004','000006','01',4,1.0000,'Y',40.9900,to_date('05/24/2001','mm/dd/yyyy')
);
INSERT INTO Orders VALUES('000005',to_date('05/21/2001','mm/dd/yyyy')
,'000005','000002','03',90,7.7500,'Y',231.6800,to_date('05/25/2001','mm/dd/yyyy')
);
INSERT INTO Orders VALUES('000006',to_date('05/21/2001','mm/dd/yyyy')
,'000003','000012','03',40,4.0000,'Y',97.9700,to_date('05/22/2001','mm/dd/yyyy')
);
INSERT INTO Orders VALUES('000007',to_date('05/22/2001','mm/dd/yyyy')
,'000002','000008','01',4,0,'Y',16.9900,to_date('05/26/2001','mm/dd/yyyy') );
INSERT INTO Orders VALUES('000008',to_date('05/22/2001','mm/dd/yyyy')
,'000002','000009','03',20,2.0000,'Y',53.9800,to_date('05/26/2001','mm/dd/yyyy')
);
INSERT INTO Orders VALUES('000009',to_date('05/22/2001','mm/dd/yyyy')
,'000004','000010','02',8,2.000,'Y',26.9900,to_date('05/25/2001','mm/dd/yyyy')
);
INSERT INTO Orders VALUES('000010',to_date('05/22/2001','mm/dd/yyyy')
,'000005','000003','02',20,4.0000,'Y',67.9700,to_date('05/26/2001','mm/dd/yyyy')
);
COMMIT;
prompt 對訂單詳情表(OrderDetail)插入數據
INSERT INTO OrderDetail
VALUES('000001','000007',2,'N',NULL,NULL,39.9800);
INSERT INTO OrderDetail
VALUES('000001','000008',1,'Y','002','生日快樂',14.9900);
INSERT INTO OrderDetail
VALUES('000002','000016',2,'Y','001','我愛你',86.5000);
INSERT INTO OrderDetail
VALUES('000003','000017',3,'N',NULL,NULL,71.9700);
INSERT INTO OrderDetail
VALUES('000004','000030',1,'Y','001','我愛你',35.9900);
INSERT INTO OrderDetail
VALUES('000005','000001',4,'Y','001','生日快樂',35.9600);
INSERT INTO OrderDetail
VALUES('000005','000024',1,'Y','002','最好的祝福',25.9900);
INSERT INTO OrderDetail
VALUES('000005','000030',2,'Y','002','生日快樂',71.9800);
INSERT INTO OrderDetail
VALUES('000006','000017',1,'Y','001','具有愛',29.9800);
INSERT INTO OrderDetail
VALUES('000006','000013',2,'Y','003','生日快樂',23.9900);
INSERT INTO OrderDetail
VALUES('000007','000006',1,'N',NULL,NULL,12.9900);
INSERT INTO OrderDetail
VALUES('000008','000023',2,'Y','001','為你帶來愛',31.9800);
INSERT INTO OrderDetail VALUES('000009','000018',1,'Y','004','祝賀',16.9900);
INSERT INTO OrderDetail
VALUES('000010','000020',2,'Y','005','你最棒',17.9800);
INSERT INTO OrderDetail
VALUES('000010','000021',1,'Y','001','生日快樂',25.9900);
COMMIT;
prompt 對運輸情況表(Shipment)插入數據
INSERT INTO Shipment VALUES('000001',to_date('05/23/2001','mm/dd/yyyy')
,'d',to_date('05/24/2001','mm/dd/yyyy') );
INSERT INTO Shipment VALUES('000002',to_date('05/23/2001','mm/dd/yyyy')
,'d',to_date('05/23/2001','mm/dd/yyyy') );
INSERT INTO Shipment VALUES('000003',to_date('05/23/2001','mm/dd/yyyy')
,'s',null);
INSERT INTO Shipment VALUES('000004',to_date('05/24/2001','mm/dd/yyyy')
,'d',to_date('05/26/2001','mm/dd/yyyy') );
INSERT INTO Shipment VALUES('000005',to_date('05/24/2001','mm/dd/yyyy')
,'d',to_date('05/25/2001','mm/dd/yyyy') );
INSERT INTO Shipment VALUES('000006',to_date('05/22/2001','mm/dd/yyyy')
,'d',to_date('05/23/2001','mm/dd/yyyy') );
INSERT INTO Shipment VALUES('000007',to_date('05/25/2001','mm/dd/yyyy')
,'s',null);
INSERT INTO Shipment VALUES('000008',to_date('05/24/2001','mm/dd/yyyy')
,'d',to_date('05/24/2001','mm/dd/yyyy') );
INSERT INTO Shipment VALUES('000009',to_date('05/24/2001','mm/dd/yyyy')
,'d',to_date('05/25/2001','mm/dd/yyyy') );
INSERT INTO Shipment VALUES('000010',to_date('05/26/2001','mm/dd/yyyy')
,'d',to_date('05/28/2001','mm/dd/yyyy') );
COMMIT;
prompt 對月銷售情況表(pickofmonth)插入數據
INSERT INTO pickofmonth
VALUES('000001',1,2000,1000);
INSERT INTO pickofmonth
VALUES('000001',2,2000,1230);
INSERT INTO pickofmonth
VALUES('000005',3,2000,4000);
INSERT INTO pickofmonth
VALUES('000007',4,2000,5000);
INSERT INTO pickofmonth
VALUES('000003',5,2000,2000);
INSERT INTO pickofmonth
VALUES('000002',6,2000,3000);
INSERT INTO pickofmonth
VALUES('000003',7,2000,5670);
INSERT INTO pickofmonth
VALUES('000007',8,2000,2340);
INSERT INTO pickofmonth
VALUES('000011',9,2000,5600);
INSERT INTO pickofmonth
VALUES('000020',10,2000,2300);
INSERT INTO pickofmonth
VALUES('000021',11,2000,4500);
INSERT INTO pickofmonth
VALUES('000026',12,2000,6500);
INSERT INTO pickofmonth
VALUES('000024',1,2001,3200);
INSERT INTO pickofmonth
VALUES('000015',2,2001,3100);
INSERT INTO pickofmonth
VALUES('000012',3,2001,2500);
COMMIT;
-- 
學海無涯