第十二课 数据库练习

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
1：dept表中有4条记录：
     部门号(dept1)  部门名称(dept_name )
     101     财务           
     102     销售           
     103     IT技术        
     104     行政           
2：emp表中有6条记录：
      员工号 员工姓名 年龄 入职时间 收入 部门号对应字段名称为： (sid name age worktime_start incoming dept2)
      1789    张三 35 1980/1/1 4000 101
      1674    李四 32 1983/4/1 3500 101
      1776    王五 24 1990/7/1 2000 101
      1568    赵六 57 1970/10/11 7500 102
      1564    荣七 64 1963/10/11 8500 102
      1879    牛八 55 1971/10/20 7300 103

1.列出每个部门的平均收入及部门名称; 
解法一：临时表和基本连接
select avg(incoming),dept2 from emp group by dept2; -t
select * from dept;
select t.a,dept.dept_name from (select avg(incoming)a,dept2 from emp group by dept2)t,dept where t.dept2=dept.dept1
解法二：临时表和基本连接 -通过部门名称分组
select * from dept,emp where dept.dept1=emp.dept2
select dept_name,avg(incoming) from dept,emp where dept.dept1=emp.dept2 group by dept_name;
 
2.财务部门的收入总和；
解法一：基本连接
select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务';
解法二：临时表
select sum(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t where dept_name='财务';
解法三：嵌套in
select sum(incoming) from emp where dept2 in(select dept1 from dept where dept_name = '财务');
解法四：左连接
select sum(incoming) from dept left join emp on dept.dept1=emp.dept2 and dept_name = '财务';
解法五：
select sum(incoming) from emp RIGHT join dept on dept.dept1=emp.dept2 and dept_name = '财务';

3.It技术部入职员工的员工号  
select sid from dept left join emp on dept.dept1=emp.dept2 where dept_name='IT技术'
select sid from dept,emp where dept.dept1=emp.dept2 and dept_name='IT技术'

 
4.财务部门收入超过2000元的员工姓名 
解法一：临时表
select * FROM dept,emp WHERE dept.dept1=emp.dept2
select NAME FROM (select * FROM dept,emp WHERE dept.dept1=emp.dept2) AS tt WHERE dept_name='财务' AND incoming>2000;
解法二：基本连接
select name FROM dept,emp WHERE dept.dept1=emp.dept2 and dept_name = '财务' and incoming>2000;

5.找出销售部收入最低的员工的入职时间；
解法一：临时表
select min(incoming) from emp,dept where dept.dept1=emp.dept2 and dept_name='销售'  --销售部最低的收入  --这个sql语句查询出来的结果为4000
select worktime_start from emp,dept where incoming = (select min(incoming) from emp,dept where dept.dept1=emp.dept2 and dept_name='销售') and dept.dept1 =emp.dept2 and dept_name='销售'
解法二：嵌套=
select * from dept,emp where dept.dept1=emp.dept2
select min(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t where dept_name='销售'
select * from emp where incoming in (select min(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t where dept_name='销售') and dept2=(select dept1 from dept where dept_name = '销售')

6.找出年龄小于平均年龄的员工的姓名，ID和部门名称 
(select * from dept,emp where dept.dept1=emp.dept2)t
select name,sid,dept_name from (select * from dept,emp where dept.dept1=emp.dept2)t where age<(select avg(age) from emp );

7.列出每个部门收入总和高于9000的部门名称 
解法一：
select * from dept;
select * from emp;
select * from dept,emp where dept.dept1=emp.dept2;
select sum(incoming)s,dept2 from emp group by dept2 HAVING s>9000
select dept.dept_name from dept,(select sum(incoming)s,dept2 from emp group by dept2 HAVING s>9000)t where dept.dept1=t.dept2
解法二：
select dept_name from (select * from dept,emp where dept.dept1=emp.dept2)t group by dept_name having sum(incoming)>9000;
select dept_name from (select dept_name,sum(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t group by dept_name having sum(incoming)>9000)f;

8.查出财务部门工资少于3800元的员工姓名   
select * from dept,emp where dept.dept1=emp.dept2
select name from(select * from dept,emp where dept.dept1=emp.dept2)a where incoming<3800 and dept_name='财务'
10.找出销售部门中年纪最大的员工的姓名  
解法一：
select max(age) from dept,emp where dept.dept1 =emp.dept2 and dept_name='销售'  --57
select name from dept,emp where age in(select max(age) from dept,emp where dept.dept1 =emp.dept2 and dept_name='销售') and dept.dept1=emp.dept2 and dept_name='销售'
解法二：
select*from dept,emp where dept.dept1=emp.dept2
select name from (select*from dept,emp where dept.dept1=emp.dept2) as t where age in(select max(age) from emp where dept2 in (select dept1 from dept where dept_name='销售')) and dept_name='销售';

9.求财务部门最低工资的员工姓名； 
select * from dept,emp where dept.dept1=emp.dept2;
select min(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t where dept_name='财务'
select name from (select * from dept,emp where dept.dept1=emp.dept2)t where incoming=(select min(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)t where dept_name='财务') and dept_name='财务';

11.求收入最低的员工姓名及所属部门名称：
select min(incoming) from emp
select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming=(select min(incoming) from emp)
select *from dept,emp where dept.dept1=emp.dept2;
select min(incoming) from dept,emp where dept.dept1=emp.dept2;
select name,dept_name from (select *from dept,emp where dept.dept1=emp.dept2)t where incoming =(select min(incoming) from emp);

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

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

13.求员工收入小于4000元的员工部门编号及其部门名称  
select * from emp,dept where emp.dept2=dept.dept1;
select dept1,dept_name from (select * from emp,dept where emp.dept2=dept.dept1)t where incoming<4000;

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
解法一：
select max(incoming),dept2 from emp group by dept2  --a
select * from dept,emp where dept.dept1 = emp.dept2   --b
select b.name,b.dept_name,b.incoming from (select max(incoming)m,dept2 from emp group by dept2 )a inner join (select * from dept,emp where dept.dept1 = emp.dept2)b on a.dept2=b.dept1 and a.m=b.incoming order by b.incoming desc;
解法二：
select name,p1.dept_name from (select * from dept,emp where dept.dept1=emp.dept2)p1 LEFT JOIN(select max(age) as max,dept_name from(SELECT * from dept,emp where dept.dept1=emp.dept2)a GROUP BY dept2)p2 on p1.dept_name=p2.dept_name WHERE p1.age=p2.max order by p1.incoming desc;

(select * from dept,emp where dept.dept1=emp.dept2)p1 LEFT JOIN(select max(incoming) as max,dept_name from(SELECT * from dept,emp where dept.dept1=emp.dept2)a GROUP BY dept2)p2 on p1.dept_name=p2.dept_name WHERE p1.age=p2.max order by p1.incoming desc;
select * from emp;

15.求出财务部门收益最高的俩位员工的姓名，工号，收益 
select name,sid,incoming from (select * from dept,emp where dept.dept1=emp.dept2)a where  dept_name='财务' order by incoming desc limit 0,2

16.查询财务部低于平均收入的员工号与员工姓名：
select avg(incoming) from emp;
select sid,name from dept,emp where dept.dept1 = emp.dept2 and dept_name = '财务' and incoming<(select avg(incoming) from emp);

错误解法：
SELECT * from dept,emp where dept.dept1=emp.dept2
SELECT avg(incoming) FROM (SELECT * FROM dept,emp where dept.dept1=emp.dept2)q;
SELECT sid,NAME FROM (SELECT * FROM dept,emp where dept.dept1=emp.dept2)q where incoming<(SELECT avg(incoming) FROM (SELECT * FROM dept,emp where dept.dept1=emp.dept2)q where dept_name='财务');

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

SELECT dept_name FROM (SELECT * from dept,emp where dept.dept1=emp.dept2)t GROUP BY dept_name HAVING count(*)>1;

18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
select age,dept1 from dept,emp where dept.dept1=emp.dept2 and incoming>3000
and incoming <=7500;
select age,dept1 from (select *from dept,emp where dept.dept1=emp.dept2)a where incoming<=7500 and incoming>3000

19.求入职于20世纪70年代的员工所属部门名称；    
select * from dept,emp where dept.dept1=emp.dept2 and worktime_start >= '1970-1-1' and worktime_start <= '1979-12-31';

SELECT dept_name from (SELECT * from dept,emp where dept.dept1=emp.dept2)y where worktime_start like'197%'

20.查找张三所在的部门名称；
select dept_name from dept where dept1 in(select dept2 from emp where name='张三');
select * from dept,emp where dept.dept1=emp.dept2;
select dept_name from(select * from dept,emp where dept.dept1=emp.dept2)t where name='张三';

21.列出每一个部门中年纪最大的员工姓名，部门名称；
错误解法一：
select name,dept_name from (select * from dept,emp where dept.dept1=emp.dept2)t where age in(select max(age) from emp group by dept2);
select * from dept;
select * from emp;
错误解法二：
select max(age)m,dept2 from emp group by dept2  --a
select * from dept,emp where dept.dept1=emp.dept2  --b
select b.name,b.dept_name from (select max(age)m,dept2 from emp group by dept2)a,(select * from dept,emp where dept.dept1=emp.dept2)b where a.dept2=b.dept1 and a.m=b.age

22.列出每一个部门的员工总收入及部门名称；
解法一：基本连接
select sum(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name
解法二：临时表
select *from dept right join emp on dept.dept1=emp.dept2;
select dept_name,sum(incoming) from (select *from dept right join emp on dept.dept1=emp.dept2) a group by dept_name;

23.列出部门员工收入大于7000的员工号，部门名称；
select sid,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming>7000
select sid,dept_name from (select * from dept,emp where dept.dept1=emp.dept2)t where incoming>7000; 

24.找出哪个部门还没有员工入职； 
select * from dept left join emp on dept.dept1=emp.dept2;
select dept_name from (select * from dept left join emp on dept.dept1=emp.dept2)t where worktime_start is null;

25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
错误解法：
select * from dept left join emp on dept.dept1=emp.dept2
select * from(select * from dept left join emp on dept.dept1=emp.dept2)t order by dept1 desc;
select * from(select * from dept left join emp on dept.dept1=emp.dept2)w order by worktime_start asc;
正确解法：
select * from emp order by dept2 desc,worktime_start asc;

26.求出财务部门工资最高员工的姓名和员工号  
正确解法：
select max(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name = '财务'
select name,sid from emp,dept where incoming=(select max(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name = '财务') and dept.dept1 = emp.dept2 and dept_name='财务';
错误解法：
select * from dept,emp where dept.dept1=emp.dept2;
SELECT max(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)as q where dept_name='财务';
select sid,name from (select * from dept,emp where dept.dept1=emp.dept2)as q where dept1=(select dept1 from dept where dept_name='财务') and incoming=(SELECT max(incoming) from (select * from dept,emp where dept.dept1=emp.dept2)as q where dept_name='财务')

27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
正确的解法一：
select max(age) from dept,emp where dept.dept1=emp.dept2 and incoming BETWEEN 7500 and 8500;
select name,dept_name  from dept,emp where dept.dept1=emp.dept2 and age=(select max(age) from dept,emp where dept.dept1=emp.dept2 and incoming BETWEEN 7500 and 8500) and incoming BETWEEN 7500 and 8500;
正确的解法二：
select name,dept_name from (SELECT * from dept,emp where dept.dept1=emp.dept2 and incoming>=7500 and incoming<=8500)t where age=(SELECT max(age) from emp where incoming>=7500 and incoming<=8500);