sql多表练习

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(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','行政');

CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;

insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');

insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');

insert into emp VALUES ('1776','王五',24,'1990/7/1',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');

insert into emp VALUES ('1880','老九',55,'1971/10/20',8000,'105');

drop table dept ;
drop table emp ;
select * from dept;
select * from emp ;

 

1.列出每个部门的平均收入及部门名称;

结果：avg（incomming），dept_name

 条件：group  by

方法：

SELECT dept_name,avg(incoming) from dept RIGHT JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name;

 

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

 

结果：sum（incoming）

条件：dept_name="财务"

select  sum(incoming)  from  dept INNER JOIN  emp   on dept.dept1=emp.dept2 where dept_name="财务

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

 

结果：sid

条件：dept_name="IT技术"

方法：SELECT sid FROM dept INNER  JOIN emp on dept.dept1=emp.dept2 where dept_name='IT技术' ;
4.财务部门收入超过2000元的员工姓名

结果：name
条件： incoming>2000  dept_name="财务"

方法：SELECT name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and incoming>2000;

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

结果：worktime  
条件： dept_name=" 销售" ，min（incoming）

方法：

SELECT worktime_start FROM dept inner JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售' AND incoming=(SELECT min(incoming) FROM dept inner JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售') ;

 方法2：

select emp.worktime_start from dept join emp on dept.dept1 = emp.dept2 where dept.dept_name = "销售" ORDER BY emp.incoming LIMIT 1;

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

 结果：name，sid，dept_name

条件：<(select  avg(age) from  emp  )

 语句：

SELECT emp.name,emp.sid,dept.dept_name from emp LEFT JOIN dept on dept.dept1 = emp.dept2 WHERE
emp.age < (SELECT avg(emp.age) from emp) ;

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

结果：dept_name   

条件：sum(incoming)>9000 ，group  by

方法：

select dept_name,sum(incoming) from dept left join emp on dept.dept1=emp.dept2 group by dept_name having sum(incoming)>9000;

8.查出财务部门工资少于3800元的员工姓名
结果：name

条件： dept_name=”财务"   ,incoming<3800

方法：

select name from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务" AND incoming<3800;

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

结果：name
条件：min（ incoming），dept_name="财务"

方法：

 

SELECT name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and incoming=(SELECT min(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务" );

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

结果：name

条件：dept_name=" 销售"  ，max（age）

方法：（缺陷）

select emp.name from dept join emp on dept.dept1 = emp.dept2 where dept.dept_name = "销售" ORDER BY emp.age desc LIMIT 1;

方法2:

SELECT name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售' AND age=(SELECT min(age) FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name='销售')

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

结果： name，dept_name

条件：   min（incoming）  

方法：SELECT NAME,dept_name from dept left join emp on dept.dept1=emp.dept2 WHERE incoming=

(SELECT MIN(incoming) FROM dept left join emp on dept.dept1=emp.dept2 );

方法2：

SELECT name,dept_name from dept RIGHT JOIN emp on dept.dept1=emp.dept2 WHERE incoming=(SELECT min(incoming) FROM emp);

方法3：

select emp.name,dept.dept_name from dept join emp on dept.dept1 = emp.dept2 ORDER BY emp.incoming LIMIT 1;
12.求李四的收入及部门名称

结果：incoming，dept_name

条件：name= '李四'

方法：select name,incoming,dept_name from dept join emp on dept.dept1 = emp.dept2 where name = "李四";

 

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

结果：

sid 、detp_name

条件：incoming<4000

方法：

select DISTINCT(dept.dept1),dept.dept_name from dept join emp on dept.dept1 = emp.dept2 where emp.incoming < 4000;

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

结果： name，dept_name,incoming 

条件 ：group by   dept_name   ,max(incoming)     incoming    desc

语句：

SELECT name,dept_name,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 where (dept1,incoming) in (
SELECT dept1,max(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2
group by dept1 )  ORDER BY incoming desc;

 

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

结果：name 、sid 、incoming

条件：dept_name="财务"  order  by     desc   limit  0,2

语句：

SELECT name,sid,incoming from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" ORDER BY incoming desc  LIMIT 0,2;

 

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

结果：sid 、name

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

方法:

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

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

结果： dept_name

条件：  count（name）>1    group  by    dept_name

方法：SELECT dept_name,count(NAME) FROM dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name HAVING count(name)>1 ;

 

 

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

结果“：age，detp_name

条件：incoming<7500,incoming>3000

语句：

select age,dept1 from emp left join dept on emp.dept2=dept.dept1
where incoming <=7500 and incoming>3000;

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

结果： dept_name

 条件：woek_time like "197%"  

语句：select dept1 from dept INNER JOIN emp on dept.dept1=emp.dept2 where worktime_start like "197%";

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

结果： dept_name

  条件：name=”张三“

语句：

select dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 where name="张三";

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

结果： name，dept_name

条件：group  by   dept_name

语句：

SELECT name,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE (dept_name,age) in (SELECT dept_name,max(age) from dept dept INNER JOIN   emp on dept.dept1=emp.dept2 GROUP BY dept_name);

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

结果： dept_name ，sum(incoming）

条件： select  dept_name,sum(incoming) from  dept INNER JOIN  emp   on dept.dept1=emp.dept2 GROUP BY dept_name;

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

结果： dept_name ,sid

条件:,incoming>7000

语句：SELECT emp.sid,dept.dept_name from emp LEFT JOIN dept ON dept.dept1 = emp.dept2 WHERE emp.incoming > 7000;

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

结果： dept_name

条件   is null    

语句：

SELECT dept.dept_name from dept LEFT JOIN emp ON dept.dept1 = emp.dept2 WHERE emp.sid is null;

语句2:

select dept.dept_name from dept left join emp on dept.dept1 = emp.dept2 GROUP BY dept.dept_name having count(emp.name) = 0;

 

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

结果：*

条件：oder  by  dept1     desc，oder  by    woektime  asc

语句：

 select  *  from  dept INNER JOIN  emp   on dept.dept1=emp.dept2  ORDER BY dept1 desc,worktime_start ASC ;

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

结果：name 、sid

条件：dept_name="财务"

语句：

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

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

结果： name、dept_name

条件：BETWEEN 7500 AND 8500，max（age）

语句：

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