建议：将复杂的查询分解成简单的查询，然后逐步的完成，达到最终结果。
前期准备：
--新建部门表。（包含部门编号、部门名称、部门所在的城市）
create table dept(
deptno int(4) primary key,
dname varchar(20),
loc varchar(20)
);
--给部门插入数据
insert into dept values
(10, 'Accounting', 'New York'),
(20, 'Research', 'Dallas'),
(30, 'Sales', 'Chicago'),
(40, 'Operations', 'Boston');
--创建雇员emp表（包含雇员编号、雇员姓名、职位、雇员领导、入职日期、薪资、津贴、部门编，部门编号外连接到部门表）
create table emp(
empno int(4) primary key,
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int(2),
constraint fk_deptno foreign key(deptno) references dept(deptno));
--给雇员表插入数据
insert into emp values
(7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20),
(7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20),
(7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30),
(7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10),
(7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20),
(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),
(7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20),
(7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30),
(7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20),
(7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);
1.列出至少有四位员工的所有部门信息
(1).查询每个部门的人数
select deptno,count(*) from emp group by deptno
(2).至少有四位
select deptno,count(*) from emp group by deptno having count(*)>4
(3).部门信息
select d.*,dd.cou
from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>4)dd
where d.deptno=dd.deptno
2.列出薪金比“SMITH”多的员工信息
(1).查询“SMITH”的薪金
select sal from emp where ename='smith'
(2).查询比子查询结果工资高的雇员信息
select * from emp where sal>(select sal from emp where ename='smith')
3.列出所有员工的姓名及其直接上级的姓名。没有领导的雇员也列出
select e.ename,m.ename
from emp e left join emp m
on e.mgr=m.empno
4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称
(1).受雇日期早于其直接上级的所有员工的编号、姓名
select e.empno,e.ename
from emp e join emp m on e.mgr=m.empno
where e.hiredate<m.hiredate
(2).连接部门表
select e.empno,e.ename,d.dname
from emp e join emp m on e.mgr=m.empno
join dept d on e.deptno=d.deptno
where e.hiredate<m.hiredate
5.列出所有“CLERK”（办事员）的姓名及其部门名称、部门人数
(1).所有“CLERK”（办事员）的姓名
select ename from emp where job='clerk'
(2).部门人数
select count(*),deptno from emp group by deptno
(3).select d.dname,dd.cou,e.ename
from dept d join (select count(*) cou,deptno from emp group by deptno)dd on d.deptno=dd.deptno
join emp e on d.deptno=e.deptno
where job='clerk'
6.列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数
(1).最低薪金大于1500的各种工作
select job from emp group by job having min(sal)>1500
(2).每个工作的雇员人数
select count(*) from emp group by job
(3). select count(*),job from emp
where job in(select job from emp group by job having min(sal)>1500)
group by job
7.列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
(1).部门“SALES”（销售部）的部门号
select deptno from dept where dname='sales'
(2).查询子查询结果的雇员姓名
select ename
from emp
where deptno=(select deptno from dept where dname='sales')
8.列出薪金高于公司平均薪金的所有员工，所在部门、上级领导
(1).薪金高于公司平均薪金的所有员工
select e.*
from emp e
where sal>(select avg(sal) from emp)
(2).连接部门表
select e.*,d.*
from emp e join dept d on e.deptno=d.deptno
where sal>(select avg(sal) from emp)
(3).上级领导连接emp
select e.*,d.*,m.*
from emp e join dept d on e.deptno=d.deptno
join emp m on e.mgr=m.empno
where e.sal>(select avg(sal) from emp)
9.列出与“SCOTT”从事相同工作的所有员工及部门名称
(1).“SCOTT”从事工作
select job from emp where ename='scott'
(2).从事子查询结果的所有员工
select e.*
from emp e
where job=(select job from emp where ename='scott') and e.ename!='scott'
(3).连接部门表查询部门名称
select e.*,d.dname
from emp e join dept d on e.deptno=d.deptno
where job=(select job from emp where ename='scott') and e.ename!='scott'
10.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门
(1).部门30的薪资
select sal from emp where deptno=30
(2).高于部门30工作的所有员工的薪金
select e.ename,e.sal
from emp e
where e.sal > all(select sal from emp where deptno=30)
(3).部门连接部门表
select e.ename,e.sal,d.dname
from emp e join dept d on e.deptno=d.deptno
where e.sal > all(select sal from emp where deptno=30)
11.列出在每个部门工作的员工数量、部门名称、平均工资和平均服务期限,
(1)平均服务期限--服务条数datediff
select datediff(sysdate(),hiredate)/365 from emp
(2).部门表和雇员表连表查询
select avg(sal),d.dname,count(*),avg(datediff(sysdate(),hiredate)/365)
from dept d join emp e on d.deptno=e.deptno
group by d.dname
12.列出所有员工的年工资，按年薪从低到高排序
(1).员工的年工资
select (sal+ifnull(comm,0))*12 yearsal from emp;
(2).年薪从低到高排序
select (sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;
13.求出部门名称中，带‘s’字符的部门员工的工资合计、部门人数
(1). 部门名称中，带‘s’字符的部门的部门号
select deptno from dept where dname like '%s%'
(2).每个工资合计、部门人数
select sum(sal),count(*) from emp group by deptno
(3). 用where：
select sum(sal),count(*),deptno from emp
where deptno in(select deptno from dept where dname like '%s%') group by deptno
用 having：
select sum(sal),count(*) from emp
group by deptno having deptno in(select deptno from dept where dname like '%s%')
