1.查询emp中最高薪水人的名字
select * from EMP;
select ename from EMP
where sal = (select max(sal) from EMP);
2.查询每个部门中的最高薪水人的名字和所在的部门编号
select n1.ename,n1.deptno
from EMP n1
inner join(select max(sal) max_sal,deptno from EMP group by deptno) n2
on n1.sal = n2.max_sal;
3.查询薪水在平均薪水之上的雇员的名字
select ename
from EMP
where sal >(select avg(sal) from EMP);
4.查询雇员的名字和所在部门的名字
select EMP.ename,DEPT.dname
from EMP,DEPT
where EMP.deptno = DEPT.deptno;
select EMP.ename,DEPT.dname
from EMP
left join DEPT
on EMP.deptno = DEPT.deptno;
5.查询薪水在在本部门平均薪水之上的雇员的名字
select avg(sal) avg_sal,deptno from EMP group by deptno;
select * from EMP order by deptno;
select t1.ename,t1.sal
from EMP t1
inner join(select avg(sal) avg_sal,deptno from EMP group by deptno)t2
on t1.sal > t2.avg_sal and t1.deptno = t2.deptno;
6.查询每个员工的薪水的等级,员工的姓名
select * from EMP;
select grade,ename
from EMP t1
inner join SALGRADE
on t1.sal between losal and hisal
7.查询每个部门的平均薪水的等级,部门的编号
select avg(sal),deptno from EMP group by deptno;
select grade,deptno
from (select avg(sal) avg_sal,deptno from EMP group by deptno) t1
inner join SALGRADE
on t1.avg_sal between losal and hisal
8.查询雇员的名字，所在部门的名字，工资的等级||
select * from EMP;
select ename,dname,grade
from EMP
inner join DEPT,SALGRADE
on (EMP.deptno = DEPT.deptno) and (EMP.sal between losal and hisal)
9.查询雇员的名字和其经理的名字
select * from EMP;
select t1.ename,t1.mgr
from EMP t1,EMP t2
where t2.mgr = t1.empno;
10.查询雇员中是经理人的名字
select * from EMP;
select distinct t1.ename
from EMP t1,EMP t2
where t2.mgr = t1.empno;
11.查询平均薪水最高的部门的编号和名称
select * from EMP;
select avg(sal) from EMP group by deptno;
select deptno,dname
from EMP t1
inner join DEPT t2
on
12.查询平均薪水等级最低的部门的部门名称
select * from EMP;
13.查询部门经理人中平均薪水最低的部门名称
select * from EMP;
14.查询部门经理人中平均薪水最低的部门名称
select * from EMP;
15.查询薪水最高的第6名到第10名雇员编号，名称，薪水
select * from EMP;
select empno,ename,sal
from EMP order by sal desc
limit 5,5
;
16.查询部门的名字和部门的人数(如果部门里没有人数，显示0个)
select * from EMP;
select count(deptno),deptno from EMP group by deptno;
select dname from DEPT;
select dname,ifnull(count_p,0)
from DEPT t1
left join (select count(deptno) count_p,deptno from EMP group by deptno) t2
on t1.deptno = t2.deptno;
17.查询员工的编号，工资和所在部门的平均工资
select * from EMP;
select avg(sal),deptno from EMP group by deptno;
select t1.empno,t1.sal,t2.avg_sal
from EMP t1
inner join (select avg(sal) avg_sal,deptno from EMP group by deptno) t2
on t1.deptno = t2.deptno;
