Oracle+SQL+PLSQL练习题带答案

《Oracle+SQL+PLSQL练习题带答案》由会员分享，可在线阅读，更多相关《Oracle+SQL+PLSQL练习题带答案（8页珍藏版）》请在装配图网上搜索。

1、Java教学网整理提供Oracle SQL & PL/SQL练习题（三）本文由java教学网整理，转发请带出处（蓝色为正确选项）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兼容等价语句？Aselect empno, ename, loc from emp join dept on emp.deptno = dept.dept

2、no where substr(emp.ename, 1, 1) = 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 join dept on emp

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

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

5、 下列代码显示了PROFITS表输出的局部：PRODUCT_NAMEPRODUCT_TYPEQTR_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;Bwh

6、ere proeuct_type = APPLIANCE 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;用下列代码所示的内容回答后面八个问题：EMPNOENAMEJOBMGRHIREDA

7、TESALCOMMDEPTNO-7369SIMTHCLERK790217-DEC-80800207499ALLENSALESMAN769820-FEB-811600300307521WARDSALESMAN769822-FEB-811250500307566JONESMANAGER783902-APR-812975207654MARTINSALESMAN769828-SEP-8112501400307698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756619-APR-87

8、3000207839KINGPRESIDENT17-NOV-815000107844TURNERSALESMAN769808-SET-8115000307876ADAMSCLERK778823-MAY-871100207900JAMESCLERK768903-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-821300108. 以下选项中哪个是下列查询返回的值：select sum(sal) + sum(comm) from emp where job = ANALYST or ename like

9、J% ?A6000 B9925 C9975 DNULL9. 以下选项中哪个是下列查询返回的值：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 CTU

10、RNER DWARD12. 以下选项中哪个是下列SQL命令产生的第三个员工：ename, sal from emp where job= SALESMAN order by 1 desc ?AALLEN BMARTIN CTURNER DWARD13. 以下选项中哪个是发出下列查询后Oracle返回的值：select substr(job, 1, 3) from emp where ename like upper(_ar%) ?AANA BCLE CMAN DSAL14. 以下选项中哪个是发出下列查询后Oracle返回的值：select trunk(months_between(min(hi

11、redate), max(hiredate) from emp ?A24 B25 C-24 D-2515. 以下选项中哪个是发出下列查询后Oracle返回的值：select * from emp where hiredate 23-JAN-82 ?（选择两个答案）AADAMS BMILLER CSCOTT DSMITH16. TEST表包含两个列，TESTCOL定义为数据类型NUMBER(10)，TESTCOL_2定义为数据类型VARCHAR2(10)。在Oracle中发出下列语句：insert into test(testcol, testcol_2) values (null, FRANC

12、IS)，然后对表进程下列查询：select nvl(testcol, EMPTY) as testcol from test where testcol_2 = FRANCIS。下列选项哪个是得到的结果？AOracle返回结果0 BOracle返回结果EMPTYCOracle返回结果NULL DOracle返回错误结果17. 要从ORDERS表中取得数据，其中包括三个列CUSTOMER、ORDER_DATE与ORDER_AMT。可以用下列哪个where子句查询ORDERS表，取得客户LESLIE超过2700的订单？Awhere customer = LESLIE;Bwhere customer

13、 = LESLIE and order_amt 2700;Dwhere customer = LESLIE and order_amt 2700;18. 用下列输出回答问题（假设这里的信息来自本章使用的EMP表）：EMPNO - SMITH-dog- ALLEN-dog-WARD-dog-dJONES-dog-MARTIN-dogBLAKE-dog-CLARK-dog-SCOTT-dog-KING-dog-dTURNER-dogJAMES-dog-FORD-dog-dMILLER-dog下列哪个SQL语句产生这个输出？Aselect trim(trailing -dog from ename)

14、 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 下列哪个单行函数无法产生这个输出？Aabs() Bceil() Cfloor() Dround()20. 对表中的某一行，VARCHAR2列包含数值S

15、MITHY，应用程序在右侧填充七个空格。length()函数处理这个列值时，返回什么值？A6 B13 C30 D6021. SQL *Plus中发出的下列语句：SQL select ceil(-97.342), 2 floor(-97.342), 3 round(-97.342), 4 trunk(-97.342) 5 from dual;下列哪个函数不返回结果-97？Aceil() Bfloor() Cround() Dtrunc()22. SQL *Plus中发出的下列语句：SQL select ceil(256.342), 2 floor(256.342), 3 round(256.34

16、2), 4 trunk(256.342) 5 from dual;下列哪个函数不返回结果256？Aceil() Bfloor() Cround() Dtrunc()23. 在Oracle中发出的下列查询：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

17、()25. 数据库中有两表PRODUCT与STORAGE_BOX，表中列出各个产品的惟一ID号、产品名和特定产品所在的箱子。另一个表中列出各个存储箱（用数字标识）可以包含许多产品，但每个箱子只能在一个地方。下列哪个语句能正确显示这个数据库中所有产品ID、名称和箱子地址？Aselect p.prod_id, p.prod_name, b.box_loc from product p, storage_box b where p.prod_id = b.prod_id and prod_name=WIDGET;Bselect p.prod_id, p.prod_name, b.box_loc fr

18、om product p, storage_box b where prod_name=WIDGET;Cselect p.prod_id, p.prod_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;

19、26. 开发报表时，要连接三个表的信息，这些表为EMP、DEPT与SALGRADE。只需要公司10级以上员工相应的员工、部门地址与工资范围的记录。这个查询需要多少比较运算？A2 B3 C4 D527. 要连接两个表PRODUST与STOTAGE的内容，列出所有包含产品的箱子地址。PRODUCT有三列ID、NAME与BOX#；STORAGE有两列BOX#与LOC。下列哪个选项无法得到想要的结果？Aselect product.id, product.name, storage.loc from product, storage where product.box#=storage.box#;Bs

20、elect product.id, product.name, storage.loc from product join storage on product.box#=storage.box#;Cselect product.id, product.name, storage.loc from product natural join storage onproduct.box#=storage.box#;Dselect product.id, product.name, storage.loc from product natural join storage;28. 你要定义外连接，下

21、列哪个选项正确描述了外连接语句？A由于外连接操作允许一个表中有NULL值，因此连接这些表时不必指定相等性比较。B在表A与B的外连接语句中，如果不管B表有无相应记录，都要显示表A的所有行，则可以使用右外连接。C在表A与B的外连接语句中，如果不管A表有无相应记录，都要显示表B的所有行，则可以使用左外连接。D尽管外连接操作允许一个表中有NULL值，但连接这些表时仍要指定相等性比较。29. 数据库中有两个表PRODUCT与STORAGE_BOX，表中列出各个产品的惟一ID号、产品名和特定产品所在的箱子。另一个表中列出各个存储箱（用数字标识）可以包含许多产品，但每个箱子只能在一个地方。下列哪个语句能正确

22、显示这个数据库中所有产品ID、名称和箱子地址，不管有没有指定存储箱？Aselect p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b onp.stor_bax_num = b. stor_bax_num where prod_name=WIDGET(+); Bselect p.prod_id, p.prod_name, b.box_loc from product p left outer join storage_box b onp.stor_bax_num = b. stor_bax

23、_num where prod_name=WIDGET ;Cselect p.prod_id, p.prod_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 p.prod_id, p.prod_name, b.box_loc from product p full outer join storage_box b onp.stor_bax_num = b. stor_bax

24、_num where b.stor_box_num is NULL ;30. Oracle中发出下列命令：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 e outer join addr a on e.empno

25、= a.empno where a.state = TEXAS; Bselect e.ename, a.street_address, a.city, a.state, a.post_code from 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 e right outer join addr a on e.empno = a.empno where a.state

26、= TEXAS;Dselect e.ename, a.street_address, a.city, a.state, a.post_code from e right outer join addr a wheree.empno = a.empno(+) and a.state = TEXAS;31. 检查SQL *Plus的下列输出：PRODUCT.IDPRODUCT.NAMEBOX.LOCATION-578-XWIDGETIDAHOTENNESSEE456-YWIDGET 下列哪个选项是产生这个结果的查询类型？A全外连接 B左外连接 C右外连接 D等连接32. 要查询PROFITS表存储

27、公司不同地区、产品类型和季度的利润信息。下列哪个SQL语句显示不同地区、产品类型和季度的利润的交叉表输出？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(re

28、gion, prod_type, time);Dselect region, product_type, time,sum(profit) from profits group by cube(region, prod_type, time);33. 对数据库运行下列哪个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, jo

29、b, 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.deptno,a.job,b.loc 5 order by sum(a.sal);这个查询按哪个列的顺序返回输出？AA.DEPTON BA.JOB CB.LOC DSUM(A.SAL)35. 要查询的P

30、ROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个SQL语句按不同地区、产品类型和季度求出平均利润大于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_

31、type, period;Cselect region, prod_type, period, avg(profits) from profits group by region, prod_type, periodhaving avg(prodit) 100000;Dselect region, prod_type, period, avg(profits) from profits group by region, prod_type, periodhaving avg(prodit) 100000;36. 公司的员工费用应用程序有两表，一个是EMP，包含所有员工数据，一个是EXPENSE

32、，包含公司每个员工提交的费用票据。下列哪个查询取得提交的费用总和超过其工资值的员工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

33、e 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(se

34、lect 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 andexists(select x.empno fro

35、m 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. 公司销售数据库有两个表，PROFITS存储不同地区不同季度的产品销售利润，REGIONS存储每个部门地区名称、该地区总部地址和该地区副总裁姓名。下列哪个查询取得SMIT

36、HERS、FUJIMORI与LIKKARAJU领导的地区的玩具销售利润？Aselect sum(profit) from profits where region in (select region froms where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS; Bselect sum(profit) from profits where region in (select region froms where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and pro

37、duct =TOYS );Cselect sum(profit) from profits where region = (select region froms where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;Dselect sum(profit) from profits where region in (select region froms where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;40. 下列代码块显示的查询

38、包含一个子查询：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); 可以用下列哪个子句重新定义这个查询，删除子查询与主查询中组函数执行的重复？Agroup by Border by Cwith Dhaving41. 用下

39、列代码块回答问题：SQL select e.deptno,e.ename,e.job,e.sal 2 from emp e 3 where e.sal = 4 ( select max(e2.sal) 5 fromemp e2 6* where nvl(e.deptno,99) = nvl(de.deptno,99);DEPTNOENAMEJOBSAL-30BLAKEMANAGER285010CLARKMANAGER245020SCOTTANALYST3000KINGPRESIDENT500020FORDANALYST3000 要在上述返回集的DEPTNO列中显示99，可以用下列哪个SQL语句

40、？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.dept, 99) = nvl(e2.deptno, 99);Csel

41、ect 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) from emp e2 where nvl(e.dept, 99

42、) = nvl(e2.deptno, 99) group by e1.deptno);42. 公司销售数据库包含一个PROFITS表，按产品名、销售地区和季度列出利润信息。如果要取得公司五个最畅销产品清单，可以用下列哪个SQL语句：Aselect p.prod_name, p.profit from (select prod_name, profit from profits order by profit desc)where rownum = 5;Bselect p.prod_name, p.profit from (select prod_name, sum(profit) from p

43、rofits group by prod_nameorder by sum(profit) desc) subq where p.prod_name = subq.prod_name;Cselect p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_nameorder by sum(profit) desc) where rownum = 5;Dselect p.prod_name, p.profit from (select prod_name, sum(profit) f

44、rom profits order by sum(profit) desc) where rownum select profit from from profits where prod_type = TOY and time_period = &v_period;Daccept v_period select profit from from profits where prod_type = TOY and time_period = &v_period;44. 检查下列代码段中的脚本dates.sql：accept v_hiredate prompt enter hire date =

45、 select empno, ename, jobfrom empwhere trunk(hiredate) = trinc(&v_hiredate);为了使脚本正确工作，要改变哪个方面？A变量v_hiredate应变成接受DATE信息。 B查询中的trunk()函数应删除。Caccept命令中的prompt子句是多余的。 D这个脚本很好。45. Oracle中要生成数据库表，下列哪个选项是无效表生成的语句？Acreate table cats(c_name varchar2(10), c_weight number, c_owner varchar2(10);Bcreate table my

46、_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 cats_over_5_lbs as select c_name, c_weight from cats where c_weight 5;46. 试图在Oracle生成表时遇到下列错误：ORA-00955-name is already used by existing obje

47、ct。下列哪个选项无法纠正这个错误？A以不同的用户身份生成对象。 B删除现有同名对象C改变生成对象中的列名。 D更名现有同名对象。47. SALES表中的PROFITS列声明为NUMBER(10, 2)。下列哪个值无法在这个列中存储？A5392845.324 B871039453.1 C75439289.34 D60079829.2548. 员工KING于1981年11月17日聘用，你对Oracle数据库发出下列查询：select vsize(hiredate) from emp where ename = KING。下列哪个选项是返回的值？A4 B7 C9 D1749. SALES表中将PR

48、ODUCT_NAME列定义为CHAR(40)。后面要在表中增加一行，PRODUCT_NAME值为CAT_TOYS。然后发出下列命令：select vsize(product_name) from sales。下列哪个选项是返回的值？A8 B12 C40 D400050. JOB表有三个列JOB_NAME、JOB_DESC和JOB_WAGE。用下列命令在JOB_DESC表中插入新行：SQL insert into job(job_name, job_desc) 2 values (LUCKEY, MAKES COFFEE);之后查询这个表时得到的结果：SQL select * from job

49、where job_name = LUCKEY;JOB_NAMEJOB_DESCJOB_WAGE-LUCKEYMAKES COFFEE 35数据是如何填信JOB_WAGE表的？AJOB表中的LACKEY行已经存在，JOB_WAGE设置为35。B生成表时JOB_WAGE列定义的default子句指定插入行时的值。Cinsert语句的values子句包含隐藏值，在插入行时加入。D惟一理由是对JOB表发出的后一个update语句增加了JOB_WAGE值51. 要把非NULL NUMBER(10)列变成NUMBER(6)列。发出适当alter table命令之后必须完成下列哪些步骤？A将列记录复制到临

50、时存储地址。 B将所有行的NUMBER列设置为NULL。C对NUMBER数据生成临时地址。 D将列记录从临时地址复制加主表中。52. 发出下列语句：alter table sales drop columm profit; 何时从Oracle中实际删除列？A执行语句后立即删除。 B发出alter table drop unused columns命令之后C发出alter table set unused columns命令之后 D发出alter table modify命令之后53. 你要把非NULL VARCHAR2(5)列增加到VARCHAR2(10)。执行适当alter table命令之

51、后必须完成下列哪些步骤？A将所有行的VARCHAR2列设置为NULL。 B对VARCHAR2数据生成临时地址。C将列记录从临时地址复制回主表中。 D不用，这个语句自动执行。54. 要把SALES表中的PRODUCT_TYPE列的大小从VARCHAR（5）列增加到VARCHAR2(10)，用下列哪个语句？Aalter table sales add(product_type varchar2(10);Balter table sales modify product_type varchar2(10);Calter table sales unused column product_type v

52、archar2(10);Dalter table sales drop column product_type;55. 删除Oracle数据库中父/子关系中的父表。在删除父表时下列哪个对象不会删除？A相关约束 B子列 C相关触发器 D相关索引56. 数据库中的中的PROFITS表中PRODUCT_NAME与SALE_PERIOD列建立主键。下列哪个语句无法定义这个主键？Acreate table profits(product_name varchar2(10), sale_perion varchar2(10), profit number, constraint pk_profits_01

53、 primary key (product_name, sale_period);Balter table profits add constraint pk_profits_01 primary key(product_name, sale_period) deferrable initially immediate;Calter table profits add(constraint pk_profits_01 primary key(product_name, sale_period);Dcreate table profits(product_name varchar2(10) pr

54、imary key, sale_period varchar2(10) Primary key, profit number)57. SALES表中定义的检查约束包含两个列PRODUCT_NAME与SALE_PERIOD。下列哪些选项表示定义的检查约束？选择两个。Aalter table sales add constraint ck_sales_01 check(product_type in(TOYS, HOT DOGS,PALM PILOTS);Balter table sales add constraint ck_sales_01 check(product_type in(sele

55、ct product_type fromvalid_products);Calter table sales modify(product_type varchar2(30) check(product_type in(TOYS, HOT DOGS,PALM PILOTS);Dalter table sales add(product_name varchar2(30) check(product_name AK-47);58. 用下列代码块回答问题：SQL create table prices 2 ( priduct_name varchar2(30), 3 price number(10

56、,4);table cteated.SQL alter table prices add constraint pk_prices_012 primary key(product_name); table altered. SQL insert into prices values(DOGGY,499.99); 1 row created. SQL alter table prices disable constraint pk_prices_01; Table altered. SQL insert into prices values(DOGGY,499.99); 1 row created. SQL alter table prices enable novalidate constraint pk_prices_01;然后发生什么情况？A检查现有项目是否违规，启动PK_PRICES_01，Oracle立即检查