数据库SQL实战（牛客网真题）21~30题

最新推荐文章于 2023-07-10 23:24:50 发布

百里守约学编程

最新推荐文章于 2023-07-10 23:24:50 发布

阅读量316

点赞数 1

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

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

版权

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

7 篇文章 2 订阅

订阅专栏

文章目录

SQL21 查找在职员工自入职以来的薪水涨幅情况
SQL22 统计各个部门的工资记录数
SQL23 对所有员工的薪水按照salary降序进行1-N的排名
SQL24 获取所有非manager员工当前的薪水情况
SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息
SQL26 汇总各个部门当前员工的title类型的分配数目
SQL29 使用join查询方式找出没有分类的电影id以及名称
SQL30 使用子查询的方式找出属于Action分类的所有电影对应的title,description

SQL21 查找在职员工自入职以来的薪水涨幅情况

在这里插入图片描述
示例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','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');

输出：
10001|3861

解答：将员工表和薪资表进行多次关联，让一条记录中既有当前薪资，又有入职时候的薪资，这时候才能计算出涨幅

select
  e.emp_no,
  (s1.salary - s2.salary) as growth
from
  employees e
  inner join salaries s1 on e.emp_no = s1.emp_no
  and s1.to_date = '9999-01-01' # 当前薪资，已经离职的不考虑
  inner join salaries s2 on e.emp_no = s1.emp_no
  and e.hire_date = s2.from_date # 薪资开始时间和入职日期相同，便是入职时的薪资
order by
  growth;

在这里插入图片描述

SQL22 统计各个部门的工资记录数

在这里插入图片描述

示例1

输入：
drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
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 `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 departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','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,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');

输出：
d001|Marketing|3
d002|Finance|1

解答：将三张表进行关联即可

select
  d.dept_no,
  d.dept_name,
  count(1)
from
  salaries s
  join dept_emp de on s.emp_no = de.emp_no
  join departments d on de.dept_no = d.dept_no
group by
  dept_no,
  dept_name
order by
  d.dept_no;

在这里插入图片描述

SQL23 对所有员工的薪水按照salary降序进行1-N的排名

在这里插入图片描述
示例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');
INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');

输出：
10001|88958|1
10002|72527|2
10004|72527|2
10003|43311|3

解答：首先介绍三种用于进行排序的专用窗口函数：

1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······

窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> ）
其中[ ]中的内容可以忽略

select
  emp_no,
  salary,
  dense_rank() over(
    order by
      salary desc
  ) as t_rank
from
  salaries
order by
  t_rank,
  emp_no;

在这里插入图片描述

SQL24 获取所有非manager员工当前的薪水情况

在这里插入图片描述

示例1

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

输出：
d001|10001|88958

解答：将相关表关联之后，筛出员工编号不是经理编号的即可

select
  dm.dept_no,
  e.emp_no,
  s.salary
from
  employees e
  join dept_emp de on e.emp_no = de.emp_no
  join dept_manager dm on de.dept_no = dm.dept_no
  join salaries s on e.emp_no = s.emp_no
where
  e.emp_no <> dm.emp_no;

在这里插入图片描述

SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息

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

输入：
drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
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 `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`));
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_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');

输出：
10001|10002|88958|72527

解答：关键点是将员工薪资和其经理的薪资放在同一条记录中，这样才好比较。此外，在连接的时候需要将自身就是经理的记录过滤掉

select
  de.emp_no,
  dm.emp_no manager_no,
  s1.salary emp_salary,
  s2.salary manager_salary
from
  dept_emp de
  join dept_manager dm on de.dept_no = dm.dept_no
  and de.emp_no <> dm.emp_no # 当前员工自身就是经理的记录可以过滤掉
  # 这里关联两次salaries表就是将员工和其经理的薪资放在同一条记录中 ， 这样才好比较 
  join salaries s1 on de.emp_no = s1.emp_no
  join salaries s2 on dm.emp_no = s2.emp_no
where
  s1.salary > s2.salary; # 员工薪资大于其经理工资

在这里插入图片描述

SQL26 汇总各个部门当前员工的title类型的分配数目

在这里插入图片描述

示例1

输入：
drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  titles ;
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
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 titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');

输出：
d001|Marketing|Senior Engineer|1
d001|Marketing|Staff|1
d002|Finance|Senior Engineer|1

解答：按照部门编号、部门title进行分组即可

select
  d.dept_no,
  max(d.dept_name),# 没有出现在group by中，故需要用到聚合函数
  t.title,
  count(1)
from
  departments d
  join dept_emp de on d.dept_no = de.dept_no
  join titles t on de.emp_no = t.emp_no
group by
  d.dept_no,
  t.title
order by
  d.dept_no,
  t.title;

在这里插入图片描述

SQL29 使用join查询方式找出没有分类的电影id以及名称

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

输入：
drop table if exists  film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');

输出：
3|ADAPTATION HOLES

解答：只需要和中间表左连接即可，左连接后category_id为空的便是需要的记录

select
  f.film_id,
  f.title
from
  film f
  left join film_category fc on f.film_id = fc.film_id
where
  fc.category_id is null;

在这里插入图片描述

SQL30 使用子查询的方式找出属于Action分类的所有电影对应的title,description

在这里插入图片描述

示例1

输入：
drop table if exists   film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');

输出：
ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
ACE GOLDFINGER|A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China

解答：简单关联查询

select
  f.title,
  f.description
from
  film f
  join film_category fc on f.film_id = fc.film_id
  join category c on fc.category_id = c.category_id
where
  c.name = 'Action';