牛客网题霸SQL篇(001-010)
001
题目描述:请查找所有已经分配部门的员工的last_name和first_name以及dept_no,也包括暂时没有分配具体部门的员工
背景描述:
员工表
部门表
题解:
select s.last_name,s.first_name,d.dept_no
from employees s
left join dept_emp d
on d.emp_no = s.emp_no;
知识:
- INNER JOIN 两边表同时有对应的数据,即任何一边缺失数据就不显示。LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。RIGHT JOIN 会读取右边数据表的全部数据,即便左边表无对应数据。
- ON与WHERE有什么区别,两个表连接时用on,在使用left jion时,on和where条件的区别如下:on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
002
题目描述:请查找employees里入职员工时间排名倒数第三的员工所有信息
背景描述:
员工表
题解:
select *
from employees
order by hire_date desc limit 2,1;
知识:
-
LIMIT m,n : 表示从第m+1条开始,取n条数据;LIMIT n : 表示从第0条开始,取n条数据,是limit(0,n)的缩写。
-
首先需要加distinct去重。若不加distinct去重,那么按照日期倒序,limit 2,1(从倒数第2行开始,取一条数据);加了distinct去重,会按入职日期进行分组,多个相同入职日期会分为一组
-
外层的where条件中根据子查询查出的倒数第三晚入职的日期,就能查询出符合条件的员工信息。
003
题目描述:请查找employees里最晚入职员工的所有信息
背景描述:
员工信息表
题解:
select *
from employees
order by hire_date DESC limit 1;
select *
from employees
where hire_date = (
select max(hire_date)
from employees);
知识:
SELECT * 选取所有列
FROM employees 提取哪个表的数据
ORDER BY hire_date desc 以哪个数据降序排列
LIMIT 1 取第一个
004
题目描述:请查找各个部门领导薪水详情以及其对应部门编号dept_no,输出结果以salaries.emp_no升序排序,并且请注意输出结果里面dept_no列是最后一列
背景描述:
全部员工薪水表
各部门领导表
题解:
select s.*,d.dept_no
from dept_manager d,salaries s
where s.to_date='9999-01-01'
and d.to_date='9999-01-01'
and s.emp_no = d.emp_no;
select s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no
from salaries s
join dept_manager d
on d.emp_no = s.emp_no;
知识:
用隐式内连接,显式内连接,和外连接均可。需要注意的是左外连接 on后不能直接加and当作限制条件,显示外连接可以。
005
题目描述:请查找所有已经分配部门的员工的last_name和first_name以及dept_no,未分配的部门的员工不显示。
背景描述:
员工表
部门表
题解:
select s.last_name,s.first_name,d.dept_no
from dept_emp d
left join employees s
on d.emp_no = s.emp_no;
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees,dept_emp
where employees.emp_no = dept_emp.emp_no;
知识:
- INNER JOIN 两边表同时有对应的数据,即任何一边缺失数据就不显示。LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。RIGHT JOIN 会读取右边数据表的全部数据,即便左边表无对应数据。
- WHERE 连接也可以。
006
题目描述:请你查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t。
背景描述:
薪水表
题解:
select emp_no,count(emp_no) as t
from salaries
group by emp_no
having t>15;
知识:
- 用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
- 根据题意,输出的变动次数为t,故用AS语句将COUNT(emp_no)的值转换为t
- 由于COUNT()函数不可用于WHERE语句中,故使用HAVING语句来限定t>15的条件
007
题目描述:查找所有员工入职时候的薪水情况,给出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
where e.hire_date = s.from_date #入职时间
order by e.emp_no desc; #逆序排列
运行结果:
008
题目描述:找出所有员工具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
背景描述:
薪水表
题解:
select salary
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc;
select distinct salary #distinct 去重
from salaries s
where s.to_date = '9999-01-01'
order by salary desc; #desc 逆序
运行结果:
009
题目描述:获取所有部门当前manager的当前薪水情况,给出dept_no,emp_no以及salary,当前表示to_date='9999-01-01'。
背景描述:
部门管理者表
薪水表
题解:
select d.dept_no,d.emp_no,s.salary
from dept_manager d
inner join salaries s
on d.emp_no = s.emp_no
where d.to_date ='9999-01-01'
and s.to_date = '9999-01-01';
运行结果:
010
题目描述:找出所有非部门领导的员工emp_no。
背景描述:
员工表
部门领导表
题解:
select emp_no
from employees
where emp_no not in (
select e.emp_no
from employees e
inner join dept_manager d
on e.emp_no = d.emp_no )
SELECT emp_no
FROM employees
WHERE emp_no NOT IN (
SELECT emp_no
FROM dept_manager)
运行结果: