Oracle筆試試題及答案

1、Oracle筆試試題及答案一、選擇題（每題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;下列哪個(gè)語句是Oracle數(shù)據(jù)庫中可用的ANSI兼容等價(jià)語句？Aselect empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1)

2、= S; Bselect empno, 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 su

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

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

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

6、 and name = GAS GRILL 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;用下列代碼所示的內(nèi)容回答后面八個(gè)問題：EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-7369SIMTHCLE

7、RK790217-DEC-80800207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207654MARTINSALESMAN769828-SEP-8112501400307698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756619-APR-823000207839KINGPRESIDENT17-NO

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

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

10、令產(chǎn)生的第三個(gè)員工：ename, sal from emp where job= SALESMAN order by 1 desc ?AALLEN BMARTIN CTURNER DWARD13. 以下選項(xiàng)中哪個(gè)是發(fā)出下列查詢后Oracle返回的值：select substr(job, 1, 3) from emp where ename like upper(_ar%) ?AANA BCLE CMAN DSAL14. 以下選項(xiàng)中哪個(gè)是發(fā)出下列查詢后Oracle返回的值：select trunc(months_between(min(hiredate), max(hiredate) from

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

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

13、 < 2700;Cwhere customer = LESLIE or order_amt > 2700;Dwhere customer = LESLIE and order_amt > 2700;18. 用下列輸出回答問題（假設(shè)這里的信息來自本章使用的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 下列哪個(gè)SQL

14、語句產(chǎn)生這個(gè)輸出？Aselect trim(trailing -dog from ename) as ename from emp;Bselect rpad(ename, 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 下列哪個(gè)單行函數(shù)無法產(chǎn)生這個(gè)輸出？Aabs()

15、Bceil() Cfloor() Dround()20. 對表中的某一行，VARCHAR2列包含數(shù)值SMITHY，應(yīng)用程序在右側(cè)填充七個(gè)空格。length()函數(shù)處理這個(gè)列值時(shí)，返回什么值？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; 下列哪個(gè)函數(shù)不返回結(jié)果-97？Aceil() Bfloor() Cround() Dtrunc()22. SQL *Plus中發(fā)出的下列語句：

16、SQL> select ceil(256.342), 2 floor(256.342), 3 round(256.342), 4 trunc(256.342) 5 from dual; 下列哪個(gè)函數(shù)不返回結(jié)果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中使用日期信息的格式掩碼。下列哪種情形不適合這個(gè)格式掩碼？At

17、o_date() Bto_char() Calter session set nls_date_format Dto_number()25. 數(shù)據(jù)庫中有兩表PRODUCT與STORAGE_BOX，PRODUCT表中列出各個(gè)產(chǎn)品的惟一ID號、產(chǎn)品名和特定產(chǎn)品所在的箱子。另一個(gè)表中列出各個(gè)存儲(chǔ)箱（用數(shù)字標(biāo)識(shí)）可以包含許多產(chǎn)品，但每個(gè)箱子只能在一個(gè)地方。下列哪個(gè)語句能正確顯示這個(gè)數(shù)據(jù)庫中所有產(chǎn)品ID、名稱和箱子地址？Aselect d_id, d_name, b.box_loc from product p, storage_box b where d_id = b.p

18、rod_id and prod_name=WIDGET;Bselect d_id, d_name, b.box_loc from product p, storage_box 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 produ

19、ct , storage_box where stor_box_num = stor_box_num and prod_name=WIDGET;26. 開發(fā)報(bào)表時(shí)，要連接三個(gè)表的信息，這些表為EMP、DEPT與SALGRADE。只需要公司10級以上員工相應(yīng)的員工、部門地址與工資范圍的記錄。這個(gè)查詢需要多少比較運(yùn)算？A2 B3 C4 D527. 要連接兩個(gè)表PRODUST與STOTAGE的內(nèi)容，列出所有包含產(chǎn)品的箱子地址。PRODUCT有三列ID、NAME與BOX#；STORAGE有兩列BOX#與LOC。下列哪個(gè)選項(xiàng)無法得到想要的結(jié)果？Aselect product.id, product.na

20、me, storage.loc from product, storage where product.box#=storage.box#;Bselect product.id, , 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,

21、, storage.loc from product natural join storage;28. 你要定義外連接，下列哪個(gè)選項(xiàng)正確描述了外連接語句？A由于外連接操作允許一個(gè)表中有NULL值，因此連接這些表時(shí)不必指定相等性比較。B在表A與B的外連接語句中，如果不管B表有無相應(yīng)記錄，都要顯示表A的所有行， 則可以使用右外連接。C在表A與B的外連接語句中，如果不管A表有無相應(yīng)記錄，都要顯示表B的所有行， 則可以使用左外連接。D盡管外連接操作允許一個(gè)表中有NULL值，但連接這些表時(shí)仍要指定相等性比較。29. 數(shù)據(jù)庫中有兩個(gè)表PRODUCT與STORAGE_BOX，表中列出

22、各個(gè)產(chǎn)品的惟一ID號、產(chǎn)品名和特定產(chǎn)品所在的箱子。另一個(gè)表中列出各個(gè)存儲(chǔ)箱（用數(shù)字標(biāo)識(shí)）可以包含許多產(chǎn)品，但每個(gè)箱子只能在一個(gè)地方。下列哪個(gè)語句能正確顯示這個(gè)數(shù)據(jù)庫中所有產(chǎn)品ID、名稱和箱子地址，不管有沒有指定存儲(chǔ)箱？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

23、 from product p left outer join storage_box b on p.stor_bax_num = b. stor_bax_num where prod_name=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_lo

24、c from product p full outer join storage_box b on p.stor_bax_num = b. stor_bax_num where b.stor_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;下列哪個(gè)選項(xiàng)顯示等價(jià)ANSI/ISO語句？Aselect e.ename, a

25、.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno where 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.cit

26、y, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno where a.state = TEXAS;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.LOCAT

27、ION-578-XWIDGETIDAHOTENNESSEE456-YWIDGET 下列哪個(gè)選項(xiàng)是產(chǎn)生這個(gè)結(jié)果的查詢類型？A全外連接 B左外連接 C右外連接 D等連接32. PROFITS表存儲(chǔ)公司不同地區(qū)、產(chǎn)品類型和季度的利潤信息。下列哪個(gè)SQL語句顯示不同地區(qū)、產(chǎn)品類型和季度的利潤的交叉表輸出？Aselect region, product_type, time, sum(profit) from profits group by region, prod_type, time;Bselect region, product_type, time from profits group by

28、rollup(region, prod_type, time);Cselect region, product_type, time, from profits group by cube(region, prod_type, time);Dselect region, product_type, time,sum(profit) from profits group by cube(region, prod_type, time);33. 對數(shù)據(jù)庫運(yùn)行下列哪個(gè)group by查詢時(shí)會(huì)從Oracle產(chǎn)生錯(cuò)誤？Aselect deptno, job, sum(sal) from emp grou

29、p by job, deptno; Bselect sum(sal), deptno, job from emp group by job, deptno;Cselect deptno, job, sum(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.d

30、eptno,a.job,b.loc 5 order by sum(a.sal);這個(gè)查詢按哪個(gè)列的順序返回輸出？AA.DEPTON BA.JOB CB.LOC DSUM(A.SAL)35. 要查詢的PROFITS表存儲(chǔ)公司不同地區(qū)、產(chǎn)品類型和季度的利潤信息。下列哪個(gè)SQL語句按不同地區(qū)、產(chǎn)品類型和季度求出平均利潤大于100000美元的利潤？Aselect region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 group by region, prod_type, period;Bse

31、lect region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 order by region, prod_type, 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) fr

32、om profits group by region, prod_type, period having avg(prodit) < 100000;36. 公司的員工費(fèi)用應(yīng)用程序有兩表，一個(gè)是EMP，包含所有員工數(shù)據(jù)，一個(gè)是EXPENSE，包含公司每個(gè)員工提交的費(fèi)用票據(jù)。下列哪個(gè)查詢?nèi)〉锰峤坏馁M(fèi)用總和超過其工資值的員工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

33、, 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 where e.sal < (select sum(x.vouch_amt) from expense x where x.empno = e.empno);Dselect e.empno, e.ename from emp e where exists (select sum(x.vouch_amt) from expen

34、se x where x.empno = e.empno);37. 看看下列語句：SQL> select ename 2 from emp 3 where empno in 4 ( select empno 5 from expense 6 where vouch_amt > 10000 );下列哪個(gè)SQL語句產(chǎn)生與上面相同的輸出，改寫成使用運(yùn)算符？Aselect e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>10000) and x.empno = e.empn

35、o;Bselect e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>10000 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_amt>10000)Dselect e.ename from emp e, expense x where x.empno = e.emp

36、no and x.vouch_amt>10000 and exists(select x.empno from 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ù)庫有兩個(gè)表，PROFITS

37、存儲(chǔ)不同地區(qū)不同季度的產(chǎn)品銷售利潤，REGIONS存儲(chǔ)每個(gè)部門地區(qū)名稱、該地區(qū)總部地址和該地區(qū)副總裁姓名。下列哪個(gè)查詢?nèi)〉肧MITHERS、FUJIMORI與LIKKARAJU領(lǐng)導(dǎo)的地區(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 (se

38、lect region froms regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) 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 regi

39、on froms regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;40. 下列代碼塊顯示的查詢包含一個(gè)子查詢：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 = de

40、pt.deptno ) 8 order by avg(sal); 可以用下列哪個(gè)子句重新定義這個(gè)查詢，刪除子查詢與主查詢中組函數(shù)執(zhí)行的重復(fù)？Agroup 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-30BLAKEM

41、ANAGER285010CLARKMANAGER245020SCOTTANALYST3000KINGPRESIDENT500020FORDANALYST3000 要在上述返回集的DEPTNO列中顯示99，可以用下列哪個(gè)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.ena

42、me, e.sal from emp e where e.sal = (select max(e2.sal) from emp e2 where nvl(e.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

43、.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) group by e1.deptno);42. 公司銷售數(shù)據(jù)庫包含一個(gè)PROFITS表，按產(chǎn)品名、銷售地區(qū)和季度列出利潤信息。如果要取得公司五個(gè)最暢銷產(chǎn)品清單，可以用下列哪個(gè)SQL語句：Aselect d_name, fit from (select prod_name, profit from profi

44、ts order by profit desc) where rownum <= 5;Bselect d_name, fit from (select 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 p

45、rod_name order by sum(profit) desc) where rownum <= 5;Dselect d_name, fit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum <= 5;43. 檢查下列代碼段中的腳本dates.sql：accept v_hiredate prompt enter hire date => select empno, ename, jobfrom empwhere trunc(

46、hiredate) = trunc(&v_hiredate);為了使腳本正確工作，要改變哪個(gè)方面？A變量v_hiredate應(yīng)變成接受DATE信息。 B查詢中的trunc()函數(shù)應(yīng)刪除。Caccept命令中的prompt子句是多余的。 D這個(gè)腳本很好。44. Oracle中要生成數(shù)據(jù)庫表,下列哪個(gè)選項(xiàng)是無效表生成的語句？Acreate table cats(c_name varchar2(10), c_weight number, c_owner varchar2(10);Bcreate table my_cats as select * from cats where owner =

47、 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生成表時(shí)遇到下列錯(cuò)誤：ORA-00955-name is already used by existing object。下列哪個(gè)選項(xiàng)無法糾正這個(gè)錯(cuò)誤？A以不同的用戶身份生成對象。 B刪除現(xiàn)有同名對象C改變生成對象中的列名。 D更名現(xiàn)有同名對象。46. SALES表中的PROFITS列聲明為NUMBER(10, 2)。下列哪個(gè)值無法在這個(gè)列中存儲(chǔ)