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