SQL_牛客网60题

1.查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 1

2.查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

• 这道题考察了知识点LIMIT中，可以从XX开始数选择几个，所以本题，可以从第二个开始，选择一个

SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 2, 1

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

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

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)

• 简单的链接查询

SELECT 
    em.last_name,
    em.first_name,
    de.dept_no
FROM dept_emp de
JOIN employees em
ON de.emp_no = em.emp_no

5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)

SELECT 
    em.last_name,
    em.first_name,
    de.dept_no
FROM employees em
LEFT JOIN dept_emp de
ON em.emp_no = de.emp_no

6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)

• 因为观察数据，可以知道hire_date = from_date 就是初次入职的员工，条件语句
• 然后

SELECT 
    em.emp_no,
    sa.salary
FROM employees em
JOIN salaries sa
ON em.emp_no = sa.emp_no
WHERE em.hire_date == sa.from_date
ORDER BY emp_no DESC

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

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

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

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

9.获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

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

10.获取所有非manager的员工emp_no

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

11.获取所有员工当前的(dept_manager.to_date='9999-01-01')manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

• 本题的难点是每一个员工都有一个领导，因此不能用

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

12获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

SELECT 
    de.dept_no,
    de.emp_no,
    MAX(s.salary)
FROM dept_emp de
JOIN salaries 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

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

SELECT 
    title,
    COUNT(*) AS t
FROM titles
GROUP BY title
HAVING t >= 2

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

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

15查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

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

16统计出当前(titles.to_date='9999-01-01')各个title类型对应的员工当前(salaries.to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。

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

17获取当前（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

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

SELECT
    em.emp_no,
    MAX(salary) AS salary,
    em.last_name,
    em.first_name
FROM employees em
JOIN salaries sa
ON em.emp_no = sa.emp_no
WHERE salary NOT IN(
    SELECT
        MAX(salary)
    FROM salaries
    WHERE to_date='9999-01-01'
) AND sa.to_date='9999-01-01'

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

SELECT 
    em.last_name,
    em.first_name,
    de.dept_name
FROM employees em
LEFT JOIN dept_emp dp
ON em.emp_no = dp.emp_no
LEFT JOIN departments de
ON dp.dept_no = de.dept_no

20查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

• 不是很会，因为一个员工可能有很多条记录表，但是有多少是不知道的，因此考虑自连接的方式
• 上面思路是错误的，实际上是因为我们可以找到一个最大的值和最小的值，然相减即可

SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 
ORDER BY salary DESC LIMIT 1)
-
(SELECT salary FROM salaries WHERE emp_no = 10001 
ORDER BY salary LIMIT 1)
) AS growth