牛客-数据库SQL实战

本篇博客用于记录在牛客网做的数据库SQL实战中的错题或重要的题。

• 查找最晚入职员工的所有信息

SELECT
	*
FROM
	employees
WHERE
	hire_date = (
		SELECT
			MAX(hire_date)
		FROM
			employees
	);

• 查找入职员工时间排名倒数第三的员工所有信息

SELECT
	*
FROM
	employees
WHERE
	hire_date = (
		SELECT DISTINCT
			hire_date
		FROM
			employees
		ORDER BY
			hire_date DESC
		LIMIT 2,
		1
	);

• 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

SELECT
	s.*, dm.dept_no
FROM
	salaries AS s
INNER JOIN dept_manager AS dm ON dm.emp_no = s.emp_no
WHERE
	s.to_date = '9999-01-01'
AND dm.to_date = '9999-01-01';

• 查找所有已经分配部门的员工的last_name和first_name

SELECT
	e.last_name,
	e.first_name,
	de.dept_no
FROM
	employees AS e
INNER JOIN dept_emp AS de ON de.emp_no = e.emp_no; 

• 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

SELECT
    e.last_name,
    e.first_name,
    de.dept_no
FROM
    employees AS e
LEFT JOIN dept_emp AS de ON de.emp_no = e.emp_no;

• 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

SELECT
	s.emp_no,
	s.salary
FROM
	employees AS e
INNER JOIN salaries AS s ON s.emp_no = e.emp_no
WHERE
	e.hire_date = s.from_date
ORDER BY
	e.emp_no DESC;

• 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

SELECT
	temp.emp_no,
	temp.t
FROM
	(
		SELECT
			emp_no,
			COUNT(salary) AS t
		FROM
			salaries
		GROUP BY
			emp_no
	) AS temp
WHERE
	temp.t > 15;

• 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

SELECT DISTINCT
	salary
FROM
	salaries
WHERE
	to_date = '9999-01-01'
ORDER BY
	salary DESC;

SELECT
	salary
FROM
	salaries
WHERE
	to_date = '9999-01-01'
GROUP BY
	salary
ORDER BY
	salary DESC;

• 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

SELECT
	dm.dept_no,
	dm.emp_no,
	s.salary
FROM
	dept_manager AS dm
INNER JOIN salaries AS s ON s.emp_no = dm.emp_no
WHERE
	dm.to_date = '9999-01-01'
AND s.to_date = '9999-01-01';

• 获取所有非manager的员工emp_no

SELECT
	emp_no
FROM
	employees
WHERE
	emp_no NOT IN (
		SELECT
			emp_no
		FROM
			dept_manager
	);

SELECT
	e.emp_no
FROM
	employees AS e
LEFT JOIN dept_manager AS dm ON dm.emp_no = e.emp_no
WHERE
	dm.dept_no IS NULL;

• 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。
  结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

SELECT
	de.emp_no,
	dm.emp_no AS manager_no
FROM
	dept_emp AS de
INNER JOIN dept_manager AS dm ON de.dept_no = dm.dept_no
WHERE
	de.to_date = '9999-01-01'
AND dm.to_date = '9999-01-01'
AND de.emp_no != dm.emp_no;

• 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

SELECT
	de.dept_no,
	de.emp_no,
	MAX(s.salary) AS salary
FROM
	dept_emp AS de
INNER JOIN salaries AS s ON de.emp_no = s.emp_no
WHERE
	de.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP BY
	de.dept_no;

• 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

SELECT
	title,
	count(0) AS t
FROM
	titles
GROUP BY
	title
HAVING
	t >= 2;

• 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t，注意对于重复的emp_no进行忽略。

SELECT
	title,
	count(DISTINCT emp_no) AS t
FROM
	titles
GROUP BY
	title
HAVING
	t >= 2;

• 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

SELECT
	*
FROM
	employees
WHERE
	emp_no % 2 != 0
AND last_name != 'Mary'
ORDER BY
	hire_date DESC;

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

SELECT
	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;

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

SELECT
	s.emp_no,
	s.salary
FROM
	salaries AS s
INNER JOIN (
	SELECT
		salary
	FROM
		salaries
	WHERE
		to_date = '9999-01-01'
	GROUP BY
		salary
	ORDER BY
		salary DESC
	LIMIT 1,
	1
) AS temp ON temp.salary = s.salary;

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

SELECT
	s.emp_no,
	s.salary,
	e.last_name,
	e.first_name
FROM
	salaries AS s
INNER JOIN employees AS e ON s.emp_no = e.emp_no
WHERE
	s.salary = (
		SELECT
			max(salary)
		FROM
			salaries
		WHERE
			salary != (
				SELECT
					max(salary)
				FROM
					salaries
				WHERE
					salary
			)
	);

• 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

SELECT
	e.last_name,
	e.first_name,
	d.dept_name
FROM
	employees AS e
LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no
LEFT JOIN departments AS d ON de.dept_no = d.dept_no;

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

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