数据库SQL实战-答案解析(1-15)

数据库SQL实战-答案解析(1-15)

地址：https://www.nowcoder.com/ta/sql

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

select * from employees order by hire_date desc limit 1;select * from employees order by hire_date desc limit 0,1;

LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。
本题limit 0,1 表示从第（0+1）条数据开始，取一条数据，即取出最晚入职员工。

排序： order by 逆序 ：desc

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

select * from employees order by hire_date desc limit 2,1;

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

select s.*, d.dept_no from salaries as s ,dept_manager as d where s.to_date ='9999-01-01' and d.to_date ='9999-01-01' and s.emp_no = d.emp_no;SELECT s.*, d.dept_no FROM salaries s ,  dept_manager d WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01' AND s.emp_no = d.emp_no;select s.*,d.dept_no from salaries s        left join dept_manager d        on s.emp_no=d.emp_no        where d.to_date='9999-01-01'AND s.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 dwhere e.emp_no = d.emp_no and d.dept_no is not null;select e.last_name,e.first_name,d.dept_no    from employees e inner join dept_emp d on e.emp_no=d.emp_no# 因为是内连接，所以d.dept_no肯定是取非空的，所以不用再加where d.dept_no is not null了

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

select e.last_name,e.first_name, d.dept_no from employees as 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进行逆序

select e.emp_no , s.salary from employees e inner join salaries s on e.emp_no=s.emp_no and e.hire_date = s.from_date order by e.emp_no descSELECT e.emp_no, s.salary FROM employees AS e, salaries AS sWHERE e.emp_no = s.emp_no AND e.hire_date = s.from_dateORDER BY e.emp_no DESC

1、salaries.emp_no不唯一 (因为号码为EMP_NO的员工会有多次涨薪的可能，所以在薪金中对应的记录不止一条)

employees.emp_no唯一 需先找到employees.emp_no在salaries表中对应的记录salaries.emp_no
2、根据题意注意到salaries.from_date和employees.hire_date的值应该要相等，因此有限制条件e.hire_date = s.from_date

3、根据题意要按照emp_no值逆序排列，因此最后要加上 ORDER BY e.emp_no DESC

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

WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。

HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

select s.emp_no , count(emp_no) as t from salaries s group by s.emp_no having t>15;

由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>15的条件

用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数

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

select distinct salary from salaries where to_date='9999-01-01' order by salary desc;

SELECT DISTINCT可去除重复值

要求逆序排列，则在最后应使用ORDER BY salary DESC

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

select d.dept_no, s.emp_no, s.salary from salaries as s ,dept_manager as d where s.to_date ='9999-01-01' and d.to_date ='9999-01-01' and s.emp_no = d.emp_no;SELECT d.dept_no, d.emp_no, s.salary FROM salaries AS s INNER JOIN dept_manager AS d ON d.emp_no = s.emp_noAND d.to_date = '9999-01-01'AND s.to_date = '9999-01-01'

先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no

根据题意，要获取当前manager的当前salary情况，再加上限制条件d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’即可

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

select e.emp_no from employees e where emp_no not in (select d.emp_no from dept_manager as d);#方法二：先使用LEFT JOIN连接两张表，再从此表中选出dept_no值为NULL对应的emp_no记录SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager ON employees.emp_no = dept_manager.emp_no)WHERE dept_no IS NULL

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

mysql中用<>与!=都是可以的，但sqlserver中不识别!=,所以建议用<>

链接：[https://www.nowcoder.com/questionTerminal/e50d92b8673a440ebdf3a517b5b37d62

本题应注意以下三点：
1、用INNER JOIN连接两张表，因为要输出自己的经理，得知自己与经理的部门要相同，故有限制条件 de.dept_no =
dm.dept_no
2、再用WHERE限制经理的条件，即 dm.to_date 等于 ‘9999-01-01’ 与 de.emp_no 不等于dm.emp_no

3、为了增强代码可读性，将dept_emp用别名de代替，dept_manager用dm代替，最后根据题意将de.emp_no用别名manager_no代替后输出

ELECT de.emp_no, dm.emp_no AS manager_no FROM dept_emp AS de INNER JOIN dept_manager AS dmON de.dept_no = dm.dept_no WHERE dm.to_date = '9999-01-01' AND de.emp_no <> dm.emp_no

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

1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no

2、选取每个员工当前的工资水平，用d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示

3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者

4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出

select d.dept_no , d.emp_no , MAX(s.salary) from dept_emp d inner join salaries s on d.emp_no = s.emp_noWHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no;select d.dept_no, s.emp_no , s.salary from dept_emp as d, salaries as s where d.emp_no = s.emp_no and d.to_date = '9999-01-01' and s.to_date = '9999-01-01' group by d.dept_no having s.salary = max(s.salary)

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

select titles.title, count(titles.title) as t from titles  group by titles.title having t>=2;

根据题意，输出每个title的个数为t，故用AS语句将COUNT(title)的值转换为t

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

1、先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数

select titles.title, count(distinct titles.emp_no) as t from titles  group by titles.title having t>=2;

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

select e.* from employees e where e.emp_no % 2 != 0 and e.last_name != 'Mary' order by e.hire_date desc;