数据库SQL实战-答案解析(15-20)

数据库sql实战-答案解析15-20">数据库SQL实战-答案解析(15-20)

15、统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

SELECT title , AVG(salary) AS avg
FROM salaries s,titles t
WHERE s.emp_no = t.emp_no AND s.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
GROUP BY t.title;

16、获取当前（to_date=’9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary from salaries where to_date = '9999-01-01' order by salary desc limit 1,1;

17、查找当前薪水(to_date=’9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

主要思想为多层SELECT嵌套与MAX()函数结合

1、先利用MAX()函数找出salaries中当前薪水最高者

s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

3、在以上限制条件下找薪水最高者，即为所有员工薪水的次高者

SELECT em.last_name, em.first_name, dp.dept_name
FROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no) LEFT JOIN departments AS dp ON de.dept_no = dp.dept_no

19、查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

1、先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录

2、再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅，最后用别名growth代替

SELECT (MAX(salary)-MIN(salary)) AS growth 
FROM salaries WHERE emp_no = '10001'

SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth

20、查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_noy以及其对应的薪水涨幅growth，并按照growth进行升序

1.找出每个员工当前工资 

     select e.emp_no,s.salary as sTo  
     from employees as e  
     left join salaries as s on e.emp_no=s.emp_no where
    s.to_date='9999-01-01'  

  2.找出每个员工入职时的工资 

     select e.emp_no,s.salary as sHire from employees as e  
     left join salaries as s on e.emp_no=s.emp_no and
    s.from_date=e.hire_date  

  3.结合找出growth 

     select t1.emp_no,(t1.sTo-t2.sHire) as growth from  
     (select e.emp_no,s.salary as sTo from employees as e  
     left join salaries as s on e.emp_no=s.emp_no where
    s.to_date='9999-01-01') as t1 join  
     (select e.emp_no,s.salary as sHire from employees as e  
     left join salaries as s on e.emp_no=s.emp_no and
    s.from_date=e.hire_date) as t2 on t1.emp_no=t2.emp_no  
     order by growth asc;