数据查询相关面试题

1. EMP-DEPT

【查询要求】 [color=darkred]显示部门工资总和高于雇员工资总和1/3的部门名称及工资总和?[/color]

select dname,sum(sal) from emp,dept where emp.deptno = dept.deptno

group by dname having sum(sal)>(select sum(sal)/3 from emp);

2. EMP

【查询要求】[color=darkred]查询固定数量的有序行：[/color]
select num, empno, ename from
(select rownum as num, empno, ename from emp) T
where T.num >= 6 and T.num <= 10;

3. 外连接的实现方式:

实现方式一: select ename,dname from emp left outer join dept on emp.deptno = dept.deptno;

实现方式二: select ename,dname from emp,dept where emp.deptno = dept.deptno(+);