oracle经典题目

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?
?????????????????????????????????????? )?
?????????????????? )?

我的异常网推荐解决方案：软件开发者薪资,http://www.myexception.cn/other/1391128.html