2018-06-27 第五十二天 oracle

本文提供了一系列SQL子查询的实际应用案例,包括查询特定部门最高工资的员工、工资高于部门平均工资的员工等。此外,还提供了多种部门与员工信息关联查询的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、相关子查询

--1:查询本部门的最高工资的员工的信息

--查询10部门的最高工资的员工的信息

--10部门的最高工资

select max(sal) from emp where deptno=10

select * from emp where deptno=10 and sal=(select max(sal) from emp where deptno=10)

select * from emp where deptno=20 and sal=(select max(sal) from emp where deptno=20)

select * from emp where deptno=30 and sal=(select max(sal) from emp where deptno=30)

select * from emp where deptno=40 and sal=(select max(sal) from emp where deptno=40)

 

select * from emp e where  sal=(select max(sal) from emp where deptno=e.deptno)

 

--2:查询工资高于其所在部门的平均工资的员工的信息

--查询工资高于10部门的平均工资的10部门员工的信息

--10部门的平均工资

select avg(sal) from emp where deptno=10

select * from emp where deptno=10 and sal > (select avg(sal) from emp where deptno=10)--1

select * from emp where deptno=20 and sal > (select avg(sal) from emp where deptno=20)--3

select * from emp where deptno=30 and sal > (select avg(sal) from emp where deptno=30)--2

select * from emp where deptno=40 and sal > (select avg(sal) from emp where deptno=40)--0

 

select e.*,(select avg(sal) from emp where deptno = e.deptno)平均工资

from emp e

where sal>(select avg(sal) from emp where deptno = e.deptno)

 

--3:查询本部门的最高工资的员工的信息

-- 使用不相关的子查询实现

--把所有部门的最高的工资求出

select deptno,max(sal) from emp group by deptno

select deptno,sal from emp

 

select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)

 

二、练习

-- 1 、列出所有员工的年工资,按年薪从低到高排序。

select ename, sal*12+nvl(comm,0)*12 year_sal from emp order by year_sal

 

-- 2 、列出薪金比 SMITH 多的所有员工。

select * from emp where sal>(select sal from emp where ename='SMITH')

 

-- 3 、列出所有员工的姓名及其直接上级的姓名。 sql92 , sql99

--sql92

select e1.ename,e2.ename

from emp e1, emp e2

where e1.mgr= e2.empno

--sql99

select e1.ename,e2.ename

from emp e1 join emp e2

on e1.mgr= e2.empno

 

-- 4 、列出受雇日期早于其直接上级的所有员工。

--sql99

select e1.*

from emp e1 join emp e2

on e1.mgr=e2.empno

where e1.hiredate < e2.hiredate

 

-- 5 、列出部门名称和这些部门的员工信息,包括那些没有员工的部门。

select d.dname,e.*

from emp e right join dept d

on e.deptno=d.deptno

 

-- 6 、列出所有job  CLERK (办事员)的姓名及其部门名称。

select e.ename,d.dname

from emp e join dept d

Using(deptno)

where e.job='CLERK'

 

-- 7 、列出最低薪金大于1500 的各种工作。

select job, min(sal)

from emp

group by job

having min(sal) > 1500

 

-- 8 、列出在部门 SALES (销售部)工作的员工的姓名,假定不知道销售部的部门编号。 

select e.deptno,e.ename

from emp e join dept d

on e.deptno=d.deptno

where d.dname='SALES'

 

-- 9 、列出薪金高于公司平均薪金的所有员工。

select * from emp where sal > (select avg(sal) from emp)

 

-- 10 、列出与 SCOTT 从事相同工作的所有员工。

select * from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT'

 

-- 11 、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。

select ename,sal from emp where sal>all(select sal from emp where deptno=30)

 

-- 12 、列出在每个部门工作的员工数量、平均工资和平均服务期限(年)。

select deptno, count(*), avg(sal) , avg(to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')) 平均服务年限

from emp

group by deptno

 

-- 13 、列出所有员工的姓名、部门名称和工资。

select e.ename,d.dname,e.sal

from emp e natural join dept d

 

-- 14 、列出从事同一种工作但属于不同部门的员工的一种组合。

select e1.ename,e1.job ,e1.deptno,e2.ename,e2.job,e2.deptno

from emp e1 join emp e2

on e1.job=e2.job and e1.deptno<>e2.deptno and e1.ename >e2.ename

 

-- 15 、列出所有部门的详细信息和部门人数。

select d.*, count(*)

from emp e join dept d

on e.deptno=d.deptno

group by d.deptno,d.dname,d.loc

 

select d.*,count(e.ename) from dept d left join emp e

on d.deptno = e.deptno 

group by d.deptno,d.dname,d.loc

 

-- 16 、列出各种工作的最低工资。

select job,min(sal)

from emp

group by job

 

-- 17 、列出各个部门的 MANAGER (经理)的最低薪金。

select deptno,min(sal)

from emp

where job='MANAGER'

group by deptno

 

-- 18 、列出至少有一个员工的所有部门。

select d.*,count(*)

from emp e join dept d

on e.deptno=d.deptno

group by d.deptno,d.dname,d.loc

having count(*)>0

 

-- 19 、人数最多部门的信息

--1

select *

from dept d

where deptno = (select deptno

from (select deptno,count(deptno) count_dept from emp group by deptno) 

where count_dept = (select max(count_dept) from (select deptno,count(deptno) count_dept from empgroup by deptno)))

 

--2

select *

from dept d

where deptno = (select deptno from emp group by deptno having (count(*) >= 

all(select count(deptno) from emp group by deptno)))

 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值