资料库实习作业四Oracle.doc

資料庫實習作業四Oracle SQL*Plus 作業繳交期限：2005年12月05日，下午5：00前 作業主題：拍拍網往來交易資料庫 繳交內容：A. 書面報告封面：註明 第四次實習作業；組別、組員、修課班級、學號及姓名。B. 書面報告內容：(請依以下順序裝訂，5個查詢亦依序排列，自行設計部份請放在最後)（一） 印出建立6個資料表的指令(即檔案內容)，其建立方式如下: 其中4個資料表是以SQL command file的檔名(.sql)來產生，例如：customer_create.sql、manufacturer_create.sql、transaction_create.sql等等。 另外2個資料表需在SQL PLUS視窗中建立，並使用SPOOL指令將過程記錄下來，檔名存成(.lst)，例如：customerLevel_create.lst與productClass_create.lst。（二） 印出6個資料表的內容 在SQL PLUS視窗中以指令將所有資料表的內容顯示出來，並以SPOOL指令將所執行的過程與結果以檔案儲存(.lst)。 例如：customer資料表內容：customer_content.lst（三） 印出5個查詢的內容及查詢結果 包括3個基本查詢，2個自行建立查詢，並附上自行設計的說明。 所有查詢需使用SPOOL指令將查詢的內容及結果記錄下來，並將檔案存成(.lst)。l 基本查詢：(參閱 作業二 )查詢一、查詢產品查詢二、查詢銷售量最多的產品查詢三、查詢前30天之交易記錄例如: 查詢一、查詢產品：query1_prodtuct.lst 作業內容說明：1. 建立六個資料表n 每個資料表中至少輸入20筆資料，除顧客級別資料表可最少6筆資料，除產品類別資料表可最少6筆資料、而交易資料表最少30筆資料。n 資料表內容要與作業一相同 (Hint：將Access資料表匯出成文字檔) n customer、manufacturer、product、transaction資料表的建立及insert資料將SQL command寫在檔案中，以執行SQL command file的方式來產生。n customerLvevel、productClass資料表的建立及insert資料直接下SQL command的方式來產生，並且利用SPOOL指令將操作過程儲存起來。2. 資料欄位說明：資料表一：顧客資料表（customer）欄位名稱資料型態條件資料輸入c_idchar(10)not null, primary keynamechar(16)not nullsexchar(2)not nullcheck(sex in(女,男)男、女telchar(13)not nullmobilechar(12)addresschar(50)not nullemailchar(30)not nullcheck(email like %)電子郵件位址需要有符號birthdaydatenot nullc_levelnumbernot null, foreign key補充說明：由於birthday 資料型態為date，因此在insert資料時，必須用TO_DATE函數，此函數是將字元資料型態轉成日期型態；函數第一項參數是字元型態的日期，第二個參數是日期資料儲存的格式。例如：TO_DATE(1976/1/1,yyyy/mm/dd)補充說明：like的用法底線_在SQL中代表一個字元、百分比%在SQL中代表多個字元補充說明：由於c_level會參考到顧客級別資料表的level_parameter欄位，因此必須設為foreign key Ex: foreign key(c_level) references customerLevel(level_parameter)補充說明：c_id設為主鍵primary keyprimary key(c_id)資料表二：顧客級別資料表（customerLevel）欄位名稱資料型態條件資料輸入level_parameternumbernot null, primary keylevel_namechar(20)not nulldiscountnumbernot null資料表三：廠商資料表（manufacturer）欄位名稱資料型態條件資料輸入manufacturer_idchar(8)not null, primary keymanufacturer_namechar(30)not nullsales_namechar(16)not nullmanufacturer_telchar(13)not nullmanufacturer_faxchar(13)sales_mobilechar(12)not nulladdresschar(50)not null資料表四：產品資料表（product）欄位名稱資料型態條件資料輸入product_idchar(12)not null, primary keyproduct_classnumbernot null, foreign keyproduct_namechar(30)not nullpricenumbernot nullcostnumbernot nullquantitynumbernot nullstock_datedatenot nullfuture_stock_datedatenot nullreturn_datedatenotechar(50)manufacturer_idchar(8)not null, foreign key補充說明：由於stock_date 資料型態為date，因此在insert資料時，必須用TO_DATE函數，此函數是將字元資料型態轉成日期型態；函數第一項參數是字元型態的日期，第二個參數是日期資料儲存的格式。例如：TO_DATE(1976/1/1 03:07:26,yyyy/mm/dd hh:mi:ss)相同的future_stock_date欄位資料型態為date，可是只需要簡短日期，所以格式只需yyyy/mm/dd相同的return_date欄位資料型態為date，可是只需要簡短日期，所以格式只需yyyy/mm/dd補充說明：由於product_calss會參考到產品類別資料表的class_parameter欄位，因此必須設為foreign key Ex: foreign key(product_class) references productClass(class_parameter)由於manufacturer_id會參考到廠商資料表的manufacturer_id欄位，因此必須設為foreign key Ex: foreign key(manufacturer_id) references manufacturer(manufacturer_id)資料表五：產品類別資料表（productClass）欄位名稱資料型態條件資料輸入class_parameternumbernot null, primary keyclass_namechar(30)not nullclass_notechar(50)資料表六：交易資料表（transaction）欄位名稱資料型態條件資料輸入transaction_parameternumbernot null, primary keyc_idchar(10)not null, foreign keyproduct_idchar(12)not null, foreign keytransaction_datedatenot nullt_numbernumbernot null補充說明：由於transaction_date 資料型態為date，因此在insert資料時，必須用TO_DATE函數，此函數是將字元資料型態轉成日期型態；函數第一項參數是字元型態的日期，第二個參數是日期資料儲存的格式。例如：TO_DATE(1976/1/1 03:07:26,yyyy/mm/dd hh:mi:ss)3. 五個查詢：u 完成指定的3個基本查詢，並且另行設計2個查詢,請將所有的查詢SQL command及結果利用SPOOL指令存成檔案。(.lst)u 自行建立的查詢必須有一個使用到outer join。u 自行建立的查詢必須有一個使用到排序(order by )。4. 基本查詢(重點提示)u 查詢所需的欄位是來自哪些資料表？這些資料表出現在保留字FROM之後。u 兩個資料表間的關聯，需透過相同的欄位來完成。例如：其中，在where中的寫法如下： where customer.c_level = customerLevel.level_parameter 即代表customer資料表與record資料表的關聯。u outer-join(外部合併)例如：select t1.id,t2.idfrom test t1,test2 t2where t1.id=t2.id(+)；where t1.id=t2.id(+)； =注意”(+)”的位置u 排序的語法： ORDER BY field_name1, file_name2, ASC|DESC ASC：由小到大（系統預設值） DESC：由大到小 start filename（儲存檔案之完整路徑名稱）例如：step1. 建立test.sql檔，內容如下：step2. 於SQL*Plus提示符號下，以START指令來執行：u SPOOL指令：將使用 SQL*Plus時所下的命令與結果儲存成紀錄檔。(.lst) 使用方法：SQLspool filename（欲儲存命令與結果檔案之完整路徑名稱） SQL撰寫SQL*Plus命令SQL*Plus指令結果顯示SQLspool off