sql练习题记录第一部分

原创 2018年04月17日 14:34:57

最近做的sql练习题,保存下来。

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 NOT NULL,

PRIMARY KEY (`emp_no`));

思路:将数据按入职时间从大到小排列,选择第一个是日期最大同时最晚入职

select * from employees order by hire_date desc limit 1
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 order by hire_date desc limit 2,1
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`));

思路:这应该是一道比较简单的表连问题,salaries这张表加上dept_manager这个表里面的`dept_no`字段加上两个where限制条件

select a.emp_no,a.salary,a.from_date,a.to_date,b.dept_no
from salaries a 
inner join dept_manager b
on a.emp_no=b.emp_no
where a.to_date='9999-01-01'
    and b.to_date='9999-01-01'
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 b.last_name,b.first_name,a.dept_no
from dept_emp a
left join employees b
on a.emp_no=b.emp_no
5.

题目描述

查找所有员工的last_name和first_name以及对应部门编号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`));

思路:更换连接的主表即可,既然包括没有分配部门的应当用employees为主表,但是这里我用right join没有调试成功

select b.last_name,b.first_name,a.dept_no
from employees b
left join dept_emp a
on a.emp_no=b.emp_no
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`));

思路:这道题left join 和 innner join都可以通过,查找满足from_date=hire_date并按要求排序即可

select a.emp_no,b.salary
from employees a
left  join salaries b
on a.emp_no=b.emp_no
where a.hire_date=b.from_date
order by a.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`));

思路:首先按照emp_no这个字段进行分组,然后添加限定条件having ,group后面的限定条件不能用where 而应该用having

select emp_no,count(distinct(salary)) t
from salaries
group by emp_no
having  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`));

思路:这道题也是属于简单题,选择salary按照where条件筛选,并且按照order by 排序,下面是我的代码

select distinct(salary)
from salaries 
where to_date='9999-01-01'
order by salary desc 

看了一下别人的思路,有个问题,

对于distinct,groupby的性能。

  1. 数据量非常巨大时候,比如1000万中有300W重复数据,这时候的distinct的效率略好于group by;
  2. 对于相对重复量较小的数据量比如1000万中1万的重复量,用groupby的性能会远优于distnct。
所以我也觉得根据实际情况用group by和distnct
select salary from salaries where to_date='9999-01-01' group by salary order by salary DESC
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`));

思路:第一次调试的时候 a和 b与下面结果相反,导致没有运行成功,这个和第三题有些类似,逻辑是差不多的,选择不同的字段。

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

思路:首先把dept_manager里面的员工号找出来,用where语句筛选

select emp_no from employees
where emp_no not in (select emp_no from dept_manager)
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`));

思路:a和b根据部门编号表连,选择相应的字段,最后用where控制当前时间并且把员工号是部门经理的剔除掉。

select a.emp_no,b.emp_no as manager_no
from dept_emp a 
inner join dept_manager b
on a.dept_no=b.dept_no
where a.to_date='9999-01-01' and b.to_date='9999-01-01' and 
a.emp_no not in (select emp_no from dept_manager )

大神的优化如下:没有用到表连,查询时间可以减少12ms

SELECT de.emp_no,dm.emp_no AS manager_no
FROM dept_manager AS dm,dept_emp AS de
WHERE de.emp_no <> dm.emp_no
AND de.dept_no = dm.dept_no
AND dm.to_date='9999-01-01';
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 a.dept_no,a.emp_no,max(b.salary) as salary
from dept_emp a
inner join salaries b
on a.emp_no=b.emp_no
where a.to_date='9999-01-01' and b.to_date='9999-01-01'
group by a.dept_no
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);


思路:count可以按照title计数也可以emp_no进行计数,首先选择分组,最后由having限定筛选条件

select  title,COUNT(emp_no) as t
from titles
group by title
having t>=2
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);

思路:这道题应该就是上面13题目的变形了,这里考察加入一个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逆序排列
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`));

思路:选择全部信息,用where限定emp_no为奇数,并且last_name不为Mary,这里要记得mary加上引号,因为I是一个字段的值。

select * 
from employees
where emp_no%2<>0 and last_name<>'Mary'
order by hire_date desc 
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);

思路:我这个思路比较固定了,先把两个表连接起来,然后找到当前,这个上面已经多次涉及了,然后按照title分组进行avg运算

select a.title,avg(b.salary)  avg
from titles a
inner join salaries b
on a.emp_no=b.emp_no
where a.to_date='9999-01-01'
 and b.to_date='9999-01-01'
group by a.title
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`));

思路:这个和按入职时间排序的题差不多,注意limit从0开始排列的

select emp_no,salary
from salaries
where to_date='9999-01-01'
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`));

思路:这道题不准使用order by很有意思了就,想法就是先把最高的薪水去掉,然后再选最高的,这样就省去了order by。在选择最高的时候也要注意需要当前薪水。

select b.emp_no,max(b.salary)as salary,a.last_name,a.first_name
from employees a
inner join salaries b
on a.emp_no=b.emp_no
where b.to_date='9999-01-01' 
and b.salary not in (select max(salary) from salaries )
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`));

思路:首先通过

dept_emp.dept_no
把前两个表连接起来,这个是为了找到dept_name,然后左连为了找到包括暂时没有分配部门的员工
select a.last_name,a.first_name,b.dept_name
from employees a 
left join (select * from departments join dept_emp on departments.dept_no=dept_emp.dept_no ) as b
on a.emp_no=b.emp_no
20.查找员工编号emp_now为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

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 asc LIMIT 1)
) as growth






SQL练习题

共同营造良好的技术氛围!大家一起努力!
  • mjj123mjj123
  • mjj123mjj123
  • 2014-02-19 08:55:51
  • 2046

Sql Server中将一个表的记录追加到另一个表中(一句经典示例)

insert into Sys_RanLiao(EmployeeName) select EmployeeName from Pub_Employee where BelongsLine='邯台' a...
  • dxnn520
  • dxnn520
  • 2012-09-27 06:22:13
  • 4613

oracle分节练习题

  • 2009年02月16日 21:40
  • 57KB
  • 下载

SQL Server 中替换字符串中第一个匹配的子串

SQL Server 中替换字符串中第一个匹配的子串
  • danny_style
  • danny_style
  • 2017-07-04 10:25:06
  • 567

史上最难oracle数据库练习题(附答案)

最难oracle练习题
  • qq_36870779
  • qq_36870779
  • 2017-01-01 16:59:31
  • 1262

sql学习人员练习题

  • 2011年01月20日 22:07
  • 25KB
  • 下载

SQL语句快速添加表的记录

1、 首先这张表名为users,主键是userId。建表的语句如下:Code:create database spdb;    create table users(  userId int prim...
  • xcxinghai
  • xcxinghai
  • 2010-08-18 12:21:00
  • 2201

深入浅出SQL(Head First SQL) [第一部分 共两部分]

  • 2011年04月19日 13:40
  • 25MB
  • 下载

收集的MSSQL练习题

  • 2008年01月24日 10:59
  • 88KB
  • 下载

mysql 增删查改-练习

在已有表格中添加新的字段: > alter table 表名 add 新字段名 类型(varchar/int等等)  not null/..; 查询teacher表中姓“李”的老师的个数:(用li...
  • u011378313
  • u011378313
  • 2017-10-12 10:20:58
  • 84
收藏助手
不良信息举报
您举报文章:sql练习题记录第一部分
举报原因:
原因补充:

(最多只允许输入30个字)