欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-10-18 22:21:21
数据库sql实战-答案解析15-20">数据库SQL实战-答案解析(15-20) 15、统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工...

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;