作业三子查询作业

查询员工的工资大于JONES的员工信息

select * from emp where sal>(select sal from emp where ename = 'JONES');

统计出公司雇佣最早，工资最低的雇员(单行多列)

select * from emp where (hiredate,sal)=(select min(hiredate),min(sal) from emp);

查询岗位与部门编号为10相同的员工信息不包含自己。

select * from emp where job in (select job from emp where deptno = 10) and deptno !=10;

查询dept表中，不存在于emp表中的部门编号和部门名称

select deptno,dname 
from dept
where deptno not in (select distinct deptno from emp);

查询大于公司平均工资的雇员姓名、职位、工资、领导姓名、领导职位、部门名称、部门人数、部门平均工资、工资等级，以及此等级的雇员人数

--1、查询部门信息
select deptno ,count(*) empnum,trunc(avg(sal)) d_avgsal
from emp
group by deptno
--2、查询大于平均工资的员工信息及领导信息
select e.deptno, e.ename,e.job,e.sal,m.ename mname,m.job mjob
from emp e,emp m
where e.mgr= m.empno and e.sal>(select avg(sal) from emp)
--3、查询大于平均工资的员工信息及领导信息及部门信息（1，2综合）
select e.ename,e.job,e.sal,mname,mjob,dname,empnum,d_avgsal
from dept d
natural join (select e.deptno,e.ename,e.job,e.sal,m.ename mname,m.job mjob
              from emp e,emp m
              where e.mgr= m.empno and e.sal>(select avg(sal) from emp)) e
natural join (select deptno ,count(*) empnum,avg(sal) d_avgsal
          from emp
          group by deptno) dm
--4、查询工资等级以及等级人数
select grade,count(*) gradenum
from salgrade s,emp e
where e.sal between losal and hisal
group by grade
--5、查询员工工资等级
select e.ename,grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal
--6、查询员工工资等级和此等级的人数（4，5综合）
select grade,ename,gradenum
from (select grade,count(*) gradenum
      from salgrade s,emp e
      where e.sal between losal and hisal
      group by grade)
natural join (select e.ename,grade
              from emp e,salgrade s
              where e.sal between s.losal and s.hisal)
--7、查询大于公司平均工资的雇员所有要查的信息(3，6综合查询)
select ename,job,sal,mname,mjob,dname,empnum,trunc(d_avgsal),grade,gradenum
from (
      select e.ename,e.job,e.sal,mname,mjob,dname,empnum,d_avgsal
      from dept d
      natural join (select e.deptno,e.ename,e.job,e.sal,m.ename mname,m.job mjob
                    from emp e,emp m
                    where e.mgr= m.empno and e.sal>(select avg(sal) from emp)) e
      natural join (select deptno ,count(*) empnum,avg(sal) d_avgsal
                from emp
                group by deptno) dm)
natural join (
            select grade,ename,gradenum
            from (select grade,count(*) gradenum
                  from salgrade s,emp e
                  where e.sal between losal and hisal
                  group by grade)
            natural join (select e.ename,grade
                          from emp e,salgrade s
                          where e.sal between s.losal and s.hisal));

练习

（1）查询20号部门的所有员工信息。

select * from emp where deptno = 20;

（2）查询所有工种为CLERK的员工的工号、员工名和部门名。

--使用子查询和自然连接（4*4+14=30）
select e.empno,e.ename,dname 
from dept natural join (select empno,ename,deptno from emp where job = 'CLERK') e; 
--使用子查询和内连接（4*4+14=30）
select e.empno,e.ename,dname 
from dept inner join (select empno,ename,deptno from emp where job = 'CLERK') e 
using(deptno);
--不使用子查询，直接多表查询（4*14=56）
select empno,ename,dname
from emp e,dept d   
where e.deptno = d.deptno and e.job = 'CLERK';

（3）查询奖金（COMM）高于工资（SAL）的员工信息。

select * from emp where comm>sal;

（4）查询奖金高于工资的20%的员工信息。

select * from emp where comm>sal*0.2;

（5）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。

select *
from emp 
where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');
--或者
select * from emp
where(deptno,job) in ((10,'MANAGER'),(20,'CLERK'));

（6）查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息。

select * from emp 
where job not in('MANAGER','CLERK') and sal>=2000;

（7）查询有奖金的员工的不同工种。

select distinct job from emp where comm is not null;

（8）查询所有员工工资和奖金的和。

select sum(sal)+sum(comm) sumsal from emp;  
--查询每个员工工资和奖金的和
select ename,job,sal+nvl(comm,0) 工资和 from emp;

（9）查询没有奖金或奖金低于100的员工信息。

select * from emp where comm is null or comm<100;

（10）查询各月倒数第2天入职的员工信息。

select * from emp where hiredate = (last_day(hiredate)-1);
--说明：last_day()：得到指定日期当月的最后一天

（11）查询员工工龄大于或等于10年的员工信息。

select * from emp where months_between(sysdate,hiredate)/12 > 10;
--说明：months_between(日期1，日期2)：返回日期2 到 日期1 之间的月数。

（12）查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

select initcap(ename) "Ename",empno,job,sal,deptno,mgr from emp;
--说明：initcap(列名|字符串) ：首字母（字符串的第一位）大写，其他小写返回

（13）查询员工名正好为6个字符的员工的信息。

select * from emp where length(ename) = 6;
--说明：length(列名|字符串)：返回字符串长度

（14）查询员工名字中不包含字母“S”员工。

select * from emp where ename not like '%S%';

（15）查询员工姓名的第2个字母为“M”的员工信息。

select * from emp where ename like '_M%';

（16）查询所有员工姓名的前3个字符。

select substr(ename,1,3) from emp;
--说明：substr(列名|字符串, 从第几位开始截取，要截取的长度)

（17）查询所有员工的姓名，如果包含字母“s”，则用“S”替换。

select 
case
    when ename like '%s%' then replace(ename,'s','S')
    else ename
end
from emp;

（18）查询员工的姓名和入职日期，并按入职日期从先到后进行排列。

select ename ,hiredate from emp
order by hiredate asc;

（19）显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列。

select ename,job,sal,comm from emp 
order by job desc,sal asc;

（20）显示所有员工的姓名、入职的年份和月份，若入职日期所在的月份排序，若月份相同则按入职的年份排序。

select ename , to_char(hiredate,'yyyy') year,to_char(hiredate,'mm') month
from emp
order by month asc,year;

（21）查询在2月份入职的所有员工信息。

select * from emp 
where to_char(hiredate,'mm')='02';

（22）查询所有员工入职以来的工作期限，用“年月**日”的形式表示。

select ename , sysdate,hiredate,
trunc(months_between(sysdate,hiredate)/12)||'年'||
  trunc(mod(months_between(sysdate,hiredate),12))||'个月'||
  trunc(mod(months_between(sysdate,hiredate),1)*30)||'日' jobtime
from emp;

（23）查询至少有一个员工的部门信息。

select deptno,dname,loc
from dept d natural join (select deptno from emp group by deptno having count(*)>=1);

（24）查询工资比SMITH员工工资高的所有员工信息。

select *
from emp where sal > (select sal from emp where ename = 'SMITH');

（25）查询所有员工的姓名及其直接上级的姓名。

select e.ename employee,m.ename manager
from emp e,emp m
where e.mgr = m.empno(+) ;
或----------------------------------------------------------------------
--以下涉及相关子查询的问题，此处会报错，怀疑相关子查询不能放在from表后，待查证。
select emp.ename ,m.ename
from emp, (select distinct ename,empno from emp m where emp.mgr = empno) m
where emp.mgr = m.empno;

（26）查询入职日期早于其直接上级领导的所有员工信息。

select e.* from emp e , emp m
where e.mgr = m.empno and e.hiredate > m.hiredate;

（27）查询所有部门及其员工信息，包括那些没有员工的部门。

select e.*,dname,loc
from emp e,dept d
where e.deptno(+) = d.deptno;

（28）查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

select e.*,dname,loc
from emp e,dept d
where e.deptno = d.deptno(+);

（29）查询所有工种为CLERK的员工的姓名及其部门名称。

select e.ename,d.dname
from dept d,(select ename,deptno from emp where job = 'CLERK') e
where d.deptno = e.deptno;

（30）查询最低工资大于2500的各种工作。

select job
from emp
group by job
having min(sal)>2500;

（31）查询最低工资低于2000的部门及其员工信息。

select *
from dept
    natural join (select distinct deptno from emp group by deptno having min(sal)<2000) 
    natural join emp
order by deptno;

（32）查询在SALES部门工作的员工的姓名信息。

select *
from emp e,(select deptno from dept where dname = 'SALES') d
where e.deptno = d.deptno;

（33）查询工资高于公司平均工资的所有员工信息。

select * 
from emp e,(select avg(sal) avg_sal from emp) t_as
where e.sal>t_as.avg_sal;

（34）查询与SMITH员工从事相同工作的所有员工信息。

select * 
from emp e,(select job from emp where ename = 'SMITH') sb
where e.job = sb.job;

（35）列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。

select e.ename,e.sal
from emp e
where sal in (select sal from emp where deptno = 30);

（36）查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资。

select ename,sal
from emp
where sal >all (select sal from emp where deptno = 30 );
或
select ename,sal
from emp
where sal > (select max(sal) from emp where deptno = 30 group by deptno);

（37）查询每个部门中的员工数量、平均工资和平均工作年限。

select deptno,dname,count(*) empnum,trunc(avg(sal),2) avgsal,trunc(avg(months_between(sysdate,hiredate)/12)) workyear
from emp e right join dept d using(deptno)
group by deptno,dname;

（38）查询从事同一种工作但不属于同一部门的员工信息。

select distinct e1.job,e1.ename,e1.deptno
from emp e1,emp e2
where e1.job=e2.job and e1.deptno != e2.deptno
order by e1.job;
--感觉有问题

（39）查询各个部门的详细信息以及部门人数、部门平均工资。

select d.*,e.cou empnum,e.avgs avgsal
from dept d left join(select deptno,count(*) cou,avg(sal) avgs from emp group by deptno) e
on d.deptno = e.deptno;

（40）查询各种工作的最低工资。

select job,min(sal) from emp group by job;

（41）查询各个部门中的不同工种的最高工资。

select job,max(sal),deptno
from emp e1 
group by deptno,job
order by deptno;
--注意思考

（42）查询10号部门员工以及领导的信息。

select e.ename empname,m.ename mgrname
from  emp m, (select ename,mgr from emp where deptno = 10) e
where e.mgr=m.empno(+);

（43）查询各个部门的人数及平均工资。

select deptno,dname,e.empnum,e.avg_sal
from dept d left join (select deptno ,count(*) empnum,avg(sal) avg_sal from emp group by deptno) e
using(deptno);

（44）查询工资为某个部门平均工资的员工信息。

select * from emp e,(select deptno,trunc(avg(sal)) avgsal from emp group by deptno) d
where e.sal = d.avgsal;

（45）查询工资高于本部门平均工资的员工的信息。

select e.* from emp e,(select deptno,trunc(avg(sal)) avgsal from emp group by deptno) d
where e.deptno = d.deptno and e.sal > d.avgsal;

（46）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

select e.*,d.avgsal from emp e,(select deptno,trunc(avg(sal)) avgsal from emp group by deptno) d
where e.deptno = d.deptno and e.sal > d.avgsal;

（47）查询工资高于20号部门某个员工工资的员工的信息。

select *
from emp e
where sal >any (select sal from emp where deptno = 20 );

（48）统计各个工种的人数与平均工资。

select job,count(*),avg(sal)
from emp
group by job;

（49）统计每个部门中各个工种的人数与平均工资。

select job,count(*),avg(sal), e.deptno
from emp e,(select deptno from emp group by deptno) d
where e.deptno = d.deptno
group by job,e.deptno
order by e.deptno;

（50）查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。

select *
from emp e
where (sal,comm) in (select sal,comm from emp where deptno = 10 );
或
select *
from emp e,(select sal,comm from emp where deptno = 10 ) d
where e.sal = d.sal and e.comm = d.comm ;

（51）查询部门人数大于5的部门的员工的信息。

select * 
from emp ,(select deptno from emp group by deptno having count(*)>5) d
where emp.deptno = d.deptno;

（52）查询所有员工工资都大于1000的部门的信息。

select *
from dept d,(select deptno from emp group by deptno having min(sal)>1000) e
where d.deptno=e.deptno;

（53）查询所有员工工资都大于1000的部门的信息及其员工信息。

select *
from dept d 
    natural join (select deptno from emp group by deptno having min(sal)>1000) e 
    natural join emp;

（54）查询所有员工工资都在900~3000之间的部门的信息。

select *
from dept d,(select deptno 
             from emp 
             group by deptno
             having 900<=min(sal) and max(sal)<=3000 ) e
where d.deptno=e.deptno;

（55）查询所有工资都在900~3000之间的员工所在部门的员工信息。

select *
from dept d 
    natural join (select deptno 
             from emp 
             group by deptno
             having 900<=min(sal) and max(sal)<=3000 ) e
    natural join emp;

（56）查询每个员工的领导所在部门的信息。

select *
from dept d natural join (select m.deptno ,m.ename mgrname,e.ename empname
            from emp e,emp m
            where e.mgr = m.empno)
order by deptno;

（57）查询人数最多的部门信息。

select *
from dept natural join 
    (select deptno 
     from (select count(*) num,deptno  from emp group by deptno) m
     where m.num = (select max(num) maxnum 
                    from (select count(*) num from emp group by deptno))
    );
--有待思考此题

（58）查询30号部门中工资排序前3名的员工信息。

select *
from (select rownum rn ,temp.* 
      from(select * from (select * from emp where deptno = 30 order by sal desc )) temp
      where rownum<= 3)
where rn>= 1;
--分页查询

（59）查询所有员工中工资排在5~10名之间的员工信息。

select * 
from (select rownum rn ,temp.* 
     from (select * from (select * from emp order by sal desc)) temp
     where rownum <=10
     )
where rn >= 5;

（60）查询SMITH员工及所有其直接、间接下属员工的信息。

--查询其直接下属
select m.ename mgrname,e.ename diremp,e.empno dirempno
from emp m ,emp e
where m.ename = 'KING' and m.empno = e.mgr;
--查询其直接和间接下属
select m.mgrname, m.diremp,e.ename indiremp
from emp e,(select m.ename mgrname,e.ename diremp,e.empno dirempno
            from emp m ,emp e
            where m.ename = 'KING' and m.empno = e.mgr) m
where e.mgr = m.dirempno;

（61）查询SOCTT员工及其直接、间接上级员工的信息。

--查询其直接领导
select e.ename emp,m.ename dirmgr, m.mgr mmgr
from emp e,emp m
where e.ename = 'SCOTT' and e.mgr = m.empno;
--查询其直接和间接领导
select e.emp,e.dirmgr,m.ename indirmgr
from emp m,(select e.ename emp,m.ename dirmgr, m.mgr mmgr
             from emp e,emp m
             where e.ename = 'SCOTT' and e.mgr = m.empno) e
where m.empno = e.mmgr;

（62）以树状结构查询所有员工与领导之间的层次关系。

--查询大boss
select m.ename boss,m.empno bossno
from emp m
where m.mgr is null;
--查询一级领导
select boss,e.ename mgr1,e.empno mgr1no
from emp e ,(select m.ename boss,m.empno bossno 
             from emp m
             where m.mgr is null) m
where e.mgr = bossno;
--查询二级领导
select boss,mgr1,e.ename mgr2,e.empno mgr2no
from emp e,(
            select boss,e.ename mgr1,e.empno mgr1no
            from emp e ,(select m.ename boss,m.empno bossno 
                         from emp m
                         where m.mgr is null) m
            where e.mgr = bossno) m
where e.mgr = m.mgr1no;
--查询三级领导
select distinct boss,mgr1,mgr2,
case
    when e.mgr = m.mgr2no then e.ename
    else null
end mgr3,
case
    when e.mgr = m.mgr2no then e.empno
    else null
end mgr3no
from emp e,(
            select boss,mgr1,e.ename mgr2,e.empno mgr2no
            from emp e,(
                        select boss,e.ename mgr1,e.empno mgr1no
                        from emp e ,(select m.ename boss,m.empno bossno 
                                     from emp m
                                     where m.mgr is null) m
                        where e.mgr = bossno) m
            where e.mgr = m.mgr1no) m;
--一直嵌套下去会出问题，暂时不会写，待思考

（63）向emp表中插入一条记录，员工号为1357，员工名字为oracle，工资为2050元，部门号为20，入职日期为2002年5月10日。

insert into emp (empno,ename,sal,deptno,hiredate) values(1357,'oracle',2050,20,to_date('2002-5-10','yyyy-mm-dd'));

（64）向emp表中插入一条记录，员工名字为FAN，员工号为8000，其他信息与SMITH员工的信息相同。

select * from emp where ename = 'SMITH';

insert into emp (ename,empno,mgr,job,sal,hiredate,deptno) 
values('FAN',8000,7902,'CLERK',800,'17-12月-80',20);

（65）将各部门员工的工资修改为该员工所在部门平均工资加1000。

update emp set sal = (select s.new_sal
                    from (select trunc(avg(sal))+1000 new_sal,deptno from emp group by deptno) s
                    where emp.deptno = s.deptno );

练习题参考答案

results matching ""

No results matching ""