sql练习题

卑微的码蚁

已于 2024-06-21 14:21:39 修改

阅读量856

点赞数 18

文章标签： sql 数据库

于 2024-06-19 17:24:22 首次发布

本文链接：https://blog.csdn.net/weixin_45024631/article/details/139750408

版权

案例1:

 需求：请你查找employees里最晚入职员工的所有信息，以上例子输出如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezale	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01

select * from employees order by hire_date limit 0,1

结果：

emp_no	birth_date	first_name	last_name	gender	hire_date
10004	1954-05-01	Christian	Koblick	M	1986-12-01

知识点：
	1.ORDER BY 根据指定的列对结果集进行排序，默认按照升序，降序 ORDER BY DESC
	2.LIMIT(m, n) 从第 m + 1 行开始取 n 条记录

案例2:

需求：请你查找employees里入职员工时间排名倒数第三的员工所有信息

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

结果：

emp_no	birth_date	first_name	last_name	gender	hire_date
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12

知识点：
	desc 降序  asc  升序 
	去重关键字 distinct

案例3:

有一个全部员工的薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01

有一个各个部门的领导表dept_manager简况如下:

dept_no	emp_no	to_date
d001	10001	9999-01-01
d002	10003	9999-01-01

需求：请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

select s.emp_no,s.salary,s.from_date,s.to_date,dm.dept_no from salaries as s inner join dept_manager as dm on s.emp_no=dm.emp_no

结果：

emp_no	salary	from_date	to_date	dept_no
10002	72527	2001-08-02	9999-01-01	d001
10004	74057	2001-11-27	9999-01-01	d004
10005	94692	2001-09-09	9999-01-01	d003
10006	43311	2001-08-02	9999-01-01	d002

知识点：
	连接查询：表1 left join 表2 on 表1.列名=表2.列名2 （左表全显示，右表显示满足条件的）
	         表1 right join 表2 on 表1.列名=表2.列名2 （右表全显示，左表显示满足条件的）
	         表1 inner join 表2 on 表1.列名=表2.列名2 （左右两表都显示只满足条件的）

案例4:

有一个员工表，如案例1中employees表。
有一个部门表dept_emp，如下：

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-2	9999-01-01
10002	d002	1989-08-03	9999-01-01

需求：查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示:

select distinct em.last_name ,em.first_name ,dm.dept_no from employees em, dept_emp dm where em.emp_no = dm.emp_no

结果：

last_name	first_name	dept_no
Facello	Georgi	d001
Simmel	Bezalel	d002

案例5:

有一个员工表，如案例1中employees表。
有一个部门表，如案例4中的dept_emp表。

需求：查找所有已经分配部门的员工的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

结果：

last_name	first_name	dept_no
Facello	Georgi	d001
Simmel	Bezalel	d002
Bamford	Parto	None
Koblick	Chirstian	None

案例6:

有一个薪水表salaries，如下：

emp_no	salary	from_date	to_date
10001	60117	1986-06-26	1987-06-26
10001	62102	1987-06-26	1988-06-25
10001	66074	1988-06-25	1989-06-26
10001	66596	1989-06-25	1990-06-25
10001	66961	1990-06-25	1991-06-26
10001	71046	1991-06-25	1992-06-24
10001	74333	1992-06-24	1993-06-24
10001	75286	1993-06-24	1994-06-24
10001	75994	1994-06-24	1995-06-24
10001	76884	1995-06-24	1996-06-23
10001	80013	1996-06-23	1997-06-23
10001	81025	1997-06-23	1998-06-23
10001	81097	1998-06-23	1999-06-23
10001	84917	1999-06-233	2000-06-22
10001	85112	2000-06-22	2001-06-22
10001	85097	2001-06-22	2002-06-22
10001	72527	1996-08-03	1997-08-03

需求：请你查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

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

结果：

知识点：
   1.采用聚合函数COUNT()来计算salary字段>15的数量。
   2.使用聚合函数作为限定条件，则需要使用分组查询GROUP BY

emp_no	t
10001	17

案例6:

有一个员工表dept_emp，如下：

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-2	9999-01-01
10002	d001	1989-08-03	9999-01-0
10003	d002	1989-08-03	9999-01-01

 有一个部门经理表dept_manager，如下：

dept_no	emp_no	from_date	to_date
d001	10002	1996-08-03	9999-01-01
d002	10003	1990-08-05	9999-01-01

需求：获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select d.emp_no,dm.emp_no manager from dept_emp d inner join dept_manager dm on d.dept_no=dm.dept_no where d.emp_no != dm.emp_no

结果：

dept_no	emp_no
10001	10002

案例7:

有一个员工表dept_emp,如案例6.
有一个全部员工的薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	92527	2001-12-01	9999-01-01

需求：获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列

先查询每个部门中薪资最高的员工，表1
select d.dept_no,max(s.salary) salary
from 
dept_emp d inner join salaries s on d.emp_no=s.emp_no
group by dept_no

结果：

dept_no	salary
d001	88958
d002	92527


再查询员工、部门、薪水，表2
select d.dept_emp,d.emp_no,s.salary 
from 
dept_emp d inner join salaries s on d.emp_no=s.emp_no

结果：

dept_no	emp_no	salary
d001	10001	88958
d001	10002	72527
d002	10003	92527

将表1和表2结合起来，通过内查询找出符合条件的结果
select t1.dept_no,t2.emp_no,t1.salary
from 
(select d.dept_no,max(s.salary) salary
from 
dept_emp d inner join salaries s on d.emp_no = s.emp_no  
group by dept_no) t1
inner join
(select d.dept_no,d.emp_no,s.salary
from 
dept_emp d inner join salaries s on d.emp_no = s.emp_no  ) t2
on t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by 
t1.dept_no

结果：

dept_no	emp_no	salary
d001	10001	88958
d002	10003	92527

案例8:

有一个员工表employees简况如下

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezale	Simmel	F	1985-11-21
10003	1959-12-03	Bezalel	Mary	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01
10005	1954-05-01	Mary	Sluis	F	1986-12-01

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

select * from employees where mod(emp_no,2)=1 and last_name <>'Mary' order by hire_date desc

结果：

emp_no	birth_date	first_name	last_name	gender	hire_date
10005	1953-11-07	Mary	Sluis	F	1990-01-22
10001	1953-09-02	Georgi	Facello	M	1986-06-26

知识点：
	1.MOD(emp_no, 2)=1也可以改成emp_no % 2=1，但是某些sql版本可能不支持前者
    2.不相等有三种表示方式：<>、!=、IS NOT

案例9:

有一个员工职称表titles,如下:

emp_no	birth_date	first_name	last_name
10001	Senior Engineer	1986-06-26	9999-01-01
10003	Senior Engineer	1986-06-26	9999-01-01
10004	Senior Engineer	1986-06-26	9999-01-01
10006	Senior Engineer	1986-06-26	9999-01-01
10007	Senior Staff	1986-06-26	9999-01-01

有一个薪水表salaries如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10003	43311	2001-08-02	9999-01-01
10004	74057	2001-12-01	9999-01-01
10006	43311	2001-12-01	9999-01-01
10007	88070	2001-12-01	9999-01-01

需求：统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序

select t.title,avg(s.salary ) from titles t join salaries s on t.emp_no=s.emp_no group by t.title order by avg(s.salary ) asc

结果：

title	avg(s.salary)
Senior Engineer	62409.2500
Senior Staff	88070.0000

案例10:

有一个薪水表salaries如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01

需求：获取薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

错误语句
 select emp_no,salary from salaries order by salary,emp_no desc, emp_no asc limit 1,1 
 这么使用确实只针对薪资不会有重复的，如果有重复的就会有问题

思路：因为可能有相同薪资的人，所以可以根据题目，先搜索薪水第二多是多少钱，使用distinct关键字去重。之后根据薪资看一下有哪些员工，最后对员工进行排序输出
select emp_no,salary from salaries where salary=(select distinct salary from salaries order by salary desc limit 1,1)

结果：

emp_no	salary
10002	72527

案例11:

有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezale	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01

有一个薪水表salaries如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	74057	2001-11-27	9999-01-01

需求：
查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

由于不能使用order by，那么就换一种思路：
 1.先查出薪资最大的
 select max(salary) from salaries 
 2.再根据最大的搜出薪资第二大的
 select max(salary) from salaries where  salary != (select max(salary) from salaries)
 3.最后根据薪资搜出员工编号，薪水等数据
 select e.emp_no,s.salary,e.last_name,e.first_name 
 from employees e join salaries s
 on e.emp_no = s.emp_no and s.emp_no =
   (select  max(salary) 
      from salaries 
      where  salary != 
       (select max(salary) 
        from salaries
       )
   )

结果：

emp_no	salary	last_name	first_name
10004	74057	Koblick	Chirstian

案例12:

有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezale	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01

有一个部门表departments表简况如下:

dept_no	dept_name
d001	Marketing
d002	Finance
d003	Human Resources

有一个，部门员工关系表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1986-06-26	9999-01-01
10003	d002	1986-06-26	9999-01-01

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

思路：此题有三张表，1.可以使用第1+第3张表查找出last_name和first_name
                 2.接着使用查询出的表再与第二张表关联查询出最后结果
             注意：题目要求也要输出没有分配部门的员工，所以需要使用外连接
            
select 
    e.first_name,
    e.last_name,
    de.dept_no 
from 
    employees e 
    left join 
       dept_emp de on e.emp_no=de.emp_no

结果：

first_name	last_name	dept_no
Georgi	Facello	d001
Bezale	Simmel	d001
Parto	Bamford	d002
Christian	Koblick	None

select
    t1.last_name,
    t1.first_name,
    d.dept_name
from
    departments d
    right join (
        select
            e.first_name,
            e.last_name,
            de.dept_no
        from
            employees e
            left join dept_emp de on e.emp_no = de.emp_no
    ) t1 on d.dept_no = t1.dept_no

最终结果：

first_name	last_name	dept_name
Facello	Georgi	Marketing
Simmel	Bezalel	Marketing
Bamford	Parto	Finance
Koblick	Chirstian	None

案例13:

有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	2001-06-22
10002	1964-06-02	Bezale	Simmel	F	1999-08-03

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	85097	2001-06-22	2002-06-22
10001	88958	2002-06-22	9999-01-01
10002	72527	1999-08-03	2000-08-02
10002	72527	2000-08-02	2001-08-02

需求：查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为
（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）

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