练习SQL利器，牛客网SQL实战题库，1~8题

牛客网SQL实战网址：https://www.nowcoder.com/ta/sql
持续更新——记录自己在牛客网SQL的做题过程

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

题目描述：
查找最晚入职员工的所有信息
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));

应该考虑的问题：
①最晚入职的当天未必就一个人，也许有多人，使用排序并限制得只能取得指定数量的结果

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

注：日期最大的就是最晚的，日期较早就是较小。

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

题目描述：
查找入职员工时间排名倒数第三的员工所有信息
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));

应该考虑的问题：
①取的是日期倒数第三的人，不是倒数第三的人

SELECT emp_no, birth_date, first_name, last_name, gender, hire_date
FROM employees
WHERE hire_date=(
                          SELECT DISTINCT hire_date
                          FROM employees
                          ORDER BY hire_date DESC
                          LIMIT 2,1)；

注：
①牛客网网友EricZeng的严谨写法，可以学习。
②LIMIT是从0开始计数。

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

题目描述：
查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_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));
应该考虑的问题：
①薪水表是按年发放的，所以要过滤掉以前的薪水，只保留现在还在的当前领导的薪水

SELECT s.*,d.dept_no FROM salaries AS s,dept_manager AS d
WHERE s.emp_no = d.emp_no 
AND s.to_date='9999-01-01'
AND d.to_date='9999-01-01'

注：
①用AND可以加条件

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

题目描述：
查找所有已经分配部门的员工的last_name和first_name
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));

应该考虑的问题：
①因为是查找已分配部门的员工，所以dept_no不应该为空，应该使用内连接。

SELECT emp.last_name,emp.first_name,dept.dept_no 
FROM dept_emp dept,employees emp
WHERE emp.emp_no = dept.emp_no

注：表连接。

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

题目描述：
查找所有员工的last_name和first_name以及对应部门编号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));

应该考虑的问题：
①没有具体分配的员工也要展示，也就是要保证employees表的完整
②内连接已经不能满足需求，需要用左连接或者又连接

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

注：
①内连接（INNER JOIN）两边表任何一边缺失都不显示。
②左连接（LEFT JOIN)，右边表可以无对应数据。
③右连接（RIGHT JOIN），左边表可以无对应数据。

6. 查找所有员工入职时候的薪水情况

题目描述：
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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));
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));

应该考虑的问题：
①因为员工会有多次涨薪，所以salaries.emp_no 不唯一，这时我们就应该确定具体确定这个薪水的时间，也就是这个入职时间hire_date

SELECT e.emp_no, s.salary FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC

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

题目描述:
查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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));

应该考虑的问题：
①需要利用分组函数GROUP BY对emp_no进行分组。
②需要利用分组限定条件限定t值

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

注：①好像是先选择列和计算，然后再进行分组和再一次计算，也就是按照语句的顺序进行。

8.找出所有员工当前具体的薪水salary情况

题目描述：
找出所有员工当前(to_date='9999-01-01')具体的薪水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));

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

注：
①大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。
②在不同记录数较小时，count group by性能普遍高于count distinct，尤其对于text类型表现的更明显。而对于不同记录数较大的场景，count group by性能反而低于直接count distinct（牛客网网友—啊啥水果的总结）