oracle习题集-高级查询

1.问题：查询每个员工的部门名称，列出员工姓名和部门名称

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno

2. 问题：查询员工表中，每个员工的工资等级，列出员工姓名，工资和工资等级

select e.ename,e.sal,s.grade from emp e,salgrade s 
where e.sal between s.losal and s.hisal; 

3. 查询所有比自己领导入职早的员工的姓名和上级领导的姓名

select w.ename as 员工姓名,m.ename as 上级领导姓名
from emp w,emp m where w.mgr=m.empno and w.hiredate<m.hiredate;

4.问题：查询每个员工的部门名称，列出员工姓名和部门名称

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

5.问题：查询部门10中每个员工的工资等级

select e.ename,s.grade from emp e 
inner join salgrade s 
on e.sal between s.losal and s.hisal --连接条件
where e.deptno=10;                   --查询条件

6.问题：查询emp表中部门名称为ACCOUNTING的员工的姓名，和部门位置

1
select e.ename,d.loc from emp e,dept d 
where e.deptno=d.deptno and d.dname='ACCOUNTING';

2
select e.ename,d.loc from emp e inner join dept d
on e.deptno=d.deptno
where d.dname='ACCOUNTING';

3
select e.empno,e.ename from emp e 
where deptno=(select deptno from dept where dept.dname = 'ACCOUNTING')
order by ename ;

7.查询高于自己部门平均工资的员工的信息，列出部门平均工资

--（1）获得每个部门的平均工资
select deptno,avg(sal) avg_sal from emp group by deptno;   --x
--(2) 将x表和emp表做表连接
select e.*,x.* from emp e,x where e.deptno=x.deptno and e.sal>x.avg_sal;
--(3)替换x
select e.*,x.* from 
emp e,(select deptno,avg(sal) avg_sal from emp group by deptno)x 
where e.deptno=x.deptno and e.sal>x.avg_sal;

8.问题：查询没有没有员工的部门信息

select d.* from dept d left join emp e on e.deptno=d.deptno where e.empno is null;

9. 问题：查询和scott工作相同的员工姓名，不包含scott在内

select emp.ename 
from emp 
where job= (select job from emp where ename='SCOTT') and ename <> 'SCOTT'

10.查询blake的上级领导的姓名和工资

select ename ,sal 
from emp
where empno =(select mgr from emp where ename='BLAKE' )

11. 查询薪水高于部门30的最低薪水的员工信息

select * 
from emp 
where sal < (select min(sal) from emp where deptno=30)

12.查询哪些部门最低薪水高于部门30的最低薪水，列出这些部门薪水最低的员工信息

--(1)查询哪些部门最低薪水高于部门30的最低薪水  --x
select deptno,min(sal) from emp 
group by deptno 
having min(sal)>(select min(sal) from emp where deptno=30);
--(2)将emp表与x做表连接
select e.* from emp e inner join x on e.sal=x.min_sal;
--(3)替换：
select e.*,x.* from 
(select deptno,min(sal) min_sal from emp 
group by deptno 
having min(sal)>(select min(sal) from emp where deptno=30))x 
inner join emp e
on e.sal=x.min_sal;

13. 查询和SALESMAN同部门，但是不是SALESMAN的员工的信息

select * from emp where 
deptno in (select distinct deptno from emp where job='SALESMAN') 
and job<>'SALESMAN';

14.查询比任何一个SALESMAN的薪水高但不是SALESMAN的员工信息

select * from emp where sal>any(select sal from emp where job='SALESMAN') and job<>'SALESMAN';

-->any:大于最小的

--<any：小于最大的

15. 查询比所有一个SALESMAN的薪水高但不是SALESMAN的员工信息。

>all:大于最大的

 

<all:小于最小的

 

select * from emp where sal>all(select sal from emp where job='SALESMAN') and job<>'SALESMAN';
select * from emp where sal>(select max(sal) from emp where job='SALESMAN') and job<>'SALESMAN';

16.按照部门名称和工作，来查看emp表每个部门，每种职位每个月的总开支，并且按照总开支进行降序排列

--（1）表连接
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
--(2)分组求和排序
select d.dname,e.job,sum(sal) from emp e,dept d where e.deptno=d.deptno
group by d.dname,e.job
order by sum(sal) desc;

17. 查询和scott相同部门相同职位的员工

select * from emp 
where (deptno,job) = (select deptno,job from emp where ename='SCOTT') 

18. 查询每个部门下面工资最低的员工

select deptno,min(sal) from emp group by deptno;
select * from emp 
where (deptno,sal) in (select deptno,min(sal) from emp group by deptno);


select * from emp e1 
where (deptno,sal)=(select deptno,min(sal) from emp e2 where e1.deptno=e2.deptno group by deptno);

19.列出emp表中，每个部门的员工人数和部门号

select deptno,count(*) from emp group by deptno;

20.列出emp表中，每个部门的员工人数和部门名称

select d.dname,count(*) 
from emp e,dept d 
where e.deptno=d.deptno 
group by d.dname;

21.列出emp表中，部门人数大于3的员工人数和部门编号

select deptno，count(*)
from emp
group by deptno
having count(*)>3;

22.列出emp表中，部门人数大于3的员工人数和部门名称

1
select d.dname,count(*)
from dept d,emp e
where d.deptno=e.deptno
group by d.dname
having count(*)>3;


2
--(1)查询每个部门的员工人数  --x
select deptno,count(*) co from emp group by deptno having count(*)>3;
--(2)表连接
select d.dname,x.co from dept d,x where d.deptno=x.deptno;
--(3）替换
select d.dname,x.co 
from dept d,(select deptno,count(*) co from emp group by deptno having count(*)>3)x 
where d.deptno=x.deptno;

 

--exists(select...from...)

--如果select语句返回的结果集为空，exists(select...from...)结果是false

--如果select语句返回的结果集不为空，exists(select...from...)结果是true

23.查询有员工的部门的部门编号和部门名称

select deptno,dname from dept d where exists(select * from emp e where d.deptno=e.deptno);

24.显示职位是’MANAGER’且薪水大于2500的员工的信息

select * from emp where job='MANAGER'
intersect
select * from emp where sal>2500;

25 显示职位是MANAGER,但是薪水低于2500的员工的信息

select * from emp where job='MANAGER'
minus
select * from emp where sal>=2500;