前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >Oracle多表查询、子查询实战练习

Oracle多表查询、子查询实战练习

作者头像
星哥玩云
发布于 2022-08-17 08:07:01
发布于 2022-08-17 08:07:01
1.5K0
举报
文章被收录于专栏:开源部署开源部署

一、基础练习:

1.查询和scott相同部门的员工姓名ename和雇用日期hiredate

SELECT ENAME,HIREDATE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT');

2.查询在部门的loc为NEW YORK的部门工作的员工的员工姓名ename,部门名称dname和岗位名称job

SELECT E.ENAME,D.DNAME,E.JOB,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.LOC='NEW YORK';

3.查询上司是king的员工姓名(ename)和工资(sal)

SELECT ENAME,SAL FROM EMP WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING');

4.查询与姓名中包含字母U的员工在相同部门的员工信息

SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%U%');

5.查询所有雇员姓名和部门名称(使用left join,inner join, right join)

SELECT E.ENAME,D.DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO; SELECT E.ENAME,D.DNAME FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO; SELECT E.ENAME,D.DNAME FROM DEPT D RIGHT JOIN EMP E ON E.DEPTNO=D.DEPTNO;

6.显示每个员工的员工姓名、部门名称、职务、工资、和工资等级信息(使用left join,inner join, right join)

SELECT E.ENAME,D.DNAME,E.JOB,E.SAL,S.GRADE FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT E.ENAME,D.DNAME,E.JOB,E.SAL,S.GRADE FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO LEFT JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT E.ENAME,D.DNAME,E.JOB,E.SAL,S.GRADE FROM DEPT D RIGHT JOIN EMP E ON E.DEPTNO=D.DEPTNO RIGHT JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

二、综合练习

1.取得每个部门最高薪水的人员名称2.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

--使用相关子查询 SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP E WHERE E.SAL=(SELECT MAX(SAL) FROM EMP M WHERE M.DEPTNO=E.DEPTNO) ORDER BY DEPTNO; --使用多表连接查询(渔舟唱晚同学的) SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP Q, (SELECT E.DEPTNO 部门, MAX(E.SAL) 最高薪资 FROM EMP E GROUP BY E.DEPTNO) R WHERE R.部门 = Q.DEPTNO AND Q.SAL = R.最高薪资 ORDER BY Q.DEPTNO; --使用DENSE_RANK()函数结合ORDER BY SELECT * FROM(SELECT EMPNO,ENAME,SAL,DEPTNO,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)RN FROM EMP) WHERE RN=1 ORDER BY DEPTNO; --使用IN子查询(有BUG) SELECT EMPNO,ENAME,DEPTNO,SAL FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) ORDER BY DEPTNO;

--测试上面的IN子查询BUG:发现10部门的NulluN也显示出来了,但其并非10部门最高工资,10部门最高工资为5000 INSERT INTO EMP(EMPNO,ENAME,DEPTNO,SAL) VALUES(1015,'NulluN',10,3000); SELECT EMPNO,ENAME,DEPTNO,SAL FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) ORDER BY DEPTNO;

2.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

--左自连接和多表查询 SELECT E.EMPNO 员工编号,E.ENAME 员工姓名,M.ENAME 主管姓名,E.HIREDATE 员工受雇日期, M.HIREDATE 上级雇用日期,D.DNAME 部门名称 FROM EMP E,EMP M,DEPT D WHERE M.EMPNO(+)=E.MGR AND E.HIREDATE<M.HIREDATE AND E.DEPTNO=D.DEPTNO ORDER BY E.EMPNO;

--相关子查询和多表查询 SELECT E.EMPNO,E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.HIREDATE <(SELECT HIREDATE FROM EMP M WHERE M.EMPNO=E.MGR) AND E.DEPTNO=D.DEPTNO ORDER BY E.EMPNO;

3.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

思路:1.先查询JOB为CLERK的所有部门编号,将该子查询结果命名为A;2.再从EMP表查询与A查询中部门编号相同的员工所在的部门人数,这一步的查询结果命名为B;3.最后从EMP表、DEPT表和B查询中进行多表查询获取JOB为CLERK的所有员工的姓名、部门名称和所在部门人数。

SELECT E.ENAME,D.DNAME,T.部门人数,E.JOB FROM EMP E,DEPT D,(SELECT DEPTNO,COUNT(1) 部门人数 FROM EMP WHERE DEPTNO IN( SELECT DISTINCT DEPTNO FROM EMP WHERE JOB='CLERK') GROUP BY DEPTNO)T WHERE E.DEPTNO=D.DEPTNO AND E.JOB='CLERK' AND T.DEPTNO=E.DEPTNO;

4.列出与"SCOTT"从事相同工作的所有员工及部门名称

SELECT E.*,D.DNAME FROM EMP E,DEPT D WHERE E.JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT') AND E.DEPTNO=D.DEPTNO;

5.查出某个员工的上级主管,并要求出这些主管中的薪水超过3000

SELECT E.EMPNO 员工编号,E.ENAME 员工姓名,M.ENAME 主管姓名,M.SAL 主管工资 FROM EMP E,EMP M WHERE M.EMPNO(+)=E.MGR AND M.SAL>3000;

6.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料

SELECT E.*,D.DNAME,D.LOC,S.* FROM EMP E,DEPT D,SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL AND (E.DEPTNO=10 AND E.JOB='MANAGER' OR E.DEPTNO=20 AND E.JOB='CLERK'); --注意:E.DEPTNO=10 AND E.JOB='MANAGER' OR E.DEPTNO=20 AND E.JOB='CLERK' 要用括号括起来,不然会与前面的AND条件混淆造成错误!

7.找出早于12年前受雇的员工. 并且按受雇年份倒序排序

思路一:用MONTHS_BETWEEN比较当前系统时间和受雇日期之前相差的月份,然后除以12,如果值大于12,则是早于12前受雇的员工。

--有错误的语句 SELECT E.*,TO_CHAR(HIREDATE,'YYYY') 受雇年份,ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E WHERE 受雇年限>12 ORDER BY 受雇年份 DESC; /*为什么“受雇年限”会是无效的标识符呢?因为SELECT语句在WHERE语句后面才执行,而列的别名(受雇年限)是在SELECT时才生成的,故在WHERE子句中看不到这个别名(受雇年限),自然无法引用这个别名了。*/--排错后的正确语句 SELECT E.*,TO_CHAR(HIREDATE,'YYYY') 受雇年份,ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E WHERE (MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>12 ORDER BY 受雇年份 DESC;

思路二:用ADD_MONTHS判断,(受雇日期+12*12)得出的日期如果小于当前系统时间,则是早于12前受雇的员工。

SELECT E.*,TO_CHAR(HIREDATE,'YYYY') 受雇年份,ADD_MONTHS(HIREDATE,12*12) 受雇十二周年日,ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E WHERE ADD_MONTHS(HIREDATE,12*12)<SYSDATE ORDER BY 受雇年份 DESC; --注意:离当前日期越远的日期越小,反之,离当前日期越近的日期越大。

8.列出从事同一种工作但属于不同部门的员工的一种组合

--不算完美但算比较接近题意的SQL语句 SELECT DISTINCT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO FROM EMP E,EMP P WHERE E.DEPTNO!=P.DEPTNO AND E.JOB=P.JOB ORDER BY JOB,DEPTNO; --其它两种不等于的写法 SELECT DISTINCT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO FROM EMP E,EMP P WHERE E.DEPTNO<>P.DEPTNO AND E.JOB=P.JOB ORDER BY JOB,DEPTNO; SELECT DISTINCT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO FROM EMP E,EMP P WHERE E.DEPTNO^=P.DEPTNO AND E.JOB=P.JOB ORDER BY JOB,DEPTNO; /*精妙之处:使用DISTINCT!如果不使用DISTINCT,查询结果会出现很多一样的重复数据!*/

分析:为什么说上面的SQL语句不算完美呢?因为从上图可看出JOB为CLERK,且DEPTNO=20的记录有两条,即分别是第2和第3条查询记录,这就与题目要求的“从事同一种工作但属于不同部门的员工”不一致了,故最理想的查询结果应该如下:

9.查询有奖金的所有员工的姓名、奖金以及所在部门名称

--如果奖金等于0也算有奖金,那如下实现: SELECT ENAME,COMM,DNAME FROM EMP E,DEPT D WHERE COMM IS NOT NULL AND E.DEPTNO=D.DEPTNO;

--如果奖金等于0不算有奖金,则如下实现: SELECT ENAME,COMM,DNAME FROM EMP E,DEPT D WHERE COMM IS NOT NULL AND COMM<>0 AND E.DEPTNO=D.DEPTNO;

10.给任职日期超过25年的员工加薪10%

SELECT E.ENAME,E.SAL 原薪水,E.SAL*1.1 加薪后薪水,ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),2) 受雇年限 FROM EMP E WHERE (MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>25;

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
Oracle 数据库查询专题 (select * from emmmm 80 T)
传送门: Oracle数据库学习笔记 (四 —— select 从入门到放弃 【上】) Oracle数据库学习笔记 (四 —— select 从入门到放弃 【下】)
Gorit
2021/12/09
4930
oracle--多表联合查询sql92版
sql92学习 -查询员工姓名,工作,薪资,部门名称 sql的联合查询(多表查询) --1、sql92标准 ----笛卡尔积:一件事情的完成需要很多步骤,而不同的步骤有很多种方式,完成这件事情的所有方式称之为笛卡尔积 select * from emp--14 select * from dept--7 select * from emp,dept order by ename--7*14=98 等值链接,链接条件。等值链接的时候字段的名
eadela
2019/09/29
6190
面试、工作中常用sql大全(建议收藏备用)
关键字UNIQUE与DISTINCT作用相同,但是UNIQUE为ORACLE特有语法,不易于移植。
半旧518
2022/10/26
6890
面试、工作中常用sql大全(建议收藏备用)
mysql数据库多表查询练习题[通俗易懂]
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/143714.html原文链接:https://javaforall.cn
全栈程序员站长
2022/09/01
1.4K0
mysql数据库多表查询练习题[通俗易懂]
MySQL经典练习题+解题思路(三)
CSDN话题挑战赛第2期 参赛话题:学习笔记 个人主页:BoBooY的CSDN博客_Java领域博主 前言:在上一期中我们讲解了MySQL的入门知识点,但理论总还是要通过实践来印证,学了再多不练也是白费,这一期我们讲解MySQL的一些经典查询例题帮助大家巩固已学知识点,如在做题过程中有知识点的遗忘,可以参考往期文章: MySQL入门知识点(上):https://blog.csdn.net/qq_58233406/article/details/127143537 MySQL入门知识点(下)
啵啵鱼
2022/11/23
5240
MySQL经典练习题+解题思路(三)
数据库MySQL学习——内含34道MySQL练习题及答案
DML(数据操作语言):insert delete update,对表中数据进行增删改
全栈程序员站长
2022/09/30
3.3K0
Oracle学习(五):多表查询
1.知识点:可以对照下面的录屏进行阅读 SQL> --等值连接 SQL> --查询员工信息: 员工号 姓名 月薪 部门名称 SQL> select empno,ename,sal,dname 2 from emp,dept 3 where emp.deptno=dept.deptno; SQL> --不等值连接 SQL> --查询员工信息: 姓名 月薪 工资级别 SQL> select ename,sal,grade 2 from emp e,salgrade s 3 wh
Java架构师必看
2021/05/17
4290
MySQL经典练习题+解题思路(四)
CSDN话题挑战赛第2期 参赛话题:学习笔记 个人主页:BoBooY的CSDN博客_Java领域博主 前言:在上一期中我们讲解了MySQL的入门知识点,但理论总还是要通过实践来印证,学了再多不练也是白费,这一期我们讲解MySQL的一些经典查询例题帮助大家巩固已学知识点,如在做题过程中有知识点的遗忘,可以参考往期文章: MySQL入门知识点(上):https://blog.csdn.net/qq_58233406/article/details/127143537 MySQL入门知识点(下):h
啵啵鱼
2022/11/23
3800
MySQL经典练习题+解题思路(四)
SQL员工部门表综合查询60题
CREATE DATABASE oa; USE oa; CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(20), loc VARCHAR(20) ) DROP TABLE emp CREATE TABLE emp( empno INT PRIMARY KEY, ename VARCHAR(20) NOT NULL, job VARCHAR(20) CHECK (job IN ('CLERK','SALESMAN','MANAGER','
Albert陈凯
2018/04/04
5.4K0
MySQL经典33题,DQL语句硬核专项练习!!!
第二步:将第一步的表当作临时表与emp表做连接 连接条件e.deptno=t.deptno and e.sal=t.maxsal
百思不得小赵
2022/12/01
6540
MySQL经典33题,DQL语句硬核专项练习!!!
hql和sql练习题
select t.ename,t.empno,t.deptno from emp t where t.job = ‘CLERK’
张哥编程
2024/12/17
1020
Oracle 查询练习
1.已知Oracle的Scott用户中提供了三个测试数据库表,名称分别为dept,emp和salgrade。使用SQL语言完成以下操作
Arebirth
2019/09/24
1.4K0
Oracle应用实战七——多表查询+PL/SQL
1 多表查询 内连接 使用一张以上的表做查询就是多表查询 语法: SELECT {DISTINCT} *|列名.. FROM 表名 别名,表名1 别名 {WHERE 限制条件 ORDER BY 排序
Java帮帮
2018/03/19
3K0
Oracle应用实战七——多表查询+PL/SQL
MySQL经典练习题+解题思路(一)
(2)再将查询结果当做一张临时表,与emp表进行表连接(因为有的最高薪水是相同的人,需要将他们都显示出来)
啵啵鱼
2022/11/23
6650
MySQL经典练习题+解题思路(一)
Oracle数据库学习笔记 (四 —— select 从入门到放弃 【上】)
基本语法 order by xxxx asc(desc) asc 升序, desc 降序
Gorit
2021/12/09
1.1K0
mysql练习(含答案)
表结构 DROP DATABASE IF EXISTS test1; CREATE DATABASE test1; USE test1; ##部门表 #DROP IF EXISTS TABLE DEPT; CREATE TABLE DEPT( DEPTNO int PRIMARY KEY,##部门编号 DNAME VARCHAR(14) , ##部门名称 LOC VARCHAR(13) ##部门地址 ) ; INSERT INTO DEPT VALUES (10,'ACCOU
Java学习
2018/04/17
2.6K0
mysql练习(含答案)
MyBatis 多对一关联和一对多关联
关联关系,体现的是两个类之间的一种强依赖关系。比如在员工类中,有一个属性是部门类的对象;先看第一种 嵌套查询:
张哥编程
2024/12/17
1490
【说站】mysql内连接是什么
1、内部连接类似于自然连接,但内部连接不要求比较两个表的同名属性列,可以用on或using指定两个表比较的字段。
很酷的站长
2022/11/23
6600
【说站】mysql内连接是什么
MySQL从入门到入魔(03)
子查询(嵌套查询) 查询工资高于1号部门平均工资的员工信息 select avg(sal) from emp where deptno=1; select * from emp where sal>2325; 把上面两条合并成一条 select * from emp where sal>(select avg(sal) from emp where deptno=1); 查询拿最高工资的员工信息 select * from emp where sal=(select max(sal) from em
海拥
2021/08/23
4750
Oracle - 函数及多表关联
函数一般是在数据上执行的,它给数据的转换和处理提供了方便。只是将取出的数据进行处理,不会改变数据库中的值。函数根据处理的数据分为单行函数和聚合函数(组函数),组函数又被称作聚合函数,用于对多行数据进行操作,并返回一个单一的结果,组函数仅可用于选择列表或查询的having子句;单行函数对单个数值进行操作,并返回一个值。
Carlos Ouyang
2019/08/19
1K0
Oracle - 函数及多表关联
相关推荐
Oracle 数据库查询专题 (select * from emmmm 80 T)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档