20150617代替专题demo的期末上机考.DOC

1、120150617 代替專題 demo 的期末上機考班級：_學號：_姓名:_ _- 請 download Oracle SQL Developer，設定好連線參數與環境-reset database tablesDROP TABLE CLASSES ;DROP TABLE INSTRUCTORS;DROP TABLE STUDENTS;DROP TABLE COURSES;DROP TABLE BOB_ORDERS;DROP TABLE BOB_BOOKS;DROP TABLE BOB_BOOKSTORES;DROP TABLE PRODUCT;DROP TABLE VENDOR;-複製一張表

2、格內的所有 tuples 到另一張表格-(5%)Q 1:請重建 PRODUCT 及 VENDOR 這兩張表格，並設定其關聯性與加入資料內容。CREATE TABLE 產品類別(類別編號 INTEGER CONSTRAINT cat_pk_catnum PRIMARY KEY, 類別名稱 VARCHAR2(15) NOT NULL);CREATE TABLE 產品資料(產品編號 INTEGER CONSTRAINT pk_product PRIMARY KEY, 類別編號 INTEGER, 供應商編號 CHAR(5), 產品名稱 VARCHAR2(40), 建議單價 NUMBER NOT NUL

3、L,優惠比例 NUMBER(4,2),庫存量 INTEGER DEFAULT 0, 安全存量 INTEGER DEFAULT 0, );CONSTRAINT product_fk_catFOREIGN KEY (類別編號) REFERENCES 產品類別( 類別編號), CONSTRAINT product_fk_supFOREIGN KEY (供應商編號) REFERENCES 供應商( 供應商編號)CREATE TABLE 供應商(供應商編號 CHAR(5), 供應商 VARCHAR2(40), 聯絡人 VARCHAR2(30), 聯絡人職稱 VARCHAR2(30), 聯絡人性別 NCH

4、AR(2), 2郵遞區號 VARCHAR2(10) DEFAULT 413 NOT NULL, 地址 VARCHAR2(60), 電話 VARCHAR2(24),);CONSTRAINT supplier_pk_supnum PRIMARY KEY(供應商編號)CONSTRAINT supplier_uk_contact UNIQUE(供應商 , 聯絡人) INSERT INTO 產品類別 VALUES(1,果汁);INSERT INTO 產品類別 VALUES(2,茶類);INSERT INTO 產品類別 VALUES(3,蘇打類);INSERT INTO 產品類別 VALUES(4,奶類)

5、;INSERT INTO 產品類別 VALUES(5,運動飲料);INSERT INTO 產品類別 VALUES(6,水類);INSERT INTO 產品類別 VALUES(7,酒類);INSERT INTO 產品類別 VALUES(8,咖啡纇);INSERT INTO 供應商 VALUES(S0001,新統,陳柏霖,業務, 男,106, 台北市忠孝東路三段 99 號,(02) 555-2222);INSERT INTO 供應商 VALUES(S0002,權勝,李姿玲,董事長,女,400, 台中市仁愛路四段 59 號,(03) 555-4822);INSERT INTO 供應商 VALUES(

6、S0003,妙恩,張家豪,董事長,男,540, 南投縣埔里路 389 號,(04) 555-5735);INSERT INTO 供應商 VALUES(S0004,丁泉,周俊安,業務, 男,931, 屏東縣石光村中巷 1 號,(08) 305-5011);INSERT INTO 供應商 VALUES(S0005,正心,薛竣生,訂貨員, 男,900, 屏東市圓通南路 23 巷 27 號,(08) 598-7654);-DISTABLE CONTRAINT 的用法與時機-當要執行下列更新語法時：UPDATE PRODUCT SET V_CODE =X0000 WHERE P_CODE=1;會有 ER

7、ROR 在行 1: ORA-02291: 違反整合性限制條件 (DB1.PROD_FK_VEND) - 找不到父項索引鍵(5%)Q2：請問為什麼會出現 “違反整合性限制條件 (DB1.PROD_FK_VEND)”的錯誤訊息? 無法找到父項鍵(10%)Q3：請問你要如何處理上述問題，才能執行 update 指令?可使用 DISTABLE 來強迫執行 UPDATEDESC USER_CONSTRAINTS;STEP2: SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME , STATUS FROM USER_CONSTRAINTSW

8、HERE TABLE_NAME=UPPER(PRODUCT) AND CONSTRAINT_TYPE=R;3/*請觀察 STATUS 欄位*/-STEP3: ALTER TABLE PRODUCTDISABLE CONSTRAINT prod_fk_vend CASCADE;-已更改表格.STEP4: SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME , STATUS FROM USER_CONSTRAINTSWHERE TABLE_NAME=UPPER(PRODUCT) AND CONSTRAINT_TYPE=R;/*請再一

9、次觀察 STATUS 欄位*/STEP5: 再執行 UPDATE PRODUCT SET V_CODE =X0000 WHERE P_CODE=1;-已更新 1 個資料列 /*就不會有錯誤訊息了。*/(5%)Q4： 請再執行下列以回復 product 的第一筆記錄(1,1,S0001,蘋果汁,18,NULL, 390,50)。(10%)Q5： 列出員工的 (員工編號 , 姓名,職稱, salary,出生日期, 任用日期) 等資料，限制條件： hired date 是在奇數月(01,03,05, 07 ,09, 11) ,請將符合條件的員工依其年紀大小 (由年長者先列出)依序列出員工的資料 SE

10、LECT LAST_DAY(SYSDATE)FROM DUAL;SELECT 員工編號, 姓名, 職稱, 任用日期,ADD_MONTHS(任用日期, 6) AS “考核日期“,MONTHS_BETWEEN(SYSDATE, 任用日期)/12 年資,LAST_DAY(任用日期) FROM 員工;SELECT 員工編號, 姓名, 任用日期, TRUNC(任用日期, YEAR), ROUND(任用日期,YEAR)FROM 員工;SELECT 員工編號, 姓名, 任用日期, TRUNC (任用日期, MONTH), ROUND(任用日期,MONTH)FROM 員工(10%)Q6：請在上述的查詢的語法中

11、，善用替代變數來增加 依【任用日期 】及【排序 】的彈性。SELECT 員工編號, 姓名, 職稱, 任用日期, 報告人FROM 員工ORDER BY 任用日期 DESC;(5%)Q7: 請在員工資料表增加一個欄位，可以輸入員工的【身份證號】，此欄位的定義域要符合中華民國國民身份證的長度、格式等限制。(5%)Q8: 請登入 員工 “陳明明”的身份證號 “A123456789”。4(5%)Q9: 請列出所有上司的員工資料(員工編號, 姓名,職稱, salary, 報告人)。select emp.員工編號, emp.姓名, emp.職稱,emp. 報告人, boss.員工編號, boss.姓名,bo

12、ss. 職稱from 員工 emp left outer join 員工 bosson emp.報告人=boss. 員工編號;(5%)Q10: 請針對下列查詢語法，你可以用來解決別名(alias)問題的方式為何? SELECT P_DESCRIPT, P_ONHAND, P_PRICE, P_ONHAND*P_PRICE AS Total ValueFROM PRODUCT(1) 加單引號 (2)加雙引號 (3) Total 與 Value 之間不可有空白 (4)去除 AS答案：CQ11:下面有四個小例題，都是有用到 order by 指令。例 aSELECT 員工編號, 姓名, 職稱, 任用

13、日期, 報告人FROM 員工ORDER BY 任用日期 DESC;例 bSELECT 員工編號, 姓名, 職稱, 任用日期, ROUND(SYSDATE-任用日期)/365,0) AS 年資, 報告人FROM 員工ORDER BY 年資 DESC; 例 cSELECT 員工編號, 姓名, 職稱, 任用日期, ROUND(SYSDATE-任用日期)/365,0) AS 年資, 報告人FROM 員工ORDER BY 5 DESC; 例 dSELECT 員工編號, 姓名, 職稱, 任用日期, ROUND(SYSDATE-任用日期)/365,0) AS 年資, 報告人FROM 員工ORDER BY R

14、OUND(SYSDATE-任用日期)/365 ) DESC; (5%) Q11:例 a 例 d 這 4 範例中，使用 ORDER BY 子句，來定義排序的欄位，以及排序的升冪或降冪方式。請問下列四種排序的定義 分別對應到那一個範例呢? 甲、依據欄位的位置排序 _c_乙、依據欄位的運算式結果排序_d_丙、依據欄位名稱排序_b_丁、依據欄位的別名排序_a_5(5%) Q12: 萬用字元(wildcard characters)是拿來用在字串的模糊比對時，其中 ORACLE SQL 語句的萬用字元：( Q12-1)用_%_代表剛好一個字母(Q12-2 用_*_代表代表零個、一個、或數個字母。(5%)

15、 Q13:請運用萬用字元(wildcard characters)在字串的模糊比對。請列出 sql 語法可以在員工資料表中，查詢與業務有關 且不住在台北或新北市的人員資料。自我合併(Self Joins): The following query uses a self join to return the name of each employee along with the name of the employees manager. WHERE clause is added to shorten the output.SELECT 員工編號, 姓名, 職稱, 報告人FROM 員工;員

16、工編號 姓名 職稱 報告人 - - - -8111131 陳明明 總經理 8111261 黃謙仁 工程師 8201141 8112061 林其達 工程師 8111261 8201141 陳森耀 工程協理 8111131 8203161 徐沛汶 業務助理 8312261 8205231 劉逸萍 業務 8308271 8209241 朱辛傑 業務協理 8111131 8210171 胡琪偉 業務 8308271 8307021 吳志梁 業務 8308271 8308271 林美滿 業務經理 8209241 8311051 劉嘉雯 業務 8308271 8312261 張懷甫 業務經理 820924

17、1 8411151 張若蘭 業務助理 8312261 已選取 13 個資料列 SELECT e.姓名 AS 員工, e.職稱 as 員工職稱, m.姓名 AS 主管姓名, m.職稱 as 主管職稱FROM 員工 m, 員工 eWHERE m.員工編號 = e.報告人員工 員工職稱 主管姓名 主管職稱 - - - -黃謙仁 工程師 陳森耀 工程協理 林其達 工程師 黃謙仁 工程師 陳森耀 工程協理 陳明明 總經理 徐沛汶 業務助理 張懷甫 業務經理 劉逸萍 業務 林美滿 業務經理 朱辛傑 業務協理 陳明明 總經理 胡琪偉 業務 林美滿 業務經理 6吳志梁 業務 林美滿 業務經理 林美滿 業務經理

18、 朱辛傑 業務協理 劉嘉雯 業務 林美滿 業務經理 張懷甫 業務經理 朱辛傑 業務協理 張若蘭 業務助理 張懷甫 業務經理 已選取 12 個資料列(10%) Q14：請觀察上述語法，確認主管與部屬的關係之後，再執行下列語法，指出錯誤之處。SELECT m.姓名| works for |e.姓名 “Employees and Their Managers“FROM 員工 m, 員工 eWHERE m.員工編號 = e.報告人AND SUBSTR(m.姓名,1, 1) LIKE -請填入 林，查看姓氏為 “林”的員工之主管為何解答:SELECT e.姓名 | works for |m.姓名 “Em

19、ployees and Their Managers“FROM 員工 m, 員工 eWHERE m.員工編號 = e.報告人AND SUBSTR(e.姓名,1, 1) LIKE -請填入 林，查看姓氏為 “林”的員工之主管為何(5%) Q 15: 下列語法有一處錯誤需要修改，請依照錯誤訊息修正之。SELECT e.員工編號 , e.姓名, e.職稱, o.客戶編號 , o.訂貨日期FROM 員工 e JOIN 訂單 o USING (e.員工編號 );解答:SELECT 員工編號, e.姓名, e. 職稱, o.客戶編號 , o.訂貨日期FROM 員工 e JOIN 訂單 o USING (員工編號 );(5%) Q 16: 下列語法有一處錯誤需要修改，請依照錯誤訊息修正之。SELECT p.p_code, p.p_descript, p.p_price, v.v_code, v.v_nameFROM product p NATURAL JOIN vendor vORDER BY p.p_code ;解答:7SELECT p.p_code, p.p_descript, p.p_price, v_code, v.v_nameFROM product p NATURAL JOIN vendor vORDER BY p.p_code ;