Oracle查询练习题

Oracle23道练习题

--1.查询平均工资最高的部门的部门编号，部门名称，和该部门的平均工资
select m.deptno, d.dname, m.avg_sal
  from (select deptno, round(avg(nvl(sal, 0) + nvl(comm, 0))) avg_sal
          from emp
         group by deptno) m,
       dept d
 where m.avg_sal =
       (select max(a.avg_sal) max_avg_sal
          from (select deptno, round(avg(nvl(sal, 0) + nvl(comm, 0))) avg_sal
                  from emp
                 group by deptno) a)
   and d.deptno = m.deptno
--2 查询所有员工的年薪，所在部门的名称，结果按年新低到高排列
select round(nvl(e.sal, 0) + nvl(e.comm, 0)) * 12 year_sal, d.dname
  from emp e
 inner join dept d
    on e.deptno = d.deptno
 order by year_sal
--3查询每种工作的工作名称，最低工资，领取该最低工资员工的姓名
select a.*, b.ename
  from (select job, min(round(nvl(e.sal, 0) + nvl(e.comm, 0))) min_sal
          from emp e
         group by job) a
 inner join emp b
    on a.job = b.job
   and a.min_sal = round(nvl(b.sal, 0) + nvl(b.comm, 0))
--4查询出管理员工人数最多的人和他管理的人的名字: 注意： is not null && join优先级大于where,join要写在where前面
select a.*,e.ename
  from (select e.mgr, count(e.empno) usr_num
          from emp e
         group by e.mgr
        having e.mgr is not null) a right outer join emp e on a.mgr=e.mgr
 where a.usr_num = (select max(a.usr_num)
                      from (select e.mgr, count(e.empno) usr_num
                              from emp e
                             group by e.mgr
                            having e.mgr is not null) a)
                            
--5查询所有员工的编号、姓名，及其上级领导的编号、姓名。显示结果按领导的年薪降序排列
select e.empno,
       e.ename,
       m.empno,
       m.ename,
       round(nvl(m.sal, 0) * 12) year_sal
  from emp e
 inner join emp m
    on e.empno = m.mgr
 order by year_sal desc
--6查询所有领取奖金和不领取奖金的员工人数、平均工资；查询结果的列名分别为：人数、平均工资；第一行为有奖金的员工，第二行为没有奖金的员工
select count(empno) usr_num, round(avg(nvl(sal, 0) + nvl(comm, 0))) avg_sal
  from emp
 where 0 != nvl(comm, 0)
union all
select count(empno) usr_num, round(avg(nvl(sal, 0) + nvl(comm, 0))) avg_sal
  from emp
 where 0 = nvl(comm, 0)

--7查询工资不超过2500的人数最多的部门名称和该部门工资不超过2500的员工的员工人数
select d.dname,a.usr_num from (
select e.deptno,count(e.empno) usr_num
  from emp e
 where round(nvl(e.sal, 0) + nvl(e.comm, 0)) < 2500
 group by e.deptno) a inner join dept d on a.deptno=d.deptno
 where a.usr_num = (select max(a.usr_num)
                      from (select e.deptno, count(e.empno) usr_num
                              from emp e
                             where round(nvl(e.sal, 0) + nvl(e.comm, 0)) < 2500
                             group by e.deptno) a)
--8查询受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select e.empno, e.ename, d.dname
  from emp e
 inner join emp m
    on e.mgr = m.empno
 inner join dept d
    on e.deptno = d.deptno
 where e.hiredate > m.hiredate
--9查询至少有4个员工的部门的部门名称
select d.dname from (select e.deptno from emp e group by e.deptno having count(e.empno) >= 4) a,dept d where a.deptno=d.deptno
--10查询工资比“SMITH”高的员工的基本信息
select *
  from emp
 where round(nvl(sal, 0) + nvl(comm, 0)) >
       (select round(nvl(sal, 0) + nvl(comm, 0))
          from emp
         where ename = 'SMITH')
--11查询部门名称中带'S'字符的部门的员工的工资总和和部门人数，显示结果为部门名称，部门员工的工资总和，部门人数
select b.dname, a.*
  from (select e.deptno, sum(e.sal), count(e.empno)
          from emp e
         inner join dept d
            on e.deptno = d.deptno
         where d.dname like '%S%'
         group by e.deptno) a
 inner join dept b
    on a.deptno = b.deptno
--12查询所有从事"CLERK"工作的雇员所在部门的部门名称、部门里的人数
select a.*
  from (select e.ename, d.deptno, d.dname
          from emp e
         inner join dept d
            on e.deptno = d.deptno
         where e.job = 'CLERK') a

--13查询雇员领导的基本信息，要求领导的薪水要超过3000
select e.*
  from emp e
 where e.empno in (select distinct (m.empno)
                     from emp e
                    inner join emp m
                       on e.mgr = m.empno)
   and round(nvl(e.sal, 0) + nvl(e.comm, 0)) >= 3000

--14查询在"sales"部门（销售部）工作的员工的姓名
select e.ename from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES'
--15查询工资高于30号部门的所有员工的工资的员工姓名、工资及部门名称
select e.ename, round(nvl(e.sal, 0) + nvl(e.comm, 0)) usr_sal, d.dname
  from emp e
 inner join dept d
    on e.deptno = d.deptno
 where round(nvl(e.sal, 0) + nvl(e.comm, 0)) > all
 (select round(nvl(e.sal, 0) + nvl(e.comm, 0))
          from emp e
         where e.deptno = 30)
--16查询所有部门的详细信息（部门编号、部门名称）和部门人
select a.*, d.dname
  from (select e.deptno, count(*) dep_num
          from emp e
         group by deptno
        having deptno is not null) a
  left join dept d
    on a.deptno = d.deptno
--17显示每个部门中每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资（没看懂题目要求，好像有点问题） ---?
select a.*, b.dept_avg, c.job_avg
  from (select deptno, job, avg(sal) dept_job_sal
          from emp
         group by deptno, job) a
 inner join (select deptno, round(avg(sal)) dept_avg
               from emp
              group by deptno) b
    on a.deptno = b.deptno
 inner join (select job, round(avg(sal)) job_avg from emp group by job) c
    on a.job = c.job
--18显示与"BLAKE"同部门的所有员工的基本信息，但不显示"BLAKE"的基本信息
select * from emp where deptno=(select deptno from emp where ename='BLAKE') and ename <> 'BLAKE'
--19查询出“KING”所在部门的部门编号、部门名称以及该部门里的员工人数  (注意：聚合函数必须和分组一起使用)
select a.*, d.dname
  from (select e.deptno, count(*)
          from emp e
         where e.deptno =
               (select e.deptno from emp e where e.ename = 'KING')
         group by e.deptno) a,
       dept d
 where a.deptno = d.deptno
--20查询出"WARD"所在部门的工作年限最大的员工的姓名
select ename from emp where (sysdate-hiredate) = 
(select max(sysdate-e.hiredate) from emp e where e.deptno = (select e.deptno from emp e where e.ename='WARD'))
--21查询出没有下属的员工的姓名及他的职位
select e.ename,e.job from emp e where e.empno not in (select distinct(mgr) from emp where mgr is not null)

--22查询出员工姓名以A开头的人数最多的部门的部门名称
select *
  from (select e.deptno, count(*) usr_count
          from emp e
         where e.ename = '%A%'
         group by e.deptno) a
 inner join dept d
    on a.deptno = d.deptno
 where a.usr_count = (select max(a.usr_count)
                        from (select e.deptno, count(*) usr_count
                                from emp e
                               where e.ename = '%A%'
                               group by e.deptno) a)

--23查询出SMITH所在部门的部门名称、部门工资的平均值
select d.dname, avg(e.sal)
  from emp e
 inner join dept d
    on e.deptno = d.deptno
 where e.deptno = (select e.deptno from emp e where ename = 'SMITH')
 group by d.dname

DML语言

CREATE TABLE zx_emp AS SELECT * FROM emp
select * from zx_emp

1. 新增一个员工：编号为8989;姓名为"z%s";职位为：咨询师;入职时间为今天;月薪为2200.00;
insert into zx_emp(empno,ename,job,hiredate,sal) values (8989,'z%s','咨询师',to_date(sysdate),2200.00)
2. 给部门编号为30且没有奖金的人加薪10%。
update zx_emp set comm=comm+10 where deptno=30 and nvl2(comm,comm,0) <> 0
3. 删除部门编号为30中的月薪高于公司平均月薪的员工。
delete from zx_emp where deptno=30 and sal > (select avg(sal) from emp)