8.9 多表连接

8.9 多表连接解题

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 * from  dept,emp where dept.dept1=emp.dept2;

#求销售部门中年龄最大
SELECT max(emp.age) from  dept,emp where dept.dept1=emp.dept2 and dept.dept_name="销售";

SELECT emp.`name` from dept,emp where dept.dept1=emp.dept2 and emp.age=(SELECT max(emp.age) from  dept,emp where dept.dept1=emp.dept2 and dept.dept_name="销售")
and dept.dept_name='销售';

-- 2.求财务部门最低工资的员工姓名
select * from dept,emp where emp.dept2=dept.dept1;
#求出财务部门最低工资
select min(emp.incoming) from dept,emp where emp.dept2=dept.dept1
and dept.dept_name='财务';
#第二步
select emp.name from dept,emp where emp.dept2=dept.dept1 and emp.incoming=(select min(emp.incoming) from dept,emp where emp.dept2=dept.dept1
and dept.dept_name='财务') AND dept.dept_name='财务';


-- 3.列出每个部门收入总和高于9000的部门名称
select dept.dept_name from dept,emp where emp.dept2=dept.dept1 group by dept_name HAVING sum(incoming)>9000;

-- 4.求工资在7500到8500元之间，年龄最大的人的姓名及部门

#求出工资在7500-8500之间年龄最大
select max(age) from dept,emp where emp.dept2=dept.dept1 and incoming BETWEEN 7500 and 8500;
#第二步
select emp.name,dept.dept_name from dept,emp where emp.dept2=dept.dept1 and emp.age=(select max(age) from dept,emp where emp.dept2=dept.dept1 and incoming BETWEEN 7500 and 8500) and emp.incoming BETWEEN 7500 and 8500;


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

#第二步
select emp.worktime_start from dept,emp where emp.dept2=dept.dept1 and emp.incoming=(select min(incoming) from dept,emp where emp.dept2=dept.dept1
and dept.dept_name='销售')and dept.dept_name='销售';

-- 6.财务部门收入超过2000元的员工姓名
select emp.name from dept,emp where emp.dept2=dept.dept1 and dept_name='财务' and incoming>2000;

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

-- 8.IT技术部入职员工的员工号
select emp.sid from dept,emp where emp.dept2=dept.dept1 and dept.dept_name='IT技术'；

-- 9.财务部门的收入总和；
select sum(incoming) from dept,emp where emp.dept2=dept.dept1 and dept.dept_name='财务'；


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

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


-- 12.列出部门员工收入大于7000的部门编号，部门名称；
SELECT dept2 from  emp where incoming>7000;

select dept_name from dept where dept1 in(SELECT dept2 from  emp where incoming>7000);

-- 13.列出每一个部门的员工总收入及部门名称；
select dept.dept_name,sum(emp.incoming) from dept LEFT JOIN emp on dept.dept1=emp.dept2 GROUP BY dept.dept_name;

-- 14.列出每一个部门中年纪最大的员工姓名，部门名称；
#先求出每个部门最大年龄
select dept.dept_name,max(emp.age) from dept LEFT JOIN emp on dept.dept1=emp.dept2 group by dept_name;
#第二步
select emp.name,dept.dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 where (dept.dept_name,emp.age)in(select dept.dept_name,max(emp.age) from dept LEFT JOIN emp on dept.dept1=emp.dept2 group by dept_name)；

-- 15.求李四的收入及部门名称
select dept.dept_name,emp.incoming from dept,emp where dept.dept1=emp.dept2 and emp.name='李四';

-- 16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序

#每个部门最高收入
select dept.dept_name,max(incoming) from dept,emp where dept.dept1=emp.dept2 group by dept.dept_name;
#第二步
select dept.dept_name,emp.name,emp.incoming from dept,emp where dept.dept1=emp.dept2 and (dept.dept_name,emp.incoming)in(select dept.dept_name,max(incoming) from dept,emp where dept.dept1=emp.dept2 group by dept.dept_name)order by emp.incoming desc;


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


-- 18.求入职于20世纪70年代的员工所属部门名称
select dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.worktime_start BETWEEN'1970-01-01'and '1979-12-31';

select dept.dept_name from dept,emp where dept.dept1=emp.dept2 and emp.worktime_start like "197%";

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

select dept_name from dept where dept1=(select dept2 from emp where name='张三');