Oracle练习题(四)
(1) 查询至少有一个员工的部门信息
select d.* from dept d,(select count(*) c,deptno from emp group by deptno ) e where d.deptno = e.deptno and c>=1;
或者
select * from dept where dept.deptno in(select emp.deptno from emp group by deptno having count(1) >=1);
(2) 查询工资比SMITH员工工资高的所有员工信息
select * from emp where sal>(select sal from emp where ename=‘SMITH’);
(3) 查询入职日期早于其直接领导的所有员工信息
select * from emp where hiredate<(select hiredate from emp e2 where e2.empno = emp.mgr);
(4) 查询所有员工的姓名及其直接领导的姓名
select e1.ename ename,e2.ename mgr from emp e1,emp e2 where e1.mgr = e2.empno;
(5) 查询所有部门及其员工信息,包括那些没有员工的部门
select d.,e. from emp e,dept d where d.deptno = e.deptno(+);
(6) 查询所有职业为CLERK的员工的姓名及其部门名称
select ename,dname from emp e,dept d where e.deptno = d.deptno and job=‘CLERK’;
(7) 查询最低工资大于 2500的各种职业。
select distinct job,min(sal) from emp group by job having min(sal)>2500;
(8) 查询在SALES部门工作的员工的姓名信息
select ename from emp where deptno = (select deptno from dept where dname =‘SALES’);
(9) 查询工资高于公司平均工资的所有员工信息
select * from emp where sal > (select avg(sal) from emp);
(10) 查询与SMITH从事相同工作的所有员工信息
select * from emp where job = (select job from emp where ename = ‘SMITH’);
(11) 列出工资等于30号部门中某个员工工资的所有员工的姓名和工资
select ename,sal from emp where sal in (select sal from emp where deptno = 30);
或者
select ename,sal from emp where sal = any(select sal from emp where deptno = 30);
(12) 查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资
select ename,sal from emp where sal > all(select sal from emp where deptno = 30);
或者
select ename,sal from emp where sal > (select max(sal) from emp where deptno = 30);