1. 查询每个部门中入职最早的员工的姓名,职位,上司姓名 和 部门名称
select ename,job,
(select ename from emp where empno=e.mgr) as 上司,
(select dname from dept where deptno=e.deptno) as 部门名称 from emp e where
(deptno,hiredate)in
(
select deptno,min(hiredate) as minhiredate from emp group by deptno
)
或
select t.ename,t.job,e.ename as 上司,dname from emp e
inner join
(
select ename,job,mgr,deptno
from emp e where
(deptno,hiredate)in
(
select deptno,min(hiredate) as minhiredate from emp group by deptno
)
)t on e.empno=t.mgr
inner join dept
on t.deptno=dept.deptno;
2. 查询员工的姓名,职位,薪水,部门名称,工资等级,按入职日期升序排列
select ename,job,sal,dname,grade from emp
inner join dept on emp.deptno=dept.deptno
inner join salgrade on sal between losal and hisal
oracle数据库查询练习
最新推荐文章于 2022-03-31 06:56:20 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)