sql练习题

本文提供了一套包含61道SQL练习题的详细解析,涵盖员工信息查询、部门管理、薪资变动等多个方面,旨在帮助读者提升SQL查询和分析能力,题目涉及子查询、连接查询、分组统计等多个核心概念。
摘要由CSDN通过智能技术生成

employees表

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:查找最晚入职员工的所有信息:

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

考点:子查询、函数。

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

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

考点:分页查询,limit offset,size;offset要显示条目的起始索引(起始索引从0开始),size要显示的条目个数。故查询倒数第三员工信息即为先将入职时间进行降序desc然后从第3个索引开始取一条数据。

题目3:查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_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 `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`));
select s.*,d.dept_no
from salaries as s
inner join dept_manager as d
on s.emp_no = d.emp_no
where s.to_date = '9999-01-01' and d.to_date = '9999-01-01';

考点:连接查询,sql99语法。题目要求是薪水情况以及部门编号,再结合输出情况dept_no 被放到了最后一列,可见是主表是“salaries”

题目4:查找所有已经分配部门的员工的last_name和first_name

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`));
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;

考点:连接查询

题目5:查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工(表和上题一致)

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

考点:主表员工表,字段emp_no,birth_date,first_name,last_name,gender,hire_date,从表部门表,字段emp_no,dept_no,from_date,to_date

  • inner join即内连接。两边表同时有对应的数据,即任何一边缺失数据就不显示。
  • left join左外连接。会读取左边数据表的全部数据,即便右边表无对应数据。
  • right join右外连接。会读取右边数据表的全部数据,即便左边表无对应数据。

题目6:查找所有员工入职时候的薪水情况,给出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`));
//方法1
select e.emp_no,s.salary
from employees e
inner join salaries s
on s.emp_no = e.emp_no and s.from_date = e.hire_date
order by e.emp_no desc;

//方法2
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

//方法3
select emp_no,salary from salaries
group by emp_no having min(from_date)
order by emp_no DESC

解析:

  • salaries.emp_no 不唯一(因为号码为 emp_no 的员工会有多次涨薪的可能,所以在 salaries 中对应的记录不止一条),employees.emp_no 唯一,即 salaries 的数据会多于 employees,因此需先找到 employees.emp_no 在 salaries 表中对应的记录salaries.emp_no,则有限制条件 e.emp_no = s.emp_no
  • 根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等,因此有限制条件 e.hire_date = s.from_date
  • 根据题意要按照 emp_no 值逆序排列,因此最后要加上 ORDER BY e.emp_no DESC

题目7:查找薪水涨幅超过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`));
select emp_no,count(emp_no) 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的条件。添加复杂的筛选条件(分组函数)一般使用having在group by后面,即分组后筛选。where放在group by前,即简单筛选,分组前筛选。

  • 最后存在一个理解误区,涨幅超过15次,salaries中相应的记录数应该超过16(从第2条记录开始算作第1次涨幅),不过题目为了简单起见,将第1条记录当作第1次涨幅,所以令t>15即可

题目8:找出所有员工当前(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`));
select distinct(salary)
from salaries
where to_date = '9999-01-01'
order by salary desc;

考点:DISTINCT函数去重

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

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`));
select d.dept_no,d.emp_no,s.salary
from dept_manager d
inner join salaries s
on d.emp_no = s.emp_no and d.to_date = s.to_date
where d.to_date = '9999-01-01';

考点:因为同一emp_no在salaries表中对应多条涨薪记录,而当s.to_date = '9999-01-01’时是该员工当前的薪水记录

题目10:获取所有非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`));
//方法1:not in
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);

//方法2
select e.emp_no
from employees e left join dept_manager d
on e.emp_no = d.emp_no
where d.emp_no is null;

解析:

  • 方法1是使用NOT IN选出在employees但不在dept_manager中的emp_no记录
  • 方法2是先使用LEFT JOIN连接两张表,再从此表中选出dept_no值为NULL对应的emp_no记录

题目11:获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date=‘9999-01-01’。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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 `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`));
select e.emp_no,m.emp_no as manager_no
from dept_emp e
inner join dept_manager m
on e.dept_no = m.dept_no
where e.to_date='9999-01-01'
and m.to_date='9999-01-01'
and e.emp_no <> m.emp_no;

解析:

  • 用 INNER JOIN 连接两张表,因为要输出自己的经理,得知自己与经理的部门要相同,故有限制条件 de.dept_no = dm.dept_no
  • 再用 WHERE 限制当前员工与当前经理的条件,即 dm.to_date 等于 ‘9999-01-01’ 、de.to_date 等于 ‘9999-01-01’ 、 de.emp_no 不等于 dm.emp_no

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

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 `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`));
select d.dept_no,d.emp_no,max(s.salary) salary
from dept_emp 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'
group by d.dept_no;

考点:

  • 先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no;
  • 选取每个员工当前的工资水平,用d.to_date = ‘9999-01-01’ AND s.to_date = '
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值