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

;OY'A1[$W0    最近在学习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：排序。51Testing软件测试网JC9daPPO9_E k

%w*P x%pT0 一下是必须掌握的SQL习题：

1、列出至少有一个员工的所有部门51Testing软件测试网&]
O)sedQ!BgC
select d.*,ed.cou51Testing软件测试网G BnN$E0o!y
from dept d,(select deptno,count(empno) cou from emp51Testing软件测试网MC0SF)Ato
group by deptno having count(empno)>1) ed51Testing软件测试网8ai s v
fu,[j
where d.deptno=ed.deptno;
#xs}8?\5n m)@H02、列出薪金比“SMITH”多的所有员工。51Testing软件测试网6F9~|1j(j%lC#k9UFf
  ·求出SMITH的薪金51Testing软件测试网El9q_B2Ql
select sal from emp where ename='SMITH';51Testing软件测试网%vf$ok;CK;G
 ·求所有
iffS7o,Z0select * from emp51Testing软件测试网|4K HRs(D9[Nh2K:z0]WB
where sal>(select sal from emp where ename='SMITH');51Testing软件测试网1A!xlUbd&C
3、列出所有员工的姓名及其直接上级的姓名
\$]E(OLmpH0select e.ename,m.ename
!H9F+x6Iv5}7|N^0from emp e,emp m
j6V7q$e%D4wW0where e.mgr=m.empno(+);
6_0z8C:p(jX3E-q-Xm04、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称51Testing软件测试网P%gJ)cy%N
select e.empno,e.ename,d.dname51Testing软件测试网/D
WIZXCxT
from emp e,emp m,dept d51Testing软件测试网Hh:kSx]
where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;51Testing软件测试网DE]}&J%q'z9@{~
5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门，
.lsT PO0select d.deptno,d.dname,e.empno,e.ename51Testing软件测试网I'C?D%l z
from dept d,emp e51Testing软件测试网'tB.ji%}(g(]%V
where d.deptno=e.deptno(+);
I/F'{b(nX$Mg!X"V06、列出所有“CLERK”人员的姓名及其部门名称，部门的人数51Testing软件测试网!C0Y`"n3j
select e.ename,d.dname,ed.cou51Testing软件测试网@ X6[#{,Pc-o1A*q5l'q
q
from emp e,dept d,(select deptno,count(empno) cou from emp group by deptno) ed
y'PGNb DU~0where job='CLERK'and e.deptno=d.deptno and ed.deptno=e.deptno;

X:bp1G.n*\07、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
!_]5KDp m-}q0·按工作分组,分组条件是最低薪金大于1500
%Qc8Y*uGrc:?!a;{0select job,min(sal)51Testing软件测试网o[!N
v.G[e(N$x
from emp51Testing软件测试网's"Y2fR T1g2e$N IQ
group by job having min(sal)>1500;
8IL$^z/d"v"c!Jx0·求全部的雇员人数51Testing软件测试网#TW4U&Z3B8w[,K
select count(e.empno),e.job51Testing软件测试网Qg_8zw]
from emp e51Testing软件测试网^"a|3@b6T7D5`GJy
where e.job in(select job
JH4?)JQ0            from emp51Testing软件测试网v{#cvT
            group by job having min(sal)>1500)51Testing软件测试网!_E/?/zX*\1xoK
group by e.job;
'J9z&A!j{08、列出在部门“SALES”工作的员工姓名，假定不知道销售部的部门编号51Testing软件测试网~nMv5l"iQT#e
·通过dept表查询出销售部的部门编号51Testing软件测试网7}$V*AD-{pw5}L m"Y
select deptno from dept where dname='SALES';51Testing软件测试网 Ai$ZJC$`#x7FF6P
·将之前的查询作为子查询
c8G:T}sX0select ename51Testing软件测试网6fI#M:dD,D$pyeo-D
from emp where deptno=(select deptno from dept where dname='SALES');
_5g!Ifa09、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。51Testing软件测试网&n2`8] n~ }
·公司的平均工资
ZB#GN%eQ
Q"t``7T0select avg(sal) from emp;51Testing软件测试网&i Q'}dgx-C
·列出薪金高于平均薪金
3tw!B0nm|3^0select * from emp
]m"n(B:mS9M})AWT F0where sal>(select avg(sal) from emp);
?+s"d&b R-d*j%L0·与部门表关联查询出所在部门的信息
_(Ut)^%QYm{0select e.*,d.loc
B+kh;bhQ&QPO0from emp e,dept d
NHH_+a'_(^0where sal>(select avg(sal) from emp)and e.deptno=d.deptno;51Testing软件测试网X3GLl'V3YC
·与自身关联查询上级领导
y*W0H_*y+[0select e.ename,e.empno,m.empno,m.ename,d.deptno,d.dname,d.loc
&x)gmKq;I C0from emp e,dept d,emp m51Testing软件测试网*s r oF;u|f'`FT
where e.sal>(select avg(sal) from emp)51Testing软件测试网g?i/d0uN5@
          and e.deptno=d.deptno
6d.M+s"[
A]0          and e.mgr=m.empno(+);51Testing软件测试网c;adD~~l
·求出雇员的工资等级
Y)FP2? ~0select e.ename,e.empno,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc51Testing软件测试网jgxm0l@6m5]
from emp e,dept d,emp m,salgrade s
,n{7k[2NfL0where e.sal>(select avg(sal) from emp)51Testing软件测试网f3}J#`6p1yI2a Ij3L-p
          and e.deptno=d.deptno51Testing软件测试网vL+}A#Iyj(`^
          and e.mgr=m.empno(+)51Testing软件测试网Z4^r*h*rI
           and e.sal between s.losal and s.hisal;
]l'r8`*S]|010、列出与scott从事相同工作的所有员工及部门名称51Testing软件测试网r1RY g*h
·找到Scott的工作
,BL0kTVO }D2sQ\#k0select job from emp where ename='SCOTT';51Testing软件测试网@UZCB
·找出与其工作相同的雇员
4B_"\$i^w}2_:K0select ename,empno,job,sal
z
xMO+e;}!E0from emp
}/v.C.o)O+K@2c0where job=(select job from emp where ename='SCOTT');
ap6`B6gs2~
Xs#km0·这是不应该出现SCOTT51Testing软件测试网 v,tZ@2N5c`0jA)i
在加个条件;and ename!='SCOTT';
wc z8v[1mQ0select ename,empno,job,sal
(A*M[r7z!T^s8tQ0from emp51Testing软件测试网4yu3Rj"o7qB n
where job=(select job from emp where ename='SCOTT')51Testing软件测试网({ \!a%k1i|9B~
o
           and ename!='SCOTT';
6N`.v}1Hu0·部门名称
g4vi
W#w1uk&Llo_0select e.ename,e.empno,e.job,e.sal,d.dname,d.loc
8]Ya8I"RZj0from emp e,dept d
&z3`l[8KE2HC0where job=(select job from emp where ename='SCOTT')
6N S-gm&_:k_%|0           and ename!='SCOTT'
#m'f"kJ3ya NY0   and e.deptno=d.deptno;
][%cNn011、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
.v&E5s~$V0\1} T0·列出30部门员工薪金
G&s4pj6U*e:f5\%c0select sal from emp where deptno=30;
m+P1O tEd/W0·以上作为子查询51Testing软件测试网V']Kqq'l-G
select ename,sal51Testing软件测试网3h TY1~*Mk
from emp
f6x6h-T*A@
Bh0 where sal in(select sal from emp where deptno=30)51Testing软件测试网1\%T$L6v ?]z!?
                 and deptno!=30;51Testing软件测试网g6G~a,MM&B-cB;v;}
12、列出薪金高于部门30中员工的薪金的所有员工的姓名和薪金、部门名称

·使用>ALL51Testing软件测试网(wfm*h g%YEN5c0OJ
select ename,sal
3i4}F9C5`;n?0from emp
_K3JI;s#f7W*C0 where sal >all(select sal from emp where deptno=30)51Testing软件测试网Z fg2S-P/lWm
 and deptno!=30;
_6wY!t+me$?+s0·使用表关联51Testing软件测试网c.|+q4u6b S#}
select e.ename,e.sal,d.dname,d.loc51Testing软件测试网zX7krW0zY1D
from emp e,dept d51Testing软件测试网/t
f+dZ+p,E?|L~}
 where e.sal >all(select sal from emp where deptno=30)51Testing软件测试网3Fo(|p/l^v+[ j![
 and e.deptno!=30 and d.deptno=e.deptno;51Testing软件测试网%Q!l*J:HPw;Iz9P5o

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

!M,F8R w+I'|0select d.dname,count(e.empno),avg(sal),avg(months_between(sysdate,e.hiredate)/12)year51Testing软件测试网$y5|D1g3F2[|!~
from emp e,dept d51Testing软件测试网*Sp0[_E @'D,_+H
where e.deptno=d.deptno51Testing软件测试网5@+A F8Mk#zy}
group by d.dname;51Testing软件测试网n'am9?,^*E%A:y
14、列出所有员工的姓名，部门名称和工资51Testing软件测试网!sN["B1`e.{
select e.ename,d.dname,e.sal
l?_{"?D0from emp e,dept d51Testing软件测试网h6E4]Me/B2z
where e.deptno=d.deptno;
\
b\qEwXn8[,n o015、列出所有部门的详细信息和部门人数51Testing软件测试网q1R L+Y.NANF
select d.*,nvl(ed.count,0)51Testing软件测试网pd$SD?a
from dept d,(select deptno,count(empno) count from emp group by deptno) ed
7Om/nAn0where d.deptno=ed.deptno(+);51Testing软件测试网,pB)dZ vGzoH-I
16、列出各种工作的最低工资及从事此工作的雇员姓名51Testing软件测试网m)K3[/ma.o
·最低工资的工作51Testing软件测试网
|M-eX/f5bD
select min(sal) from emp group by job;51Testing软件测试网T2D)t*\1H} e*k
·按工资查询
b^.Q~nc`;h0select * from emp
x-H*V'z!U0]
N0where sal in(select min(sal) from emp group by job);51Testing软件测试网/z9C UfdVz
17、列出各部门经理的最低薪金
|K)l VR|"jqK,K0select deptno,min(sal)51Testing软件测试网Z
bw,Avq
from emp
4d,Xr@m[-]0where job='MANAGER'51Testing软件测试网&\ t8zuI
group by deptno;51Testing软件测试网_9o&Mr$t.oo
18、列出所有员工的年薪，按年薪降序排列51Testing软件测试网(N+iY Jx/Z6up
select ename,(sal+nvl(comm,0))*12 income
%} ^0C/J`y0from emp
T9r]dD{#t@)t0order by income;51Testing软件测试网8r8DP%F%]'W8A~gi8vD
19、查出某个员工的上级主管，并求出这些主管薪水超过3000的51Testing软件测试网VK\&?.h!d"e7U
select distinct m.ename,m.sal
M U,y
U x:\V%wL!\w0from emp e,emp m
y0L+q)UTq*Z2A0where m.sal>3000 and e.mgr=m.empno;
TfR@"f_v[020、求出部门名称中带‘S’字符的部门员工的工资合计，部门人数
+MY-[c9]9nW0·求部门名称带‘S’的部门
0o;gvp!~
?Vsk$j o0select deptno,sum(sal),count(empno)
,q%H]%|d'Gd0from emp
eq;z%g9`qfQ0where deptno in(select deptno from dept where dname like '%S%')51Testing软件测试网6Q uHfy+z
B$e
group by deptno;51Testing软件测试网I1_0P9aP1IK#A$~a

U3n3z }aPqx\E:E ` q021、给任职超过10年的人加薪10%51Testing软件测试网#A Q k"kZ
t*eFtM
update emp set sal=sal+sal*0.1
Zvq
{3j(S3o `0where months_between(sysdate,hiredate)/12>10;51Testing软件测试网0Uam&Ou}*R%j^ ]