有20条MySQL的练习题自己敲的(除了两个)，有的是做复杂了
问题和具体数据在这个链接：问题和数据资源
数据源，提取码：izak
统计人力资源部(Human Resources)一共有多少人任职(包括在职和离职)，字段包括员工数量(count)。
select
count(1)
from
dept_emp
inner join
departments
on dept_emp.dept_no=departments.dept_no
where departments.dept_name='Human Resources';统计开发部(Development)在1990年，一年里入职又离职的员工信息，字段包括员工编号(emp_no)，员工编号(名称)，入职日期(from_date)，离职日期(to_date)。
select
E.emp_no emp_no,E.last_name,DE.from_date,to_date
from
departments D
inner join
dept_emp DE
on D.dept_no=DE.dept_no
inner join
employees E
on E.emp_no=DE.emp_no
where D.dept_name='Development'
and year(from_date)='1990' and year(to_date)='1990';统计开发部(Development)在2000年度里，每月员工人事调动或离职的情况，并按照月份排序，字段包括月份名称(month)，员工数量(count)。
select
monthname(to_date),count(1)
from
dept_emp DE
inner join
departments D
on D.dept_no = DE.dept_no
where D.dept_name='Development' and year(to_date)='2000'
group by month(to_date);统计开发部(Development)在2000年1月份，每天员工离职的情况，并按照日期排序，字段包括日期(day)，员工数量(count)。
select
day(to_date) day,count(1)
from
dept_emp DE
inner join
departments D
on D.dept_no = DE.dept_no
where D.dept_name='Development' and year(to_date)='2000' and month(to_date)='1'
group by day;统计开发部(Development)在2000年1月份，每天员工离职的情况，并按照日期排序，字段包括日期(day)，星星数量(stars)。
select
day(to_date) day, replace(space(count(1)),' ','*') stars
from
dept_emp DE
inner join
departments D
on D.dept_no = DE.dept_no
where D.dept_name='Development' and year(to_date)='2000' and month(to_date)='1'
group by day;统计开发部(Development)在2000年1月份，不是工作日(周一到周五)员工离职的情况，并按照周一到周五排序。
select
A.dayname,count(1) count
from
(select
E.emp_no,E.last_name,dayname(to_date) dayname,weekday(to_date) W
from
employees E
inner join dept_emp DE
on E.emp_no=DE.emp_no
inner join
departments D
on DE.dept_no=D.dept_no
where D.dept_name = 'Development'and year(to_date)='2000'
and month(to_date)='1' and weekday(to_date) in(0,1,2,3,4)) A
group by A.dayname
order by w;统计开发部(Development)在2000年1月份，每天员工离职的情况，并按照日期排序，字段包括日期(day)，员工列表(emp_list)。
select
day(to_date) day, group_concat(last_name separator',') emp_list
from
dept_emp DE
inner join
departments D
on D.dept_no = DE.dept_no
inner join
employees E
on E.emp_no=DE.emp_no
where D.dept_name='Development' and year(to_date)='2000' and month(to_date)='1'
group by day;（不是自己做的）统计开发部(Development)在2000年1月份，每天员工离职的情况，并按照日期排序，员工列表按照员工名称首字母排序，字段包括日期(day)，员工列表(emp_list)。
select
DATE1 day, group_concat(
if(gender='F',concat(last_name,'(',gender,')'),last_name)order by substr(last_name,1,1) separator',') emp_list
from
(select
E.emp_no,E.last_name,E.gender,day(DE.to_date) DATE1
from
employees E
inner join
dept_emp DE
on E.emp_no=DE.emp_no
inner join
departments D
on D.dept_no = DE.dept_no
where D.dept_name='Development' and year(to_date)='2000' and month(to_date)='1') A
group by DATE1
order by DATE1;对各部门的在职员工数进行排序，希望有如下所示的结果集，字段包括名次(rank),员工数量(count)
select
@rank:=@rank+1 rank,A.count
from(
select
dept_name,count(1) count
from
dept_emp DE
inner join
departments D
on D.dept_no=DE.dept_no
where year(to_date)>='9999'
group by dept_name
order by count desc) A,
(SELECt @rank:= 0)B;对各部门的在职员工数进行横向排序，希望有如下所示的结果集，字段包括名次(rank1-rank9)。
（这边做复杂了）
drop procedure if exists rankSelect;
//
create procedure rankSelect()
begin
drop temporary table if exists rankOfEmp;
create temporary table rankOfEmp
(
select
@rank:=@rank+1 rank,A.count
from(
select
dept_name,count(1) count
from
dept_emp DE
inner join
departments D
on D.dept_no=DE.dept_no
where year(to_date)>='9999'
group by dept_name
order by count desc) A,
(SELECt @rank:= 0)B
);
select
sum(if(R.rank='1',R.count,null)) rank1,
sum(if(R.rank='2',R.count,null)) rank2,
sum(if(R.rank='3',R.count,null)) rank3,
sum(if(R.rank='4',R.count,null)) rank4,
sum(if(R.rank='5',R.count,null)) rank5,
sum(if(R.rank='6',R.count,null)) rank6,
sum(if(R.rank='7',R.count,null)) rank7,
sum(if(R.rank='8',R.count,null)) rank8,
sum(if(R.rank='9',R.count,null)) rank9
from
rankOfEmp R;
end;
//
call rankSelect();
//返回薪水在150000以上的在职(to_date=‘9999-01-01’)员工信息
select
E.emp_no,concat(first_name,' ',last_name) name,gender,birth_date,hire_date
from
employees E
inner join
salaries S
on
S.emp_no=E.emp_no
where to_date='9999-01-01' and salary>150000;查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情，字段包括部门编号(dept_no)，员工编号(emp_no)，员工名称(last_name)，薪水(salary）
select distinct
DM.dept_no,DM.emp_no,E.last_name,S.salary
from
dept_manager DM
inner join
employees E
on E.emp_no=DM.emp_no
inner join
salaries S
on S.emp_no=DM.emp_no
where S.to_date=DM.to_date and DM.to_date='9999-01-01';返回员工入职年龄在25(包含25)岁以下1990年以后入职在职员工信息
select
DE.emp_no,last_name,birth_date,hire_date
from
employees E
inner join
dept_emp DE
on
E.emp_no=DE.emp_no
where datediff(E.hire_date,birth_date)<='9130' and year(E.hire_date)>='1990'and year(to_date)>='9999' ;返回first_name,last_name首尾字母都相同的在职员工 ，字段包括员工全名name。
select
concat(E.first_name,E.last_name) name
from
employees E
inner join
dept_emp DE
on DE.emp_no=E.emp_no
where left(E.first_name,1)=right(E.first_name,1) and left(E.last_name,1)=right(E.last_name,1)
and right(E.first_name,1)=left(E.last_name,1) and year(DE.to_date)>='9999';返回头衔为Engineer的1990年以后入职薪水在40000(包括40000)元以下的在职员工
select distinct
T.emp_no,last_name,T.title title
from
employees E
inner join
dept_emp DE
on
E.emp_no = DE.emp_no
inner join
salaries S
on
S.emp_no=DE.emp_no
inner join
titles T
on
T.emp_no=S.emp_no
where T.title like '%Engineer' and year(hire_date)>='1990' and S.salary<=40000
and year(S.to_date)>='9999' and year(T.from_date)>='1990';返回人力资源部(Human Resources)最年轻的男(M)性在职员工
select
E.emp_no,last_name,dept_name,gender,birth_date
from
employees E
inner join
dept_emp DE
on
DE.emp_no=E.emp_no
inner join
departments D
on
D.dept_no=DE.dept_no
where D.dept_name='Human Resources' and E.gender='M'
order by date_format(birth_date,'%Y%m%d')
limit 0,1;将员工分成若干个组，一个组最多8人
注意：解答此题前先执行以下脚本
DROp TABLE if exists employees.temp_employees ;
CREATE TABLE if not exists employees.temp_employees AS SELECT e.emp_no, e.last_name FROM
employees e
select
ceil(rank / 8.0) grp, emp_no, last_name
from
(select
e.emp_no,
e.last_name,
(select
count(*)
from
temp_employees d
where
e.emp_no < d.emp_no ) + 1 rank
from
temp_employees e ) a
order by grp;请以当前日期为基准(2020-07-04)分别求出员工编号为10134,10200,10425,10434,10449,10459,10466,10532的任职情况,
字段包括员工编号(emp_no)，入职日期(from_date)，离职日期(to_date)，工作年限(years)，状态(status)。
select
DE.emp_no,DE.dept_no,DE.from_date,DE.to_date ,
if(year(DE.to_date)<=2020,
TIMESTAMPDIFF(year,DE.from_date,DE.to_date),
TIMESTAMPDIFF(year,DE.from_date,DATE_FORMAT(curdate(), '%Y-%m-%d'))) years,
if(year(DE.to_date)>=2020,'on','off') status
from
dept_emp DE
where DE.emp_no in (10134,10200,10425,10434,10449,10459,10466,10532);（非自己做的，第一个周五和最后一个周五不会确定）公司规定，新员工必须要进行1次岗前培训，培训是1个月2次，分别为每个月的第1个星期五和最后1个星期五，
请给以下员工编号为10019,10298,10684,11325,11697,11754,11829,12015,12299,12389,15368,16166的员工安排培训日期，
字段包括员工编号(emp_no)，入职日期(from_date)，培训日期(edu_date)。
SELECt
emp_no,
hire_date,
IF(hire_date <= first_friday,
first_friday,
IF(hire_date > first_friday
AND hire_date <= last_friday,
last_friday,
ADDDATE(last_friday, INTERVAL 7 DAY))) edu_date
FROM
(SELECt
emp_no,
hire_date,
first_friday,
CASE MONTH(ADDDATE(first_friday, 28))
WHEN mth THEN ADDDATE(first_friday, 28)
ELSE ADDDATE(first_friday, 21)
END last_friday
FROM
(SELECt
CASE SIGN(DAYOFWEEK(dy) - 6)
WHEN 0 THEN dy
WHEN - 1 THEN ADDDATE(dy, ABS(DAYOFWEEK(dy) - 6))
WHEN 1 THEN ADDDATE(dy, (7 - (DAYOFWEEK(dy) - 6)))
END first_friday,
mth,
emp_no,
hire_date
FROM
(SELECt
ADDDATE(ADDDATE(hire_date, - DAY(hire_date)), 1) dy,
MONTH(hire_date) mth,
e.emp_no,
e.hire_date
FROM
employees e
WHERe
e.emp_no IN ('10019' , '10298', '10684', '11325', '11697', '11754', '11829', '12015', '12299', '12389','15368','16166')) x) y) z;请对以下离职员(10008,10011,10021,10025,10028)工薪资变动情况做一下统计字段包括员工编号(emp_no)，开始日期(from_date)，结束日期(to_date)
select A.emp_no,from_date,max(to_date)
from
(select
emp_no,salary,from_date,to_date
from
salaries
where emp_no in (10008,10011,10021,10025,10028))A
group by emp_no;