【SQL】牛客网SQL练习 Q1-10

牛客网练习地址

Q1

1.1 题目

查找最晚入职员工的所有信息。
为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)。

 CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,  -- '员工编号'
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`));

1.2 答案

select * from employees
order by hire_date DESC
limit 1

运行时间：21ms; 占用内存：3424k

1.3 参考

select * from employees
where hire_date = (select max(hire_date) from employees);

运行时间：19ms; 占用内存：3560k
top和limit方法有牵强之处，与给定数据集有关
最晚入职的当天未必就一个人，也许有多人，使用排序并限制得只能取得指定数量的结果

Q2

2.1 题目

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

 CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` char(1) NOT NULL,
 `hire_date` date NOT NULL,
 PRIMARY KEY (`emp_no`))

2.2 答案

SELECT * FROM employees
order by hire_date DESC
limit 1 offset 2

2.3 参考

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

limit 2,1 = limit 1 offset 2
所有的数据里员工入职的日期都不是同一天，所以可以用 limit

select *
from
employees
where hire_date=
(select distinct hire_date from employees 
order by hire_date 
desc limit 2,1);

所有的数据里员工入职的日期如果有重复的，用distinct去重

Q3

3.1 题目

查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no (注:请以salaries表为主表进行查询，输出结果以salaries.emp_no升序排序，并且请注意输出结果，dept_no列是最后一列)

CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL, --  '员工编号',
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`));
  CREATE TABLE `dept_manager` (
 `dept_no` char(4) NOT NULL, -- '部门编号'
 `emp_no` int(11) NOT NULL, --  '员工编号'
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`dept_no`));

3.2 答案

为什么一定要两个表格的时间都限制成规定时间（9999-01-01）呢？
因为薪水表是按年发的，而题目要查找的是当前的薪水，所以要过滤掉以前.
而dept_manager是因为有领导会离职，to_date时间不一定是9999-01-01，所以要过滤过离职的领导

select salaries.emp_no,salaries.salary, salaries.from_date,salaries.to_date ,dept_manager.dept_no from salaries
left join dept_manager 
on salaries.emp_no = dept_manager.emp_no
where dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01'
order by salaries.emp_no ASC

3.3 参考

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'

select s.* from #直接提取主表所有字段
where s.emp_no=d.emp_no # 没有用join而是直接where

Q4

4.1 题目

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

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

4.2 答案

select employees.last_name ,employees.first_name ,dept_emp.dept_no
from employees, dept_emp
where employees.emp_no=dept_emp.emp_no

利用 where 条件句而非join

4.3 参考

SELECT 
    employees.last_name,
    employees.first_name,
    dept_emp.dept_no
FROM employees
inner join dept_emp
on dept_emp.emp_no = employees.emp_no
;

Q5

5.1 题目

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

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

5.2 答案

select employees.last_name, employees.first_name,dept_emp.dept_no from employees
left join dept_emp
on employees.emp_no=dept_emp.emp_no

用left join且employees为主表

5.3 参考

在使用left jion时，on和where条件的区别如下：

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

Q6

6.1 题目

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

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

6.2 答案

select employees.emp_no,salaries.salary FROM salaries
left join employees
on employees.emp_no=salaries.emp_no
where hire_date=from_date
order by employees.emp_no DESC

hire_date=from_date, 解决一名多个对应薪酬的问题，不用distinct解决

6.3 参考

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

Q7

7.1 题目

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

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

7.2 答案

select emp_no, count(emp_no)  AS t FROM salaries
group by emp_no
having t>=15

group by emp_no 不要放在select里面

7.3 参考

Q8

8.1 题目

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

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

8.2 答案

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

8.3 参考

distinct与group by的使用：

1. 当对系统的性能高并数据量大时使用group by
2. 当对系统的性能不高时使用数据量少时两者皆可
3. 尽量使用group by
4. mysql获取当前时间 : now(); sqlServer获取当前时间: getDate()

Q9

9.1 题目

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

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

9.2 答案

select dept_manager.dept_no, salaries.emp_no,salaries.salary 
from dept_manager
inner join salaries
on dept_manager.emp_no=salaries.emp_no
where dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01'

inner join 而非right join/full join
走right join左表某些行空白 显示null

9.3 参考

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_no
AND d.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
ORDER BY d.emp_no

表格按照关键字段连接后，用 and 表示条件

Q10

10.1 题目

获取所有非manager的员工emp_no

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)); 

10.2 答案

select employees.emp_no from employees
left join dept_manager
on employees.emp_no=dept_manager.emp_no
where employees.emp_no not in (select dept_manager.emp_no from dept_manager)

10.3 参考

方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录

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

方法二：先使用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

方法三：方法二的简版，使用单层SELECT语句即可

SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL

非**，可以利用表格连接后的 NULL 字段判断