牛客数据库SQL刷题记录 D-5

牛客数据库SQL刷题记录 D-5

1. 题目：

• 题目描述：统计出当前各个title类型对应的员工当前薪水对应的平均工资

统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

• 输出：

title	avg
Engineer	94409.0
Senior Engineer	69009.2
Senior Staff	91381.0
Staff	72527.0

• 代码：

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

• 思路：
  最后要用GROUP BY 分组，我忘了这个就一直通不过，尴尬…

2.题目：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

• 题目描述：

获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no
以及其对应的薪水salary
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

• 输出：

emp_no	salary
10009	94409

• 代码：

SELECT emp_no, salary
FROM salaries
WHERE to_date='9999-01-01' 
Order BY salary DESC LIMIT 1, 1

• 思路：
  还是LIMIT 的使用方法，前面讲过了

3. 题目：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by

• 题目描述：

查找当前薪水(to_date='9999-01-01')排名第二多的员工
编号emp_no、薪水salary、last_name
以及first_name，不准使用order by
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

• 输出：

emp_no	salary	last_name	first_name
10009	94409	Peac	Sumant

• 代码：

SELECT e.emp_no, MAX(s.salary), e.last_name, e.first_name
FROM employees as e
INNER JOIN salaries as s
ON e.emp_no=s.emp_no
WHERE s.to_date='9999-01-01'
AND s.salary NOT IN 
(
    SELECT MAX(salary) from salaries
    WHERE to_date='9999-01-01'
)

• 思路：
  由于不能使用ORDER BY，所以先用MAX()函数找出工资最高的，
  然后用NOT IN排除工资最高的，剩下的工资中最高的就是第二高的。这里利用了SELECT和MAX()函数的嵌套。

4. 题目：查找所有员工的last_name和first_name以及对应的dept_name

• 题目描述：

查找所有员工的last_name和first_name以及对应的dept_name，
也包括暂时没有分配部门的员工
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

• 输出：

last_name	first_name	dept_name
Facello	Georgi	Marketing
省略	省略	省略
Sluis	Mary	NULL

• 代码：

SELECT e.last_name, e.first_name, d.dept_name
FROM employees as e
LEFT JOIN dept_emp as dept
ON e.emp_no=dept.emp_no
LEFT JOIN departments as d
ON dept.dept_no=d.dept_no

• 思路：
  这个题要连接3个表。第一次用LEFT JOIN连接dept_emp表和employees表，得到所有员工的last_name，first_name和emp_no；第二次用LEFT JOIN连接之前产生的新表和departments，得到所有员工的dept_no，包括没有分配部门的员工。

4. 题目：查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

• 题目描述：

查找员工编号emp_no为10001其自入职以来的薪水salary的
涨幅值growth
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

• 输出：

growth
28841

• 代码

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

• 代码：
  将to_date降序排列，得到最近一次的工资记录；
  将to_date升序排列，得到第一次的工资记录；
  两者相减，即为增长growth。
  如果选择用MAX()函数和MIN()函数分别找出最高工资和最低工资，相减得到growth。有可能存在最后一次工资记录是降薪，导致不能完全满足。