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;