牛客网数据库SQL实战详细剖析(11-20)

简介: 牛客网数据库SQL实战详细剖析(11-20)

这是一个系列文章,总共61题,分6期,有答案以及解题思路,并附上解题的一个思考过程。


具体题目可参考牛客网的SQL实战模块:

https://www.nowcoder.com/ta/sql?page=0



一、牛客网数据库SQL实战详细剖析(1-10)

第十一题:获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。



SELECT  de.emp_no,  dm.emp_no AS manager_no FROM  dept_emp deLEFT JOIN dept_manager dm ON de.dept_no = dm.dept_no WHERE  de.to_date = '9999-01-01'AND dm.to_date = '9999-01-01'AND de.emp_no <> dm.emp_no;


解题思路:重点要理解两表之间的逻辑关系和题目要求。有三个关键点:


①每个部门都有一个manager 每个人都有自己所在的部门。所以用部门号dept_no作为联结条件,左联结和内联结都可以;②如果员工当前的manager是自己的话结果不显


示,这就要求dept_emp表的员工编号emp_no和dept_manager的员工编号不相等;③题目要求当前的manager,所以最后的经理表dept_manager的时间为to_date='9999-01-01',严谨起见,员工表dept_emp的时间也应该是to_date='9999-01-01'。 


第十二题:获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary


SELECT  d.dept_no,  s.emp_no,max( s.salary ) AS salary FROM  salaries sLEFT JOIN dept_emp d ON s.emp_no = d.emp_no WHERE  s.to_date = '9999-01-01'AND d.to_date = '9999-01-01'GROUP BY  d.dept_no;


解题思路:①用GROUP BY d.dept_no将每个部门分为一组,用MAX()函数选取每组中工资最高者。②为获得当前薪水,工资表和部门表的to_date都是'9999-01-01' 。 


第十三题:从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t


SELECT  title,count( title ) AS t FROM  titles GROUP BY  title HAVING  t >= 2;


解题思路:使用group by将title分组,再用having子句找出大于等于2的组,having必须在group by之后,并且有group by才能使用having,聚合参数也可以是count(*) 和count(emp_no) ,还要注意>=号之间不能有空格,否则会出错。 


第十四题:从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略


SELECT  title,count( DISTINCT emp_no ) AS t FROM  titles GROUP BY  title HAVING  t >= 2;


解题思路:参考上题思路,同时使用distinct去重emp_no。


 第十五题:查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列


SELECT  * FROM  employees WHERE  emp_no % 2 = 1AND last_name != 'Mary'ORDER BY  hire_date DESC;


解题思路:员工号为奇数,则emp_no取余应为1,last_name不为Mary,用‘!=’表示,另外注意字符串要用单括号括起来,逆序用desc。 


第十六题:统计出当前各个title类型对应的员工当前(to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。


SELECT  t.title,avg( salary ) AS avgFROM  salaries sINNER JOIN titles t ON s.emp_no = t.emp_no WHERE  s.to_date = '9999-01-01'AND t.to_date = '9999-01-01'GROUP BY  t.title;

解题思路:使用group by 对title进行分组,当前用两表to_date='9999-01-01'进行限定,平均工资用聚合函数avg。 


第十七题:获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary


SELECT  emp_no,  salary FROM  salaries WHERE  salary = ( SELECT max( salary ) FROM salaries WHERE to_date = '9999-01-01' AND salary < ( SELECT max( salary ) FROM salaries ) );

解题思路:本题难点在于如何找出薪水排第二的员工,使用子查询里嵌套子查询。①先在工资表里找出最多工资,再以工资小于最大工资为限定条件,即salary < (select max(salary)可以得到排名第二的工资。


②再使用max(salary)在除去排名第一工资的表里找最大的工资,即找到排名第二的工资。


③这个写法,不管工资第一多的员工有多少个,都能查到工资第二多的员工,且当工资第二多的员工有多个时,也都能查询出来,可以插入数据验证。 以下这种写法,尽管第一名工资有多个时仍然能查出工资第二多的,但当工资第二多的有多个员工的时候,只能查询出一个,不能查询出多个。



SELECT  emp_no,max( salary ) FROM  salaries WHERE  salary < ( SELECT max( salary ) FROM salaries ) AND to_date = '9999-01-01';

 
第十八题:查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by


SELECT  e.emp_no,  s.salary,  e.last_name,  e.first_name FROM  employees eINNER JOIN salaries s ON e.emp_no = s.emp_no WHERE  salary = ( SELECT max( salary ) FROM salaries WHERE to_date = '9999-01-01' AND salary < ( SELECT max( salary ) FROM salaries ) );


解题思路:本题只需要在上一题的基础上增加一个内联结(左联结)即可将薪水salary、last_name以及first_name查询出来。  


第十九题:查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工



SELECT  last_name,  first_name,  dept_name FROM  (SELECT  e.last_name,  e.first_name,  de.dept_no FROM  employees eLEFT JOIN dept_emp de ON e.emp_no = de.emp_no   ) aLEFT JOIN departments dp ON a.dept_no = dp.dept_no;


解题思路:本题关键在于理清三表之间关联关系。需要使用两次左联结。红色部分可以省略。


①题目要求包括暂时没有分配部门的员工,所以将employees作为主表,使用左联结将其和dept_emp表联结,联结条件为员工编号emp_no,得出所有员工所在部门,并将结果命名为a。


②将查询结果a与departments左联结,联结条件为部门编号dept_no,求出部门名字dept_name。 


第二十题:查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

方法一:


SELECTmax( salary ) - min( salary ) AS growth FROM  salaries WHERE  emp_no = '10001';


方法二:


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;
FROM  salaries;


解题思路:对“入职以来的薪水salary涨幅值growth”有两种理解,第一种是员工入职以来工资最大值减去工资最小值,参考方法一。第二种理解是最新一次工资减去入职时的工资,即为涨幅值growth,参考方法二,方法二更严谨。


--end--

相关文章
|
1月前
|
SQL 机器学习/深度学习 人工智能
从“写SQL”到“聊数据”:NL2SQL如何用自然语言解锁数据库?
本文系统性地阐述了自然语言转SQL(NL2SQL) 技术如何让非技术背景的业务分析师实现数据自助查询,从而提升数据驱动决策的效率与准确性。
从“写SQL”到“聊数据”:NL2SQL如何用自然语言解锁数据库?
|
3天前
|
SQL 人工智能 Linux
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
62 5
SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
|
2天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
3天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
20天前
|
SQL Oracle 关系型数据库
Oracle数据库创建表空间和索引的SQL语法示例
以上SQL语法提供了一种标准方式去组织Oracle数据库内部结构,并且通过合理使用可以显著改善查询速度及整体性能。需要注意,在实际应用过程当中应该根据具体业务需求、系统资源状况以及预期目标去合理规划并调整参数设置以达到最佳效果。
77 8
|
25天前
|
SQL 人工智能 Java
用 LangChain4j+Ollama 打造 Text-to-SQL AI Agent,数据库想问就问
本文介绍了如何利用AI技术简化SQL查询操作,让不懂技术的用户也能轻松从数据库中获取信息。通过本地部署PostgreSQL数据库和Ollama模型,结合Java代码,实现将自然语言问题自动转换为SQL查询,并将结果以易懂的方式呈现。整个流程简单直观,适合初学者动手实践,同时也展示了AI在数据查询中的潜力与局限。
168 8
|
1月前
|
SQL 人工智能 Linux
SQL Server 2025 RC0 发布 - 从本地到云端的 AI 就绪企业数据库
SQL Server 2025 RC0 发布 - 从本地到云端的 AI 就绪企业数据库
111 5
|
8天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
2月前
|
SQL 缓存 监控
SqlRest让SQL秒变Http API,还支持20+数据库(含国产数据库)
杭州奥零数据科技有限公司成立于2023年,专注于数据中台业务,维护开源项目AllData并提供商业版解决方案。AllData提供数据集成、存储、开发、治理及BI展示等一站式服务,支持AI大模型应用,助力企业高效利用数据价值。
|
12月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")