Oracle筆試題及答案

1、一、選擇題（每題1分）1. Oracle發(fā)出下列select語句：SQL select e.empno, e.ename, d.loc 2 from emp e, dept d 3 where e.deptno = d.deptno 4 and substr(e.ename, 1, 1) = S;下列哪個語句是Oracle數(shù)據(jù)庫中可用的ANSI兼容等價語句？Aselect empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = S; Bselect empn

2、o, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = S;Cselect empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename, 1, 1) = S;Dselect empno, ename, loc from emp left join dept on emp.deptno = dept.deptno and substr(emp.ename, 1

3、, 1) = S;2. 你要對操縱Oracle數(shù)據(jù)庫中的數(shù)據(jù)。下列哪個選項表示Oracle中select語句的功能，并且不需要使用子查詢？A可以用select語句改變Oracle中的數(shù)據(jù) B可以用select語句刪除Oracle中的數(shù)據(jù)C可以用select語句和另一個表的內容生成一個表 D可以用select語句對表截斷3. Oracle數(shù)據(jù)庫中發(fā)出一個查詢。下列哪個選項不能查詢對用戶定義靜態(tài)表達式執(zhí)行數(shù)學運算時的查詢組件？A列子句 B表子句 CDUAL表 Dwhere子句4. 你要操縱Oracle數(shù)據(jù)，下列哪個不是SQL命令？Aselect * from dual; Bset defineCu

4、pdate emp set ename = 6543 where ename = SMITHERS;Dcreate table employees(empid varchar2(10) primary key);5. 你要在Oracle中定義SQL查詢。下列哪個數(shù)據(jù)庫對象不能直接從select語句中引用？A表 B序列 C索引 D視圖6. 你要根據(jù)PRODUCT_NAME列從PROFITS表查詢中過濾返回的數(shù)據(jù)。下列哪個子句包含相應過濾條件的引用？Aselect Bfrom Cwhere Dhaving7. 下列代碼顯示了PROFITS表輸出的局部：PRODUCT_NAMEPRODUCT_TYP

5、EQTR_END_DATEPROFIT-BARNEY DOLLTOY31-MAR-20016575430.30GAS GRILLAPP31-MAR-20011234023.88PENCILOFFICE30-JUN-200134039.99下列查詢的where子句哪個能正確計算2001年1月1日到6月1日六個月內銷售的所有家電總利潤？Awhere product_name = GAS GRILL and qtr_end_date between 01-JAN-2001 and 01-JUN-2001;Bwhere proeuct_type = APPLIANCE and name = GAS G

6、RILL and qtr_end_date = 31-JAN-2001 or 30-JUN-2001;Cwhere proeuct_type = APPLIANCE and qtr_end_date between 01-JAN-2001 and 01-JUN-2001;Dwhere proeuct_name = GAS GRILL and qtr_end_date = 01-JAN-2001 or 01-JUN-2001;用下列代碼所示的內容回答后面八個問題：EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-7369SIMTHCLERK790217-DEC-8080

7、0207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207654MARTINSALESMAN769828-SEP-8112501400307698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756619-APR-823000207839KINGPRESIDENT17-NOV-815000107844TUR

8、NERSALESMAN769808-SET-8115000307876ADAMSCLERK778823-DEC-821100207900JAMESCLERK769803-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-821300108. 以下選項中哪個是下列查詢返回的值：select sum(sal) + sum(comm) from emp where job = ANALYST or ename like J% ?A6000 B9925 C9975 DNULL9. 以下選項中哪個是下列查詢返回的

9、值：select count(mgr) from emp where deptno = 10 ?A1 B2 C3 DNULL10. 以下選項中哪個是下列查詢返回的值：select count(*) from emp where mgr = 7700-2 ?A5 B6 C7 DNUL11. 以下選項中哪個是下列SQL命令產生的第三個員工：select ename, sal from emp where job= SALESMAN order by empno desc ?AALLEN BMARTIN CTURNER DWARD12. 以下選項中哪個是下列SQL命令產生的第三個員工：ename,

10、sal from emp where job= SALESMAN order by 1 desc ?AALLEN BMARTIN CTURNER DWARD13. 以下選項中哪個是發(fā)出下列查詢后Oracle返回的值：select substr(job, 1, 3) from emp where ename like upper(_ar%) ?AANA BCLE CMAN DSAL14. 以下選項中哪個是發(fā)出下列查詢后Oracle返回的值：select trunc(months_between(min(hiredate), max(hiredate) from emp ?A24 B25 C-24

11、 D-2515. 以下選項中哪個是發(fā)出下列查詢后Oracle返回的值：select * from emp where hiredate 23-JAN-82 ?（選擇兩個答案）AADAMS BMILLER CSCOTT DSMITH16. TEST表包含兩個列，TESTCOL定義為數(shù)據(jù)類型NUMBER(10)，TESTCOL_2定義為數(shù)據(jù)類型VARCHAR2(10)。在Oracle中發(fā)出下列語句：insert into test(testcol, testcol_2) values (null, FRANCIS)，然后對表進行下列查詢：select nvl(testcol, EMPTY) as

12、testcol from test where testcol_2 = FRANCIS。下列選項哪個是得到的結果？AOracle返回結果0 BOracle返回結果EMPTYCOracle返回結果NULL DOracle返回錯誤結果17. 要從ORDERS表中取得數(shù)據(jù)，其中包括三個列CUSTOMER、ORDER_DATE與ORDER_AMT?？梢杂孟铝心膫€where子句查詢ORDERS表，取得客戶LESLIE超過2700的訂單？Awhere customer = LESLIE;Bwhere customer = LESLIE and order_amt 2700;Dwhere customer

13、= LESLIE and order_amt 2700;18. 用下列輸出回答問題（假設這里的信息來自本章使用的EMP表）：EMPNO - SMITH-dog- ALLEN-dog- WARD-dog-d JONES-dog- MARTIN-dog BLAKE-dog- CLARK-dog- SCOTT-dog- KING-dog-d TURNER-dog JAMES-dog- FORD-dog-d MILLER-dog 下列哪個SQL語句產生這個輸出？Aselect trim(trailing -dog from ename) as ename from emp;Bselect rpad(e

14、name, 10, -dog) as ename from emp;Cselect substr(ename,1 , 10) as ename from emp;Dselect lpad(ename, 10, -dog) as ename from emp;19. 用下列代碼塊回答問題：SQL select _(-45) as output from dual;OUTPUT-45 下列哪個單行函數(shù)無法產生這個輸出？Aabs() Bceil() Cfloor() Dround()20. 對表中的某一行，VARCHAR2列包含數(shù)值SMITHY，應用程序在右側填充七個空格。length()函數(shù)處理這

15、個列值時，返回什么值？A6 B13 C30 D6021. SQL *Plus中發(fā)出的下列語句： SQL select ceil(-97.342), 2 floor(-97.342), 3 round(-97.342), 4 trunc(-97.342) 5 from dual; 下列哪個函數(shù)不返回結果-97？Aceil() Bfloor() Cround() Dtrunc()22. SQL *Plus中發(fā)出的下列語句： SQL select ceil(256.342), 2 floor(256.342), 3 round(256.342), 4 trunc(256.342) 5 from du

16、al; 下列哪個函數(shù)不返回結果256？Aceil() Bfloor() Cround() Dtrunc()23. 在Oracle中發(fā)出的下列查詢：SQL select months_between(15-MAR-83,15-MAR-97) form dual;Oracle返回什么？A14 B-14 C168 D-16824. 你要在Oracle中使用日期信息的格式掩碼。下列哪種情形不適合這個格式掩碼？Ato_date() Bto_char() Calter session set nls_date_format Dto_number()25. 數(shù)據(jù)庫中有兩表PRODUCT與STORAGE_BO

17、X，PRODUCT表中列出各個產品的惟一ID號、產品名和特定產品所在的箱子。另一個表中列出各個存儲箱（用數(shù)字標識）可以包含許多產品，但每個箱子只能在一個地方。下列哪個語句能正確顯示這個數(shù)據(jù)庫中所有產品ID、名稱和箱子地址？Aselect d_id, d_name, b.box_loc from product p, storage_box b where d_id = d_id and prod_name=WIDGET;Bselect d_id, d_name, b.box_loc from product p, storage_bo

18、x b where prod_name=WIDGET;Cselect d_id, d_name, b.box_loc from product p, storage_box b where p.stor_box_num = b.stor_box_num and prod_name=WIDGET;Dselect prod_id, prod_name, box_loc from product , storage_box where stor_box_num = stor_box_num and prod_name=WIDGET;26. 開發(fā)報表時，要連接三個表的信息，這些表為

19、EMP、DEPT與SALGRADE。只需要公司10級以上員工相應的員工、部門地址與工資范圍的記錄。這個查詢需要多少比較運算？A2 B3 C4 D527. 要連接兩個表PRODUST與STOTAGE的內容，列出所有包含產品的箱子地址。PRODUCT有三列ID、NAME與BOX#；STORAGE有兩列BOX#與LOC。下列哪個選項無法得到想要的結果？Aselect product.id, , storage.loc from product, storage where product.box#=storage.box#;Bselect product.id, produc

20、, storage.loc from product join storage on product.box#=storage.box#;Cselect product.id, , storage.loc from product natural join storage on product.box#=storage.box#;Dselect product.id, , storage.loc from product natural join storage;28. 你要定義外連接，下列哪個選項正確描述了外連接語句？A由于外連接操

21、作允許一個表中有NULL值，因此連接這些表時不必指定相等性比較。B在表A與B的外連接語句中，如果不管B表有無相應記錄，都要顯示表A的所有行， 則可以使用右外連接。C在表A與B的外連接語句中，如果不管A表有無相應記錄，都要顯示表B的所有行， 則可以使用左外連接。D盡管外連接操作允許一個表中有NULL值，但連接這些表時仍要指定相等性比較。29. 數(shù)據(jù)庫中有兩個表PRODUCT與STORAGE_BOX，表中列出各個產品的惟一ID號、產品名和特定產品所在的箱子。另一個表中列出各個存儲箱（用數(shù)字標識）可以包含許多產品，但每個箱子只能在一個地方。下列哪個語句能正確顯示這個數(shù)據(jù)庫中所有產品ID、名稱和箱子地

22、址，不管有沒有指定存儲箱？Aselect d_id, d_name, b.box_loc from product p left outer join storage_box b on p.stor_bax_num = b. stor_bax_num where prod_name=WIDGET(+); Bselect d_id, d_name, b.box_loc from product p left outer join storage_box b on p.stor_bax_num = b. stor_bax_num where prod_nam

23、e=WIDGET ;Cselect d_id, d_name, b.box_loc from product p right outer join storage_box b where b.stor_bax_num = p. stor_bax_num(+) and prod_name=WIDGET(+);Dselect d_id, d_name, b.box_loc from product p full outer join storage_box b on p.stor_bax_num = b. stor_bax_num where b.stor_

24、box_num is NULL ;30. Oracle中發(fā)出下列命令：SQL select e.ename,a.street_address,a.city,a.post_code 2 from emp e,addr a 3 where e.empno = a.empno(+) 4 and a.state = TEXAS;下列哪個選項顯示等價ANSI/ISO語句？Aselect e.ename, a.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno wher

25、e a.state = TEXAS; Bselect e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno where a.state = TEXAS;Cselect e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = TEXA

26、S;Dselect e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a where e.empno = a.empno(+) and a.state = TEXAS;31. 檢查SQL *Plus的下列輸出：PRODUCT.IDPRODUCT.NAMEBOX.LOCATION-578-XWIDGETIDAHOTENNESSEE456-YWIDGET 下列哪個選項是產生這個結果的查詢類型？A全外連接 B左外連接 C右外連接 D等連接32. PROFITS表存儲公司不同

27、地區(qū)、產品類型和季度的利潤信息。下列哪個SQL語句顯示不同地區(qū)、產品類型和季度的利潤的交叉表輸出？Aselect region, product_type, time, sum(profit) from profits group by region, prod_type, time;Bselect region, product_type, time from profits group by rollup(region, prod_type, time);Cselect region, product_type, time, from profits group by cube(regio

28、n, prod_type, time);Dselect region, product_type, time,sum(profit) from profits group by cube(region, prod_type, time);33. 對數(shù)據(jù)庫運行下列哪個group by查詢時會從Oracle產生錯誤？Aselect deptno, job, sum(sal) from emp group by job, deptno; Bselect sum(sal), deptno, job from emp group by job, deptno;Cselect deptno, job, s

29、um(sal) from emp;Dselect deptno, sum(sal), job from emp group by job, deptno;34. 檢查下列SQL的輸出SQL select a.deptno,a.job,b.loc,sum(a.sal) 2 from dmp a,dept b 3 where a.deptno = b.depton 4 group by a.deptno,a.job,b.loc 5 order by sum(a.sal);這個查詢按哪個列的順序返回輸出？AA.DEPTON BA.JOB CB.LOC DSUM(A.SAL)35. 要查詢的PROFI

30、TS表存儲公司不同地區(qū)、產品類型和季度的利潤信息。下列哪個SQL語句按不同地區(qū)、產品類型和季度求出平均利潤大于100000美元的利潤？Aselect region, prod_type, period, avg(profits) from profits where avg(prodit) 100000 group by region, prod_type, period;Bselect region, prod_type, period, avg(profits) from profits where avg(prodit) 100000 order by region, prod_type

31、, period;Cselect region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) 100000;Dselect region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) 100000;36. 公司的員工費用應用程序有兩表，一個是EMP，包含所有員工數(shù)據(jù)，一個是EXPENSE，包

32、含公司每個員工提交的費用票據(jù)。下列哪個查詢取得提交的費用總和超過其工資值的員工ID與姓名？Aselect e.empno, e.ename from emp e where e.sal (select sum(x.vouch_amt) from expense x) and x.empno = e.empno;Bselect e.empno, e.ename from emp e where e.sal (select x.vouch_amt from expense x where x.empno = e.empno);Cselect e.empno, e.ename from emp e

33、where e.sal select ename 2 from emp 3 where empno in 4 ( select empno 5 from expense 6 where vouch_amt 10000 );下列哪個SQL語句產生與上面相同的輸出，改寫成使用運算符？Aselect e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt10000) and x.empno = e.empno;Bselect e.ename from emp e where exists(sele

34、ct x.empno from expense x where x.vouch_amt10000 and x.empno = e.empno);Cselect e.ename from emp e where x.empno = e.empno and exists(select x.empno from expense x where x.vouch_amt10000)Dselect e.ename from emp e, expense x where x.empno = e.empno and x.vouch_amt10000 and exists(select x.empno from

35、 expense x where)38. 用下列代碼塊回答問題：SQL select deptno,job,avg(sal) 2 from emp 3 group by deptno,job 4 having avg(sal) 5 ( select sal 6 from emp 7 where ename = MARTIN ); 上述語句使用下面哪種子查詢？A單行子查詢 B多行子查詢 Cfrom子句子查詢 D多列子查詢39. 公司銷售數(shù)據(jù)庫有兩個表，PROFITS存儲不同地區(qū)不同季度的產品銷售利潤，REGIONS存儲每個部門地區(qū)名稱、該地區(qū)總部地址和該地區(qū)副總裁姓名。下列哪個查詢取得SMITH

36、ERS、FUJIMORI與LIKKARAJU領導的地區(qū)的玩具銷售利潤？Aselect sum(profit) from profits where region in (select region froms regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;Bselect sum(profit) from profits where region like (select region froms regions where reg_head in (SMITHERS, FUJMORI, LA

37、KKARAJU) and product =TOYS );Cselect sum(profit) from profits where region = (select region froms regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;Dselect sum(profit) from profits where region is (select region froms regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU)

38、and product =TOYS;40. 下列代碼塊顯示的查詢包含一個子查詢：SQL select dname, avg(sal) as dept_avg 2 from emp, dept 3 where emp.deptno = dept.deptno 4 group by dname having avg(sal) 5 ( select avg(sal)*1/4 6 from emp,dept 7 where emp.deptno = dept.deptno ) 8 order by avg(sal); 可以用下列哪個子句重新定義這個查詢，刪除子查詢與主查詢中組函數(shù)執(zhí)行的重復？Agrou

39、p by Border by Cwith Dhaving41. 用下列代碼塊回答問題：SQL select e.deptno,e.ename,e.job,e.sal 2 from emp e 3 where e.sal = 4 ( select max(e2.sal) 5 from emp e2 6* where nvl(e.deptno,99) = nvl(e2.deptno,99);DEPTNOENAMEJOBSAL-30BLAKEMANAGER285010CLARKMANAGER245020SCOTTANALYST3000KINGPRESIDENT500020FORDANALYST300

40、0 要在上述返回集的DEPTNO列中顯示99，可以用下列哪個SQL語句？Aselect nvl(e.deptno,99), e.ename, e.sal from emp e where (e.deptno, e.sal) = (select max(e2.sal) from emp e2 where nvl(e.dept, 99) = nvl(e2.deptno, 99); Bselect nvl(e.deptno,99), e.ename, e.sal from emp e where e.sal = (select max(e2.sal) from emp e2 where nvl(e.

41、dept, 99) = nvl(e2.deptno, 99);Cselect nvl(e.deptno,99), e.ename, e.sal from emp e where (e.deptno, e.sal) = (select e2.deptno,max(e2.sal) from emp e2 where nvl(e.dept, 99) = nvl(e2.deptno, 99);Dselect nvl(e.deptno,99), e.ename, e.sal from emp e where (e.deptno, e.sal) = (select e2.deptno,max(e2.sal

42、) from emp e2 where nvl(e.dept, 99) = nvl(e2.deptno, 99) group by e1.deptno);42. 公司銷售數(shù)據(jù)庫包含一個PROFITS表，按產品名、銷售地區(qū)和季度列出利潤信息。如果要取得公司五個最暢銷產品清單，可以用下列哪個SQL語句：Aselect d_name, fit from (select prod_name, profit from profits order by profit desc) where rownum = 5;Bselect d_name, fit from (s

43、elect prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where d_name = d_name;Cselect d_name, fit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum = 5;Dselect d_name, fit

44、from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum select empno, ename, jobfrom empwhere trunc(hiredate) = trunc(&v_hiredate);為了使腳本正確工作，要改變哪個方面？A變量v_hiredate應變成接受DATE信息。 B查詢中的trunc()函數(shù)應刪除。Caccept命令中的prompt子句是多余的。 D這個腳本很好。44. Oracle中要生成數(shù)據(jù)庫表,下列哪個選項是無效表生成的語句？Acreat

45、e table cats(c_name varchar2(10), c_weight number, c_owner varchar2(10);Bcreate table my_cats as select * from cats where owner = ME;Ccreate global temporary table temp_cats(c_name varchar2(10), c_weight number, c_owner varchar2(10);Dcreate table 51cats as select c_name, c_weight from cats where c_weight 5;45. 試圖在Oracle生成表時遇到下列錯誤：ORA-00955-name is already used by existing object。下列哪個選項無法糾正這個錯誤？A以不同的用戶身份生成對象。 B刪除現(xiàn)有同名對象C改變生成對象中的列名。 D更名現(xiàn)有同名對象。46. SALES表中的PROFITS列聲明為NUMBER(10, 2)。下列哪個值無法在這個列中存儲？A5392845.324 B871039453.1 C75439289.34