一、选择题(40分)1.Oracle发出下列select语句:SQL> select , ,2 from emp e, dept d3 where =4 and substr, 1, 1) = ‘S’;下列哪个语句是Oracle数据库中可用的ANSI兼容等价语句A.select empno, ename, loc from emp join dept on = wheresubstr, 1, 1) = ‘S’;B.select empno, ename, loc from emp, dept on = wheresubstr, 1, 1) = ‘S’;C.select empno, ename, loc from emp join dept where = andsubstr, 1, 1) = ‘S’;D.select empno, ename, loc from emp left join dept on = andsubstr, 1, 1) = ‘S’;2.下列哪个选项表示Oracle中select语句的功能A.可以用select语句改变Oracle中的数据 B.可以用select语句删除Oracle 中的数据C.可以用select语句和另一个表的内容生成一个表 D.可以用select语句对表截断3.你要操纵Oracle数据,下列哪个不是SQL命令A.select * from dual; B.set defineC.update emp set ename = 6543 where ename = ‘SMITHERS’;D.create table employees(empid varchar2(10) primary key);4.你要在Oracle中定义SQL查询。
下列哪个数据库对象不能直接从select语句中引用A.表B.序列 C.索引 D.视图5.你要根据PRODUCT_NAME列从PROFITS表查询中过滤返回的数据。
下列哪个子句包含相应过滤条件的引用A.select B.from C.where D.having用下列代码所示的内容回答后面五个问题:EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO------ ---------- --------- ---- --------- ---- ---- ------7369 SIMTH CLERK 7902 17-DEC-80 800 207499 ALLEN 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 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 106.以下选项中哪个是下列查询返回的值:select sum(sal) + sum(comm) from emp wherejob = ‘ANALYST’ or ename like ‘J%’A.6000 B.9925 C.9975 D.NULL null参与运算后的结果还是null7.以下选项中哪个是下列查询返回的值:select count(mgr) from emp where deptno = 10 A.1 B.2 count运算可以忽略空行C.3 D.NULL8.以下选项中哪个是下列查询返回的值:select count(*) from emp where mgr = 7700-2 A.5 B.6 C.7 D.NULL9.以下选项中哪个是下列SQL命令产生的第三个员工:select ename, sal from emp wherejob= ‘SALESMAN’ order by empno descA.ALLEN B.MARTIN C.TURNER D.WARD10.以下选项中哪个是发出下列查询后Oracle返回的值:select substr(job, 1, 3) fromemp where ename like upper(‘__ar%’)A.ANA B.CLE C.MAN D.SAL11.要从ORDERS表中取得数据,其中包括三个列CUSTOMER、ORDER_DATE与ORDER_AMT。
可以用下列哪个where子句查询ORDERS表,取得客户LESLIE超过2700的订单A.where customer = ‘LESLIE’;B.where customer = ‘LESLIE’ and order_amt < 2700;C.where customer = ‘LESLIE’ or order_amt > 2700;D.where customer = ‘LESLIE’ and order_amt > 2700;12.对表中的某一行,VARCHAR2列包含数值SMITHY,应用程序在右侧填充七个空格。
length()函数处理这个列值时,返回什么值A.6 B.13 C.30 D.6013.在Oracle中发出的下列查询:SQL> select months_between(‘15-MAR-83’,’15-MAR-97’) form dual;Oracle返回什么A.14 B.-14 C.168 D.-16814.开发报表时,要连接三个表的信息,这些表为EMP、DEPT与SALGRADE。
只需要公司10级员工相应的员工姓名、部门地址与工资的记录。
这个查询需要多少条件A.2 B.3 C.4 D.515.Oracle中发出下列命令:SQL> select ,,,2 from emp e,addr a3 where = (+)4 and = ‘TEXAS’;下列哪个选项显示等价ANSI/ISO语句A.select , , , , from emp e outer join addr a on = where = ‘TEXAS’; B.select , , , , from emp e left outer join addr a on = where = ‘TEXAS’; C.select , , , , from emp e right outer join addr a on = where = ‘TEXAS’; D.select , , , , from emp e right outer join addr a where = (+) and = ‘TEXAS’;16.对数据库运行下列哪个group by查询时会从Oracle产生错误A.select deptno, job, sum(sal) from emp group by job, deptno;B.select sum(sal), deptno, job from emp group by job, deptno;C.select deptno, job, sum(sal) from emp;D.select deptno, sum(sal), job from emp group by job, deptno;17.检查下列SQL的输出SQL> select ,,,sum2 from dmp a,dept b3 where =4 group by ,,5 order by sum;这个查询按哪个列的顺序返回输出A. B. C.D.SUM18.用下列代码块回答问题:SQL> select deptno,job,avg(sal)2 from emp3 group by deptno,job4 having avg(sal)>5 ( select sal6 from emp7 where ename = ‘MARTIN’ );上述语句使用下面哪种子查询A.单行子查询B.多行子查询 C.from子句子查询 D.多列子查询查询语句返回的结果是单行就是单行子查询查询语句返回的是多行就是多行子查询19.Oracle中要生成数据库表,下列哪个选项是无效表生成的语句A.create table cats(c_name varchar2(10), c_weight number, c_owner varchar2(10)); B.create table my_cats as select * from cats where owner = ‘ME’;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;20.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 where job_name = ‘LUCKEY’;JOB_NAME JOB_DESC JOB_WAGE--------- ------------ --------LUCKEY MAKES COFFEE 35数据是如何填信JOB_WAGE表的A.JOB表中的LUCKEY行已经存在,JOB_WAGE设置为35。