1.19 多表查询作业

create table dept (dept1 int(10) PRIMARY key,dept_name VARCHAR(20));
desc dept;
SELECT * from dept;
INSERT INTO dept VALUES(101,'财务');
INSERT into dept VALUES(102,'销售');
INSERT into dept VALUES(103,'IT技术');
INSERT into dept VALUES(104,'行政');

create table emp(sid int(10) PRIMARY key,name VARCHAR(20),age int(20),worktime_start date,incoming int(20),dept2 int(20));
desc emp;
SELECT * from emp;
INSERT INTO emp VALUES(1789,'张三',35,"1980-01-01",4000,101);
INSERT into emp VALUES(1674,'李四',32,"1983-04-01",3500,101);
INSERT into emp VALUES(1776,'王五',24,"1990-07-01",2000,101);
INSERT into emp VALUES(1568,'赵六',57,"1970-10-11",7500,102);
INSERT into emp VALUES(1564,'荣七',64,"1963-10-11",8500,102);
INSERT into emp VALUES(1879,'牛八',55,"1971-10-20",7300,103);


-- 1.找出销售部门中年纪最大的员工的姓名
select name from dept,emp where emp.dept2=dept.dept1 and age=(select max(age) from dept,emp where emp.dept2=dept.dept1 and dept_name="销售") and dept_name="销售";

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

-- 3.列出每个部门收入总和高于9000的部门名称
select dept_name from dept right join emp on emp.dept2=dept.dept1 group by dept_name having sum(incoming)>9000;
select t.dept_name from (select * from dept right join emp on emp.dept2=dept.dept1) as t group by t.dept_name having sum(t.incoming)>9000;
select t.dept_name from (select * from emp left join dept on emp.dept2=dept.dept1) as t group by t.dept_name having sum(t.incoming)>9000;

-- 4.求工资在7500到8500元之间，年龄最大的人的姓名及部门名称
select name,dept_name from emp left join dept on emp.dept2=dept.dept1 where age=(select max(age) from emp where incoming between 7000 and 8500)and incoming between 7000 and 8500;

-- 5.找出销售部门收入最低的员工入职时间
select worktime_start from dept,emp where emp.dept2=dept.dept1 and incoming=(select min(incoming) from dept,emp where emp.dept2=dept.dept1 and dept_name="销售") and dept_name="销售";


-- 6.财务部门收入超过2000元的员工姓名
select name from dept right join emp on emp.dept2=dept.dept1 where incoming>2000 and dept_name="财务";
select t.name from (select * from dept right join emp on emp.dept2=dept.dept1) as t where t.incoming>2000 and t.dept_name="财务";

-- 7.列出每个部门的平均收入及部门名称
select dept_name,avg(incoming) from emp right join dept on emp.dept2=dept.dept1 group by dept_name;
select t.dept_name,avg(t.incoming) from (select * from dept right join emp on emp.dept2=dept.dept1) as t group by t.dept_name;

-- 8.IT技术部入职员工的员工号
select sid  from dept right join emp on emp.dept2=dept.dept1 where dept_name="IT技术";
select sid from emp where dept2=(select dept1 from dept where dept_name="IT技术");
select t.sid from (select * from dept right join emp on emp.dept2=dept.dept1) as t where t.dept_name="IT技术";

-- 9.财务部门的收入总和；
select sum(incoming) from dept right join emp on emp.dept2=dept.dept1 where dept_name="财务";
select sum(incoming) from (select * from dept right join emp on emp.dept2=dept.dept1) as t where t.dept_name="财务";

-- 10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
select * from dept left  join emp on dept.dept1=emp.dept2 order by dept1 desc,worktime_start asc;

-- 11.找出哪个部门还没有员工入职；
select dept_name from dept where dept1 not in (select dept2 from emp);

-- 12.列出部门员工收入大于7000的部门编号，部门名称；
select dept1,dept_name from dept where dept1 in(select dept2 from emp where incoming>7000);


-- 13.列出每一个部门的员工总收入及部门名称；
select sum(incoming),dept_name from dept left join emp on emp.dept2=dept.dept1 group by dept_name;
select sum(incoming),t.dept_name from(select * from dept left join emp on emp.dept2=dept.dept1) as t group by dept_name;

-- 14.列出每一个部门中年纪最大的员工姓名，部门名称；
select name,dept_name from dept left join emp on dept.dept1=emp.dept2 where (dept_name,age)in (select dept_name,max(age) from dept left join emp on dept.dept1=emp.dept2 group by dept_name);

-- 15.求李四的收入及部门名称
select incoming,dept_name from dept left join emp on emp.dept2=dept.dept1 where name="李四";
select t.incoming,t.dept_name from (select * from dept left join emp on emp.dept2=dept.dept1) as t where t.name="李四";       


-- 16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
select name,dept_name,incoming from dept left join emp on emp.dept2=dept.dept1 where (dept_name,incoming) in (select dept_name,max(incoming) from dept left join emp on dept.dept1=emp.dept2 group by dept_name) order by incoming desc;

-- 17.列出部门员工数大于1个的部门名称
select dept_name from dept left join emp on emp.dept2=dept.dept1 group by dept_name having count(*)>1;
select t.dept_name from (select * from dept left join emp on emp.dept2=dept.dept1) as t group by t.dept_name having count(*)>1;

-- 18.求入职于20世纪70年代的员工所属部门名称
select dept_name from dept left join emp on emp.dept2=dept.dept1 where worktime_start like "197%";
select dept_name from dept left join emp on emp.dept2=dept.dept1 where worktime_start>="1970-1-1"and worktime_start<"1980-1-1";

-- 19.查找张三所在的部门名称
select dept_name from dept where dept1=(select dept2 from emp where name="张三");
select dept_name from dept left join emp on emp.dept2=dept.dept1 where name="张三";
select t.dept_name from (select * from dept left join emp on emp.dept2=dept.dept1) as t where t.name="张三";