数据库SQL实战（牛客网真题）11~20题

最新推荐文章于 2024-09-03 15:27:57 发布

百里守约学编程

最新推荐文章于 2024-09-03 15:27:57 发布

阅读量187

点赞数

分类专栏： # 牛客网MySQL刷题文章标签：数据库 sql dba

本文链接：https://blog.csdn.net/YouMing_Li/article/details/125249509

版权

牛客网MySQL刷题专栏收录该内容

7 篇文章 2 订阅

订阅专栏

文章目录

SQL11 获取所有员工当前的manager
SQL12 获取每个部门中当前员工薪水最高的相关信息
SQL15 查找employees表emp_no与last_name的员工信息
SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资
SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
SQL19 查找所有员工的last_name和first_name以及对应的dept_name

SQL11 获取所有员工当前的manager

在这里插入图片描述
示例1

输入：
drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');

输出：
10001|10002

解答：通过部门表内连接后，排除掉员工编号和经理编号相同的即可

select
  e.emp_no,
  m.emp_no as manager
from
  dept_emp e
  inner join dept_manager m on e.dept_no = m.dept_no
where
  e.emp_no <> m.emp_no
  and e.to_date = '9999-01-01'
  and m.to_date = '9999-01-01';

在这里插入图片描述

SQL12 获取每个部门中当前员工薪水最高的相关信息

在这里插入图片描述
示例1

输入：
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');

输出：
d001|10001|88958
d002|10003|92527

解答：窗口函数，以部门分组对薪水做排名，然后找到排第一的那个

select
  dept_no,
  emp_no,
  salary
from
  (
    select
      dept_no,
      t1.emp_no,
      salary,
      (
        rank() over(
          partition by dept_no
          order by
            salary desc
        )
      ) as s_rank
    from
      dept_emp t1
      inner join salaries t2 on t1.emp_no = t2.emp_no
    order by
      dept_no
  ) as t
where
  t.s_rank = 1;

在这里插入图片描述

SQL15 查找employees表emp_no与last_name的员工信息

在这里插入图片描述
示例1

输入：
drop table if exists  `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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Bezalel','Mary','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1953-11-07','Mary','Sluis','F','1990-01-22');

输出：
10005|1953-11-07|Mary|Sluis|F|1990-01-22
10001|1953-09-02|Georgi|Facello|M|1986-06-26

解答：按着题目意思写就行了，直译题

select
  *
from
  employees
where
  emp_no % 2 <> 0
  and last_name <> 'Mary'
order by
  hire_date desc;

在这里插入图片描述

SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资

在这里插入图片描述
示例1

输入：
drop table if exists  `salaries` ; 
drop table if exists  titles;
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');

输出：
Senior Engineer|62409.2500
Senior Staff|88070.0000

解答：先关联两个表，将每个员工的薪资和员工title能出现在一个表中，而后根据title分组聚合即可

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

在这里插入图片描述

SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

在这里插入图片描述
示例1

输入：
drop table if exists  `salaries` ; 
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`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

输出：
10002|72527

解答：找到薪水第二多的数值（通过一个子查询，去重，排序，limit），而后筛选出薪水等于第二多薪水的员工信息即可

select
  emp_no,
  salary
from
  salaries
where
  salary = (
    select
      distinct salary
    from
      salaries
    order by
      salary desc
    limit
      1, 1
  )
  and to_date = '9999-01-01'
order by
  emp_no;

在这里插入图片描述

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

在这里插入图片描述
示例1

输入：
drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

输出：
10004|74057|Koblick|Chirstian

解答：首先去除最高工资，然后算出第二高工资，最后表子查询筛选第二高工资即可

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

在这里插入图片描述

SQL19 查找所有员工的last_name和first_name以及对应的dept_name

在这里插入图片描述
示例1

输入：
drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

输出：
Facello|Georgi|Marketing
Simmel|Bezalel|Marketing
Bamford|Parto|Finance
Koblick|Chirstian|None

解答：简单的三表关联

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