课堂练习1

1.查询和simth一个部门的其他员工姓名。

 select ename from emp  where deptno=（select deptno from emp where ename='SMITH'）and ename !='SMITH';

2.查询存在员工的部门的工作地点。

 select loc from dept   where deptno in(select distinct deptno from emp);

3.查询工资为全公司最高工资的员工的部门名称。

 select dname from dept   where deptno in (select deptno from emp where sal=(select max(sal) from emp ));

4.查询高于公司平均高工资的员工的岗位。

 select job from emp  where sal=(select avg(sal) from emp);

 

课堂练习2

1.查询所有员工工资都大于1000的部门的信息

 select * from dept where deptno in   (select deptno from emp having min(sal)>1000 );

2.查询所有员工的工资都大于1000的部门信息及其员工信息

 select *from dept , emp where emp.deptno=dept.deptno and emp.sal>1000;

3.查询所有员工工资都在900~3000之间的部门信息

 select * from dept where deptno in (select deptno from emp where sal in (900,3000));

4.查询所有工资都在900~3000之间的员工所在部门的员工信息

 select * from emp where deptno in (select deptno from emp where sal in (900,3000));

 

课堂练习3

1.查询出每个人的名字，岗位,部门编号，所在部门的最低工资。

 select ename,job,emp.deptno,minsal  from emp,(select deptno,min(sal) minsal from emp group by deptno) dmp  where emp.deptno=dmp.deptno;

2.查询每个人的工资等级和姓名还有所在岗位的平均薪资  

select grade,ename,jobavgsal  from emp,salgrade,(select avg(sal) jobavgsal from emp group by job) jobsal  where sal between losal and hisal and emp.job=jobsal.job;

3.查询出各个部门工资最高的人信息  

 select * from emp   where  (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

 

oracle

第七章

练习1

 1.查询入职日期最早的员工姓名

 select ename from emp where hiredate=(select min(hiredate) from emp );

 2.查询工资比Jones工资高的员工姓名，工资  

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

 select ename,sal  from emp a,emp b  where a.sal>b.sal and b.ename='JONES';  

 select ename,sal  from emp a  where exists(select 1 from emp b where a.sal>b.sal and b.name='JONES';)

 3.查询工资最低的员工姓名

 select ename from emp where sal =(select min(sal) from emp );

 4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

 select emp.deptno,dname,count(ename)  from emp,dept  where emp.deptno=dept.deptno   having  count(ename)>(select avg(count(ename)) from emp group by deptno)  group by emp.deptno,dname;

 

练习2

 1.查询入职日期比20部门任何一个员工晚的员工姓名、入职日期，不包括20部门员工  

select ename,hiredate from emp  where hiredate>(select min(hiredate) from emp )where deptno!= 20;

 select ename,hiredate from emp  where hiredate>any(select hiredate from emp) where deptno!= 20;

 select ename,hiredate  from emp a  where exists(select 1 from emp b where b.deptno=20 having a.hiredate >min(hiredate))and a.deptno;

 2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

 select ename,hiredate from emp  where hiredate>(select max( hiredate) from emp)where deptno!= 10;

 select ename,hiredate from emp  where hiredate>all(select hiredate from emp) where deptno!= 10;

 3.查询职位和20部门任何一个员工职位相同的员工姓名，职位，不包括10部门员工  

select ename,job from emp  where job in (select job from emp where deptno=20) and deptno!=10;

 4.查询比本职位平均工资高的员工姓名、职位，部门名称，部门平均工资

 select ename,  a.job,  (select dname from dept c where a.deptno = c.deptno),   deptnoAvgSal  

 from emp a,   (select deptno, avg(sal) deptnoAvgSal from emp group by deptno) b,  (select job, avg(sal) jobAvgSal from emp group by job) d  

  where a.deptno = b.deptno        

  and a.job = d.job        

  and a.sal > d.jobAvgSal;

 

     select ename,   job,   (select dname from dept c where a.deptno = c.deptno),   (select avgsal   from (select avg(sal) avgsal, deptno from emp group by deptno) e            

   where e.deptno = a.deptno)      

  from emp a     

  where exists       (select 1   from emp b      where a.job = b.job having a.sal > avg(b.sal));

 5.查询职位和经理同SCOTT或BLAKE相同的员工姓名、职位，不包括SCOOT和BLAKE本人。

 select ename,job from emp  where job,mgr in (select job,mgr from emp where ename in('SCOTT','BLAKE'))   and not ename in ('SCOTT','BLAKE');

 select a.ename,a.job  from emp a  where a.job=(select job from emp b where a.mgr=b.mgr and b.ename in ('SCOTT','BLAKE'))  and not ename in ('SCOTT','BLAKE');    

 6.查询不是经理的员工姓名，及他所管理的员工人数。

 select a.ename ,count(b.ename) from emp a,emp b  where a.job !='MANAGER' and  b.mgr=a.empno  group by a.ename;

 

练习3

 1.查询入职日期最早的前5名员工姓名，入职日期。

 select ename,hiredate  from emp a  where exists (select 1 from emp b where a.hiredate>b.hiredate having count(1)<5);

 2.查询工资最高的前5名员工姓名、工资、部门名称。 

  select ename,sal,(select dname from dept where a.deptno=b.deptno)  from emp a  where a.sal<b.sal having count(1)<5;   

 

练习4

 1.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称。

 select ename,hiredate,dname    from dept,(select rownum rn,ename,hiredate,deptno from emp where rownum<(1*5)+1 ) hd    where rn>(1-1)*5 and dept.deptno=hd.deptno ;

 2.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称、工资。

 select ename,hiredate,sal,dname  from dept,(select rownum rn,ename,hiredate,sal,deptno from (select * from emp order bysal) where rownum <(1*5)+1) hs  where rn>(1-1)*5 and dept.deptno=hs.deptno;

 

课后作业

 1.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

 select sal,deptno,ename  from emp  where sal>(select sal from emp where empno=7782) and job=(select job from emp where empno=7369);

 2.查询工资最高的员工姓名和工资。

 select ename,sal from emp  where sal=(select max(sal) from emp);

 3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

 select emp.deptno,dname,min(sal)   

from emp,dept    

where emp.deptno=dept.deptno  

 having min( sal) >(select min(sal) from emp where deptno=10)  

 group by emp.deptno,dname;

 4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

 select empno,ename,sal  from emp,(select min(sal)  minsal,deptno from emp group by deptno) a  where sal in minsal and emp.deptno=a.deptno;

 5.显示经理是KING的员工姓名，工资。

 select a.ename,a.sal  

 from emp a,emp b  

 where a.mgr=b.empno and a.mgr=(select empno from emp where ename='KING');

 6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

 select ename,sal,hiredate

 from emp

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

 7.使用子查询的方式查询哪些职员在NEW YORK工作。

 select ename  

from emp

 where deptno=(select deptno from dept where loc='NEW YORK');

 8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。

 select ename,hiredate

 from emp

 where ename!='SMITH' and deptno=(select deptno from emp where ename='SMITH');

 9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。

 select empno,ename   from emp    where sal>(select avg(sal) from emp);

 10.写一个查询显示其上级领导是King的员工姓名、工资。

 select ename,sal  from emp  where mgr=(select empno from emp where ename='KING');

 11.显示所有工作在RESEARCH部门的员工姓名，职位。

 select ename,job  from emp  where deptno=(select deptno from dept where dname='RESEARCH');

 12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。

  select deptno,avg(sal)    from emp    having avg(sal)>(select avg(sal) from emp where deptno=20)    group by deptno ;

 13.查询大于本部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。

 select ename,sal,deptnoavgsal,sal-deptnoavgsal  from emp a,(select avg(sal) deptnoavgsal,deptno from emp group by deptno) b  where a.deptno=b.deptno and a.sal>b.deptnoavgsal;

 14. 列出至少有一个雇员的所有部门

 select * from dept a where exists(select 1 from emp b where a.deptno = b.deptno );

 15. 列出薪金比"SMITH"多的所有雇员

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

 16. 列出入职日期早于其直接上级的所有雇员

 select a.ename  from emp a,emp b  where a.hiredate< b.hiredate and b.empno=a.mgr;

 17. 找员工姓名和直接上级的名字  

select a.ename,b.ename  from emp a,emp b  where a.mgr=b.empno(+);

 18. 显示部门名称和人数  

select dname,count(1)  from emp,dept  where emp.deptno=dept.deptno  group by dname;

 19. 显示每个部门的最高工资的员工

 select ename   from emp ,(select max(sal) maxsal,deptno from emp group by deptno) a    where sal=maxsal and emp.deptno=a.deptno;

 select ename  from emp  where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

 20. 显示出和员工号7369部门相同的员工姓名，工资  

select ename,sal  from emp  where deptno=(select deptno from emp where empno=7369);

 21. 显示出和姓名中包含"W"的员工相同部门的员工姓名

 select ename  from emp  where deptno=(select deptno from emp where ename like '%W%');

 22. 显示出工资大于平均工资的员工姓名，工资

 select ename,sal  from emp  where sal>(select avg(sal) from emp);

 23. 显示出工资大于本部门平均工资的员工姓名，工资

 select ename,sal    from emp,(select avg(sal) avgsal ,deptno from emp group by deptno) a    where sal>avgsal and emp.deptno=a.deptno;

 24. 显示每位经理管理员工的最低工资，及最低工资者的姓名  

select ename,minsal   from emp a,(select min(sal) minsal ,mgr from emp b group by mgr) b    where job='MANAGER' and a.mgr=b.mgr and a.sal=b.minsal;

 25. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间  

select ename,hiredate  from emp  where hiredate>(select hiredate from emp where sal=(select max(sal) from emp));

 26. 显示出平均工资最高的的部门平均工资及部门名称  

 select dname,avg(sal) avgsal  

   from dept,emp,(select  deptno from emp  where sal=(select max(sal) from emp)) a   

 where dept.deptno=a.deptno  and emp.deptno=dept.deptno     

    group by dname;