MySQL——练习1-21

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

SELECT
	emp_no,
	birth_date,
	first_name,
	last_name,
	gender,
	hire_date 
FROM
	employees 
ORDER BY
	hire_date DESC 
	LIMIT 1;

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

#假如同一天三个人入职 
# 排序入职时间 取倒数第三
# 标量子查询
SELECT
	* 
FROM
	employees 
WHERE 
	hire_date = ( SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2, 1 )

SQL3 查找当前薪水详情以及部门编号dept_no

SELECT
	s.emp_no,
	salary,
	from_date,
	m.to_date,
	dept_no 
FROM
	salaries s,
	dept_manager m 
WHERE
	s.emp_no = m.emp_no
	ORDER BY s.emp_no asc	

SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no

SELECT
	last_name,
	first_name,
	d.dept_no 
FROM
	employees e,
	dept_emp d 
WHERE
	e.emp_no = d.emp_no	

SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no

SELECT
	last_name,
	first_name,
	dept_no 
FROM
	employees e
LEFT JOIN dept_emp de on e.emp_no = de.emp_no

SQL7 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

SELECT
	emp_no,
	COUNT( * ) AS t 
FROM
	salaries 
GROUP BY
	emp_no 
HAVING
	t > 15

SQL8 找出所有员工当前薪水salary情况

SELECT DISTINCT
	salary 
FROM
	salaries 
ORDER BY
	salary DESC;

SQL10 获取所有非manager的员工emp_no

SELECT
	e.emp_no 
FROM
	employees e
	LEFT JOIN dept_manager m ON e.emp_no = m.emp_no
	WHERE m.dept_no is NULL

SQL11 获取所有员工当前的manager

SELECT
	em.emp_no,
	mg.emp_no AS manager 
FROM
	dept_emp em,
	dept_manager mg 
WHERE
	em.dept_no = mg.dept_no 
	AND em.emp_no <> mg.emp_no

SQL12 获取每个部门中当前员工薪水最高的相关信息

# 非等值查询  子查询 
SELECT
	ma.dept_no,
	de.emp_no,
	ma.sa 
FROM
	salaries sa
	INNER JOIN dept_emp de ON sa.emp_no = de.emp_no
	JOIN (
SELECT
	dept_no,
	MAX( salary ) sa 
FROM
	salaries sa
	INNER JOIN dept_emp de ON sa.emp_no = de.emp_no 
GROUP BY
	dept_no 
	) ma ON ma.dept_no = de.dept_no 
	AND ma.sa = sa.salary 
ORDER BY
	ma.dept_no ASC 

SQL15 查找employees表emp_no与last_name的员工信息

# 请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列，以上例子查询结果如下:
SELECT
	* 
FROM
	employees 
WHERE
	emp_no % 2 = 1 
	AND last_name <> 'Mary' 
ORDER BY
	hire_date DESC;    

SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资

SELECT
	title,
	AVG( salary ) 
FROM
	titles tl,
	salaries sa 
WHERE
	tl.emp_no = sa.emp_no 
GROUP BY
	title
	ORDER BY 	AVG( salary )  asc   

SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

# 先查出第二多薪水
SELECT
	emp_no,
	salary 
FROM
	salaries 
WHERE
salary = ( SELECT DISTINCT salary FROM salaries ORDER BY salary DESC LIMIT 1, 1 )  

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

# ① 先排除掉最大的 再去第二个最大的 
SELECT
	es.emp_no,
	salary,
	last_name,
	first_name 
FROM
	employees es,
	salaries sa 
WHERE
	es.emp_no = sa.emp_no 
	AND salary = ( SELECT MAX( salary ) FROM salaries WHERE salary <> ( SELECT MAX( salary ) FROM salaries ) )

SQL19 查找所有员工的last_name和first_name以及对应的dept_name

# employees 作为主表 
SELECT
	last_name,
	first_name,
	dept_name 
FROM
	employees es
	LEFT JOIN dept_emp de ON de.emp_no = es.emp_no
	LEFT JOIN departments dm ON de.dept_no = dm.dept_no

SQL21 查找在职员工自入职以来的薪水涨幅情况

# 得到他最晚的在职时间 减掉最早入职时间的薪资  [这题有问题 指的是牛客] 
SELECT
	b.emp_no,
	( b.salary - a.salary ) AS growth 
FROM
	(
SELECT
	e.emp_no,
	s.salary 
FROM
	employees e
	LEFT JOIN salaries s ON e.emp_no = s.emp_no 
	AND e.hire_date = s.from_date 
	) a -- 入职工资表
	INNER JOIN (
SELECT
	e.emp_no,
	s.salary 
FROM
	employees e
	LEFT JOIN salaries s ON e.emp_no = s.emp_no 
WHERE
	s.to_date = '9999-01-01' 
	) b -- 现在工资表
	ON a.emp_no = b.emp_no 
ORDER BY
	growth