数据库学习7-25

课堂练习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 = dept.deptno) dname, job
  from emp a
 where ename not in ('SCOTT', 'BLAKE') and exists (select 1
          from emp b where ename not in ('SCOTT', 'BLAKE') and 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;

mysql

练习1
1.查询部门20的员工，每个月的工资总和及平均工资。
select sum(sal),avg(sal)
from emp
where deptno=20;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。
select count(1),max(sal),min(sal)
from emp,dept
where emp.deptno=dept.deptno and loc='CHICAGO';

3.查询员工表中一共有几种岗位类型
select distinct job
from emp;

练习2

1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select emp.deptno,dname,count(emp.deptno),max(sal),min(sal),sum(sal),avg(sal)
from emp,dept
where emp.deptno=dept.deptno
group by emp.deptno,dname;

2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select emp.deptno,dname,job,count(emp.deptno),max(sal),min(sal),sum(sal),avg(sal)
from emp,dept
where emp.deptno=dept.deptno
group by emp.deptno,dname,job;

3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
select count(a.ename),a.empno,a.ename,b.*
from emp a,emp b
where a.empno=b.mgr
group by a.empno,a.ename;

练习3

1.查询部门人数大于2的部门编号，部门名称，部门人数。
select deptno,dname,count(1)
from dept
having count(2)>2;

2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
????
select emp.deptno,dname,count(1),avg(sal)
from emp,dept
where emp.deptno=dept.deptno and sal>(select avg(sal) from emp where avg(sal)=2000)
having count(1)>2
group by emp.deptno,dname
order by count(1);
练习4
1.查询入职日期最早的员工姓名，入职日期
select ename,min(hiredate)
from emp
group by ename;

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
select ename,sal,dname
from emp,dept
where emp.deptno=dept.deptno and loc='CHICAGO' and sal>(select sal from emp where ename='SMITH');

3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select ename,hiredate
from emp
where hiredate<(select min(hiredate) from emp where deptno=20);

4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
select emp.deptno,dname,count(emp.deptno)
from emp,dept
where emp.deptno=dept.deptno
having count(emp.deptno)>(select avg(emp.deptno) from emp);

练习5
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
select ename,hiredate
from emp
where deptno!=10 and hiredate>any(select hiredate from emp where deptno=10);

2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
select ename,hiredate
from emp
where deptno!=10 and hiredate>all(select hiredate from emp where deptno=10);

3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
select ename,job
from emp
where deptno!=10 and job=any(select job from emp where deptno=10);

课后作业
1.查询部门平均工资在2500元以上的部门名称及平均工资。
select dname,avg(sal)
from emp,dept
where emp.deptno=dept.deptno
having avg(sal)>2500;

2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
select avg(sal)
from emp
where ename not like "SA%"
having avg(sal)>2500
order by avg(sal);

3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
select dname,minsal,round(maxsal)
from dept d ,(select min(sal) minsal ,max(sal) maxsal from emp ) a
where d.deptno=a.deptno
having count(d.deptno)>2;

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
select job,sumsal
from emp a,(select sum(sal) sumsal from emp having sum(sal)>=2500) b
where job<>"SALESMAN" and a.deptno=b.deptno;

5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
select a.empno,a.ename,minsal
from emp a left outer join emp b on a.deptno=b.deptno
join (select min(sal) minsal from emp having min(sal)>3000) con a.deptno=c.deptno
order by minsal desc;

6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
select sal,empno,ename
from emp
where sal>(select sal feom emp where empno=7782) and job in(select job from emp where wmpno=7369);

7.查询工资最高的员工姓名和工资。
select ename,sal
from emp
where sal>(select max(sal) from emp );

8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select d.deptno,dname,minsal
from emp a,dept d,(select min(sal) from emp having min(sal) >(select min(sal) from emp where deptno=10)) c
where a.deptno=d.deptno and a.deptno=c.deptno;

9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
select empno,ename,sal
from emp a
where sal in (select min(sal) from emp group by deptno);

10.显示经理是KING的员工姓名，工资。
select a.ename,a.sal
from emp a,emp b
where a.deptno=b.deptno and a.mgr=b.empno and b.ename="KING";

11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
select ename,sal,hiredate
from emp
where hiredate>(select hiredate from emp where ename="SMITH");

12.使用子查询的方式查询哪些职员在NEW YORK工作。
select ename
from emp
where ename in (select ename from emp where deptno in (select deptno from dept where loc ="NEW YORK"));

13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
select ename,hiredate
from emp
where ename not like "SMITH" and deptno in (select deptno from emp where ename ="SMITH");

14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
select empno,ename
from emp
where sal>(select avg(sal) fom emp );

15. 显示部门名称和人数
select dname,count(1)
from emp,dept
where emp.deptno=dept.deptno
group by dname;

16. 显示每个部门的最高工资的员工
select *
from emp a
where sal=(select max(sal) from emp b where a.deptno=b.deptno);

17. 显示出和员工号7369部门相同的员工姓名，工资。
select ename,sal
from emp
where deptno in (select deptno from emp where empno =7369);

18. 显示出和姓名中包含“W”的员工相同部门的员工姓名
select ename
from emp
where deptno in (select deptno from emp where ename like %W%);