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

解答：首先要注意查询的目标是所有信息，其次要注意最晚入职的含义是hire_date字段的值最大：

--ORDER BY hire_date DESC倒序排列 再通过limit取一个 也就是倒数第一个
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;

--子查询，内层查询的结果是最大hire_date的表
SELECT * FROM employees WHERE hire_date = 
(SELECT MAX(hire_date) FROM employees);

变式：查找入职员工时间排名倒数第三的员工所有信息

SELECT * from employees order by hire_date DESC limit 2,1

limit m,n 的 含义是 从第(m+1)个开始，查询n条数据，在本题中从第三个开始，查一个，又是降序排列的，所以结果就是hire_date第三大的。

二.查找各个部门当前(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  s 
left join dept_manager  d 
on s.emp_no=d.emp_no
where s.to_date = '9999-01-01'
    and    d.to_date='9999-01-01';

为什么一定要两个表格的时间都限制成规定时间（9999-01-01）呢？

• 因为薪水表是按年发的，而题目要查找的是当前的薪水，所以要过滤掉以前，而dept_manager是因为有领导会离职，to_date时间不一定是9999-01-01，所以要过滤过离职的领导

三.查找所有已经分配部门的员工的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`));

跟前一题一样，最终查询的结果last_name,first_name,dept_no，所以选择employees表作为主表，然后再考虑怎么连接，这题可以用左外连接，然后再判断dept_no是否为null，但是这样就麻烦了，我直接用join 就可以自动去掉为空不存在的那些行（没分配部门的员工）

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

四.查找员工的姓名和部门id，包括展示没有分配具体部门的员工

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

这道题包含了没有分配部门的员工，所以不能用内连接查询，因为要查的lastname,firstname都是employees表的信息，所以该表为主表，用左外连接查询即可。

五.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

此题初看以为只需要用薪水表，直接查询就可以，但实际上一个员工可能经过加薪，所以emp_no这个字段必须从emp表中取，而且，还要满足“入职时候”这个条件，也就是让e.hire_date = s.from_date ,最后再逆序输出

select e.emp_no,s.salary 
from salaries s join employees e
on s.from_date = e.hire_date and s.emp_no = e.emp_no
order by e.emp_no DESC

六.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

select emp_no,count(emp_no) AS t
from salaries s
group by emp_no
having count(emp_no)>15

• 涨幅次数超过15次=记录数多于15，用count聚集函数
• 用having语句和count一起使用指定范围
• 不要忘记加group by语句对员工号进行分组

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

要查找的只有salary字段，而且只提供了一个表

SELECT DISTINCT salary 
FROM salaries
WHERE to_date = '9999-01-01' 
ORDER BY salary DESC

• 注意Sql语句的编写顺序 ：select dinstinct …from …join …on …where …group by …having …order by …limit …
• DISTINCT必须在select后面

八.获取所有非manager的员工emp_no

select e.emp_no
from employees e
where e.emp_no not in
(select emp_no from dept_manager)

用not in 字段查不在dept_manager表中的emp_no即可

九.获取所有员工当前的manager，如果当前的manager是自己的话结果不显示

首先，数据表中没有manager_no字段，所以必须在查询时使用AS关键字将manager表的员工号转为manager_no

select de.emp_no, dm.emp_no AS manager_no
from dept_emp as de 
join dept_manager as dm 
on dm.dept_no = de.dept_no 
Where dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' AND de.emp_no <> dm.emp_no

连接查询的ON 条件：要通过dept_no连接，因为自己和manager是同部门的，而且如果通过emp_no连接就不符合题意了。

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

此题和第六题类似，都只有一张表，用聚集函数的结果作为一列所以需要用AS，按照title进行分组所以要用group by，限定每组个数大于等于2，所以需要用having

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

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

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

这个题难点在于重复的title，是要统计不同员工的重复的title数，一个员工重复的就可以忽略了，因此不应该把distinct 加在title上