1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
/*分析:
需查询的列:dept.*,部门人数
在哪几个表里查:dept d,emp e
条件:d.deptno=e,deptno */select d.*,z1.cnt
from dept d,(select deptno,count(*) cnt from emp groupby deptno)z1
where d.deptno=z1.deptno;
2. 列出薪金比关羽高的所有员工。
select *
from emp
where sal>(select sal from emp where ename='关羽');
3. 列出所有员工的姓名及其直接上级的姓名。
select e.*,IFNULL(m.ename,'BOSS') leader
from emp e
leftjoin emp m on e.mgr=m.empno;
4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
/*列:e.empno,e.ename,d.dname
表:emp e,emp m(直接上级表),dept d
条件:e.hiredate<m.hiredate
select e.empno,e.ename,e.deptno,e.hiredate,m.ename,m.hiredate
from emp e,emp m
where e.mgr=m.empno and e.hiredate<m.hiredate; */select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
select *
from emp e rightouterjoin dept d
on e.deptno=d.deptno;
6. 列出所有文员的姓名及其部门名称,部门的人数。
select p.ename,d.dname,z1.cnt
from (select ename,deptno from emp where job='文员')p
leftjoin dept d
on p.deptno=d.deptno
leftjoin (select deptno,count(*) cnt from emp groupby deptno)z1
on d.deptno=z1.deptno;
7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。(单表也可)
select job,count(*)
from emp
groupby job
havingmin(sal)>15000;select e.job,m.cnt
from (select job,count(*),min(sal) minsal from emp groupby job)e
leftjoin(select job,count(*) cnt from emp groupby job)m
on e.job=m.job
where e.minsal>15000;
8. 列出在销售部工作的员工的姓名,假定不知道销售部的部门编号。
select emp.ename,m.dname
from emp
leftjoin (select dname,deptno from dept) m on emp.deptno=m.deptno
where m.dname='销售部';select e.ename,m.dname
from emp e,dept m
where e.deptno=m.deptno and m.dname='销售部';
9. 列出薪金高于公司平均薪金的所有员工信息,所在部门名称,上级领导,工资等级。
/*内连接得到的数据不全*/select e.ename,d.dname,m.ename,s.grade
from emp e,dept d,emp m,salgrade s
where e.sal>(selectavg(sal) from emp) and e.deptno=d.deptno and e.mgr=m.empno and e.sal between s.losal and s.hisal;select e.ename,d.dname,m.ename leader,s.grade
from emp e
leftjoin dept d on e.deptno=d.deptno
leftjoin emp m on e.mgr=m.empno
leftjoin salgrade s on e.sal between s.losal and s.hisal
where e.sal>(selectavg(sal) from emp);
10.列出与庞统从事相同工作的所有员工及工资及部门名称。
select e.ename,e.sal,d.dname
from emp e,dept d
where e.job=(select job from emp where ename='庞统') and e.deptno=d.deptno;select e.ename,e.sal,d.dname
from emp e
leftjoin dept d on e.deptno=d.deptno
where e.job=(select job from emp where ename='庞统');
11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
select e.ename,e.sal,d.dname
from emp e,dept d
where e.sal>all(select sal from emp where deptno=30) and e.deptno=d.deptno
orderby e.sal;select e.ename,e.sal,d.dname
from emp e
leftjoin dept d on e.deptno=d.deptno
where e.sal>all(select sal from emp where deptno=30);
12.列出在每个部门工作的员工数量、平均工资。
/*平均工资也是在分组后的每组中进行*/select d.deptno,d.dname,m.cnt,m.asal
from dept d
leftjoin (select deptno,count(*)cnt,avg(sal)asal from emp groupby deptno)m on d.deptno = m.deptno;