牛客《数据库SQL实战》练习总结(一)

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

难点：最晚，即排序后只选一条，可用LIMIT

SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 1

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

难点：倒数第三，还是可以用LIMIT。

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

注意，LIMIT的用法有两种：1. LIMIT m，表示返回不多于m行记录。2. LIMIT m-1,n-(m-1)，表示返回m到n的记录,可以理解为从m-1开始（不包括第m-1个）偏移多少个记录。例如要返回第三行，则可以是LIMIT 2,1；要返回第2到第4行，则可以是LIMIT 1,3

 

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

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'

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

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

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

难点：包括没有分配部门的员工，即有些员工在employees表但不在dept_emp表，可以用left join/right join

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

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

难点：入职时候，即薪水的from_date = hire_date

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

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

难点：这里认为薪水涨一次就会多一条记录，涨幅次数可以通过count函数来统计

注意：SQL语句执行顺序，GROUP BY -> 聚合函数(sum,count,max...) -> HAVING，执行count的时候已经group by了，所以count()所有记录就ok了。

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

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

难点：相同薪水只显示一次，DISTINCT

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

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

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

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

难点：非manager。非manager的员工的不在dept_manager表，可以通过left join/right join将两表关联，再选出dept_no为空的记录

SELECT emp_no
FROM (SELECT * FROM employees AS e 
LEFT JOIN dept_manager AS d
ON e.emp_no = d.emp_no)
WHERE dept_no is NULL

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

难点：manager不能是自己。可以通过员工号不能等于部门经理表的员工号来筛选

SELECT e.emp_no,m.emp_no AS 'manager_no'
FROM dept_emp AS e,dept_manager AS m
WHERE e.dept_no = m.dept_no
AND e.emp_no<>m.emp_no
AND m.to_date = '9999-01-01'

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

难点：不同部门薪水最高的员工，group by 再 max()

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

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

难点：分组后每组的个数。与第7题类似，group by后再统计每组的记录数，只选取记录数大于等于2的。由于已分组，这里的count(emp_no)等价于count(*)，但有空值的时候不能用，count(*)会把空值统计进去。

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

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

难点：计数的时候忽略重复的emp_no。要对重复值进行筛选，故这里count()的一定要为emp_no不能是*了。去重可以用distinct

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

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

难点：判断奇数。取余不为零则为奇数

SELECT * FROM employees
WHERE last_name <> 'Mary'
AND emp_no%2 <> 0
ORDER BY hire_date DESC

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

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

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

难点：第二多。LIMIT

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

难点：选第二多的，但不能用order by排序。

有两种思路：(1).选出最多的，排除掉(NOT IN) 。(2).选出最多的，然后再选比最多的要小的。

（1）

SELECT e.emp_no,MAX(salary),last_name,first_name
FROM employees AS e,salaries AS s
WHERE e.emp_no = s.emp_no
AND to_date = '9999-01-01'
AND salary Not IN (SELECT MAX(salary) FROM salaries AS s
WHERE s.to_date = '9999-01-01')

（2） 

SELECT e.emp_no,MAX(salary),last_name,first_name
FROM employees AS e,salaries AS s
WHERE e.emp_no = s.emp_no
AND to_date = '9999-01-01'
AND s.salary < (SELECT MAX(salary) FROM salaries AS s
WHERE s.to_date = '9999-01-01')

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

难点：多表联结还要用left join。先把departments和dept_emp两个表联结(where/join)成表t，再将表employeesleft join 表t获得没有分配部门的员工。

SELECT e.last_name,e.first_name,t.dept_name
FROM employees e LEFT JOIN 
(SELECT * FROM departments d,dept_emp de
WHERE d.dept_no = de.dept_no) t
ON e.emp_no = t.emp_no

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

难点：查出第一天入职和最后一天的薪水。可以用order by，也可以用 min，max

SELECT (a.salary - b.salary) AS growth
FROM (SELECT salary FROM salaries WHERE emp_no = 10001 
ORDER BY to_date DESC LIMIT 1 ) AS a,
(SELECT salary FROM salaries WHERE emp_no = 10001 
      ORDER BY to_date LIMIT 1 ) AS b

21.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

难点：要将所有员工的入职薪水建一个表，当前薪水一个表，最后再相减。入职则是from_date=hire_date，当前则是to_date='9999-01-01'

SELECT now.emp_no,(now.salary - old.salary) AS growth
FROM (SELECT s.emp_no,s.salary FROM employees AS e INNER JOIN salaries AS s 
      ON e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS old,
(SELECT s.emp_no,s.salary FROM employees AS e INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS now
WHERE old.emp_no = now.emp_no
ORDER BY growth