员工数据信息查询练习（mysql）

01_6

已于 2024-05-13 20:34:58 修改

阅读量430

点赞数 6

分类专栏： MYSQL 文章标签： mysql 数据库

于 2024-05-07 20:19:28 首次发布

本文链接：https://blog.csdn.net/2301_77444219/article/details/138543896

版权

MYSQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

5.列出在每个部门工作的员工数量、平均工资和平均服务期限

6.列出所有部门的详细信息和部门人数

7.列出每个员工从受雇开始到2018-12-12 为止共受雇了多少天

8.列出每个部门薪水前两名最高的人员名称以及薪水

1.基于数据结构编写每张表建表语句

create table emp
(	emp_no int
 	,ename varchar(255)
 	,job varchar(255)
 	,mgr int
 	,hire_date varchar(255)
 	,sal int
 	,bonus int
 	,dept_no int
)

create table dept(
	dept_no int
    ,dept_name varchar(255)
    ,dept_addr varchar(255)
)

2.数据加载

员工数据：

(7369,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)
,(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)
,(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20)
,(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30)
,(7698,'BSMITH','CLERK',7902,'1980-12-17',800,null,20)
,(7499,'LAKE','MANAGER',7839,'1981-05-01',2850,null,30)
,(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10)
,(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20)
,(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10)
,(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)
,(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20)
,(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30)
,(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20)
,(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10)

部门数据：

10,ACCOUNTING,NEW YORK
10,ACCOUNTING,shanghai
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

薪水 = SAL 

薪金 = 12*SAL加奖金

年工资 = 年薪 = 12*SAL

3.列出所有员工的姓名及其直接上级的姓名

select 
	t1.*
	,t2.ename as boss_name 
from emp t1
left join emp t2
on t1.mgr=t2.emp_no

4.列出受雇日期早于其直接上级的所有员工

select 
	t1.*
	,t2.ename as boss_name 
	,t2.hire_date as boss_date
from emp t1
left join emp t2
on t1.mgr=t2.emp_no
where t1.hire_date < t2.hire_date

5.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金

select 
	* 
	,sal*12+bonus
from emp 
where sal*12+bonus >
	(select 
		max(sal*12+bonus) as xj 
	from emp
	where dept_no=30)

5.列出在每个部门工作的员工数量、平均工资和平均服务期限

select 
	dept_no
	,count(*) 
	,avg(sal)
	,avg(DATEDIFF(CURRENT_DATE,hire_date))
from emp 
group by dept_no

6.列出所有部门的详细信息和部门人数

select 
	t2.*
	,t1.cnt
from
	(select 
		dept_no
		,count(*) cnt
	from emp 
	group by dept_no) t1
join dept t2
on t1.dept_no=t2.dept_no

7.列出每个员工从受雇开始到2018-12-12 为止共受雇了多少天

select 
    *
    ,DATEDIFF('2018-12-12',hire_date) 
from emp

8.列出每个部门薪水前两名最高的人员名称以及薪水

select * from 
	(select 
		dept_no
		,max(sal)  as max_sal
	from emp 
	where sal not in
		(select 
			max(sal)
		from emp 
		group by dept_no)
	group by dept_no) t1
UNION 
	(select 
			dept_no
			,max(sal) as max_sal
		from emp 
		group by dept_no)