打卡第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
匯總各個(gè)部門當(dāng)前員工的title類型的分配數(shù)目，即結(jié)果給出部門編號(hào)dept_no、dept_name腺劣、其部門下所有的當(dāng)前(dept_emp.to_date = '9999-01-01')員工的當(dāng)前(titles.to_date = '9999-01-01')title以及該類型title對應(yīng)的數(shù)目count
(注：因?yàn)閱T工可能有離職屏富，所有dept_emp里面to_date不為'9999-01-01'就已經(jīng)離職了，不計(jì)入統(tǒng)計(jì)腔丧，而且員工可能有晉升惩阶，所以如果titles.to_date 不為 '9999-01-01'辐赞，那么這個(gè)可能是員工之前的職位信息括享，也不計(jì)入統(tǒng)計(jì))
-- 整理邏輯
-- 使用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
給出每個(gè)員工每年薪水漲幅超過5000的員工編號(hào)emp_no搂根、薪水變更開始日期from_date以及薪水漲幅值salary_growth，并按照salary_growth逆序排列铃辖。
提示：在sqlite中獲取datetime時(shí)間對應(yīng)的年份函數(shù)為strftime('%Y', to_date)
(數(shù)據(jù)保證每個(gè)員工的每條薪水記錄to_date-from_date=1年剩愧，而且同一員工的下一條薪水記錄from_data=上一條薪水記錄的to_data)
-- 學(xué)會(huì)使用表的自連接
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'
