项目描述
上传时间
浏览人数
-- (21)查询月收入在2000~3000元的员工信息,最后一列为薪资列。
select *,sy.emp_income as 薪资 from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where sy.emp_income >=2000 and sy.emp_income <=3000;
-- (22)查询1990年以后出生的员工的姓名和地址,以及每周工作休息情况。
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where year(es.emp_birth) > 1990;
-- (23)查询“王林”的基本情况和所工作的部门名称。
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where es.emp_name = '王林';
-- (24)查询财务部、研发部、市场部的员工信息。
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where ds.depart_name in ('财务部','研发部','市场部');
-- (25)查询每个雇员的基本情况和薪水情况。
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num;
-- (26)查询 员工的姓名、住址和收入水平,要求2000元以下显示为“低收入”,2000~3000显示为“中等收入”,3000元以上时显示为“高收入”。
select *, case when sy.emp_income < 2000 then '低收入'
when sy.emp_income <3000 and sy.emp_income >= 2000 then '中等收入'
when sy.emp_income then '高等收入' end as 收入状态
from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num;
-- (27)按部门列出该部门工作的员工人数。
select ds.depart_name, count(1) from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
group by ds.depart_num;
-- (28)查找雇员数超过2人的部门名称和员工数量。
select ds.depart_name, count(1) as number from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
group by ds.depart_num
having number >2;
-- (29)按员工学历分组统计各种学历人数。
select es.emp_edu, count(1) as number from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
group by es.emp_edu;
-- (30)将员工薪水按收入多少从小到大排序。
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
order by sy.emp_income asc;
-- (31)按员工的工作年限进行分组,统计各个工作年限的人数,并按人数从小到大排序。
select es.emp_years_of_service ,count(1) as number from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
group by es.emp_years_of_service
order by number asc;
-- (32)查实际收入前三 的 人名/部门名/收入/支出/实际收入
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id where sy.emp_income in
(
select distinct sy.emp_income from employees as es
left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num order by sy.emp_income desc limit 0,3
) ;
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
order by sy.emp_income limit 0,3;
-- (33)查实际收入后三 的 人名/部门名/学历/电话/收入/支出/实际收入
-- ???
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id where sy.emp_income in
(
select distinct sy.emp_income from employees as es
left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num order by sy.emp_income asc limit 0,3
);
-- (34)周末双休的人的平均工作年限
-- ???
select avg(es.emp_years_of_service) from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where ds.depart_comment = '周末双休' ;
-- (35)查询收入大于支出的人
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join salary as sy1 on es.emp_id = sy1.emp_id
where sy.emp_income > sy1.emp_expend;
-- (36)查询平均薪资小于6k的部门
select ds.depart_name ,(sy.emp_income) as avg_s from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
group by ds.depart_num having avg_s < 6000 order by avg_s desc;
-- (37)查询薪资低于整个公司平均薪资的 员工姓名/薪资
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join salary as sy1 on es.emp_id = sy1.emp_id
where sy.emp_income < (select avg(sy.emp_income) from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join salary as sy1 on es.emp_id = sy1.emp_id);