测试必须掌握的SQL语句带习题

http://blog.csdn.net/yu0089/article/details/9024011

最近在学习Oracle，对测试人员而言必须掌握两种语言：第一种是DML，数据操纵语言 (Data Manipulation Language) 是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。第二种是：DQL，数据查询语言 (Data Query Language) 是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。核心指令为SELECT，以及一些辅助指令，如FROM、WHERE等，FROM：表示来源，可以搭配JOIN做链接查询； WHERE：过滤条件；GROUP BY：在使用聚合函数时用到，如SUM,COUNT,MAX,AVG；HAVING：对聚合结果进行筛选，这是和WHERE的不同点；ORDER BY：排序。

 一下是必须掌握的SQL习题：

1、列出至少有一个员工的所有部门
select d.*,ed.cou
from dept d,(select deptno,count(empno) cou from emp
group by deptno having count(empno)>1) ed
where d.deptno=ed.deptno;
2、列出薪金比“SMITH”多的所有员工。
  ·求出SMITH的薪金
select sal from emp where ename='SMITH';
 ·求所有
select * from emp
where sal>(select sal from emp where ename='SMITH');
3、列出所有员工的姓名及其直接上级的姓名
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno(+);
4、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门，
select d.deptno,d.dname,e.empno,e.ename
from dept d,emp e
where d.deptno=e.deptno(+);
6、列出所有“CLERK”人员的姓名及其部门名称，部门的人数
select e.ename,d.dname,ed.cou
from emp e,dept d,(select deptno,count(empno) cou from emp group by deptno) ed
where job='CLERK'and e.deptno=d.deptno and ed.deptno=e.deptno;
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
·按工作分组,分组条件是最低薪金大于1500
select job,min(sal)
from emp
group by job having min(sal)>1500;
·求全部的雇员人数
select count(e.empno),e.job
from emp e
where e.job in(select job
            from emp
            group by job having min(sal)>1500)
group by e.job;
8、列出在部门“SALES”工作的员工姓名，假定不知道销售部的部门编号
·通过dept表查询出销售部的部门编号
select deptno from dept where dname='SALES';
·将之前的查询作为子查询
select ename
from emp where deptno=(select deptno from dept where dname='SALES');
9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
·公司的平均工资
select avg(sal) from emp;
·列出薪金高于平均薪金
select * from emp
where sal>(select avg(sal) from emp);
·与部门表关联查询出所在部门的信息
select e.*,d.loc
from emp e,dept d
where sal>(select avg(sal) from emp)and e.deptno=d.deptno;
·与自身关联查询上级领导
select e.ename,e.empno,m.empno,m.ename,d.deptno,d.dname,d.loc
from emp e,dept d,emp m
where e.sal>(select avg(sal) from emp)
          and e.deptno=d.deptno
          and e.mgr=m.empno(+);
·求出雇员的工资等级
select e.ename,e.empno,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc
from emp e,dept d,emp m,salgrade s
where e.sal>(select avg(sal) from emp)
          and e.deptno=d.deptno
          and e.mgr=m.empno(+)
           and e.sal between s.losal and s.hisal;
10、列出与scott从事相同工作的所有员工及部门名称
·找到Scott的工作
select job from emp where ename='SCOTT';
·找出与其工作相同的雇员
select ename,empno,job,sal
from emp
where job=(select job from emp where ename='SCOTT');
·这是不应该出现SCOTT
在加个条件;and ename!='SCOTT';
select ename,empno,job,sal
from emp
where job=(select job from emp where ename='SCOTT')
           and ename!='SCOTT';
·部门名称
select e.ename,e.empno,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where job=(select job from emp where ename='SCOTT')
           and ename!='SCOTT'
   and e.deptno=d.deptno;
11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
·列出30部门员工薪金
select sal from emp where deptno=30;
·以上作为子查询
select ename,sal
from emp
 where sal in(select sal from emp where deptno=30)
                 and deptno!=30;
12、列出薪金高于部门30中员工的薪金的所有员工的姓名和薪金、部门名称

·使用>ALL
select ename,sal
from emp
 where sal >all(select sal from emp where deptno=30)
 and deptno!=30;
·使用表关联
select e.ename,e.sal,d.dname,d.loc
from emp e,dept d
 where e.sal >all(select sal from emp where deptno=30)
 and e.deptno!=30 and d.deptno=e.deptno;

13、列出在每个部门工作的员工数量，平均工资和平均服务期限

select d.dname,count(e.empno),avg(sal),avg(months_between(sysdate,e.hiredate)/12)year
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
14、列出所有员工的姓名，部门名称和工资
select e.ename,d.dname,e.sal
from emp e,dept d
where e.deptno=d.deptno;
15、列出所有部门的详细信息和部门人数
select d.*,nvl(ed.count,0)
from dept d,(select deptno,count(empno) count from emp group by deptno) ed
where d.deptno=ed.deptno(+);
16、列出各种工作的最低工资及从事此工作的雇员姓名
·最低工资的工作
select min(sal) from emp group by job;
·按工资查询
select * from emp
where sal in(select min(sal) from emp group by job);
17、列出各部门经理的最低薪金
select deptno,min(sal)
from emp
where job='MANAGER'
group by deptno;
18、列出所有员工的年薪，按年薪降序排列
select ename,(sal+nvl(comm,0))*12 income
from emp
order by income;
19、查出某个员工的上级主管，并求出这些主管薪水超过3000的
select distinct m.ename,m.sal
from emp e,emp m
where m.sal>3000 and e.mgr=m.empno;
20、求出部门名称中带‘S’字符的部门员工的工资合计，部门人数
·求部门名称带‘S’的部门
select deptno,sum(sal),count(empno)
from emp
where deptno in(select deptno from dept where dname like '%S%')
group by deptno;

21、给任职超过10年的人加薪10%
update emp set sal=sal+sal*0.1
where months_between(sysdate,hiredate)/12>10;