mysql查询之项目高级查询练习下

所用到的表见上章：mysql查询之项目高级查询练习上

题目及答案：

1.列出最低薪金大于15000的各种工作及从事此工作的员工人数。
根据题找信息
employee 

select job,count(*) from employee group by job having min(sal)>15000;

2.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
根据题找信息
employee dept
select deptnu from dept where dname="销售部";

内连接查询
select ename from employee where deptnu=(select deptnu from dept where dname="销售部"); 

3.列出与诸葛亮从事相同工作的所有员工及部门名称。

根据题找信息
employee dept 
select job from employee where ename='诸葛亮'

左连接查询
select a.ename,b.dname from employee a left join dept b on a.deptnu=b.deptnu where job=(select job from employee where ename='诸葛亮' );

内连接查询
select  a.ename,b.dname from employee a,dept b where a.job=(select job from employee where ename='诸葛亮') and b.deptnu=a.deptnu;

4.列出薪金比 在部门30工作的员工的薪金 还高的员工姓名和薪金、部门名称。
根据题找信息
employee dept
select max(sal) from employee where deptnu='30';

内连接查询
select a.ename,a.sal,b.dname from employee a,dept b where a.sal>(select max(sal) from employee where deptnu='30') and a.deptnu=b.deptnu;


5.列出每个部门的员工数量、平均工资。
根据题找信息
employee 
select deptnu,avg(sal) as sals ,count(*) as person from employee group by deptnu;

左连接查询
select a.dname,b.sals,b.person from dept a left join (select deptnu,avg(sal) as sals ,count(*) as person from employee group by deptnu) b on a.deptnu=b.deptnu;

6.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
根据题找信息
emploee dept

select avg(sal) from employee; 

内连接查询
select a.*,c.dname,b.ename,d.grade from employee a,employee b,dept c,salgrade d where a.sal>(select avg(sal) from employee) and a.deptnu=c.deptnu and a.mgr=b.empno and a.sal>=lowsal and a.sal<=higsal;