打卡第7天 -- 3S2A1P : 三道sql,2道算法,1道简答

sql-1

https://www.nowcoder.com/practice/4bcb6a7d3e39423291d2f7bdbbff87f8?tpId=82&&tqId=29778&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = '9999-01-01')员工的当前(titles.to_date = '9999-01-01')title以及该类型title对应的数目count

(注：因为员工可能有离职，所有dept_emp里面to_date不为'9999-01-01'就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 '9999-01-01'，那么这个可能是员工之前的职位信息，也不计入统计)

-- 整理逻辑
-- 使用join
-- 使用 group by
-- 使用 count(*)

select dep.dept_no,dep.dept_name,t.title,count(*) from 
titles t inner join dept_emp d on t.to_date='9999-01-01' and t.emp_no=d.emp_no 
inner join departments dep on d.to_date='9999-01-01' and d.dept_no=dep.dept_no
group by dep.dept_no,t.title

sql-2

https://www.nowcoder.com/practice/eb9b13e5257744db8265aa73de04fd44?tpId=82&&tqId=29779&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

-- 学会使用表的自连接

select s1.emp_no,
       s1.from_date,
       s1.salary-s2.salary as salary_growth
from salaries s1 inner join salaries s2
on s1.emp_no=s2.emp_no
and  s1.from_date=s2.to_date
and s1.salary-s2.salary>5000
order by salary_growth desc

select s1.emp_no,
       s1.from_date,
       s1.salary-s2.salary as salary_growth
from salaries s1 inner join salaries s2
on s1.emp_no=s2.emp_no
and (strftime('%Y', s1.to_date)-strftime('%Y', s2.to_date)=1 or
    strftime('%Y', s1.from_date)-strftime('%Y', s2.from_date)=1)
and s1.salary-s2.salary>5000
order by salary_growth desc

sql-3

https://www.nowcoder.com/practice/859f28f43496404886a77600ea68ef59?tpId=82&&tqId=29811&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

-- 掌握 update 语法
update titles_test set from_date='2001-01-01' , to_date=null where to_date='9999-01-01'