1.列出每个部门的平均收入及部门名称; --每个部门 平均收入和部门名称
select dept.dept_name,avg(emp.incoming) from dept,emp where dept.dept1=emp.dept2 group by dept.dept_name;
2.财务部门的收入总和；
所求的字段只在一张表中可以考虑用嵌套
dept_name = "财务"
1.嵌套方法
select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务");
2.左连接
select sum(emp.incoming) from dept left join emp on dept.dept1=emp.dept2 where dept.dept_name="财务";
3.It技术部入职员工的员工号
第一种方法嵌套：
select dept1 from dept where dept_name="IT技术";
select sid from emp where dept2=(select dept1 from dept where dept_name="IT技术");
第二种方法：
select emp.sid from dept left join emp on dept.dept1=emp.dept2 where dept.dept_name="IT技术";
4.财务部门收入超过2000元的员工姓名
第一种嵌套思路：
select dept1 from dept where dept_name="财务";
select name from emp where dept2= (select dept1 from dept where dept_name="财务") and incoming>2000;
第二种：
select emp.`name` from dept left join emp on dept.dept1=emp.dept2 where dept.dept_name="财务" and emp.incoming>2000;
5.找出销售部收入最低的员工的入职时间;
第一种方法：
select min(incoming) from emp where dept2=(select dept1 from dept where dept_name="销售"); # 先把销售部门最低薪资求出来
select worktime_start from emp where dept2=(select dept1 from dept where dept_name="销售") and incoming=(select min(incoming) from emp where dept2=(select dept1 from dept where dept_name="销售"));
# 需要用到两个条件去约束第一部门一定要是销售和销售部门最低薪资
6.找出年龄小于平均年龄的员工的姓名，员工号和部门名称
# 1.先把平均年纪求出来
select avg(age) from emp; # 44.5
# 2.求的数据来自于两表，就先把两表连起来
# 3.加上筛选条件，和需要求的字段
select emp.`name`,emp.sid,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.age<(select avg(age) from emp);
7.列出每个部门收入总和高于9000的部门名称
# 1.把每个部门的收入总和先求出来,然后筛选出来总和大于9000的
select dept2,sum(incoming) from emp group by dept2 having sum(incoming)>9000;
# 2.把已上求出来数据的语句当成是一个临时表与有部门名称的表关联
select * from dept,(select dept2,sum(incoming) from emp group by dept2 having sum(incoming)>9000)t where dept.dept1=t.dept2;
8.查出财务部门工资少于3800元的员工姓名
# 1.用嵌套，通过dept_name="财务"的出dept1从而得到dept2
select dept1 from dept where dept_name="财务";
select name from emp where dept2=(select dept1 from dept where dept_name="财务") and incoming<3800;
9.求财务部门最低工资的员工姓名；
# 1.先把财务部门最低工资求出来
select min(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务"); # 2000
# 2.把薪资等于2000的人的名字求出来
select name from emp where incoming=((select min(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务"))) and dept2=(select dept1 from dept where dept_name="财务");
10.找出销售部门中年纪最大的员工的姓名
# 1.先求出销售部门最大的年纪
select max(age) from emp where dept2=(select dept1 from dept where dept_name="销售"); # 64
# 2.再求出年龄等于64的姓名且这个人是销售部门的
select name from emp where dept2=(select dept1 from dept where dept_name="销售") and age=(select max(age) from emp where dept2=(select dept1 from dept where dept_name="销售"));
11.求收入最低的员工姓名及所属部门名称
# 1.把收入最低的工资是多少求出来
select min(incoming) from emp; # 2000
# 2.找到工资为2000的人和这个人所在的部门
select emp.`name`,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.incoming=(select min(incoming) from emp);
12.求员工收入小于4000元的员工部门编号及其部门名称
# 1.把收入小于4000的员工编号筛选出来
select dept2 from emp where incoming<4000;
# 2.把上面求出来的语句当成是临时表，然后与dept表关联，得到dept_name
select t.dept2,dept.dept_name from (select dept2 from emp where incoming<4000)t,dept where t.dept2=dept.dept1;
13.列出每个部门收入最高的员工姓名，部门名称，收入，并按照收入降序；
# 1.求出每个部门收入最高的工资
select dept2,max(incoming) from emp group by dept2;
# 2.所求的字段来自于2个表，先把两个表关联
select * from dept,emp where dept.dept1=emp.dept2;
select k.`name`,k.dept_name,k.incoming from (select dept2,max(incoming) m from emp group by dept2)t,(select * from dept,emp where dept.dept1=emp.dept2)k where t.dept2=k.dept1 and t.m=k.incoming order by k.incoming desc;
14.查询财务部低于平均收入的员工号与员工姓名：
# 1.把所有人的平均薪资求出来
select avg(incoming) from emp; # 5466.6667
# 2.把财务部门的收入低于5466.6667的求出来
select sid,name from emp where dept2=(select dept1 from dept where dept_name="财务") and incoming<(select avg(incoming) from emp);
15.列出部门员工数大于1个的部门名称；
# 1.先把每个部门的员工数,分组后过滤
select dept2,count(*) from emp group by dept2 having count(*)>1;
# 2.把dept表与上面的临时表关联
select dept.dept_name from dept,(select dept2,count(*) from emp group by dept2 having count(*)>1)t where dept.dept1=t.dept2;
16.列出部门员工收入不超过7500，且大于3000的员工年纪及部门名称；
# 1.先连接两表
select dept.dept_name,emp.age from dept,emp where dept.dept1=emp.dept2 and emp.incoming>3000 and emp.incoming<=7500;
17.求入职于20世纪70年代的员工所属部门名称；
# 1.先连接两表
select dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.worktime_start like "197%";
18.查找张三所在的部门名称；
# 1.先求出张三所在部门的部门编号
select dept2 from emp where name="张三";
# 2.通过部门张三所在的部门编号，求出张三所在的部门名称
select dept_name from dept where dept1=(select dept2 from emp where name="张三");
19.列出每一个部门中年纪最大的员工姓名，部门名称；
# 1.把每个部门中年纪最大的年纪求出
select max(age) m from emp group by dept2;
select * from dept,emp where dept.dept1=emp.dept2;
select k.`name`,k.dept_name from (select max(age) m,dept2 from emp group by dept2)t,(select * from dept,emp where dept.dept1=emp.dept2)k where t.dept2=k.dept1 and t.m=k.age;
20.列出每一个部门的员工总收入及部门名称；
# 1.先把两表关联
select sum(emp.incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept.dept_name;
21.列出部门员工收入大于7000的员工号，部门名称；
# 1.先筛选出员工的薪资大于7000的
select * from emp where incoming>7000;
# 2.找到有部门名称的表dept进行关联
select t.sid,dept.dept_name from (select * from emp where incoming>7000)t,dept where t.dept2=dept.dept1;
22.找出哪个部门还没有员工入职；
1.关联表
select dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.sid is null;
23.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
select * from dept left join emp on dept.dept1=emp.dept2 order by dept.dept1 desc,emp.worktime_start asc;
24.求出财务部门工资最高员工的姓名和员工号
# 1.求出财务部门最高工资
select max(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务");
# 2.员工信息与员工号都在emp
select name,sid from emp where incoming=(select max(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务")) and dept2=(select dept1 from dept where dept_name="财务");
25.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称
# 1.先根据工资在7500到8500之间筛选
select max(age) from emp where incoming between 7500 and 8500;
# 2.找出上表中年纪最大的人的姓名和部门名称
select emp.`name`,dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.age=(select max(age) from emp where incoming between 7500 and 8500);
26.查出大于自己部门平均工资的员工编号及部门名称
# 1.求出每个部门的平均薪资
select dept2,avg(incoming) from emp group by dept2;
# 2.把上面的语句查询出来的数据当成是一个临时表，与emp表进行关联，才能把自己的薪资与自己部门的平均薪资做比较
select emp.sid es,emp.dept2 ed from (select dept2,avg(incoming)a from emp group by dept2)t,emp where t.dept2=emp.dept2 and emp.incoming>t.a;
# 3.把以上的语句查询出来的数据当成是一个临时表与dept表进行关联从而得到dept_name
select k.es,dept.dept_name from dept,(select emp.sid es,emp.dept2 ed from (select dept2,avg(incoming)a from emp group by dept2)t,emp where t.dept2=emp.dept2 and emp.incoming>t.a)k where dept.dept1=k.ed
# 第二种方法
# 1.求出每个部门的平均薪资
select dept2,avg(incoming) from emp group by dept2;
# 2.先关联2个表
select emp.sid,dept.dept_name from dept,emp,(select dept2,avg(incoming)a from emp group by dept2)t where dept.dept1=emp.dept2 and t.dept2=dept.dept1 and emp.incoming>t.a;
# 第三种方法
表很多的情况就使用左连接left join
# 1.求出每个部门的平均薪资
select dept2,avg(incoming) from emp group by dept2;
# 2.先关联两个表
select emp.sid,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 left join (select dept2,avg(incoming)a from emp group by dept2)t on dept.dept1=t.dept2 where emp.incoming>t.a;
|
|
239
0
多测师社区 
