SQL练习题（上）

在公司实习第一天，带我的老师就让我看看数据库并发了学习资料让我学习，我恍惚地记得我曾在牛客网上练习过题目，因为之前学过数据库原理，资料很快的看的差不多了，但是实战还是少了一点，所以想把上次的坑填了，把61个题目做完并总结总结。
图片说明
一：对61个题目进行逐一练习
先给出题目所用的表：

mysql> describe employees;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no     | int(11)     | NO   | PRI | NULL    |       |
| birth_date | date        | NO   |     | NULL    |       |
| first_name | varchar(14) | NO   |     | NULL    |       |
| last_name  | varchar(16) | NO   |     | NULL    |       |
| gender     | char(1)     | NO   |     | NULL    |       |
| hire_date  | date        | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

mysql> describe dept_manager;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| dept_no | char(4) | NO   | PRI | NULL    |       |
| emp_no  | int(11) | NO   | PRI | NULL    |       |
| to_date | date    | NO   |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

mysql> describe salaries;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| salary    | int(11) | NO   |     | NULL    |       |
| from_date | date    | NO   | PRI | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

mysql> describe dept_emp
    -> ;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| dept_no   | char(4) | NO   | PRI | NULL    |       |
| from_date | date    | NO   |     | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

1：查找最晚入职员工的所有信息
关键词：最晚所有
思路： max函数嵌套

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

2：查找入职员工时间排名倒数第三的员工所有信息
关键词：入职时间倒数第三所有信息
思路：嵌套 limit关键字 desc关键字

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

/*distinct 表示去重
limit 2,1 表示从第二个后的第一个*/

3：查找各个部门当前(dept_manager.to_date='9999-01-01')领导当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号dept_no
关键词：薪水详情部门编号
思路：连接两张表并加上题目所给条件即可

select s.*,d.dept_no 
from salaries s,dept_manager d
where 
s.emp_no=d.emp_no
and s.to_date='9999-01-01'
and d.to_date='9999-01-01';

4：查找所有已经分配部门的员工的last_name和first_name以及dept_no
关键词：姓名编号
思路：连接两张表即可（有效连接）

select e.last_name,e.first_name,d.dept_no
from dept_emp as d inner join employees as e
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 as e left join dept_emp as d
on e.emp_no=d.emp_no;

6：查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)
关键词：编号薪水序号逆序刚入职（隐含条件：雇佣日期等于开始日期）
思路：有效连接 order by关键字

select e.emp_no,s.salary
from employees as e inner join salaries as s
on e.emp_no=s.emp_no and s.from_date=e.hire_date
order by e.emp_no desc;

7：查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
关键词：次数超过15次
思路：count函数（一般与group by 组合使用）

select emp_no,count(emp_no)
from salaries
group by emp_no 
having count(emp_no)>15;

8：找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
关键词：按薪资分组并且逆序
思路：group by 关键字解决分组 order by 解决逆序

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

9：获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)
关键字：无
思路：连接表并限制条件即可

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
where d.to_date='9999-01-01'
and s.to_date='9999-01-01';

10：获取所有非manager的员工emp_no
关键词：非manager
思路：not in 关键字嵌套查询

select emp_no from employees
where emp_no not in 
(select emp_no 
from dept_manager);

11:获取所有员工当前的(dept_manager.to_date='9999-01-01')manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。
关键词：非manager不显示
思路：不等于关键字

select e.emp_no,m.emp_no as manager_no
from dept_emp as e inner join dept_manager as 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;

12：获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
关键字：薪水最高
思路：按编号分组调用max函数

select d.dept_no,d.emp_no,max(s.salary) as salary
from dept_emp as d inner join salaries as 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

13：从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
关键字：title分组个数大于等于2
思路：group by 关键字 having 限制条件

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

14：从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。
关键词：重复不计算
思路：distinct 关键字

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

15：查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)
关键词：奇数不为某人逆序
思路：考察表达式的应用（不能用mod函数）

select * from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc

16：统计出当前(titles.to_date='9999-01-01')各个title类型对应的员工当前(salaries.to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。
关键字：平均工资
思路：avg函数（注意输出结果以title分组）

select title,avg(salary) as avg
from titles a inner join salaries b
on a.emp_no=b.emp_no
And a.to_date = '9999-01-01' 
AND b.to_date = '9999-01-01'
group by a.title

以上16题是系统给出难度为简单的题目，为了让篇幅小一点，还是决定分批记录。暂且先写前十六题，剩下的题目将在《SQL练习题（下）》补充