数据库多表查询

dept；emp

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 dept.dept_name,t.a from dept join (select dept2,avg(incoming) a from emp group by dept2)t on dept.dept1=t.dept2;

2.财务部门的收入总和；

select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务');

3.It技术部入职员工的员工号;

select sid from emp where dept2=(select dept1 from dept where dept_name='IT技术');

4.财务部门收入超过2000元的员工姓名;

select name from emp where dept2=(select dept1 from dept where dept_name='财务') and incoming>2000;

5.找出销售部收入最低的员工的入职时间；

select emp.worktime_start from(select dept2,min(incoming) m from emp,dept  where dept.dept1=emp.dept2 and dept_name='销售')t , emp where t.m=emp.incoming and emp.dept2=t.dept2 ;

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称;

select name,sid,dept_name from dept,emp where dept.dept1=emp.dept2 and age<(select avg(age) from emp);

7.列出每个部门收入总和高于9000的部门名称;

①select dept_name from(select dept2,sum(incoming) from emp group by dept2 having sum(incoming)>9000) t,dept where dept.dept1=t.dept2;

②select dept_name from dept where dept1 in(select dept2 from emp group by dept2 having sum(incoming)>9000);

8.查出财务部门工资少于3800元的员工姓名;

select name from emp where dept2=(select dept1 from dept where dept_name='财务') and incoming<3800;

9.求财务部门最低工资的员工姓名;

select name from(select dept2,min(incoming) m from emp,dept where emp.dept2=dept.dept1 and dept_name='财务') t ,emp where t.dept2=emp.dept2 and t.m=emp.incoming;

10.找出销售部门中年纪最大的员工的姓名;

select name from(select dept2,max(age) m from emp,dept where emp.dept2=dept.dept1 and dept_name='销售') t ,emp where emp.dept2=t.dept2 and emp.age=t.m;

11.求收入最低的员工姓名及所属部门名称;

select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming=(select min(incoming) from emp);

12.求李四的收入及部门名称;

select incoming,dept_name from dept,emp where dept.dept1=emp.dept2 and name='李四';

13.求员工收入小于4000元的员工部门编号及其部门名称;

select distinct dept1 ,dept_name  from dept,emp where dept.dept1=emp.dept2 and incoming<4000;

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

select name,emp1.dept_name,incoming from(select dept2 d,max(incoming) m,dept_name from emp,dept where emp.dept2=dept.dept1 group by dept2)emp1,emp where emp1.d=emp.dept2 and emp.incoming=emp1.m order by emp.incoming desc;

15.求出财务部门收益最高的俩位员工的姓名，工号，收益

select name,sid,incoming from emp where dept2=(select dept1 from dept where dept_name='财务') order by incoming asc limit 0,2;

16.查询财务部低于平均收入的员工号与员工姓名

select sid,name from emp where dept2=(select dept1 from dept where dept_name='财务') and incoming<(select avg(incoming) from emp);

17.列出部门员工数大于1个的部门名称；

select dept.dept_name from(select dept2 from emp group by dept2 having count(sid)>1)t ,dept where dept.dept1=t.dept2;

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号;

select age,dept2 from emp where incoming>3000 and (incoming<7500 or incoming=7500);

19.求入职于20世纪70年代的员工所属部门名称;

select dept_name from dept,emp where dept.dept1=emp.dept2 and year(worktime_start) between 1970 and 1979;

20.查找张三所在的部门名称;

select dept_name from dept,emp where dept.dept1=emp.dept2 and name='张三';

21.列出每一个部门中年纪最大的员工姓名，部门名称；

select name,dept_name from(select dept2 d,dept_name,max(age)m from dept,emp where dept.dept1=emp.dept2 group by dept2)t ,emp where t.d=emp.dept2 and age=t.m;

22.列出每一个部门的员工总收入及部门名称;

select sum(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept2;

23.列出部门员工收入大于7000的员工号，部门名称;

select sid,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming>7000;

24.找出哪个部门还没有员工入职；

select dept_name from dept where dept1 not in(select dept2 from emp);

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表；

select * from emp order by dept2 desc,worktime_start asc;

26.求出财务部门工资最高员工的姓名和员工号

select name,sid from(select dept2 d,max(incoming)m from emp,dept where dept.dept1=emp.dept2 and dept_name='财务') t ,emp where t.d=emp.dept2 and emp.incoming=t.m;

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称

select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500 and age=(select max(age) from emp where incoming between 7500 and 8500 );