多表练习

#1.列出每个部门的平均收入及部门名称;    --每个部门  平均收入和部门名称
select * from dept,emp where dept.dept1=emp.dept2;
select dept.dept_name,avg(emp.incoming) from dept,emp where dept.dept1=emp.dept2 group by dept.dept_name;
select t.dept_name,avg(t.incoming) from (select * from dept inner join emp on dept.dept1=emp.dept2)t group by t.dept_name;
2#.财务部门的收入总和；
嵌套方法
select dept1 from dept where dept_name="财务";
select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务");
select sum(incoming) from dept left join emp on dept.dept1=emp.dept2 and 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,emp where dept.dept1=emp.dept2 and 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 emp.incoming>2000 and dept_name="财务";
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.找出年龄小于平均年龄的员工的姓名，员工号和部门名称
select avg(age) from emp;   
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的部门名称
select dept2,sum(incoming) from emp group by dept2 having sum(incoming)>9000;
select dept.dept_name from dept,(select dept2,sum(incoming) from emp group by dept2 having sum(incoming)>9000)t where dept.dept1=t.dept2;
8.#查出财务部门工资少于3800元的员工姓名
select * from dept left join emp on dept.dept1=emp.dept2;
select emp.name from dept,emp where dept_name="财务" and incoming<3800;

#9.求财务部门最低工资的员工姓名；
select min(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务");
select name from emp where incoming=(select min(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务"));

#10.找出销售部门中年纪最大的员工的姓名
select max(age) from emp where dept2=(select dept1 from dept where dept_name="销售");
select name from emp where age=(select max(age) from emp where dept2=(select dept1 from dept where dept_name="销售"));
#11.求收入最低的员工姓名及所属部门名称
select min(incoming) from emp;

select emp.name,dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.incoming=(select min(incoming) from emp);
#12.求员工收入小于4000元的员工部门编号及其部门名称
select emp.dept2,dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.incoming<4000;
#13.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；  
select dept2,max(incoming) from emp group by dept2;
select * from dept left join emp on dept.dept1=emp.dept2;
select k.name,k.dept_name,k.incoming from (select dept2,max(incoming)s from emp group by dept2)t,(select * from dept left join emp on dept.dept1=emp.dept2)k where t.dept2=k.dept1 and t.s=k.incoming order by k.incoming desc;




# 14.查询财务部低于平均收入的员工号与员工姓名：
  select avg(incoming) from emp;
        select dept1 from dept where dept_name="财务";
        select emp.sid,name from emp where emp.dept2=(select dept1 from dept where dept_name="财务") and emp.incoming<(select avg(incoming) from emp);

# 15.列出部门员工数大于1个的部门名称；
select dept_name from dept,(select dept2,count(*) from emp group by dept2 having count(*)>1)t where dept.dept1=t.dept2;
select dept2,count(*) from emp group by dept2 having count(*)>1;

# 16.列出部门员工收入不超过7500，且大于3000的员工年纪及部门名称；
select emp.age,dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.incoming in (3000,7500);
#17.求入职于20世纪70年代的员工所属部门名称；
select dept.dept_name from dept,emp where dept.dept1=emp.dept2 and worktime_start like "197%";

#18.查找张三所在的部门名称；
select dept_name from dept,emp where dept.dept1=emp.dept2 and emp.name="张三";

######19.列出每一个部门中年纪最大的员工姓名，部门名称；
select * from dept,emp where dept.dept1=emp.dept2;  
select dept2,max(age) from emp group by dept2;
select t.name,t.dept_name from (select * from dept,emp where dept.dept1=emp.dept2)t,(select dept2,max(age)a from emp group by dept2)k where t.dept1=k.dept2 and k.a=t.age;



#20.列出每一个部门的员工总收入及部门名称；
select dept2,sum(incoming) from emp group by dept2;
select dept_name,t.a from dept,(select dept2,sum(incoming)a from emp group by dept2)t where dept.dept1=t.dept2;
select dept.dept_name,t.s from dept,(select dept2,sum(incoming)s from emp group by dept2)t where dept.dept1=t.dept2;


#21.列出部门员工收入大于7000的员工号，部门名称；
select emp.sid,dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.incoming>7000;
select t.sid,t.dept_name from (select * from dept inner join emp on dept.dept1=emp.dept2)t where t.incoming>7000;
*#22.找出哪个部门还没有员工入职；

select dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.sid is null;
#24.求出财务部门工资最高员工的姓名和员工号
select max(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务");
select name,sid from emp where incoming=(select max(incoming) from emp where dept2=(select dept1 from dept where dept_name="财务"));
select t.name,t.sid from (select * from dept left join emp on dept.dept1=emp.dept2)t where t.dept_name="财务" having max(t.incoming);
#25.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称
select max(age) from emp
where incoming between 7500 and 8500
select emp.name,dept.dept_name from dept left join emp on dept.dept1=emp.dept2 where emp.age=(select max(age) from emp) and emp.incoming between 7500 and 8500
#26.查出大于自己部门平均工资的员工编号及部门名称
select dept2,avg(incoming) from emp group by dept2;
select dept2,incoming from emp
select * from  dept left join emp on dept.dept1=emp.dept2;
select emp.sid,dept.dept_name from emp,dept,(select dept2,avg(incoming)a from emp group by dept2)t where dept.dept1=emp.dept2 and dept.dept1=t.dept2 and emp.incoming>t.a;