多表练习题

create table dept  (
dept1 int(10),
dept_name char(20)
) default charset=utf8;
insert into dept values (101,'财务');
insert into dept values (102,'销售');
insert into dept values (103,'IT技术');
insert into dept values (104,'行政');
SELECT * from  dept;

create table emp(
    sid int  primary key, -- 员工编号
    name  varchar(10), -- 员工姓名
    age int, -- 员工年龄
    worktime_start date, -- 入职时间
    incoming double, -- 收入
    dept2 int,  -- 部门号
    foreign key (dept2) references dept(dept2)) default  charset=utf8;  -- 关联dept表  

-- alter table emp add foreign key (deptno) references dept(deptno);
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);
select * from emp;


1.列出每个部门的平均收入及部门名称;
核心词汇：emp  、  incoming    、   dept  dept_name     、avg    、group by、
select  avg(incoming ),dept_name from dept inner join  emp  on  dept.dept1=emp.dept2.




方法一：select  avg(incoming ),dept_name from (select * from dept left join  emp  on  dept.dept1=emp.dept2)c group by dept_name;

方法二：select avg(incoming),dept_name from dept   left  JOIN emp on dept.dept1=emp.dept2   GROUP BY dept_name ;




2.财务部门的收入总和；
dept   "财务"
emp    incoming
sum
select  sum(incoming ),dept_name from dept inner join  emp  on  dept.dept1=emp.dept2  where dept_name="财务" ;

方法一：select sum(incoming) from (select   *  from  dept,emp where   dept.dept1=emp.dept2)c where dept_name='财务';

方法二：select  sum(incoming )   from dept inner join  emp  on  dept.dept1=emp.dept2  where dept_name="财务" ;

方法三：select sum(incoming) from  emp where   dept2=(select  dept1  from  dept  where  dept_name="财务");



3.It技术部入职员工的员工号  
emp     sid
dept   it技术部门

name ，sid

方法一：select emp.sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="IT技术 ";

方法二：select sid from (select * from dept,emp where dept.dept1=emp.dept2)c where dept_name='IT技术';

方法三：select  sid  from  emp  where dept2=(select  dept1  from  dept where dept_name="IT技术") ;

方法四：select  sid from  dept,emp  where   dept1=dept2  and  dept_name="IT技术" ;





4.财务部门收入超过2000元的员工姓名
emp   incoming>2000
dept  财务

方法一：SELECT name,incoming FROM Dept INNER JOIN Emp on Dept1=DEPT2 WHERE incoming>2000 AND Dept_name="财务";

方法二：SELECT emp.name from  emp  where dept2=(select dept1 from dept where dept_name='财务') and incoming>2000;

方法三：select name from (select * from dept,emp where dept.dept1=emp.dept2)c where incoming>2000 and dept_name= '财务';

方法四：select name from (select*from dept inner join emp on dept1=dept2 where incoming>2000)s where dept_name='财务'





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


方法一：select woektime_start FROM emp INNER JOIN dept on dept.dept1=emp.dept2 where  incoming =(select min(incoming) from emp INNER JOIN dept on emp.dept2=dept.dept1 where dept.dept_name="销售")  and   dept_name="销售";

方二：（这种方法可以，弊端在与有重复的数据，只显示一条）
select  a.dept_name,b.sid,b.name,b.woektime_start  from dept a  join emp b on  a.dept1 =b.dept2 where a.dept_name = "销售"  ORDER BY b.incoming asc LIMIT 1;




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

思路：条件  n：ame  ，sid ，dept_name

方法一:SELECT name,sid,dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 where age< (SELECT AVG(age) FROM emp);

方法二：
select sid,name,dept_name from (select * from emp,dept where dept1=dept2)s where age<(select avg(age)from emp);


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

条件： group  by     dept_name        

       sum (incoming)   >9000

结果：dept_name

方法一：

select s.dept_name from (select a.dept_name,sum(b.incoming) as k from dept a join emp b on a.dept1 = b.dept2  GROUP BY  a.dept_name HAVING k >9000)s;

方法二：

SELECT dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2  GROUP BY dept_name having sum(incoming)>90000;

方法三：

SELECT dept_name FROM
( SELECT sum( incoming ) a, dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 GROUP BY dept_name ) b
WHERE a > 9000;

方法四：SELECT dept_name from   (SELECT sum(incoming) as s,dept2 from emp GROUP BY dept2 having s>9000)t1  on dept.dept1=t1.dept2;




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


incoming<3800      emp

name
方法一：

SELECT name FROM   dept INNER JOIN emp on dept.dept1=emp.dept2  where incoming<3800  and dept_name="财务";

方法二：

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


方法三

select name from (select*from dept,emp where dept1=dept2)s where incoming<3800 and dept_name="财务"




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

条件

min   
dept表   dept_name=财务
emp 表   min（ incoming ）

结果：员工姓名  name

方法一：

select name  from dept inner join emp on dept.dept1=emp.dept2 where
dept2=(select dept1 from dept where dept_name="财务" ) and incoming=(select min(incoming) from emp ,dept where emp.dept2=dept.dept1 and  dept_name="财务");

方法二：
select  name  from   emp ,dept  where  dept1=dept2  and  dept_name="财务"  ORDER BY incoming asc  LIMIT 1


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

条件  ：dept  表   dept_name="销售"

     emp    max（age）

  结果：name

方法一:select name "姓名", age "年龄" from dept,emp where dept.dept1=emp.dept2 and
age=(select max(age) from dept,emp where dept.dept1=emp.dept2 and dept_name="销售")and dept_name="销售";

方法二：（缺陷是多个同样数据就显示一个）

SELECT name,age  from emp INNER JOIN dept on emp.dept2=dept.dept1  WHERE dept_name="销售"  ORDER BY  age  desc  LIMIT 0,1;


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

条件：emp      min（incoming ）

结果：name，dept_name

方法一：

SELECT NAME,dept_name FROM dept inner join  emp  on  dept.dept1=emp.dept2 WHERE incoming=(SELECT MIN(incoming) FROM emp );

方法二：

SELECT name ,dept_name FROM(SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2)a WHERE incoming =( SELECT MIN(incoming) FROM emp);

方法三：SELECT name,dept_name from  emp left join dept on emp.dept2=dept.dept1 where incoming=(SELECT min(incoming) from emp left join dept on emp.dept2=dept.dept1);

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

条件：emp   name=“李四”   

结果：incoming     dept_name

方法一：

SELECT incoming,dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2  where name="李四"

方法二：

select name,dept_name from  (select * from dept,emp where dept.dept1=emp.dept2)c where name='李四';

方法三：select incoming,dept_name from (select*from dept,emp where dept1=dept2)s where name="李四";



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

条件：

emp    incoming<4000

结果：dept1    ,deptname

方法一：select   dept1,dept_name   from  (select * from dept,emp where dept.dept1=emp.dept2)c  where incoming<4000;


方法二：select dept1,dept_name from emp left join dept on emp.dept2=dept.dept1 where incoming<4000;

方法三：  
select   dept1,dept_name from   dept  where  dept1 in (select  dept2  from  emp  where  incoming<4000) ;

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

方法一：（缺陷：重复就显示一个）

SELECT name "姓名",incoming "收入",dept_name"部门" from  (select * from dept,emp where dept.dept1=emp.dept2   order by incoming desc  ) b group by  dept_name  ORDER BY  incoming desc  ;

方法二：

SELECT name "姓名",incoming "收入",dept_name"部门" from (select * from dept,emp where dept.dept1=emp.dept2)a where
(a. incoming ,a.dept_name )in (select max(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name  order by incoming desc  ) order by incoming desc;

方法三:

select  b.name,a.dept_name, b.incoming  from  (select max(incoming) as c,dept_name from dept, emp  where  dept1=dept2 group  by dept_name)a LEFT JOIN ( select  * from  emp ,dept  where  dept1=dept2)b  on  a.dept_name=b.dept_name  and  a.c =b.incoming order  by  b.incoming desc;



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

条件：    dept     dept_name="财务"     max（incoming）   limit 2  

结果 ：  emp   name   sid   incoming

方法一：select name,sid,incoming from emp inner join dept on dept1=dept2 where dept_name='财务' order by incoming desc limit 2;

方法二：select * from (select name,sid,incoming from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务" order by incoming desc)as s where sid limit 0,2

方法三: select   a.dept1,a.dept_name,b.name,b.incoming from dept a ,  emp b where  a.dept1 = b.dept2  and a.dept_name ='财务'  ORDER BY  b.incoming desc LIMIT 2;

16.查询财务部低于平均收入的员工号与员工姓名：（按所有的平均薪资）

条件： dept     dept_name=“财务”     avg（incoming） >

结果：sid     name

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

方法二：

SELECT sid,name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE incoming<(SELECT avg(incoming) FROM emp)  and dept_name="财务";

备注（在多表中求得平均值）SELECT sid,name from  emp left join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming<(SELECT avg(incoming) from emp left join dept on emp.dept2=dept.dept1)

方法三：

select sid,name  from (select * from dept,emp where dept.dept1=emp.dept2)c  where incoming<(select avg(incoming) from emp) and dept_name='财务';
17.列出部门员工数大于1个的部门名称；

条件：dept       count（name）

结果：dept_name

方法一：select dept_name from dept where dept1 in(select dept2 from emp group by dept2 having count(dept2)>1)

方法二：SELECT DISTINCT(dept_name) FROM emp  INNER JOIN dept ON emp.dept2 = dept.dept1  WHERE (SELECT count(name) from emp)>1;

方法三：SELECT dept_name from  (SELECT  dept_name  ,count(name)s from  dept INNER JOIN emp on dept1=dept2 GROUP BY dept_name having s>1)d



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

条件：emp    incoming<=750      and   incoming>3000

结果：age ,dept_name

方法一：select age, dept2 from emp where incoming >3000 and incoming<=7500;

方法二：

select age,dept2  from (select * from dept,emp where dept.dept1=emp.dept2)c  where 7500>=incoming and incoming>3000;

方法三：SELECT name,age,dept1  FROM  dept inner JOIN emp on dept.dept1=emp.dept2   WHERE incoming BETWEEN 3000 and 7500  and  incoming!=3000;

错误写法：

SELECT name,age,dept1  FROM   dept inner JOIN emp on dept.dept1=emp.dept2   WHERE incoming BETWEEN 3000 and 7500;  （包含3000了）

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

条件：20世纪 表示1900-1999内  ，     70年代   ：大于等于1970~小于1980    woerktime =“197%”

结果： dept      dept_name

方法一：select dept_name from emp left join dept on emp.dept2=dept.dept1 where  woektime_start >="19700101" and woektime_start<"19800101";


方法二：SELECT name,dept_name FROM  dept RIGHT JOIN emp on dept.dept1=emp.dept2  WHERE worktime BETWEEN "1970-01-01" and "1979-12-31";


方法三：select dept_name "部门"  from dept,emp where  dept.dept1=emp.dept2 and  woektime_start like "197%" ;

方法四：select dept_name from emp inner join dept on dept1=dept2 where  woektime_start >="19700101" and woektime_start<="19791231";

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

条件： emp     name=“张三”

结果：dept       dept_name

方法一：
select dept_name from emp inner join dept on dept1=dept2 where name='张三';

方法二：


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

方法三：

select dept_name "部门" from dept,emp where dept2=dept1 and name="张三";



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

条件：  group   by    dept_name      max(age)         

结果：name  ，dept_name

方法一：sel ect name "姓名",dept_name "部门名" from dept,emp where dept1=dept2 and
(dept_name,age) in (select dept_name,max(age) from dept,emp where dept1=dept2 group by dept_name )；



方法二：

select  b.name,a.dept_name, b.incoming  from  (select max(age) as c,dept_name from dept, emp  where  dept1=dept2 group  by dept_name)a LEFT JOIN ( select  * from  emp ,dept  where  dept1=dept2)b  on  a.dept_name=b.dept_name  and  a.c =b.age

方法三：（是一种方法，当出现多条相同数据，就显示一条）

select name,dept_name from (select * from emp inner join dept on dept.dept1=emp.dept2 order by age desc) a group by dept_name ;




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

条件:   group  by    dept_name

结果;  sum(incoming)     dept_name

方法一：SELECT sum(incoming),dept_name FROM  dept left  JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name

方法二：select sum(incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept1;

方法三：（不合理的写法，答案正确）
SELECT sum(incoming),dept_name FROM dept iNNER JOIN emp on dept.dept1=emp.dept2 WHERE (SELECT sum(incoming) FROM emp ) GROUP by dept_name;

方法四：select dept_name,sum(incoming)  from (select * from dept,emp where dept.dept1=emp.dept2)c  group by dept_name；


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

条件：   dept        dept_name   

              emp    incoming>7000    sid

结果： sid   dept_name

方法一：SELECT sid,dept_name FROM dept left JOIN emp on dept1=dept2 where incoming>7000



方法二：select a.sid,a.dept_name from(select * from emp inner join dept on emp.dept2=dept.dept1 where incoming>7000) a;



方法三：
select sid,dept_name  from (select * from dept,emp where dept.dept1=emp.dept2)c    where incoming>7000;

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

条件：  dept_name              name或sid 判断

结果：dept_name

方法一：
select dept_name  from dept left join emp on dept.dept1=emp.dept2 group by dept1 having (count(sid)=0)


方法二：select * from dept where dept1 not in ( select dept2 from emp);



方法三：select dept_name from  emp right join dept on dept1=dept2 where name is null;



方法四：

select dept_name from dept left join emp on dept.dept1=emp.dept2  where name is NULL;




方法五：SELECT dept_name   FROM    (SELECT * FROM emp RIGHT JOIN dept ON emp.dept2 = dept.dept1  )a WHERE  sid is NULL;



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

条件：二次排序     order  by          desc  从大到小      早到晚  asc  

结果：所有信息

方法一：

SELECT * from  dept left join emp on dept.dept1=emp.dept2 ORDER BY dept1 desc,woektime_start asc ;

方法二：（部门编号104不显示，内连接方法）

select * from  dept a ,emp b where a.dept1 = b.dept2 ORDER BY  a.dept1 desc,b.woektime_start asc;

方法三：SELECT * from emp  ORDER BY dept2 DESC ,woektime_start asc;



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

条件： dept_name ="财务"       max（incoming）

结果：name  ，sid

方法一：SELECT name,sid  from dept left join emp on dept.dept1=emp.dept2 where incoming=(SELECT max(incoming) from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务")and dept_name="财务" ;

方法 二：

select name, sid from emp inner join dept on dept1= dept2 where dept_name='财务' order by incoming desc limit 1

方法三：SELECT name,sid FROM dept,emp WHERE dept.dept1=emp.dept2 AND (SELECT max(incoming) from (SELECT incoming from dept a ,emp b where a.dept1=b.dept2 and dept_name="财务")s )=incoming  and  dept_name="财务";



方法四：（缺陷有重复数据）

select name,sid FROM dept d INNER JOIN emp e on d.dept1=e.dept2 WHERE dept_name="财务" AND incoming in(SELECT MAX(incoming) FROM emp GROUP BY dept2);#



方法五：

SELECT name,dept1  FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1    WHERE dept_name='财务'  ORDER BY incoming DESC  LIMIT 0,1;



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

条件：  emp    incming         max ( age)   

结果：name    dept_name



方法一：

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



方法二：


SELECT name,dept_name from (SELECT * from emp INNER JOIN dept on emp.dept2=dept.dept1 ORDER BY age desc) a where incoming>=7500 and incoming<=8500 LIMIT 1;



方法三：


SELECT name,dept_name FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b WHERE age=(SELECT max(age) FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b)



方法四：

SELECT name,dept_name FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b ORDER BY age desc LIMIT 1