Oracle笔试题及答案

1、、选择题（每题 1 分）1. Oracle 发出下列 select 语句:SQL> select e.empno, e.ename, d.loc2 from emp e, dept d3 where e.deptno = d.deptno4 and substr(e.ename, 1, 1) =S' ;下列哪个语句是 Oracle数据库中可用的 ANSI兼容等价语句？A select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = S&

2、#39;B select empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = S'C select empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename, 1, 1) = S'D select empno, ename, loc from emp left join dept on emp.deptno = dept.dep

3、tno and substr(emp.ename, 1, 1) = S'2. 你要对操纵 Oracle 数据库中的数据。 下列哪个选项表示 Oracle 中 select 语句的功能， 并且不需要使用子查询？A可以用中的数据Cselect 语句改变 Oracle 中的数据可以用 select 语句删除 Oracle3.A4.ACD5.A6.A可以用select 语句和另一个表的内容生成一个表 D 可以用 select 语句对表截断数据库中发出一个查询。 下列哪个选项不能查询对用户定义静态表达式执行数学Oracle运算时的查询组件？列子句 B .表子句C . DUAL表 D . wher

4、e子句你要操纵Oracle数据，下列哪个不是 SQL命令？select * from dual; Bset defineupdate emp set ename = 6543 where ename =SMITHERS'create table employees(empid varchar2(10) primary key);你要在Oracle中定义SQL查询。下列哪个数据库对象不能直接从select语句中引用？表 B 序列 C 索引 D 视图你要根据PRODUCT_NAM列从PROFITS表查询中过滤返回的数据。下列哪个子句包含相 应过滤条件的引用？select Bfrom C w

5、here D having下列代码显示了 P ROFITS表输出的局部:PRODUCT_NAMEPRODUCT_TYPE QTR_END_DATEPROFITBARNEY DOLLTOY31-MAR-20016575430.30GAS GRILLAPP31-MAR-20011234023.887.PENCIL OFFICE 30-JUN-2001 34039.99A where product_name = 01-JUN-2001 ' ;下列查询的 where 子句哪个能正确计算 2001 年 1 月 1 日到 6 月 1 日六个月内销售的所有家 电总利润？GASGRILL'

6、and qtr_end_date between ' 01-JAN-2001 ' and8.A以下选项中哪个是下列查询返回的值： job = ANALYS'T or ename like 6000 B 9925 C 9975select sum(sal) + sum(comm) from emp where J%' ?D NULL9.A以下选项中哪个是下列查询返回的值：1 B 2 C3select count(mgr) from empwhere deptno = 10 ? NULLB where proeuct_type =APPLIANCE' and

7、 name = GAS GRILL' and qtr_end_date= ' 31-JAN-2001 ' or30-JUN-2001 ' ;Cwhere proeuct_type = APPLIANCE' and qtr_end_date between '01-JAN-2001 'and '01-JUN-2001;D where proeuct_name = GAS GRILL' and qtr_end_date =' 01-JAN-2001 ' or01-JUN-2001 ' ;用下列代码所示的

8、内容回答后面八个问题：EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO7369SIMTHCLERK790217-DEC-80800207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207654MARTINSALESMAN769828-SEP-8112501400307698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-81245

9、0107788SCOTTANALYST756619-APR-823000207839KINGPRESIDENT17-NOV-815000107844TURNERSALESMAN769808-SET-8115000307876ADAMSCLERK778823-DEC-821100207900JAMESCLERK769803-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-8213001010. 以下选项中哪个是下列查询返回的值：A5 B 6 C 7select count(*) from empwher

10、e mgr = 7700-2 ? NUL11.以下选项中哪个是下列 SQL命令产生的第三个员工：select ename, sal from empwherejob= SALESMA'N order by empno desc ?AALLEN B MARTIN C TURNER D WARD12.以下选项中哪个是下列 SQL命令产生的第三个员工: SALESMA'N order by 1 desc ?AALLEN B MARTIN C TURNER D WARDename, sal from emp where job=13. 以下选项中哪个是发出下列查询后 Oracle 返回

11、的值: emp where ename like upper( _ar%' ) ?AANA B CLEC MAN D SALselect substr(job, 1, 3) from14. 以 下 选 项 中 哪 个 是 发 出 下 列 查 询 后 trunc(months_between(min(hiredate), max(hiredate) from emp ?A 24B 25C -24D -25Oracle 返 回 的 值 : select15. 以下选项中哪个是发出下列查询后 hiredate >'23-JAN-82 ' ? （选择两个答案） AADAM

12、S B MILLER C SCOTTOracle 返回的值: select * from emp whereD SMITH16. TEST表包含两个列，TESTCO定义为数据类型 NUMBER(10) TESTCOL_定义为数据类型VARCHAR2(10。) 在 Oracle 中发出下列语句: insert into test(testcol, testcol_2)values (null, FRANCIS)，然后对表进行下列查询：select n vl(testcol, EMP TY)as testcol from test where testcol_2 = FRANCIS。下列选项哪个是

13、得到的结果？AOracle 返回结果 0B Oracle 返回结果 EMPTYCOracle 返回结果 NULLD Oracle 返回错误结果17.要从ORDER表中取得数据，其中包括三个列CUSTOMERORDER_DAT与ORDER_AMT可以用下列哪个 where子句查询 ORDER表，取得客户 LESLIE超过2700的订单？Awhere customer = LESLIE'JBwhere customer = LESLIE'and order_amt < 2700;Cwhere customer = LESLIE'or order_amt > 27

14、00;Dwhere customer = LESLIE'and order_amt > 2700;假设这里的信息来自本章使用的18. 用下列输出回答问题EMPNOEMP表):SMITH-dog- ALLEN-dog- WARD-dog-d JONES-dog- MARTIN-dog BLAKE-dog- CLARK-dog- SCOTT-dog- KING-dog-d TURNER-dogJAMES-dog-FORD-dog-dMILLER-dog下列哪个SQL语句产生这个输出？ select trim(trailing-dog ' from ename) as enam

15、e from emp; select rpad(ename, 10, -dog ') as ename from emp; select substr(ename,1 , 10) as ename from emp; select lpad(ename, 10, -dog ') as ename from emp;ABCD19. 用下列代码块回答问题：SQL> select (-45) as output from dual;OUTPUT-45 下列哪个单行函数无法产生这个输出？ A abs()B ceil()C floor() D round()20.对表中的某一行，V

16、ARCHAR列包含数值SMITHY应用程序在右侧填充七个空格。length() 函数处理这个列值时，返回什么值？A6B 13C306021. SQL *Plus 中发出的下列语句： SQL> select ceil(-97.342),234floor(-97.342), round(-97.342), trunc(-97.342) from dual;下列哪个函数不返回结果 -97 ？A ceil()B floor()round() trunc()22. SQL *Plus 中发出的下列语句： SQL> select ceil(256.342),2Aceil()B floor()r

17、ound() trunc()floor(256.342), round(256.342), trunc(256.342) from dual;下列哪个函数不返回结果 256？15-MAR-83','15-MAR-97') form dual;168 D -16823. 在 Oracle 中发出的下列查询： SQL> select months_between( Oracle 返回什么？A14B -14 C24. 你要在 Oracle A to_date() D to_number()中使用日期信息的格式掩码。下列哪种情形不适合这个格式掩码？B to_char()C

18、 alter session set nls_date_formatPRODUC与STORAGE_BO>PRODUC表中列出各个产品的惟一 ID号、产25. 数据库中有两表 品名和特定产品所在的箱子。另一个表中列出各个存储箱(用数字标识)可以包含许多 产品，但每个箱子只能在一个地方。 下列哪个语句能正确显示这个数据库中所有产品 名称和箱子地址？A select d_id, d_name, b.box_loc from product p, storage_box b where d_id = d_id and prod_name='WIDG

19、ET'B select d_id, d_name, b.box_loc from product p, storage_box b where prod_name= 'WIDGE'T ;C select 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'D select prod_id, prod_name, box_loc from pr

20、oduct , storage_box where stor_box_num = stor_box_num and prod_name='WIDGET'ID、26.开发报表时，要连接三个表的信息，这些表为EMP DEPT与SALGRAD。只需要公司10级以上员工相应的员工、部门地址与工资范围的记录。这个查询需要多少比较运算？27.要连接两个表 PRODUS与STOTAG的内容，列出所有包含产品的箱子地址。PRODUC有三列ID、NAME与 BOX# STORAG有两列BOX与LOC下列哪个选项无法得到想要的结 果？A select product.id, product.nam

21、e, storage.loc product.box#=storage.box#;B select product.id, , storage.loc product.box#=storage.box#;C select product.id, , storage.loc fromon product.box#=storage.box#;from product, storage wherefrom product join storage onproduct natural joinstorageA2B 3 C 4 D 5D select pr

22、oduct.id, , storage.loc from product natural join storage;28. 你要定义外连接，下列哪个选项正确描述了外连接语句？ ABCD由于外连接操作允许一个表中有在表A与B的外连接语句中，如果不管 则可以使用右外连接。在表A与B的外连接语句中，如果不管 则可以使用左外连接。尽管外连接操作允许一个表中有NULL值，但连接这些表时仍要指定相等性比较。NULL值，因此连接这些表时不必指定相等性比较。 A的所有行,B 表有无相应记录，都要显示表A 表有无相应记录，都要显示表B的所有行,29.数据库中有两个表 PRODUC与STO

23、RAGE_BOX表中列出各个产品的惟一ID号、产品名和特定产品所在的箱子。另一个表中列出各个存储箱(用数字标识)可以包含许多产品， 但每个箱子只能在一个地方。下列哪个语句能正确显示这个数据库中所有产品ID、名称和箱子地址，不管有没有指定存储箱？A d_id,d_name,b.box_locfromproductstorage_box b on p.stor_bax_num = b. stor_bax_num where prod_name= B d_id,d_name,b.box_locfromproductstorage_box

24、 b on p.stor_bax_num = b. stor_bax_num where prod_name= C d_id, d_name, b.box_loc from productleftouter join'WIDGET'(+);leftouter join'WIDGET' ;outer join' WIDGE'Trightstorage_box b where b.stor_bax_num = p. stor_bax_num(+) and prod_name= (+);D d_id

25、, d_name, b.box_loc from product pstorage_box b on p.stor_bax_num = b. stor_bax_num where b.stor_box_num is NULL ;fullouter join30. Oracle 中发出下列命令： SQL> select e.ename,a.street_address,a.city,a.post_code2 from emp e,addr a3 where e.empno = a.empno(+)4 and a.state = TEXAS' ;下列哪个选项显示等价 ANS

26、I/ISO 语句？ A select e.ename, a.street_address, a.city, join addr a on e.empno = a.empno where a.state =a.state,a.post_code from TEXAS' ;emp e outerB select 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 = C select e.ena

27、me, a.street_address,a.city,a.state,outer join addr a on e.empno = a.empno where a.state = D select e.ename, a.street_address,a.city,a.state,outer join addr a where e.empno = a.empno(+) and a.state =TEXAS'a.post_code fromTEXAS'a.post_code fromTEXAS'emp e rightemp e right31. 检查 SQL *Plus

28、的下列输出：578-XIDAHOWIDGETTENNESSEEWIDGETPRODUCT.ID PRODUCT.NAME BOX.LOCATION456-Y 下列哪个选项是产生这个结果的查询类型？A. 全外连接B .左外连接C .右外连接 D .等连接SQL语句显示不32. PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个 同地区、产品类型和季度的利润的交叉表输出？A select region, product_type, time, sum(profit) from profits group by region, prod_type, time;Bselectregi

29、on,product_type,time from profitsgroup by rollup(region,prod_type,time);C selectregion,product_type,time, from profitsgroup by cube(region,prod_type,time);D selectregion,product_type,time,sum(profit)from profits group by cube(region,prod_type, time);33. 对数据库运行下列哪个 group by 查询时会从 Oracle 产生错误？ ABCDsel

30、ect deptno, job, sum(sal) from emp group by job, deptno; select sum(sal), deptno, job from emp group by job, deptno; select deptno, job, sum(sal) from emp;select 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 b3 where

31、a.deptno = b.depton4 group by a.deptno,a.job,b.loc5 order by sum(a.sal);这个查询按哪个列的顺序返回输出？35.要查询的PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个 句按不同地区、产品类型和季度求出平均利润大于A select region, prod_type, period, avg(profits)100000 group by region, prod_type, period;B select region, prod_type, period, avg(profits)100000 ord

32、er by region, prod_type, period;SQL语100000 美元的利润？from profits where avg(prodit)from profits where avg(prodit)AA.DEPTON B A.JOB C B.LOC D SUM(A.SAL)C select region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) > 100000;D select region, prod_typ

33、e, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) < 100000;36.公司的员工费用应用程序有两表，一个是 EMP包含所有员工数据，一个是EXPENSE包含公司每个员工提交的费用票据。 下列哪个查询取得提交的费用总和超过其工资值的 员工 ID 与姓名？A select e.empno, e.ename from emp e where e.sal < (select sum(x.vouch_amt) from expense x) and x.em

34、pno = e.empno;B select e.empno, e.ename from empe where e.sal < (select x.vouch_amt from expense x where x.empno = e.empno);C select e.empno, e.ename from emp e where e.sal < (select sum(x.vouch_amt) from expense x where x.empno = e.empno);D select e.empno, e.ename from emp e where exists (sel

35、ect sum(x.vouch_amt) from expense x where x.empno = e.empno);37. 看看下列语句： SQL> select ename2 from emp3 where empno in4 ( select empno5 from expense6 where vouch_amt > 10000 );下列哪个SQL语句产生与上面相同的输出，改写成使用运算符？A select e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>

36、;10000) and x.empno = e.empno;B select e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>10000 and x.empno = e.empno);C select e.ename from emp e where x.empno = e.empno and exists(select x.empno from expense x where x.vouch_amt>10000)38. 用下列代码块回答问题：SQL> select

37、 deptno,job,avg(sal)23D select e.ename from empe, expense x where x.empno = e.empno and x.vouch_amt>10000 and exists(select x.empno from expense x where)from empgroup by deptno,jobhaving avg(sal)>( select sal from emp where ename = MARTIN' );上述语句使用下面哪种子查询？A.单行子查询B .多行子查询C . from子句子查询D .多列子

38、查询39. 公司销售数据库有两个表， PROFITS 存储不同地区不同季度的产品销售利润， REGIONS 存储每个部门地区名称、该地区总部地址和该地区副总裁姓名。下列哪个查询取得SMITHERS FUJIMORI与LIKKARAJU领导的地区的玩具销售利润？Aselect sum(profit) from profits where region in (select region froms regions where reg_head in ( SMITHERS', FUJMOR'I , LAKKARAJU') and product ='TOYS'

39、;FUJMOR'I , LAKKARAJ'U) and product ='TOYS' ); regions where 'TOYS' regions where 'TOYS'B select sum(profit) from profits where region like (select region froms regions where reg_head in (SMITHERS',region froms ) and product =region froms ) and product =C select s

40、um(profit) from profits where region = (select reg_head in (SMITHERS', FUJMOR'I , LAKKARAJU'40. 下列代码块显示的查询包含一个子查询： SQL> select dname, avg(sal) as dept_avg234Dselect sum(profit) from profits where region is (select reg_head in (SMITHERS', FUJMOR'I , LAKKARAJU'from emp, dept

41、 where emp.deptno = dept.deptno group by dname having avg(sal) >( select avg(sal)*1/4from emp,deptwhere emp.deptno = dept.deptno ) order by avg(sal);8可以用下列哪个子句重新定义这个查询,删除子查询与主查询中组函数执行的重复?A group by Border by Cwith D having 41. 用下列代码块回答问题：from emp e where e.sal = ( select max(e2.sal) from emp e2SQ

42、L> select e.deptno,e.ename,e.job,e.sal2DEPTNOENAMEJOBSAL30BLAKEMANAGER285010CLARKMANAGER245020SCOTTANALYST3000KINGPRESIDENT500020FORDANALYST300056* where nvl(e.deptno,99) = nvl(e2.deptno,99);DEP TN倒中显示99,可以用下列哪个 SQL语句?要在上述返回集的A select nvl(e.deptno,99), e.ename, e.sal from emp e where (e.deptno, e

43、.sal) = (select max(e2.sal) from emp e2 where nvl(e.dept, 99) = nvl(e2.deptno, 99);B select nvl(e.deptno,99), e.ename, e.sal from emp e where e.sal = (select max(e2.sal) from emp e2 where nvl(e.dept, 99) = nvl(e2.deptno, 99);C select nvl(e.deptno,99), e.ename, e.sal from emp e where (e.deptno, e.sal

44、) = (select e2.deptno,max(e2.sal) from emp e2 where nvl(e.dept, 99) = nvl(e2.deptno, 99);D select 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) group by e1.deptno);42.公司销售数据库包含一个PROFITS表，按产品名

45、、销售地区和季度列出利润信息。如果要取得公司五个最畅销产品清单，可以用下列哪个SQL语句：A select d_name, fit from (select prod_name, profit from profits order by profit desc) where rownum <= 5;B select d_name, fit from (select prod_name, sum(profit) from profitsgroup by prod_name order by sum(profit) desc) subq where p

46、.prod_name d_name;C select d_name, fit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum <= 5;D select d_name, fit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum <= 5

47、;43. 检查下列代码段中的脚本 dates.sql ： accept v_hiredate prompt enter hire date => select empno, ename, jobfrom empwhere trunc(hiredate) = trunc( &v_hiredate为了使脚本正确工作，要改变哪个方面？A.变量v_hiredate 应变成接受DATE信息。Caccept命令中的 prompt 子句是多余的。');B .查询中的 trunc() 函数应删除。D .这个脚本很好。中要生成数据库表 , 下列哪个选项是无效表生成的语句？table cat

48、s(c_name varchar2(10), c_weight number, c_owner varchar2(10);ME'44. OracleA. createB. create table my_cats as select * from cats where owner =C. create global temporary table temp_cats(c_name varchar2(10), c_weight number, c_owner varchar2(10);D. create 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 删除现有同名对象C.改变生成对象中的列名。D更名现有同名对象。46. SALES表中的PROFITS列声明为NUMBER(10, 2)。下列哪个值无法在这个列中存储？A5392845.324 B 871039453.1 C 754