1、Oracle PLSQL考试题Oracle SQL & PL/SQL 测试题1. Oracle发出下列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数据库中可用的兼容等价语句?Aselect empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename,
2、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 left join dept on emp.deptno = dept.deptno
3、and substr(emp.ename, 1, 1) = S;2. 你要对操纵Oracle数据库中的数据。下列哪个选项表示Oracle中select语句的功能,并且不需要使用子查询?A可以用select语句改变Oracle中的数据 B可以用select语句删除Oracle中的数据C可以用select语句和另一个表的内容生成一个表 D可以用select语句对表截断3. 你要操纵Oracle数据,下列哪个不是SQL命令?Aselect * from dual; Bset defineCupdate emp set ename = 6543 where ename = SMITHERS;Dcrea
4、te table employees(empid varchar2(10) primary key);4. 你要在Oracle中定义SQL查询。下列哪个数据库对象不能直接从select语句中引用?A表 B序列 C索引 D视图5. 你要根据PRODUCT_NAME列从PROFITS表查询中过滤返回的数据。下列哪个子句包含相应过滤条件的引用?Aselect Bfrom Cwhere Dhaving6. 下列代码显示了PROFITS表输出的局部:PRODUCT_NAME PRODUCT_TYPE QTR_END_DATE PROFIT- - - -BARNEY DOLL TOY 31-MAR-200
5、1 6575430.30GAS GRILL APP 31-MAR-2001 1234023.88PENCIL OFFICE 30-JUN-2001 34039.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 GRILL and qtr_end_date = 31-JAN-2001 o
6、r 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;用下列代码所示的内容回答后面八个问题:EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7369 SIMTH CLERK 7902 17-DEC-80 800 207499 AL
7、LEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-82 3000 207839
8、 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SET-81 1500 0 307876 ADAMS CLERK 7788 23-DEC-82 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 107. 以下选项中哪个是下列查询返回的值:select sum(sal) + sum(comm) from emp where jo
9、b = ANALYST or ename like J% ?A6000 B9925 C9975 DNULL8. 以下选项中哪个是下列查询返回的值:select count(mgr) from emp where deptno = 10 ?A1 B2 C3 DNULL9. 以下选项中哪个是下列查询返回的值:select count(*) from emp where mgr = 7700-2 ?A5 B6 C7 DNUL10. 以下选项中哪个是下列SQL命令产生的第三个员工:select ename, sal from emp where job= SALESMAN order by empno
10、 desc ?AALLEN BMARTIN CTURNER DWARD11. 以下选项中哪个是下列SQL命令产生的第三个员工:ename, sal from emp where job= SALESMAN order by 1 desc ?AALLEN BMARTIN CTURNER DWARD12. 以下选项中哪个是发出下列查询后Oracle返回的值:select substr(job, 1, 3) from emp where ename like upper(_ar%) ?AANA BCLE CMAN DSAL13. 以下选项中哪个是发出下列查询后Oracle返回的值:select tr
11、unc(months_between(min(hiredate), max(hiredate) from emp ?A24 B25 C-24 D-2514. 以下选项中哪个是发出下列查询后Oracle返回的值:select * from emp where hiredate 23-JAN-82 ?(选择两个答案)AADAMS BMILLER CSCOTT DSMITH15. TEST表包含两个列,TESTCOL定义为数据类型NUMBER(10),TESTCOL_2定义为数据类型VARCHAR2(10)。在Oracle中发出下列语句:insert into test(testcol, testc
12、ol_2) values (null, FRANCIS),然后对表进行下列查询:select nvl(testcol, EMPTY) as testcol from test where testcol_2 = FRANCIS。下列选项哪个是得到的结果?AOracle返回结果0 BOracle返回结果EMPTYCOracle返回结果NULL DOracle返回错误结果16. 要从ORDERS表中取得数据,其中包括三个列CUSTOMER、ORDER_DATE与ORDER_AMT。可以用下列哪个where子句查询ORDERS表,取得客户LESLIE超过2700的订单?Awhere customer
13、 = LESLIE;Bwhere customer = LESLIE and order_amt 2700;Dwhere customer = LESLIE and order_amt 2700;17. 用下列输出回答问题(假设这里的信息来自本章使用的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语句产生这个输出?A
14、select 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;18. 用下列代码块回答问题:SQL select _(-45) as output from dual;OUTPUT-45 下列哪个单行函数无法产生这个输出?Aabs() Bceil() Cfloor
15、() Dround()19. 对表中的某一行,VARCHAR2列包含数值SMITHY,应用程序在右侧填充七个空格。length()函数处理这个列值时,返回什么值?A6 B13 C30 D6020. SQL *Plus中发出的下列语句: SQL select ceil(-97.342), 2 floor(-97.342), 3 round(-97.342), 4 trunc(-97.342) 5 from dual; 下列哪个函数不返回结果-97?Aceil() Bfloor() Cround() Dtrunc()21. SQL *Plus中发出的下列语句: SQL select ceil(25
16、6.342), 2 floor(256.342), 3 round(256.342), 4 trunc(256.342) 5 from dual; 下列哪个函数不返回结果256?Aceil() Bfloor() Cround() Dtrunc()22. 在Oracle中发出的下列查询:SQL select months_between(15-MAR-83,15-MAR-97) form dual;Oracle返回什么?A14 B-14 C168 D-16823. 你要在Oracle中使用日期信息的格式掩码。下列哪种情形不适合这个格式掩码?Ato_date() Bto_char() Calter
17、 session set nls_date_format Dto_number()24. 数据库中有两表PRODUCT与STORAGE_BOX,PRODUCT表中列出各个产品的惟一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=WIDGE
18、T;Bselect p.prod_id, p.prod_name, b.box_loc from 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 sto
19、r_box_num = stor_box_num and prod_name=WIDGET;25. 开发报表时,要连接三个表的信息,这些表为EMP、DEPT与SALGRADE。只需要公司10级以上员工相应的员工、部门地址与工资范围的记录。这个查询需要多少比较运算?A2 B3 C4 D526. 要连接两个表PRODUST与STOTAGE的内容,列出所有包含产品的箱子地址。PRODUCT有三列ID、NAME与BOX#;STORAGE有两列BOX#与LOC。下列哪个选项无法得到想要的结果?Aselect product.id, product.name, storage.loc from produ
20、ct, storage where product.box#=storage.box#;Bselect 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 on product.box#=storage.box#;Dselect product.id, product.name, storage.loc
21、from product natural join storage;27. 你要定义外连接,下列哪个选项正确描述了外连接语句?A由于外连接操作允许一个表中有NULL值,因此连接这些表时不必指定相等性比较。B在表A与B的外连接语句中,如果不管B表有无相应记录,都要显示表A的所有行, 则可以使用右外连接。C在表A与B的外连接语句中,如果不管A表有无相应记录,都要显示表B的所有行, 则可以使用左外连接。D尽管外连接操作允许一个表中有NULL值,但连接这些表时仍要指定相等性比较。28. 数据库中有两个表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 on p.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
23、 join storage_box b on p.stor_bax_num = b. stor_bax_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 oute
24、r join storage_box b on p.stor_bax_num = b. stor_bax_num where b.stor_box_num is NULL ;29. 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.sta
25、te, 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.city, a.state, a.post_code from e
26、mp 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;30. 检查SQL *Plus的下列输出:PRODUCT.ID PRODUCT.NAME BOX.LOCATION- - -578-X WIDGET IDAHO T
27、ENNESSEE456-Y WIDGET 下列哪个选项是产生这个结果的查询类型?A全外连接 B左外连接 C右外连接 D等连接31. PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个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_
28、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);32. 对数据库运行下列哪个group by查询时会从Oracle产生错误?Aselect deptno, job, sum(sal) from emp group by job, deptno; Bs
29、elect 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;33. 检查下列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 orde
30、r by sum(a.sal);这个查询按哪个列的顺序返回输出?AA.DEPTON BA.JOB CB.LOC DSUM(A.SAL)34. 要查询的PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个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 regi
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1