牛客网SQL 练习 一
SQL195 查找最晚入职员工的所有信息
描述
有一个员工employees表简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 10004 1954-05-01 Christian Koblick M 1986-12-01 请你查找employees里最晚入职员工的所有信息，以上例子输出如下:
emp_no birth_date first_name last_name gender hire_date 10004 1954-05-01 Christian Koblick M 1986-12-01
从 "employees" 表中选择所有的数据，并按照"hire_date"列进行降序排序。然后，使用"limit 1"限制结果集只返回最顶部的一行数据。
select * from employees
order by hire_date desc
limit 1 SQL196 查找入职员工时间排名倒数第三的员工所有信息
描述
有一个员工employees表简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 10004 1954-05-01 Christian Koblick M 1986-12-01 请你查找employees里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 注意：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。
从"employees"表中选择所有的数据，并使用子查询来查找第二个最近的"hire_date"。子查询"select distinct hire_date from employees order by hire_date desc limit 2,1"首先从"employees"表中选择所有不重复的"hire_date"，然后按降序排序并使用"limit 2,1"来获取第二个结果。之后，外部查询使用"where hire_date ="将结果与"employees"表中的"hire_date"进行比较，从而获取与第二个最近的"hire_date"相等的员工记录。
select * from employees
where hire_date = (select distinct hire_date from employees order by hire_date desc limit 2,1)SQL197 查找当前薪水详情以及部门编号dept no
描述
有一个全部员工的薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 2002-06-22 9999-01-01 10002 72527 2001-08-02 9999-01-01 10003 43311 2001-12-01 9999-01-01 有一个各个部门的领导表dept_manager简况如下:
dept_no emp_no to_date d001 10001 9999-01-01 d002 10003 9999-01-01 请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:
emp_no salary from_date to_date dept_no 10001 88958 2002-06-22 9999-01-01 d001 10003 43311 2001-12-01 9999-01-01 d002
查询从"dept_manager"表开始，使用"left join"将"dept_manager"表与"salaries"表进行连接。连接条件是"dept_manager"表中的"emp_no"与"salaries"表中的"emp_no"相等。
通过左连接，将会返回所有在"dept_manager"表中的记录，无论是否有与之匹配的薪水记录。如果在"salaries"表中没有对应的记录，那么相关列将会显示为NULL。
最终，查询选择了需要的列，包括"dept_manager"表中的"emp_no"和"dept_no"列，以及"salaries"表中的"salary，from_date，to_date"列。
select d.emp_no,s.salary,s.from_date,s.to_date,d.dept_no
from dept_manager d left join salaries s on d.emp_no = s.emp_noSQL198 查找所有已经分配部门的员工的lastname和firstname以及dept no
描述
有一个员工表，employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 10004 1954-05-01 Christian Koblick M 1986-12-01 有一个部门表，dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d002 1989-08-03 9999-01-01 请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示，以上例子如下:
last_name first_name dept_no Facello Georgi d001 Simmel Bezalel d002
和上一题差不多
select e.last_name,e.first_name,d.dept_no
from dept_emp d left join
employees e on d.emp_no = e.emp_noSQL199 查找所有员工的last name和first name以及对应部门编号dept no
描述
有一个员工表，employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 10004 1954-05-01 Christian Koblick M 1986-12-01 有一个部门表，dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d002 1989-08-03 9999-01-01 请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，以上例子如下:
last_name first_name dept_no Facello Georgi d001 Simmel Bezalel d002 Bamford Parto NULL Koblick Chirstian NULL
select last_name,first_name,dept_no from
employees e left join dept_emp d on
e.emp_no = d.emp_no
SQL201 查找薪水记录超过15条的员工号emp no以及其对应的记录次数t
描述
有一个薪水表，salaries简况如下:
emp_no salary from_date to_date 10001 60117 1986-06-26 1987-06-26 10001 62102
1987-06-26 1988-06-25 10001 66074 1988-06-25 1989-06-25 10001 66596 1989-06-25 1990-06-25 10001 66961 1990-06-25 1991-06-25 10001 71046 1991-06-25 1992-06-24 10001 74333 1992-06-24 1993-06-24 10001 75286 1993-06-24 1994-06-24 10001 75994 1994-06-24 1995-06-24 10001 76884 1995-06-24 1996-06-23 10001 80013 1996-06-23 1997-06-23 10001 81025 1997-06-23 1998-06-23 10001 81097 1998-06-23 1999-06-23 10001 84917 1999-06-23 2000-06-22 10001 85112 2000-06-22 2001-06-22 10001 85097 2001-06-22 2002-06-22 10002 72527 1996-08-03 1997-08-03 请你查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t，以上例子输出如下:
emp_no t 10001 16
查询从"salaries"表开始，使用"group by"子句按照"emp_no"列分组。这意味着相同的"emp_no"值将会被聚合到一起。
然后使用"count(*)"函数统计每个"emp_no"分组中的记录数量，并且将结果命名为"t"。
最后，在"having"子句中，过滤掉只有15条或更少薪水记录的员工。只有满足"t>15"条件的分组（即记录数量大于15）才会被包含在查询结果中。
select emp_no,count(*) as t
from salaries group by emp_no
having t>15SQL202 找出所有员工当前薪水salary情况
描述
有一个薪水表，salaries简况如下:
emp_no salary from_date to_date 10001 72527 2002-06-22 9999-01-01 10002 72527 2001-08-02 9999-01-01 10003 43311 2001-12-01 9999-01-01 请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示，以上例子输出如下:
salary 72527 43311
查询从"salaries"表开始，使用"distinct"关键字来去除重复的薪水值，确保查询结果中只包含唯一的薪水值。
然后使用"order by"子句按照薪水值进行降序排序。"desc"关键字表示降序排序，从高到低排列。
select distinct salary
from salaries
order by salary descSQL204 获取所有非manager的员工emp no
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 有一个部门领导表dept_manager简况如下:
dept_no emp_no from_date to_date d001 10002 1996-08-03 9999-01-01 d002 10003 1990-08-05 9999-01-01 请你找出所有非部门领导的员工emp_no，以上例子输出:
emp_no 10001
查询首先从"employees"表开始，使用"where"子句来筛选符合条件的员工。
然后使用子查询子句"select emp_no from dept_manager"来获取所有的部门经理的员工编号。
通过使用"not in"关键字，查询筛选出不在部门经理员工编号列表中的员工编号。
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager
)SQL205 获取所有员工当前的manager
描述
有一个员工表dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 10003 d002 1995-12-03 9999-01-01 第一行表示为员工编号为10001的部门是d001部门。
有一个部门经理表dept_manager简况如下:
dept_no emp_no from_date to_date d001 10002 1996-08-03 9999-01-01 d002 10003 1990-08-05 9999-01-01 第一行表示为d001部门的经理是编号为10002的员工。
获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下:
emp_no manager 10001 10002
查询使用左连接（left join）将"dept_emp"表和"dept_manager"表连接起来。
连接条件是"dept_no"字段相等。
然后使用"where"子句来筛选出在"dept_emp"表中的员工编号与"dept_manager"表中的员工编号不匹配的记录。
select de.emp_no,dm.emp_no
from dept_emp de
left join dept_manager dm on
de.dept_no = dm.dept_no
where de.emp_no != dm.emp_noSQL206 获取每个部门中当前员工薪水最高的相关信息
描述
有一个员工表dept_emp简况如下:
emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 10003 d002 1996-08-03 9999-01-01 有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 2002-06-22 9999-01-01 10002 72527 2001-08-02 9999-01-01 10003 92527 2001-08-02 9999-01-01 获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:
dept_no emp_no maxSalary d001 10001 88958 d002 10003 92527
首先，我们使用dept_emp表和salaries表进行内连接（join），将两个表根据emp_no（员工编号）进行匹配。
然后，在内连接的结果集中，我们使用子查询来找到每个部门中的最高工资。子查询首先将dept_emp表和salaries表再次进行内连接，并根据emp_no匹配。然后使用GROUP BY语句按照dept_no进行分组，并使用MAX()函数找到每个部门的最高工资。
最后，我们将子查询的结果与外部查询的结果进行匹配，只选择工资等于最高工资的记录。并且选取dept_no、de.emp_no和salary作为结果。
select dept_no,de.emp_no,salary as maxSalary from
dept_emp de join salaries s on de.emp_no = s.emp_no
where (dept_no,salary) in(
select dept_no,max(salary) as maxSalary
from dept_emp de join salaries s on de.emp_no = s.emp_no
group by dept_no)
order by dept_noSQL209 查找employees表emp_no与last name的员工信息
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Bezalel Mary M 1986-08-28 10004 1954-05-01 Christian Koblick M 1986-12-01 10005 1953-11-07 Mary Sluis F 1990-01-22 请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列，以上例子查询结果如下:
emp_no birth_date first_name last_name gender hire_date 10005 1953-11-07 Mary Sluis F 1990-01-22 10001 1953-09-02 Georgi Facello M 1986-06-26
首先，我们使用employees表进行查询。
然后，在查询结果中，使用WHERE子句来筛选last_name不等于'Mary'的记录和emp_no是奇数的记录。条件last_name != 'Mary'用于排除姓为'Mary'的记录，emp_no%2=1用于过滤奇数编号的员工。
接下来，我们使用ORDER BY子句按照hire_date降序对结果进行排序。这样可以将最新入职的员工排在前面。
最后，我们从employees表中选择满足条件的记录，并按照设置的排序规则进行排序。
select * from employees
where last_name != 'Mary' and emp_no%2=1
order by hire_date desc SQL210 统计出当前各个title类型对应的员工当前薪水对应的平均工资
描述
有一个员工职称表titles简况如下:
emp_no title from_date to_date 10001 Senior Engineer 1986-06-26 9999-01-01 10003 Senior Engineer 2001-12-01 9999-01-01 10004 Senior Engineer 1995-12-01 9999-01-01 10006 Senior Engineer 2001-08-02 9999-01-01 10007 Senior Staff 1996-02-11 9999-01-01 有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 1986-06-26 9999-01-01 10003 43311 2001-12-01 9999-01-01 10004 74057 1995-12-01 9999-01-01 10006 43311 2001-08-02 9999-01-01 10007 88070 2002-02-07 9999-01-01 请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序，以上例子输出如下:
title avg(s.salary) Senior Engineer 62409.2500 Senior Staff 88070.0000
-
SELECT title, avg(s.salary)：选择title列和s.salary的平均值作为结果集的列。 -
FROM titles t JOIN salaries s ON t.emp_no = s.emp_no：将titles表和salaries表通过emp_no列进行连接。这样可以将职位和工资信息关联起来。 -
GROUP BY title：按照title列进行分组，这样可以计算每个职位的平均工资。 -
ORDER BY avg(s.salary)：按照平均工资的升序对结果进行排序，即按照工资从低到高的顺序排列
select title,avg(s.salary)
from titles t join salaries s on t.emp_no = s.emp_no
group by title
order by avg(s.salary)
SQL211 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
描述
有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 2002-06-22 9999-01-01 10002 72527 2001-08-02 9999-01-01 10003 43311 2001-12-01 9999-01-01 请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，
若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。
emp_no salary 10002 72527
-
首先，执行子查询
SELECT salary FROM salaries ORDER BY salary DESC LIMIT 1,1，这个子查询会返回按照工资降序排序后的第二个工资金额。 -
然后，将返回的工资金额作为条件，执行主查询
SELECT emp_no, salary FROM salaries WHERE salary = (子查询)。 -
主查询会根据子查询返回的工资金额筛选出与之匹配的员工信息，即工资为第二高的员工。
-
最后，返回结果集包含工资为第二高的员工的员工号（emp_no）和对应的工资金额（salary）
select emp_no,salary from salaries
where salary =
(select salary from salaries
order by salary desc limit 1,1)SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name gender hire_date 10001 1953-09-02 Georgi Facello M 1986-06-26 10002 1964-06-02 Bezalel Simmel F 1985-11-21 10003 1959-12-03 Parto Bamford M 1986-08-28 10004 1954-05-01 Chirstian Koblick M 1986-12-01 有一个薪水表salaries简况如下:
emp_no salary from_date to_date 10001 88958 2002-06-26 9999-01-01 10002 72527 2001-08-02 9999-01-01 10003 43311 2001-12-01 9999-01-01 10004 74057 2001-11-27 9999-01-01 请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:
（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)
emp_no salary last_name first_name 10004 74057 Koblick Chirstian
-
首先，执行子查询
(SELECT max(salary) as salary FROM salaries WHERE salary != (SELECT max(salary) as salary FROM salaries))。-
内层子查询
(SELECT max(salary) as salary FROM salaries)会返回工资表中的最高工资金额。 -
外层子查询
(SELECT max(salary) as salary FROM salaries WHERE salary != (SELECT max(salary) as salary FROM salaries))会返回除了最高工资之外的最高工资金额。
-
-
接下来，执行主查询
SELECT salaries.emp_no, salary, last_name, first_name FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no WHERE salary = (子查询)。- 主查询会根据子查询返回的工资金额筛选出与之匹配的员工信息，即工资为除了最高工资之外的最高工资的员工信息。
-
最后，返回结果集包含员工号（emp_no）、工资金额（salary）、姓氏（last_name）和名字（first_name）的信息，这些信息都是工资为除了最高工资之外的最高工资的员工的信息。
select salaries.emp_no,salary,last_name,first_name
from employees join salaries on
employees.emp_no = salaries.emp_no
where salary =
(select max(salary) as salary from salaries
where salary !=
(select max(salary) as salary from salaries) )