SQL两表练习

最新推荐文章于 2024-07-09 17:10:02 发布

shao_dushu

最新推荐文章于 2024-07-09 17:10:02 发布

阅读量647

点赞数

文章标签： sql Powered by 金山文档

本文链接：https://blog.csdn.net/shao_dushu/article/details/129590835

版权

已知2张基本表：部门表：dept （部门号，部门名称）；员工表emp（员工号，员工姓名，年龄，入职时间，收入，部门号）

1：dept表中有4条记录：

部门号部门名称

101 财务

102 销售

103 IT技术

104 行政

2：emp表中有6条记录：

员工号员工姓名年龄入职时间收入部门号

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

#dept 左连接查询

SELECT * FROM dept d LEFT JOIN emp e on d.dept1=e.dept2

#dept 右连接查询

SELECT * FROM dept d RIGHT JOIN emp e on d.dept1=e.dept2

注：左连接查询和右连接查询的区别在域dept表中104 行政数据为显示

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

SELECT dept_name 部门名称 ,AVG(incoming) 平均收入 from dept d RIGHT JOIN emp e on d.dept1=e.dept2 GROUP BY dept_name

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

SELECT SUM(incoming) 收入总和 from dept d left JOIN emp e on d.dept1=e.dept2 WHERE dept_name = '财务'

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

SELECT sid 员工号 from dept d left JOIN emp e on d.dept1=e.dept2 WHERE dept_name = 'IT技术' ;

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

SELECT name 员工姓名 from dept d left JOIN emp e on d.dept1=e.dept2 where incoming > 2000 and dept_name = '财务'

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

SELECT worktime_start 入职时间 from dept d RIGHT JOIN emp e on d.dept1=e.dept2

where dept_name = '销售' AND incoming =(SELECT MIN(incoming)FROM dept d RIGHT JOIN emp eon d.dept1=e.dept2 where dept_name = '销售')

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

SELECT name 姓名 , sid ID, dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 where age<(SELECT AVG(age) FROM emp)

#扩展6.找出每个部门年龄小于或等于平均年龄的员工的姓名，ID和部门名称

SELECT name 名字 ,sid ID ,dept_name 部门名称 FROM emp e left JOIN dept d on e.dept2=d.dept1 LEFT JOIN (SELECT dept2 ,avg(age)a FROM emp GROUP BY dept2)b on e.dept2=b.dept2 WHERE e.age <= b.a

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

SELECT dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 GROUP BY dept_name HAVING sum(incoming)>9000

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

SELECT name 姓名 from emp left JOIN dept on emp.dept2=dept.dept1 where incoming < 3800 and dept_name = '财务'

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

SELECT 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 where dept_name = '财务')

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

SELECT name from emp left JOIN dept on emp.dept2=dept.dept1 WHERE age= (SELECT max(age) from emp left JOIN dept on emp.dept2=dept.dept1 where dept_name = '销售' ) and dept_name ='销售'

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

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.求李四的收入及部门名称

SELECT incoming 收入, dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 where name = '李四';

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

SELECT DISTINCT(dept1) 部门编号,dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 where incoming < 4000

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

SELECT name 名字 ,dept_name 部门名称 ,incoming 收入 from emp left JOIN dept on emp.dept2=dept.dept1 where (dept_name,incoming) in (SELECT dept_name, max(incoming) FROM emp left JOIN dept on emp.dept2=dept.dept1 GROUP BY dept_name) ORDER BY incoming DESC

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

select name 姓名,sid 工号,incoming 收入 from emp e right join dept d on e.dept2 = d.dept1

where incoming in (select * from (select incoming from emp e right join dept d on e.dept2 = d.dept1 where dept_name = '财务' order by incoming desc limit 2)a) and dept_name = '财务'

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

SELECT sid工号,name姓名 from emp left JOIN dept on emp.dept2=dept.dept1 WHERE incoming < (SELECT avg(incoming) FROM emp) and dept_name='财务'

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

SELECT dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 GROUP BY dept_name having count(dept_name) >1

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

SELECT age 年龄, dept1 部门编号 FROM dept d RIGHT JOIN emp e on d.dept1=e.dept2 WHERE incoming >3000 and incoming <=7500

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

SELECT dept_name 部门名称 FROM emp left JOIN dept on emp.dept2=dept.dept1 where worktime_start like "197%"

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

SELECT dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 WHERE name = '张三'

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

SELECT name 名称 ,dept_name 部门名称 FROM emp left JOIN dept on emp.dept2=dept.dept1 WHERE (emp.dept2,age) in (SELECT dept2 ,max(age) from emp GROUP BY emp.dept2)

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

SELECT sum(incoming)部门总收入,dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 GROUP BY dept_name

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

SELECT sid 员工号 ,dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 WHERE incoming > 7000

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

SELECT dept_name 部门名称 from dept left JOIN emp on dept.dept1=emp.dept2 WHERE name is NULL

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

SELECT dept1 部门号,worktime_start 入职时间 from dept RIGHT JOIN emp on dept.dept1=emp.dept2 ORDER BY dept1 DESC , worktime_start ASC

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

SELECT name 名字,sid 员工号 from emp left JOIN dept on emp.dept2=dept.dept1 WHERE dept_name = '财务' and incoming =(select max(incoming) from emp e right join dept d on e.dept2 = d.dept1 where dept_name = '财务')

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

SELECT name 名字, dept_name 部门名称 from emp left JOIN dept on emp.dept2=dept.dept1 WHERE incoming BETWEEN 7500 and 8500 and age=(SELECT max(age) FROM emp left JOIN dept on emp.dept2=dept.dept1)