牛客网sql题目总结（三）

最新推荐文章于 2022-06-14 00:36:40 发布
最新推荐文章于 2022-06-14 00:36:40 发布
阅读量582 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33186949/article/details/80040983
版权
本文总结了牛客网上关于SQL的几道题目，涵盖了员工薪水排名、非经理员工薪水、薪水高于经理的员工、部门职位分布、员工薪水涨幅及电影分类等实际问题的SQL解决方案，涉及JOIN、子查询、聚合函数等技巧。
摘要由CSDN通过智能技术生成

21.

  • 题目描述
    对所有员工的当前(to_date=’9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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));
  • 题目解答
    本题的主要思想是复用salaries表进行比较排名，具体思路如下：
    1、从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
    2、本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
    3、千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()
    4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果
SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC

22.

  • 题目描述
    获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=’9999-01-01’
    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));
  • 题目解答
    1、先用JOIN连接employees和salaries，找出当前所有员工的工资情况
    2、再用JOIN连接dept_emp表，找到所有员工所在的部门
    3、最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = ‘9999-01-01’)选出当前所有非manager员工，再依次输出dept_no、emp_no、salary
SELECT de.dept_no, s.emp_no, s.salary 
FROM (employees AS e JOIN salaries AS s ON s.emp_no = e.emp_no AND s.to_date = '9999-01-01')
JOIN dept_emp AS de ON e.emp_no = de.emp_no
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')

23.

  • 题目描述
    获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=’9999-01-01’,
    结果第一列给出员工的emp_no，
    第二列给出其manager的manager_no，
    第三列给出该员工当前的薪水emp_salary,
    第四列给该员工对应的manager当前的薪水manager_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 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));
  • 题目解答
    本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：
    1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem
    2、再用INNER JOIN连接salaries和demp_manager，建立当前所有部门经理的工资记录sdm
    3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem, 
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

24.

  • 题目描述
    汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
    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 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);

  • 题目解答
    1、先用 INNER JOIN 连接 dept_emp 与 salaries，根据测试数据添加限定条件 de.to_date = ‘9999-01-01’ AND t.to_date = ‘9999-01-01’，即当前员工的当前头衔
    2、再用 INNER JOIN 连接departments，限定条件为 de.dept_no = dp.dept_no，即部门编号相同
    3、最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，用 COUNT(t.title) 统计相同部门下相同头衔的员工个数

SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS de 
ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp 
ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title

25.

  • 题目描述
    给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
    提示：在sqlite中获取datetime时间对应的年份函数为strftime(‘%Y’, to_date)

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表求出每年薪水涨幅超过5000的员工，具体思路如下：
    1、假设s1是涨薪水前的表，s2是涨薪水后的表，因为每个员工涨薪水的时间不全固定，有可能一年涨两次，有可能两年涨一次，所以每年薪水的涨幅，应该理解为两条薪水记录的from_date相同或to_date相同。
    2、找到s1与s2符合要求的记录后，用s2的薪水减去s1的薪水，用salary_growth表示，加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000，即同一员工每年涨幅超过5000的记录
    3、最后依次输出emp_no、from_date、salary_growth，并以salary_growth逆序排列
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC

26.

  • 题目描述
    film表

字段 说明
film_id 电影id
title 电影名称
description 电影描述信息

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));
category表

字段 说明
category_id 电影分类id
name 电影分类名称
last_update 电影分类最后更新时间

CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, last_update timestamp,
PRIMARY KEY ( category_id ));
film_category表

字段 说明
film_id 电影id
category_id 电影分类id
last_update 电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, last_update timestamp);

查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

  • 题目解答
    1、找到对应电影数量>=5的所有分类，建立成虚表cc：(select category_id, count(film_id) as category_num from film_category group by category_id having count(film_id)>=5) as cc
    2、设定限制条件 film.description like ‘%robot%’
    3、在表cc、film、film_category、category中查找包括robot的电影对应的分类名称和对应的电影数目。
select category.name,count(film.film_id)
from ((category join film_category on category.category_id=film_category.category_id)as temple
join film on temple.film_id=film.film_id)as tt 
join (select category_id, COUNT(film_id) AS category_num FROM
     film_category  GROUP BY category_id HAVING count(film_id)>=5) AS cc  on tt.category_id=cc.category_id
where film.description LIKE '%robot%'

27.

  • 题目描述
    仍有与26题中相同的film表、category表、film_category表。使用join查询方式找出没有分类的电影id以及名称
  • 题目解答
    解题思路是运用 LEFT JOIN 连接两表，用 IS NULL 语句限定条件：
    1、用 LEFT JOIN 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，即连接电影 id 和电影分类 id，如果电影没有分类，则电影分类 id 显示 null
    2、再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影
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

28.

  • 题目描述
    仍有与26题中相同的film表、category表、film_category表。使用子查询的方式找出属于Action分类的所有电影对应的title,description
  • 题目解答
select f.title,f.description
from film f 
where f.film_id in
  (select fc.film_id from film_category fc join category c
       on fc.category_id=c.category_id and c.name='Action')

29.

EXPLAIN SELECT * FROM employees

30.

  • 题目描述
    将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
    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));
  • 题目解答
    不同数据库连接字符串的方法不完全相同，MySQL、SQL Server、Oracle等数据库支持CONCAT方法，而本题所用的SQLite数据库只支持用连接符号”||”来连接字符串
SELECT last_name||" "||first_name AS Name FROM employees
「已注销」
关注
专栏目录
牛客网数据库SQL实战91题详细剖析（1-10）
卷柏的博客
08-19 339
针对牛客网90道数据库SQL实战题，作出个人解题思路及题解的整理，不定时持续更新 具体题目来自于牛客网SQL实战模块：牛客题霸-SQL篇
牛客网数据库SQL实战题目汇总
最新发布
05-20
项目内容主要来源于牛客网，一个提供IT技术类题目练习和挑战的平台。项目中的题目设计旨在帮助用户掌握和巩固SQL（结构化查询语言）的使用，这是与数据库交互、管理和查询数据的标准语言。 项目包含了一系列不同...
牛客网 数据库SQL试题
ymszzu的博客
10-28 390
Sql1.查找最晚入职员工的所有信息
牛客网SQL练习题
路漫漫兮
06-02 381
01. 查找最晚入职员工的所有信息 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
牛客网sql题目（一）
qq_33186949的博客
04-10 466
1. 题目描述 查找各个部门当前(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, P...
牛客网SQL题目解析（答案+解析+理解）
weixin_43822124的博客
02-15 5255
本文记录了牛客网sql全部题目的答案与难题解析，部分题目包含多种解法，并且涵盖了开窗函数等各种语法点的理解 标题中高亮的题目，是易错题 牛客网刷题链接：牛客网sql在线练习 参考教程资源： ​ mysql教程1 ​ mysql教程2 1.where+子查询 select * from employees order by hire_date desc limit 0,1; select * from employees where hire_date = .
数据库SQL实战牛客网数据库SQL实战题目汇总源代码
03-07
数据库SQL实战牛客网数据库SQL实战题目汇总源代码。编号 分类 标题 提交 1 过滤 查找最晚入职员工的所有信息 :monkey_face: 2 过滤 查找入职员工时间排名倒数第三的员工所有信息 :monkey_face: 3 连接 查找当前薪水...
数据库实战-牛客网数据库SQL实战题目汇总
05-11
牛客网作为一个知名的在线学习和技能提升平台，提供了丰富多样的数据库SQL实战题目，涵盖了从基础到高级的各种题型，为学习者提供了一个系统、全面的学习平台。 这些SQL实战题目的汇总包括但不限于以下方面： 1. *...
牛客网数据库SQL实战题目汇总.zip
04-16
差不多花了一周的时间做完了牛客网平台的61道SQL题，先说一说这个平台的坑: 题目含糊不清，有一些题没有准确表达意思，会让人产生误解。 OJ的答案判断比较死板，不是所有正确的解法都能通过。 难度不是缓慢递增，...
牛客网SQL例题详解
海棠花未眠的博客
03-06 714
例题1、 查找薪水排名第二多的员工信息，不能使用order by完成（难度：较难） 本题目摘抄自牛客网：SQL18题 有一个员工表employees简况如下: 有一个薪水表salaries简况如下: 请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为: 看到此题，其实使用order by是可以很简单完成得。但是题目既然规定了，那么显而易见，为我们思维发散提供了空间。 说实话，看到题目，我刚开始也不知道
牛客网SQL练习题总结
weixin_44113090的博客
12-16 427
练习过程中总结的零碎知识点 题目是找最晚入职员工信息，最先想到的做法是对date排序 desc，最后limit 1。问题是，严格意义上的最晚（严格到最新一秒）这个方法是可以的 但是如果那一天都算是最新的入职日，那天可能会有很多记录，so给date一个条件，这个date＝max（date）。 **知识点：**1. limit M, n 表示从m＋1条数据开始，取n条数据。 问题是找到入职时间倒数第三...
牛客网sql题目总结（四）
qq_33186949的博客
04-28 464
31. 题目描述 题目解答 根据题意，本题关键点是actor_id的主键设置与last_update的默认获取系统时间： 1、在actor_id字段末尾加上PRIMARY KEY是将该字段设置为主键，或者在表的最后一行加上PRIMARY KEY(actor_id) 2、在last_update末尾加上DEFAULT是为该字段设置默认值，且默认值为(datetime(‘now’,’lo...
牛客网SQL试题库（简单）
Java
09-10 377
SQL2：查找入职时间较晚（排名倒数第三）的员工所有信息 //方法一 select * from employees order by hire_date DESC limit 2,1 //方法二 select * from employees where hire_date = ( select distinct hire_date from employees order by hire_date desc limit 2,1 ) 分析： distinct 排除了入职时间重复的行 ord.
牛客网sql题目总结（一）
0117
07-19 922
select * from employees where hire_date = ( select max(hire_date) from employees ) 思路：因为说是最xxx，所以可以直接用聚合函数max，最晚入职，就是hire_date，所以可以用嵌套select语句，先找到最晚入职员工的日期，再用日期作为where条件，查询到所有信息 select * from empl...
【牛客网SQL篇】非技术快速入门
随缘博客
05-25 918
sql-quick-study 01 基础查询 概念：基础查询 SQL1 查询多列 题目： 现在运营同学想要用户的设备 id 对应的性别、年龄和学校的数据，请你取出相应数据 语法： select 要显示的列名 from 表名; SELECT 关键字后给出多个列名，列名之间必须以逗号分隔 SELECT device_id, gender, age, university FROM user_profile; SQL2 查询所有列 题目： 现在运营想要查看用户信息表中所有的数据，请你取
牛客sql_全部题目
Today_2018的博客
03-15 1238
SQL1查找最晚入职员工的所有信息 select * from employees where hire_date = (select max(hire_date) from employees) SQL2查找入职员工时间排名倒数第三的员工所有信息 select emp_no ,birth_date ,first_name ,last_name ,gender ,hire_date from ( select * ,dense_rank
牛客网sql题目总结（五）
qq_33186949的博客
05-06 558
41. 题目描述 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。 CREATE TABLE IF NOT EXISTS titles_test ( id int(11) not null primary key, emp_no int(11) NOT NULL, title varchar(50) NOT NULL,...
数据库SQL实战（牛客网真题）31~40题
YouMing_Li的博客
06-14 1053
数据库SQL实战（牛客网真题）31~40题
牛客网sql练习题解(43-51)
Braylon的博客
07-05 1894
文章目录简介NO.43NO.44NO.45NO.46NO.47NO.48NO.49NO.50NO.51 简介 往期文章： 牛客网sql练习题解 (1-11) 牛客网sql练习题解(12-21) 牛客网sql练习题解(22-32) 牛客网sql练习题解(34-42) 他来了他来了，他带着sql走来了。 这次的知识点涵盖 update用法 replace函数 replace into 修改表名 外键 intersect用法 下面我们直接上题目。 NO.43 update titles_test set t
牛客网SQL数据库实战题目精粹
资源摘要信息: 本资源文件名为《数据库SQL实战案例.zip》，源自牛客网，是一套关于数据库SQL实战题目的汇总资料。该资源包含了数据库基础知识点及实战应用案例，非常适合想要提高SQL技能和解决实际问题的学习者和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值