C．select product.id, product.name, storage.loc from product natural join storage on product.box#=storage.box#; D．select product.id, product.name, storage.loc from product natural join storage;
26.数据库中有两个表PRODUCT与STORAGE_BOX，表中列出各个产品的惟一ID号、产品名和特定产品所在的箱子。另一个表中列出各个存储箱（用数字标识）可以包含许多产品，但每个箱子只能在一个地方。下列哪个语句能正确显示这个数据库中所有产品ID、名称和箱子地址，不管有没有指定存储箱？ A．select 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’(+); B．select 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’ ; C．select 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’(+); D．select p.prod_id, p.prod_name, b.box_loc 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 ;
27. 列出薪水高于所在部门平均工资的员工信息
A.select deptno,ename,sal from emp a where sal>(select avg(a.sal) from emp where deptno=a.deptno); B. select deptno,ename,sal from emp a where sal>(select avg(sal) from emp where deptno=a.deptno);
C.select a.deptno,a.ename,a.sal from emp a,emp b where a.deptno=b.deptno and a.sal>avg(b.sal);
D. select a.deptno,a.ename,a.sal from emp a,emp b where a.deptno=b.deptno and b.sal>avg(a.sal); 28.要查询PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个SQL语句显示不同地区、产品类型和季度的利润的交叉表输出？
A．select region, product_type, time,sum(profit) from profits group by region, prod_type, time;
B．select region, product_type, time from profits group by rollup(region, prod_type, time);
C．select region, product_type, time, from profits group by cube(region, prod_type, time);
D．select region, product_type, time,sum(profit) from profits group
by cube(region, prod_type, time);
29.对数据库运行下列哪个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; 30.检查下列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);
这个查询按哪个列的顺序返回输出？
A．A.DEPTON B．A.JOB C．B.LOC D．SUM(A.SAL) 31.要查询的PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个SQL语句按不同地区、产品类型和季度求出平均利润大于100000美元的利润？ A．select region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 group by region, prod_type, period; B．select region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 order by region, prod_type, period; C．select region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) > 100000; D．select region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) < 100000;
32.公司的员工费用应用程序有两表，一个是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.empno = e.empno; B．select e.empno, e.ename from emp e 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 (select sum(x.vouch_amt) from expense x where x.empno = e.empno; 33.看看下列语句： SQL> select ename 2 from emp
3 where empno in 4 ( select empno 5 from expense
6 where vouch_amt > 10000 );
下列哪个SQL语句产生与上面相同的输出，改写成使用运算符？ A．select e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>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)
D．select e.ename from emp e, expense x where x.empno = e.empno and x.vouch_amt>10000 and
exists(select x.empno from expense x where) 34.用下列代码块回答问题：
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．多行子查询 C．from子句子查询 D．多列子查询 35.公司销售数据库有两个表，PROFITS存储不同地区不同季度的产品销售利润，REGIONS存储每个部门地区名称、该地区总部地址和该地区副总裁姓名。下列哪个查询取得SMITHERS、FUJIMORI与LIKKARAJU领导的地区的玩具销售利润？
A．select sum(profit) from profits where region in (select region froms where reg_head in
(‘SMITHERS’, ‘FUJMORI’, ‘LAKKARAJU’)) and product =’TOYS’;
B．select sum(profit) from profits where region in (select region froms where reg_head in
(‘SMITHERS’, ‘FUJMORI’, ‘LAKKARAJU’) and product =’TOYS’ ); C．select sum(profit) from profits where region = (select region froms
where reg_head in
(‘SMITHERS’, ‘FUJMORI’, ‘LAKKARAJU’)) and product =’TOYS’;
D．select sum(profit) from profits where region in (select region froms where reg_head in
(‘SMITHERS’, ‘FUJMORI’, ‘LAKKARAJU’)) and product =’TOYS’;
36.查询出EMP表中COMM字段为空的记录( ) A. select * from emp where comm=''; B. select * from emp where comm=null; C. select * from emp where nvl(comm)=0; D. select * from emp where comm is null; 37.用下列代码块回答问题：
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)); DEPTNO ENAME JOB SAL
--------- ---------- ---------- --------- 30 BLAKE MANAGER 2850 10 CLARK MANAGER 2450 20 SCOTT ANALYST 3000 KING PRESIDENT 5000 20 FORD ANALYST 3000
要在上述返回集的DEPTNO列中显示99，可以用下列哪个SQL语句？ A．select 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));
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) = (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);
相关推荐：
loading