软件测试人员常用SQL掌握

对测试人员而言必须掌握两种语言：第一种是DML，数据操纵语言(DataManipulationLanguage)是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。第二种是：DQL，数据查询语言(DataQueryLanguage)是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。核心指令为SELECT，以及一些辅助指令，如FROM、WHERE等，FROM：表示来源，可以搭配JOIN做链接查询；WHERE：过滤条件；GROUPBY：在使用聚合函数时用到，如SUM,COUNT,MAX,AVG；HAVING：对聚合结果进行筛选，这是和WHERE的不同点；ORDERBY：排序。以下是必须掌握的SQL习题：1、列出至少有一个员工的所有部门selectd.*,ed.coufromdeptd,(selectdeptno,count(empno)coufromempgroupbydeptnohavingcount(empno)1)edwhered.deptno=ed.deptno;2、列出薪金比“SMITH”多的所有员工。·求出SMITH的薪金selectsalfromempwhereename='SMITH';·求所有select*fromempwheresal(selectsalfromempwhereename='SMITH');3、列出所有员工的姓名及其直接上级的姓名selecte.ename,m.enamefromempe,empmwheree.mgr=m.empno(+);4、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称selecte.empno,e.ename,d.dnamefromempe,empm,deptdwheree.mgr=m.empnoande.hiredatem.hiredateande.deptno=d.deptno;5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门，selectd.deptno,d.dname,e.empno,e.enamefromdeptd,empewhered.deptno=e.deptno(+);6、列出所有“CLERK”人员的姓名及其部门名称，部门的人数selecte.ename,d.dname,ed.coufromempe,deptd,(selectdeptno,count(empno)coufromempgroupbydeptno)edwherejob='CLERK'ande.deptno=d.deptnoanded.deptno=e.deptno;7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数·按工作分组,分组条件是最低薪金大于1500selectjob,min(sal)fromempgroupbyjobhavingmin(sal)1500;·求全部的雇员人数selectcount(e.empno),e.jobfromempewheree.jobin(selectjobfromempgroupbyjobhavingmin(sal)1500)groupbye.job;8、列出在部门“SALES”工作的员工姓名，假定不知道销售部的部门编号·通过dept表查询出销售部的部门编号selectdeptnofromdeptwheredname='SALES';·将之前的查询作为子查询selectenamefromempwheredeptno=(selectdeptnofromdeptwheredname='SALES');9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。·公司的平均工资selectavg(sal)fromemp;·列出薪金高于平均薪金select*fromempwheresal(selectavg(sal)fromemp);·与部门表关联查询出所在部门的信息selecte.*,d.locfromempe,deptdwheresal(selectavg(sal)fromemp)ande.deptno=d.deptno;·与自身关联查询上级领导selecte.ename,e.empno,m.empno,m.ename,d.deptno,d.dname,d.locfromempe,deptd,empmwheree.sal(selectavg(sal)fromemp)ande.deptno=d.deptnoande.mgr=m.empno(+);·求出雇员的工资等级selecte.ename,e.empno,s.grade,m.empno,m.ename,d.deptno,d.dname,d.locfromempe,deptd,empm,salgradeswheree.sal(selectavg(sal)fromemp)ande.deptno=d.deptnoande.mgr=m.empno(+)ande.salbetweens.losalands.hisal;10、列出与scott从事相同工作的所有员工及部门名称·找到Scott的工作selectjobfromempwhereename='SCOTT';·找出与其工作相同的雇员selectename,empno,job,salfromempwherejob=(selectjobfromempwhereename='SCOTT');·这是不应该出现SCOTT在加个条件;andename!='SCOTT';selectename,empno,job,salfromempwherejob=(selectjobfromempwhereename='SCOTT')andename!='SCOTT';·部门名称selecte.ename,e.empno,e.job,e.sal,d.dname,d.locfromempe,deptdwherejob=(selectjobfromempwhereename='SCOTT')andename!='SCOTT'ande.deptno=d.deptno;11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金·列出30部门员工薪金selectsalfromempwheredeptno=30;·以上作为子查询selectename,salfromempwheresalin(selectsalfromempwheredeptno=30)anddeptno!=30;12、列出薪金高于部门30中员工的薪金的所有员工的姓名和薪金、部门名称·使用ALLselectename,salfromempwheresalall(selectsalfromempwheredeptno=30)anddeptno!=30;·使用表关联selecte.ename,e.sal,d.dname,d.locfromempe,deptdwheree.salall(selectsalfromempwheredeptno=30)ande.deptno!=30andd.deptno=e.deptno;13、列出在每个部门工作的员工数量，平均工资和平均服务期限selectd.dname,count(e.empno),avg(sal),avg(months_between(sysdate,e.hiredate)/12)yearfromempe,deptdwheree.deptno=d.deptnogroupbyd.dname;14、列出所有员工的姓名，部门名称和工资selecte.ename,d.dname,e.salfromempe,deptdwheree.deptno=d.deptno;15、列出所有部门的详细信息和部门人数selectd.*,nvl(ed.count,0)fromdeptd,(selectdeptno,count(empno)countfromempgroupbydeptno)edwhered.deptno=ed.deptno(+);16、列出各种工作的最低工资及从事此工作的雇员姓名·最低工资的工作selectmin(sal)fromempgroupbyjob;·按工资查询