sql练习题

最新推荐文章于 2023-02-01 07:30:00 发布
最新推荐文章于 2023-02-01 07:30:00 发布
阅读量632 收藏 1
点赞数
文章标签： MySQL习题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_43216903/article/details/95082938
版权
本文提供了一套包含61道SQL练习题的详细解析，涵盖员工信息查询、部门管理、薪资变动等多个方面，旨在帮助读者提升SQL查询和分析能力，题目涉及子查询、连接查询、分组统计等多个核心概念。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

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 = '9999-01-01’作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；
  • 用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者记住这种情况；

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

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select title,count(title) t
from titles
group by title
having count(title) > 1;

考点：having复杂分组后筛选

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

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select title,count(distinct(emp_no)) t
from titles
group by title
having count(title) > 1;

考点：理解题意，分组后再次去重，计算每组去重后的个数

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

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 emp_no,birth_date,first_name,last_name,gender,hire_date
from employees
where last_name <> "Mary"
and emp_no % 2 == 1
order by hire_date desc;

考点：添加多个筛选条件and连接

题目16：统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

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 IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select title,avg(s.salary) avg
from titles t
inner join salaries s
on t.emp_no = s.emp_no
where t.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by title;

考点：

  • “Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。
  • “Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

题目17：获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水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 emp_no,salary
from salaries
where to_date = '9999-01-01'
order by salary desc
limit 1,1

//更好的答案
select emp_no, salary from salaries
where to_date = '9999-01-01' 
and salary = (select distinct salary from salaries order by salary desc limit 1,1)

考点：钱最多的人可能存在多个

题目18：查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

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

select s.emp_no,max(s.salary),e.last_name,e.first_name
from salaries s
inner join employees e
on s.emp_no = e.emp_no
where to_date = '9999-01-01'
and s.salary not in (select max(salary) from salaries where to_date = '9999-01-01');

解析：

  • 本题做法很多，主要思想为多层SELECT嵌套与MAX()函数结合
  • 先利用MAX()函数找出salaries中当前薪水最高者，即SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’
  • 再利用INNER JOIN连接employees与salaries表，限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = '9999-01-01’与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’)
  • 在以上限制条件下找薪水最高者，即为所有员工薪水的次高者

题目19：查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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`));

select e.last_name,e.first_name,dp.dept_name
from employees e
left join dept_emp de on e.emp_no = de.emp_no
left join departments dp on de.dept_no = dp.dept_no;

解析：本题思路为运用两次LEFT JOIN连接嵌套

  • 第一次LEFT JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工
  • 第二次LEFT JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

题目20：查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

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 (max(salary)-min(salary)) growth
from salaries
where emp_no = 10001;

//方法2
SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1)
- (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date LIMIT 1)
) AS growth;

考点：本题的另一种解法也能通过测试，但实际上不严谨，只有在员工最后一条工资记录为最大值时成立，如果最后一次的工资调整为降薪，则此思路通不过。

题目21：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

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

select sCurrent.emp_no, (sCurrent.salary-sStart.salary) as growth
from (select s.emp_no, s.salary 
      from employees e 
      left join salaries s 
      on e.emp_no = s.emp_no 
      where s.to_date = '9999-01-01') 
as sCurrent
inner join (select s.emp_no, s.salary 
            from employees e 
            left join salaries s 
            on e.emp_no = s.emp_no 
            where s.from_date = e.hire_date) 
as sStart
on sCurrent.emp_no = sStart.emp_no
order by growth;

解析：

  • 先得到一个当前所有员工的薪水表（employees join salaries on employees.emp_no = salaries.emp_no and salaries.to_date=‘9999-01-01’）和一个入职以来员工的薪水表(employees join salaries on employees.emp_no = salaires.emp_no and employees.hire_date= salaries.from_date)
  • 然后连接这两个表

题目22：统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 `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 dp.dept_no,dp.dept_name,count(sl.salary) sum
from departments dp
inner join dept_emp de
on dp.dept_no = de.dept_no
inner join salaries sl
on de.emp_no = sl.emp_no
group by de.dept_no;

解析：

  • 用INNER JOIN连接dept_emp表和salaries表，并以dept_emp.no分组，统计每个部门所有员工工资的记录总数
  • 再将上表用INNER JOIN连接departments表，限制条件为两表的dept_no相等，找到dept_no与dept_name的对应关系，最后依次输出dept_no、dept_name、sum

题目23：对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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`));

select s1.emp_no,s1.salary,count(distinct s2.salary) rank
from salaries s1, salaries s2
where s1.salary <= s2.salary
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no
order by rank;

解析：

  • 从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
  • 本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
  • 千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()
  • 最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果
  • 其实就是emp_no=001号员工下，符合筛选条件的s2.salary有很多种可能；emp_no=002号员工下，符合筛选条件的s2.salary也有很多种可能，以此类推，故必须将s1.emp_no分组，以确保对每个s1.emp_no都会有一个rank输出值

题目24：获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

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`));//部门经理表

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：不使用employees表
select de.dept_no, s.emp_no, s.salary 
from dept_emp de
inner join salaries s 
on s.emp_no = de.emp_no 
and s.to_date = '9999-01-01'
where de.emp_no not in 
(select emp_no from dept_manager where to_date = '9999-01-01');

//法2
select de.dept_no, s.emp_no, s.salary 
from (employees e inner join salaries s on s.emp_no = e.emp_no 
      and s.to_date = '9999-01-01')
inner join dept_emp de on e.emp_no = de.emp_no
where de.emp_no not in
(select emp_no from dept_manager where to_date = '9999-01-01');

解析：

  • 法1思路：不使用employees表，将部门员工表与薪资表内连接，筛选部门员工号不在部门经理表员工号里
  • 法2思路：先用INNER JOIN连接employees和salaries，找出当前所有员工的工资情况；再用INNER JOIN连接dept_emp表，找到所有员工所在的部门；最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = ‘9999-01-01’)选出当前所有非manager员工，再依次输出dept_no、emp_no、salary

题目25：获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,

结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 `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 sem.emp_no emp_no, sdm.emp_no manager_no, sem.salary emp_salary, sdm.salary manager_salary
from (select s.salary, s.emp_no, de.dept_no 
      from salaries s 
      inner join dept_emp de
      on s.emp_no = de.emp_no 
      and s.to_date = '9999-01-01' ) sem,
      (select s.salary, s.emp_no, dm.dept_no
      from salaries s
      inner join dept_manager dm
      on s.emp_no = dm.emp_no
      and s.to_date = '9999-01-01' ) sdm
where sem.dept_no = sdm.dept_no
and sem.salary > sdm.salary;

解析：本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：

  • 先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem
  • 再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录sdm
  • 最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary

题目26：汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select de.dept_no, dp.dept_name, t.title, count(t.title) count
from titles t 
inner join dept_emp de
on t.emp_no = de.emp_no 
and de.to_date = '9999-01-01' 
and t.to_date = '9999-01-01'
inner join departments dp
on de.dept_no = dp.dept_no
group by de.dept_no, t.title;

解析：本题的关键在于用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组

  • 先用 INNER JOIN 连接 dept_emp 与 salaries，根据测试数据添加限定条件 de.to_date = ‘9999-01-01’ AND t.to_date = ‘9999-01-01’，即当前员工的当前头衔
  • 再用 INNER JOIN 连接departments，限定条件为 de.dept_no = dp.dept_no，即部门编号相同
  • 最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，用 COUNT(t.title) 统计相同部门下相同头衔的员工个数

题目27：给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)

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 s2.emp_no, s2.from_date, (s2.salary - s1.salary) as salary_growth
from salaries as s1, salaries as s2
where s1.emp_no = s2.emp_no
and s2.salary - s1.salary > 5000
and strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
order by salary_growth desc;

解析：

  • 假设s1是涨薪水前的表，s2是涨薪水后的表，因为每个员工涨薪水的时间不全固定，有可能一年涨两次，有可能两年涨一次，所以每年薪水的涨幅，应该理解为两条薪水记录的from_date相同或to_date相同。
  • 找到s1与s2符合要求的记录后，用s2的薪水减去s1的薪水，用salary_growth表示，加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000，即同一员工每年涨幅超过5000的记录
  • 最后依次输出emp_no、from_date、salary_growth，并以salary_growth逆序排列

题目28：查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

select c.name,count(fcc.film_id)as num
from film_category fc
inner join category c 
on c.category_id = fc.category_id
inner join (select * 
            from film f 
            where f.description like '%robot%') as f //film表但字段description含有robot
on f.film_id = fc.film_id
inner join (select *,count (fc.film_id)as num 
            from film_category fc 
            group by category_id 
            having num >= 5)as fcc //film_category表，筛选后字段category_id电影数量大于等于5个
on fc.category_id = fcc.category_id;

解析：

  • 找到对应电影数量>=5的所有分类，建立成虚表cc：(select category_id, count(film_id) as category_num from film_category group by category_id having count(film_id)>=5) as cc
  • 设定限制条件 f.description like ‘%robot%’
  • 在表cc、f、fc、c中查找包括robot的电影对应的分类名称和对应的电影数目。

题目29：使用join查询方式找出没有分类的电影id以及名称

select f.film_id,f.title
from film f
left join film_category fc
on f.film_id = fc.film_id
left join category c
on c.category_id = fc.category_id
where fc.category_id is null;

解析：

  • 用 LEFT JOIN 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，即连接电影 id 和电影分类 id，如果电影没有分类，则电影分类 id 显示 null
  • 再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影

题目30：使用子查询的方式找出属于Action分类的所有电影对应的title,description

select title, description
from film
where film_id in (select film_id 
                  from film_category 
                  where category_id = (select category_id 
                                       from category 
                                       where name = 'Action')
                 );

解析：
title和description都是在film表中，即查询film_id是Action类的film_id，而Action类的film_id需要筛选类film_category中category_id是Action情形。

题目31：获取select * from employees对应的执行计划

explain select * from employees;

解析：
在SQLite数据库中，可以用 “EXPLAIN” 关键字或 “EXPLAIN QUERY PLAN” 短语，用于描述表的细节，具体说明与用法可参考：
http://www.runoob.com/sqlite/sqlite-explain.html
http://www.sqlite.org/lang_explain.html

题目32：将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

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

//我的做法，未经测试MySQL
select concat(upper(substr(last_name,1,1)),
              lower(substr(last_name,2)),
             ' ',
             upper(substr(first_name,1,1)),
              lower(substr(first_name,2))
             ) Name
from employees;

//SQLite
select last_name||" "||first_name as Name 
from employees

解析：
不同数据库连接字符串的方法不完全相同，MySQL、SQL Server、Oracle等数据库支持CONCAT方法，而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串

题目33：创建一个actor表，包含如下列信息

列表类型是否为NULL含义
actor_idsmallint(5)not null主键id
first_namevarchar(45)not null名字
last_namevarchar(45)not null姓氏
last_updatetimestampnot null最后更新时间，默认是系统的当前时间
creat table actor(
    actor_id smallint(5) not null primary key,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update timestamp not null default (datetime('now','localtime'))
);

解析：
OJ系统使用的是sqlite3数据库，不是mysql，所以对于默认时间的写法和mysql不同。大家不必过于纠结。

题目34：对于表actor批量插入如下数据

actor_idfirst_namelast_namelast_update
1PENELOPEGUINESS2006-02-15 12:34:33
2NICKWAHLBERG2006-02-15 12:34:33
insert into actor values
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

题目35：对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

actor_idfirst_namelast_namelast_update
3‘ED’‘CHASE’‘2006-02-15 12:34:33’
//mysql，那么把or去掉
insert or ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

题目36：创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：

列表类型是否为null值含义
first_namevarchar(45)not null名字
last_namevarchar(45)not null姓氏’
create table actor_name(
    first_name varchar(45) not null,
    last_name varchar(45) not null 
);
insert into actor_name select first_name,last_name from actor;

题目37：针对如下表actor结构创建索引：对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

题目38：针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v

create view actor_name_view (first_name_v,last_name_v) as
select first_name,last_name from actor;

题目39：针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

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 index idx_emp_no on salaries(emp_no);

//SQLite
select * from salaries
indexed by idx_emp_no
where emp_no = '10005';

//mysql
select * from salaries force index idx_emp_no where emp_no = 10005;

解析：

题目40：actor表，现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000 00:00:00’

alter table actor add column create_date datetime not null default '0000-00-00 00:00:00';

题目41：构造一个触发器audit_log，在employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

create trigger audit_log after insert on employees_test
begin
     insert into audit values(new.id,new.name);
end;

解析：构造触发器时注意以下几点：

  • 用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
  • 触发器执行的内容写出 BEGIN与END 之间
  • 可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录
  • https://www.runoob.com/sqlite/sqlite-trigger.html

题目42：删除emp_no重复的记录，只保留最小的id对应的记录。

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

delete from titles_test
where id not in (select min(id) from titles_test);

题目43:将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

update titles_test 
set to_date=null,from_date='2001-01-01'
where to_date='9999-01-01';

题目44：将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

//方法1
replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');

//方法2
replace into titles_test
select 5, 10005, title, from_date, to_date
from titles_test
where id = 5;

解析：本题运用 REPLACE 有两种解法

  • 方法一：全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，故会替换掉表中 id=5 的记录，否则会插入一条新记录（例如新插入的记录 id = 10）。并且要将所有字段的值写出，否则将置为空。
  • 方法二：运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过。UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

题目45：将titles_test表名修改为titles_2017。

alter table titles_test rename to titles_2017;

题目46：在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

//OJ 系统
drop table audit;
create table audit(
    EMP_no int not null,
    create_date datetime not null,
    foreign key(EMP_no) references employees_test(ID));

题目47：如何获取emp_v和employees有相同的数据？

create view emp_v as select * from employees where emp_no >10005;

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 em.* from employees as em, emp_v as ev where em.emp_no = ev.emp_no;

题目48：将所有获取奖金的员工当前的薪水增加10%。

create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

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

update salaries
set salary = salary*1.1
where to_date = '9999-01-01'
and emp_no in (select emp_no from emp_bonus);

题目49：针对库中的所有表生成select count(*)对应的SQL语句

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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

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

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

输出：

cnts
select count(*) from employees;
select count(*) from departments;
。。
。。
。。
select "select count(*) from " || name || ";" as cnts
from sqlite_master 
where type = 'table'

//MySQL
select concat('select count(*) from', ' ', TABLE_NAME, ';') as cnts
from (select table_name from information_schema.tables 
where table_schema='shop') as hi;

解析：

  • 在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 ‘table’
  • 在 SQLite 中用 “||” 符号连接字符串

题目50：将employees表中的所有员工的last_name和first_name通过(’)连接起来。

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 last_name||"'"||first_name as name 
from employees

解析：类似题目32，在本题所用的SQLite数据库中，只支持用连接符号"||"来连接字符串，不支持用函数连接

题目51：查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

select (length('10,A,B')-length(replace('10,A,B',',',''))) as cnt

解析：

  • replace(字符串，“需要替换的子串”，“用于替换子串的字符串”）
  • 如果考虑到中文的话，其实应该用char_length()函数，只是牛客这里不支持char_length()函数，不知道是不是版本问题
  • char_length（）统计的是字符长度，而length()函数统计的是字符串的字节长度

题目52：获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

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 first_name
from employees
order by substr(first_name,length(first_name)-1);

解析：本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

题目53：按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

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

select dept_no,group_concat(emp_no) employees
from dept_emp
group by dept_no;

//mysql
select dept_no,group_concat(emp_no SEPARATOR ',') 
from dept_emp 
group by dept_no;

解析：

  • 本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
  • 此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。
  • group_concat()函数返回X的非null值的连接后的字符串。如果给出了参数Y，将会在每个X之间用Y作为分隔符。如果省略了Y，“，”将作为默认的分隔符。每个元素连接的顺序是随机的。

题目54：查找排除当前最大、最小salary之后的员工的平均工资avg_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 avg(salary) avg_salary
from salaries
where salary != (select max(salary) from salaries)
and salary != (select min(salary) from salaries)//不用not in不是字符而是数字
and to_date = '9999-01-01';

解析：

  • min()和max()都是聚合函数，是对结果集中的列进行操作而不是对单个记录进行操作（故not in不对）

题目55：分页查询employees表，每5行一页，返回第2页的数据

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 * 
from employees
limit 5,5;//要显示的页数page,每页的条目数size,limit (page-1)*size,size;

题目56：获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示

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

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

create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

输出：

e.emp_nodept_nobtyperecevied
10001d00112010-01-01
10002d00122010-10-01
10003d00432011-12-03
10004d00412010-01-01
10005d003
select de.emp_no, de.dept_no, eb.btype, eb.recevied 
from dept_emp as de 
left join emp_bonus as eb
on de.emp_no = eb.emp_no;

解析：
以上是代码，该题目中的dept_manager 表和salaries表不会用到，并且缺了一个 emp_bonus 表，emp_bonus 表可以从题库里获取奖金的员工当前工资加10%的那道题找到。

题目57：使用含有关键字exists查找未分配具体部门的员工的所有信息。

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

select * 
from employees 
where not exists (select emp_no from dept_emp where emp_no=employees.emp_no);

//另法1
select e.*
from employees e
left join dept_emp d
on e.emp_no = d.emp_no
where d.dept_no is null;

//另法2
select * 
from employees 
where emp_no not in (select emp_no from dept_emp)

解析：本题用 EXISTS 关键字的方法如下：
意为在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录，也就是当 employees.emp_no=10011的时候。反之，把NOT去掉，则输出 employees.emp_no=10001~10010时的记录。

题目58：获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

create view emp_v as select * from employees where emp_no >10005;

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

输出格式:

emp_nobirth_datefirst_namelast_namegenderhire_date
100061953-04-20AnnekePreusigF1989-06-02
select e.*
from employees e
inner join emp_v v
on e.emp_no = v.emp_no;

题目59：获取有奖金的员工相关信息。

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 `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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

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

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’

//凑巧
select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,(s.salary * b.btype * 0.1) bonus
from employees e
inner join emp_bonus b
on e.emp_no = b.emp_no 
inner join salaries s
on e.emp_no = s.emp_no 
where s.to_date='9999-01-01';

//真正的case when
select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
(case b.btype
when 1 then s.salary*0.1
when 2 then s.salary*0.2
else s.salary*0.3 
 end) as bonus
from employees e
inner join emp_bonus b
on e.emp_no = b.emp_no
inner join salaries s
on s.emp_no = e.emp_no 
and s.to_date = '9999-01-01';

题目60：按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。

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`));
emp_nosalaryrunning_total
100018895888958
select a.emp_no,a.salary,(select sum(s.salary) 
                          from  salaries as s 
                          where s.emp_no <=a.emp_no 
                          and s.to_date='9999-01-01')as running_total
from salaries as a 
where a.to_date='9999-01-01' 
order by a.emp_no;

解析：本题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。

  • 输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和
  • 注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = ‘9999-01-01’

题目61：对于employees表中，给出奇数行的first_name

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 e1.first_name
from employees e1
where(select count(*) 
      from employees e2 
      where e1.first_name >= e2.first_name) % 2 = 1;

解析：首先题目的叙述有问题，导致理解有误，输出的数据与参考答案不同。先给出正确的题目叙述：【对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name】。

数据库模拟测试
m0_63293066的博客
04-17 1134
1. 【MySQL】SQL存储过程：查询订单 #创建存储过程：total_order(IN orderid INT , OUT totals NUMERIC(9,2), OUT cid INT) 要求：根据输入的订单编号(order_id)，返回订单总金额(total_money),顾客编号(customer_id)。 订单表：orders如下： 2. 【MySQL】创建成绩表插入触发器 #创建触发器，插入学生.
sql实战练习题(1)
一个旅人
02-08 405
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 NO...
牛客网SQL实战实战练习题
小茜的博客
12-06 230
https://blog.youkuaiyun.com/weixin_41167848/article/details/92628103 中国科学院老哥
面试题一
qq_42317239的博客
10-18 240
一.去重id,并且按时间排序倒序，30万条数据 select * from (select * from T_ORDER order by orderdate desc) group by id; 优化 1.创建索引 create index varchar_index on kind(id); 二.java设计开发模式 一.单例模式 其中单例模式最重要的是确保对象只有一个。 简单来说，保证一个类在内存中的对象就一个。 RunTime就是典型的单例设计，我们通过对RunTime类的分析，一窥究竟。 目的：控
6、数据的合并
PY洋洋
02-01 4015
一、添加变量即横向合并。 二、添加个案即纵向合并
题解-牛客网-SQL-(SQL10)获取所有非manager的员工emp_no
清溪醉客的博客
05-08 349
文章目录1. 题目描述1.1 输入1.2 输出2. 题目分析及解答2.1 题目分析2.2 详细解答 1. 题目描述 1.1 输入 有一个员工表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 N
SQL牛客40题总结
小刘的专栏
10-07 370
SQL牛客题目总结 1 题目 获取select * from employees对应的执行计划 解答 explain select * from employees 解释 在SQLite数据库中，可以用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节 2 题目 将employees表的所有员工的last_name和first_name拼接起来作...
SQL练习题 SQL练习题 SQL练习题
09-06
以下是一些SQL练习题，涵盖了SELECT、INSERT、UPDATE、DELETE、JOIN、SUBQUERY、AGGREGATE、GROUP BY等语句和函数： 选择数据 从 employees 表中选择所有列： SELECT * FROM employees; 从 employees 表中选择 ...
Hive SQL练习题库
04-24
### Hive SQL练习题库知识点详解 #### 一、Hive SQL概述 Hive 是一个基于 Hadoop 的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，使不熟悉 MapReduce 的用户可以利用 SQL...
sql练习题sql练习题sql练习题sql练习题
06-04
SQL练习题通常涵盖了以下核心概念和操作： 1. **数据类型**：SQL中的数据类型包括数值型（如INT, FLOAT）、字符串型（VARCHAR, CHAR）、日期时间型（DATE, DATETIME）等，了解并熟练使用这些数据类型是基础。 2. *...
50道SQL练习题及答案与详细分析
12-14
通过这50道练习题，我们可以深入理解和掌握SQL的基础语法以及高级特性。以下是一些关键知识点的讲解： 1. **创建表**：在SQL中，我们使用`CREATE TABLE`语句来创建新的表格。例如，`CREATE TABLE Student`定义了...
2024年经典SQL练习题-IT类课程习题资源（源代码和文档）
最新发布
09-26
2024年经典SQL练习题-IT类课程习题资源（史上最全资料）2024年经典SQL练习题-IT类课程习题资源（史上最全资料）2024年经典SQL练习题-IT类课程习题资源（史上最全资料）2024年经典SQL练习题-IT类课程习题资源（史上最...
数据库SQL实战-在audit表上创建外键约束，其emp_no对应employees_test表的主键id(mysql)
weixin_48440312的博客
03-12 365
1. 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01 1.1 题目描述 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。 CREATE TABLE IF NOT EXISTS titles_test ( id int(11) not null primary key, emp_no int(11) NOT NULL, title varchar(50) NOT NULL, f
MySQL官网示例数据库emploees分析使用
风过留声
04-15 479
MySQL官网示例数据库emploees分析使用 1.下载与安装 下载地址：https://github.com/datacharmer/test_db 安装博客：https://blog.youkuaiyun.com/appleyuchi/article/details/79439387   2.ER图          注意：这里使用单下...
SQL练习(中等)
sky_zzk的博客
02-24 1745
有一个员工表，employees简况如下: 有一个部门表，dept_emp简况如下: 建表语句如下: 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
数据库练习
诗_吟的博客
11-12 2876
（二~四）数据库和数据库表管理 #创建表 create table employee( EID int PRIMARY KEY, ENAME varchar(25), DATE DATE, DEPARTMENT_ID int, SALARY FLOAT, CONSTRAINT employee_fk FOREIGN KEY (DEPARTMENT_ID) REFERENCES departme...
Mysql执行计划
学无止境
08-02 551
前段时间了解了下mysql的执行计划，今天整理了下。 介绍前先把涉及到的几张表简单介绍下： 员工表：employees，该表的主键是emp_no，无其他索引。 员工表信息 部门表：departments，主键是dept_no，还有一个唯一索引dept_name。 部门表 dept_name索引信息 员工部门表：dept_emp，
mysql将没有员工的部门都删除_牛客SQL题解-找出所有非部门领导的员工emp_no
weixin_30158793的博客
02-28 1528
牛客SQL题解-找出所有非部门领导的员工emp_no题目描述有一个员工表employees简况如下:有一个部门领导表dept_manager简况如下:建表语句如下:CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last...
多表操作
piquebec的博客
05-21 883
多表查询 多个表之间是有关系的，那么关系靠谁来维护? 多表约束：外键列 多表的关系 一对多/多对一关系 客户和订单，分类和商品，部门和员工. 一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键. 多对多关系 学生和课程 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一 方的主键. 一对一关系 在实际的开发中应用不多.因为一对一可以创建成一张表. 两种建表原则： 唯一外键对应：假设一对...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值