oracle经典题目

--01 按以下格式显示下面的信息，条件是工资大于1500的。
-- 部门名称 姓名 工资
select dname,ename,sal
from dept,emp
where dept.deptno = emp.deptno and sal > 1500

--2 按以下格式显示下面信息，条件是此人工资在所有人中最高。
-- 部门 姓名 工资
select dname,ename,sal
from dept,emp
where dept.deptno = emp.deptno
and emp.sal = (select max(sal) from emp)

--3 按以下格式显示下面信息
-- 某人 为 某人 工作
select e.ename || '为' || m.ename || '工作' as 描述
from emp e,emp m
where e.mgr = m.empno

--4 为所有人长工资，标准是：10部门长10%；20部门长15%；
-- 30部门长20%其他部门长18%（要求用DECODE函数）
select ename,deptno,sal,sal*(1+nvl(decode(deptno,10,0.1,20,0.15,30,0.2),0.18)) as newsal
from emp

--5
--根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。
select ename,hiredate,sal,sal*(1+round(months_between(sysdate,hiredate)/100.0)) as newsal
from emp

--6 查询出king所在部门的部门号\部门名称\部门人数
--ex1
select d.deptno,d.dname,count(*)
from dept d,emp e,emp m
where d.deptno = e.deptno
and e.deptno = m.deptno
and m.ename = 'KING'
group by d.deptno,d.dname

--ex2
select d.deptno,d.dname,count(*)
from dept d,emp e
where d.deptno = e.deptno
and e.deptno = (select deptno from emp where ename = 'KING')
group by d.deptno,d.dname

--7 查询出king所在部门的工作年限最大的员工名字
select ename,hiredate
from emp
where (deptno,hiredate) in (select deptno ,min(hiredate)
from emp
where deptno in (select deptno
from emp
where ename = 'KING')
group by deptno)

--8 查询出管理员工人数最多的人的名字和他管理的人的名字
with m as
( select empno,ename
from emp
where empno in ( select mgr
from emp
group by mgr
having count(*)>= all ( select count(*)
from emp
group by mgr)
)
)
select ename,'manager' as type from m
union
select e.ename, 'emp' as type from m,emp e where e.mgr = m.empno


--9 查询出工资成本最高的部门的部门号和部门名称
select d.deptno,d.dname
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname
having sum(e.sal) >= all (select sum(sal)
from emp
group by deptno)

--10 查询出工资不超过2500的人数最多的部门名称
select d.deptno,d.dname
from dept d,emp e
where d.deptno = e.deptno
and e.sal <= 2500
group by d.deptno,d.dname
having count(*) >= all (select count(*)
from emp
where sal <= 2500
group by deptno)

--11 查询出没有下属员工的人的名字和他的职位
select ename,job
from emp
where empno not in ( select distinct nvl(mgr,0)
from emp)

--12 查询出人数最多的那个部门的部门编号和部门名称
select d.deptno,d.dname
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname
having count(*) >= all (select count(*)
from emp
group by deptno)

--13 查询出没有员工的那个部门的部门编号和部门名称(要求用两种方法,其中一种要用集合运算)
--ex1
select deptno,dname
from dept
where deptno not in (select deptno from emp)

--ex2
select deptno,dname
from dept
where not exists
( select deptno deptno
from emp
where dept.deptno = emp.deptno )

--ex3
select deptno,dname
from dept
minus
select d.deptno,d.dname
from dept d ,emp e
where d.deptno = e.deptno

--14 查询出员工名字以A打头的人数最多的部门名称和员工名字
select d.dname,e.ename
from dept d,emp e
where d.deptno = e.deptno
and e.deptno in ( select deptno
from emp
where ename like 'A%'
group by deptno
having count(*) >= all (select count(*)
from emp
where ename like 'A%'
group by deptno)
)

--15 现在公司要给员工增加工龄工资,规则是:30*工作年限，请按以下格式显示下面结果:
-- 部门名称 员工姓名 原工资 增加额度 新工资
select d.dname as 部门名称,e.ename as 员工姓名,e.sal as 原工资,
trunc(months_between(sysdate,hiredate)/12,0) * 30 as 增加额度,
e.sal + trunc(months_between(sysdate,hiredate)/12,0) * 30 as 新工资
from dept d,emp e
where d.deptno = e.deptno

--16 针对DEPT和EMP表，查询出下面格式的结果并要求按部门编号和工资降序排列。
-- 部门名称 员工姓名 工资
select d.dname as 部门名称,e.ename as 员工姓名,
e.sal as 工资
from dept d,emp e
where d.deptno = e.deptno
order by d.deptno ,e.sal desc

--17 针对DEPT和EMP表，查询出下面格式的结果。
-- 部门编号 部门名称 部门工资最小值 部门工资最大值 部门工资平均值 部门工资合计值
select d.deptno as 部门编号,d.dname as 部门名称,
min(e.sal) as 部门工资最小值,
max(e.sal) as 部门工资最大值,
avg(e.sal) as 部门工资平均值,
sum(e.sal) as 部门工资合计值
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname

--18 针对DEPT和EMP表，查询出SMITH所在部门的部门名称、部门工资平均值。（要求使用子查询）
select d.deptno,d.dname,avg(e.sal)
from dept d,emp e
where d.deptno = e.deptno
and e.deptno in (select deptno from emp where ename = 'SMITH')
group by d.deptno,d.dname


--19 针对DEPT和EMP表，查询出下面格式的结果。（要求使用外连接，没有员工的部门名也要显示。
-- 员工姓名如果是空值，要求用"不存在"代替；如果工资是空值，要求用0代替。）
-- 部门名称 员工姓名 工资
select d.deptno,nvl(e.ename,'不存在'),nvl(e.sal,0)
from dept d ,emp e
where d.deptno = e.deptno(+)

--20 针对DEPT和EMP表，查询出没有员工的部门号和部门名称（要求用两种方法）
--ex1
select deptno,dname
from dept
where deptno not in (select deptno from emp)

--ex2
select deptno,dname
from dept
where not exists
( select deptno deptno
from emp
where dept.deptno = emp.deptno )

--ex3
select deptno,dname
from dept
minus
select d.deptno,d.dname
from dept d ,emp e
where d.deptno = e.deptno

--21 查询出平均工资最高的部门编号、部门名称和平均工资。
select d.deptno,d.dname,avg(e.sal)
from dept d,emp e
where d.deptno = e.deptno
group by d.deptno,d.dname
having avg(e.sal) >= all ( select avg(sal) from emp group by deptno)

--22 查询出工资高于全体平均工资人数最多的部门编号、部门名称和员工姓名、工资。
select d.deptno,d.dname,e.ename,e.sal
from dept d,emp e
where d.deptno = e.deptno
and d.deptno in ( select deptno
from emp
where sal > (select avg(sal) from emp)
group by deptno
having count(*) >= all ( select count(*)
from emp e
where e.sal > (select avg(sal) from emp)
group by e.deptno
)
)