欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-11 08:13:27
...

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