数据库SQL实战-答案解析(15-20)
程序员文章站
2022-10-18 22:21:21
数据库sql实战-答案解析15-20">数据库SQL实战-答案解析(15-20)
15、统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工...
数据库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;
推荐阅读
-
数据库中的sql完整性约束语句解析
-
数据库SQL实战题:获取员工其当前的薪水比其manager当前薪水还高的相关信息(教程)
-
数据库SQL实战:从titles表获取按照title进行分组,注意对于重复的emp_no进行忽略(题解)
-
ibatis读取数据库链接和解析sql教程
-
数据库SQL实战:查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth(教程)
-
关系数据库标准语言SQL的在线测试题及答案
-
解析PL/SQL Developer导入导出数据库的方法以及说明
-
数据库SQL实战之查找所有员工入职时候的薪水情况(题解)
-
数据库SQL实战:获取所有部门中当前员工薪水最高的相关信息(教程)
-
数据库SQL实战题:汇总各个部门当前员工的title类型的分配数目(教程)