Oracle SQL经典查询练手四篇全.docx

1、Oracle SQL经典查询练手四篇全Oracle SQL：经典查询练手四篇【IT168 技术】本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询，欢迎大家补充，同时你认为有那些经典的也可分享出来。在本文中，对每一个问题，你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛，感谢!经典查询练手第一篇本文使用的实例表结构与表的数据如下：scott.emp员工 表结构如下：Name Type NullableDefaultComments- - - - -EMPNONUMBER(4) 员工号ENAMEVARCHAR2(10) Y 员工姓名JOBVARCHAR2(9)

2、 Y 工作MGRNUMBER(4) Y 上级编号HIREDATE DATE Y 雇佣日期SALNUMBER(7,2) Y 薪金COMMNUMBER(7,2) Y 佣金DEPTNONUMBER(2) Y 部门编号scott.dept部门表Name Type NullableDefaultComments- - - - -DEPTNONUMBER(2) 部门编号DNAMEVARCHAR2(14) Y 部门名称LOCVARCHAR2(13) Y 地点提示：工资=薪金+佣金scott.emp表的现有数据如下：SQLselect*fromemp;EMPNO ENAMEJOB MGR HIREDATESA

3、LCOMM DEPTNO- - - - - - - -7369SMITHCLERK79021980-12-17800.00207499ALLENSALESMAN76981981-2-201600.00300.00307521WARD SALESMAN76981981-2-221250.00500.00307566JONESMANAGER78391981-4-22975.00207654MARTIN SALESMAN76981981-9-281250.001400.00307698BLAKEMANAGER78391981-5-12850.00307782CLARKMANAGER78391981-

4、6-92450.00107788SCOTTANALYST75661987-4-194000.00207839KING PRESIDENT1981-11-175000.00107844TURNER SALESMAN76981981-9-81500.000.00307876ADAMSCLERK77881987-5-231100.00207900JAMESCLERK76981981-12-3950.00307902FORD ANALYST75661981-12-33000.00207934MILLER CLERK77821982-1-231300.0010102EricHu Developer145

5、52011-5-2615500.0014.0010104huyong PM14552011-5-2615500.0014.0010105WANGJING Developer14552011-5-2615500.0014.001017rows selectedScott.dept表的现有数据如下：SQLselect*fromdept;DEPTNO DNAMELOC- - -10ACCOUNTING NEW YORK20RESEARCH DALLAS30SALESCHICAGO40OPERATIONS BOSTON5050abc50def60DeveloperHaiKou6rows selecte

6、d用SQL完成以下问题列表：1列出至少有一个员工的所有部门。2列出薪金比“SMITH”多的所有员工。3列出所有员工的姓名及其直接上级的姓名。4列出受雇日期早于其直接上级的所有员工。5列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门6列出所有“CLERK”（办事员）的姓名及其部门名称。7列出最低薪金大于1500的各种工作。8列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。9列出薪金高于公司平均薪金的所有员工。10列出与“SCOTT”从事相同工作的所有员工。11列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。12列出薪金高于在部门30工作的所有员

7、工的薪金的员工姓名和薪金。13列出在每个部门工作的员工数量、平均工资和平均服务期限。14列出所有员工的姓名、部门名称和工资。15列出所有部门的详细信息和部门人数。16列出各种工作的最低工资。17列出各个部门的MANAGER（经理）的最低薪金。18列出所有员工的年工资,按年薪从低到高排序。各答案如下，欢迎大家给出不出的解答方式。-1列出至少有一个员工的所有部门。-SQLselectdnamefromdeptwheredeptnoin(selectdeptnofromemp);DNAME-RESEARCHSALESACCOUNTING-或-SQLselectdnamefromdeptwherede

8、ptnoin(selectdeptnofromempgroupbydeptnohavingcount(deptno)=1);DNAME-ACCOUNTINGRESEARCHSALES-2列出薪金比“SMITH”多的所有员工。-SQLselect*fromempwheresal(selectsalfromempwhereename=SMITH);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7499ALLEN SALESMAN76981981-2-201600.00300.00307521WARD SALESMAN76981

9、981-2-221250.00500.00307566JONES MANAGER78391981-4-22975.00207654MARTIN SALESMAN76981981-9-281250.001400.00307698BLAKE MANAGER78391981-5-12850.00307782CLARK MANAGER78391981-6-92450.00107788SCOTT ANALYST75661987-4-194000.00207839KING PRESIDENT1981-11-175000.00107844TURNER SALESMAN76981981-9-81500.000

10、.00307876ADAMS CLERK77881987-5-231100.00207900JAMES CLERK76981981-12-3950.00307902FORD ANALYST75661981-12-33000.00207934MILLER CLERK77821982-1-231300.0010102EricHu Developer14552011-5-2615500.0014.0010104huyong PM14552011-5-2615500.0014.0010105WANGJING Developer14552011-5-2615500.0014.001016rows sel

11、ected-3列出所有员工的姓名及其直接上级的姓名。-SQLselecta.ename,(selectenamefromemp bwhereb.empno=a.mgr)asboss_namefromemp a;ENAME BOSS_NAME- -SMITH FORDALLEN BLAKEWARD BLAKEJONES KINGMARTIN BLAKEBLAKE KINGCLARK KINGSCOTT JONESKINGTURNER BLAKEADAMS SCOTTJAMES BLAKEFORD JONESMILLER CLARKEricHuhuyongWANGJING17rows select

12、ed-4列出受雇日期早于其直接上级的所有员工。-SQLselecta.enamefromemp awherea.hiredateselecta.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno2fromdept aleftjoinemp bona.deptno=b.deptno;DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO- - - - - - - -RESEARCH7369SMITH CLERK79021980-12-17800.0020SALES7499ALLEN SALES

13、MAN76981981-2-201600.0030SALES7521WARD SALESMAN76981981-2-221250.0030RESEARCH7566JONES MANAGER78391981-4-22975.0020SALES7654MARTIN SALESMAN76981981-9-281250.0030SALES7698BLAKE MANAGER78391981-5-12850.0030ACCOUNTING7782CLARK MANAGER78391981-6-92450.0010RESEARCH7788SCOTT ANALYST75661987-4-194000.0020A

14、CCOUNTING7839KING PRESIDENT1981-11-175000.0010SALES7844TURNER SALESMAN76981981-9-81500.0030RESEARCH7876ADAMS CLERK77881987-5-231100.0020SALES7900JAMES CLERK76981981-12-3950.0030RESEARCH7902FORD ANALYST75661981-12-33000.0020ACCOUNTING7934MILLER CLERK77821982-1-231300.0010ACCOUNTING102EricHu Developer

15、14552011-5-2615500.0010ACCOUNTING104huyong PM14552011-5-2615500.0010ACCOUNTING105WANGJING Developer14552011-5-2615500.001050abcOPERATIONSDeveloper20rows selected-6列出所有“CLERK”（办事员）的姓名及其部门名称。-SQLselecta.ename,b.dnamefromemp ajoindept bona.deptno=b.deptnoanda.job=CLERK;ENAME DNAME- -SMITH RESEARCHADAMS

16、 RESEARCHJAMES SALESMILLER ACCOUNTING-7列出最低薪金大于1500的各种工作。-SQLselectdistinctjobasHighSalJobfromempgroupbyjobhavingmin(sal)1500;HIGHSALJOB-ANALYSTDeveloperMANAGERPMPRESIDENT-8列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。-SQLselectenamefromempwheredeptno=(selectdeptnofromdeptwheredname=SALES);ENAME-ALLENWAR

17、DMARTINBLAKETURNERJAMES6rows selected-9列出薪金高于公司平均薪金的所有员工。-SQLselectenamefromempwheresal(selectavg(sal)fromemp);ENAME-JONESBLAKESCOTTKINGFORDEricHuhuyongWANGJING8rows selected-10列出与“SCOTT”从事相同工作的所有员工。-SQLselectenamefromempwherejob=(selectjobfromempwhereename=SCOTT);ENAME-SCOTTFORD-11列出薪金等于部门30中员工的薪金的

18、所有员工的姓名和薪金。-SQLselecta.ename,a.salfromemp awherea.salin(selectb.sal2fromemp bwhereb.deptno=30)anda.deptno30;ENAME SAL- -12列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。-SQLselectename,salfromempwheresal(selectmax(sal)fromempwheredeptno=30);ENAME SAL- -JONES2975.00SCOTT4000.00KING5000.00FORD3000.00EricHu5500.00huyon

19、g5500.00WANGJING5500.007rows selected-13列出在每个部门工作的员工数量、平均工资和平均服务期限。-SQLselect(selectb.dnamefromdept bwherea.deptno=b.deptno)asdeptname ,count(deptno)asdeptcount,avg(sal)asdeptavgsal2fromemp agroupbydeptno;DEPTNAME DEPTCOUNT DEPTAVGSAL- - -ACCOUNTING64208.33333RESEARCH52375SALES61566.66666-14列出所有员工的姓

20、名、部门名称和工资。-SQLselecta.ename,(selectb.dnamefromdept bwhereb.deptno=a.deptno)asdeptname,salfromemp a;ENAME DEPTNAME SAL- - -SMITH RESEARCH800.00ALLEN SALES1600.00WARD SALES1250.00JONES RESEARCH2975.00MARTIN SALES1250.00BLAKE SALES2850.00CLARK ACCOUNTING2450.00SCOTT RESEARCH4000.00KING ACCOUNTING5000.0

21、0TURNER SALES1500.00ADAMS RESEARCH1100.00JAMES SALES950.00FORD RESEARCH3000.00MILLER ACCOUNTING1300.00EricHu ACCOUNTING5500.00huyong ACCOUNTING5500.00WANGJING ACCOUNTING5500.0017rows selected-15列出所有部门的详细信息和部门人数。-SQLselecta.deptno,a.dname,a.loc,(selectcount(deptno)fromemp bwhereb.deptno=a.deptnogroup

22、byb.deptno)asdeptcountfromdept a;DEPTNO DNAME LOC DEPTCOUNT- - - -10ACCOUNTING NEW YORK620RESEARCH DALLAS530SALES CHICAGO640OPERATIONS BOSTON5050abc 50def60Developer HaiKou6rows selected-16列出各种工作的最低工资。-SQLselectjob,avg(sal)fromempgroupbyjob;JOBAVG(SAL)- -ANALYST3500CLERK1037.5Developer5500MANAGER275

23、8.33333PM5500PRESIDENT5000SALESMAN14007rows selected-17列出各个部门的MANAGER（经理）的最低薪金。-SQLselectdeptno,min(sal)fromempwherejob=MANAGERgroupbydeptno;DEPTNOMIN(SAL)- -102450202975302850-18列出所有员工的年工资,按年薪从低到高排序。-SQLselectename,(sal+nvl(comm,0)*12assalpersalfromemporderbysalpersal;ENAME SALPERSAL- -SMITH9600JAMES11400ADAMS13200MILLER15600TURNER