[IT认证]oracle sql试题答案

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

2、门30工作的所有员工的薪金的员工姓名和薪金。13列出在每个部门工作的员工数量、平均工资和平均服务期限。14列出所有员工的姓名、部门名称和工资。15列出所有部门的详细信息和部门人数。16列出各种工作的最低工资。17列出各个部门的manager（经理）的最低薪金。18列出所有员工的年工资,按年薪从低到高排序。-1列出至少有一个员工的所有部门。-sqlselectdnamefromdeptwheredeptnoin(selectdeptnofromemp);dname-researchsalesaccounting-或-sqlselectdnamefromdeptwheredeptnoin(sele

3、ctdeptnofromempgroupbydeptnohavingcount(deptno)=1);dname-accountingresearchsales-2列出薪金比“smith”多的所有员工。-sqlselect*fromempwheresal(selectsalfromempwhereename=smith);empnoenamejobmgrhiredatesalcommdeptno-7499allensalesman76981981-2-201600.00300.00307521wardsalesman76981981-2-221250.00500.00307566jonesma

4、nager78391981-4-22975.00207654martinsalesman76981981-9-281250.001400.00307698blakemanager78391981-5-12850.00307782clarkmanager78391981-6-92450.00107788scottanalyst75661987-4-194000.00207839kingpresident1981-11-175000.00107844turnersalesman76981981-9-81500.000.00307876adamsclerk77881987-5-231100.0020

5、7900jamesclerk76981981-12-3950.00307902fordanalyst75661981-12-33000.00207934millerclerk77821982-1-231300.0010102erichudeveloper14552011-5-2615500.0014.0010104huyongpm14552011-5-2615500.0014.0010105wangjingdeveloper14552011-5-2615500.0014.001016rowsselected-3列出所有员工的姓名及其直接上级的姓名。-sqlselecta.ename,(sele

6、ctenamefromempbwhereb.empno=a.mgr)asboss_namefromempa;enameboss_name-smithfordallenblakewardblakejoneskingmartinblakeblakekingclarkkingscottjoneskingturnerblakeadamsscottjamesblakefordjonesmillerclarkerichuhuyongwangjing17rowsselected-4列出受雇日期早于其直接上级的所有员工。-sqlselecta.enamefromempawherea.hiredateselec

7、ta.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno2fromdeptaleftjoinempbona.deptno=b.deptno;dnameempnoenamejobmgrhiredatesaldeptno-research7369smithclerk79021980-12-17800.0020sales7499allensalesman76981981-2-201600.0030sales7521wardsalesman76981981-2-221250.0030research7566jonesmanager78

8、391981-4-22975.0020sales7654martinsalesman76981981-9-281250.0030sales7698blakemanager78391981-5-12850.0030accounting7782clarkmanager78391981-6-92450.0010research7788scottanalyst75661987-4-194000.0020accounting7839kingpresident1981-11-175000.0010sales7844turnersalesman76981981-9-81500.0030research787

9、6adamsclerk77881987-5-231100.0020sales7900jamesclerk76981981-12-3950.0030research7902fordanalyst75661981-12-33000.0020accounting7934millerclerk77821982-1-231300.0010accounting102erichudeveloper14552011-5-2615500.0010accounting104huyongpm14552011-5-2615500.0010accounting105wangjingdeveloper14552011-5

10、-2615500.001050abcoperationsdeveloper20rowsselected-6列出所有“clerk”（办事员）的姓名及其部门名称。-sqlselecta.ename,b.dnamefromempajoindeptbona.deptno=b.deptnoanda.job=clerk;enamedname-smithresearchadamsresearchjamessalesmilleraccounting-7列出最低薪金大于1500的各种工作。-sqlselectdistinctjobashighsaljobfromempgroupbyjobhavingmin(sa

11、l)1500;highsaljob-analystdevelopermanagerpmpresident-8列出在部门“sales”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。-sqlselectenamefromempwheredeptno=(selectdeptnofromdeptwheredname=sales);ename-allenwardmartinblaketurnerjames6rowsselected-9列出薪金高于公司平均薪金的所有员工。-sqlselectenamefromempwheresal(selectavg(sal)fromemp);ename-jon

12、esblakescottkingforderichuhuyongwangjing8rowsselected-10列出与“scott”从事相同工作的所有员工。-sqlselectenamefromempwherejob=(selectjobfromempwhereename=scott);ename-scottford-11列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。-sqlselecta.ename,a.salfromempawherea.salin(selectb.sal2fromempbwhereb.deptno=30)anda.deptno30;enamesal-12列出薪金

13、高于在部门30工作的所有员工的薪金的员工姓名和薪金。-sqlselectename,salfromempwheresal(selectmax(sal)fromempwheredeptno=30);enamesal-jones2975.00scott4000.00king5000.00ford3000.00erichu5500.00huyong5500.00wangjing5500.007rowsselected-13列出在每个部门工作的员工数量、平均工资和平均服务期限。-sqlselect(selectb.dnamefromdeptbwherea.deptno=b.deptno)asdeptn

14、ame,count(deptno)asdeptcount,avg(sal)asdeptavgsal2fromempagroupbydeptno;deptnamedeptcountdeptavgsal-accounting64208.33333research52375sales61566.66666-14列出所有员工的姓名、部门名称和工资。-sqlselecta.ename,(selectb.dnamefromdeptbwhereb.deptno=a.deptno)asdeptname,salfromempa;enamedeptnamesal-smithresearch800.00allens

15、ales1600.00wardsales1250.00jonesresearch2975.00martinsales1250.00blakesales2850.00clarkaccounting2450.00scottresearch4000.00kingaccounting5000.00turnersales1500.00adamsresearch1100.00jamessales950.00fordresearch3000.00milleraccounting1300.00erichuaccounting5500.00huyongaccounting5500.00wangjingaccou

16、nting5500.0017rowsselected-15列出所有部门的详细信息和部门人数。-sqlselecta.deptno,a.dname,a.loc,(selectcount(deptno)fromempbwhereb.deptno=a.deptnogroupbyb.deptno)asdeptcountfromdepta;deptnodnamelocdeptcount-10accountingnewyork620researchdallas530saleschicago640operationsboston5050abc50def60developerhaikou6rowsselect

17、ed-16列出各种工作的最低工资。-sqlselectjob,avg(sal)fromempgroupbyjob;jobavg(sal)-analyst3500clerk1037.5developer5500manager2758.33333pm5500president5000salesman14007rowsselected-17列出各个部门的manager（经理）的最低薪金。-sqlselectdeptno,min(sal)fromempwherejob=managergroupbydeptno;deptnomin(sal)-102450202975302850-18列出所有员工的年工资

18、,按年薪从低到高排序。-sqlselectename,(sal+nvl(comm,0)*12assalpersalfromemporderbysalpersal;enamesalpersal-smith9600james11400adams13200miller15600turner18000ward21000allen22800clark29400martin31800blake34200jones35700ford36000scott48000king60000erichu66168huyong66168wangjing6616817rowsselected第二部分：1. 找出emp表中的

19、姓名（ename）第三个字母是a 的员工姓名。2. 找出emp表员工名字中含有a 和n的员工姓名。3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。4. 列出部门编号为20的所有职位。5. 列出不属于sales 的部门。6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。7. 显示职位为manager 和salesman，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。8. 说明以下两条sql语句的输出结果：select empno,comm from emp where comm is null;s

20、elect empno,comm from emp where comm = null; 9. 判断select ename,sal from emp where sal 1500是否报错，为什么？10. 找出每个部门里薪资最低的员工-1.找出emp表中的姓名（ename）第三个字母是a的员工姓名。-sqlselectenamefromscott.empwhereenamelike_a%;ename-adamsblakeclark-2.找出emp表员工名字中含有a和n的员工姓名。-sqlselectenamefromscott.empwhereenamelike%a%andenamelike%

21、n%;ename-allenmartinwangjing-或-sqlselectenamefromscott.empwhereenamelike%a%n%;ename-allenmartinwangjing/*-3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。-*/sqlselectename,sal+commaswage,comm2fromscott.emp3orderbywage,commdesc;enamewagecomm-turner15000.00ward1750500.00allen1900300.00martin26501400.00eric

22、hu551414.00wangjing551414.00huyong551414.00smithjonesjamesmillerfordadamsblakeclarkscottking17rowsselected-4.列出部门编号为20的所有职位。-sqlselectdistinctjobfromempwheredeptno=20;job-analystclerkmanager-5.列出不属于sales的部门。-sqlselectdistinct*fromscott.deptwherednamesales;deptnodnameloc-10accountingnewyork20research

23、dallas40operationsboston5050abc50def60developerhaikou110信息科海口6rowsselected-或者：sqlselectdistinct*fromscott.deptwheredname!=sales;sqlselectdistinct*fromscott.deptwherednamenotin(sales);sqlselectdistinct*fromscott.deptwherednamenotlikesales;-6.显示工资不在1000到1500之间的员工信息：名字、工资，按工资从大到小排序。-sqlselectename,sal+

24、commaswagefromscott.emp2wheresal+commnotbetween1000and15003orderbywagedesc;enamewage-erichu5514huyong5514wangjing5514martin2650allen1900ward17506rowsselected-或者sqlselectename,sal+commaswagefromscott.emp2wheresal+comm15003orderbywagedesc;enamewage-erichu5514huyong5514wangjing5514martin2650allen1900wa

25、rd17506rowsselected/*-7.显示职位为manager和salesman，年薪在15000和20000之间的员工的信息：名字、职位、年薪。-*/sqlselectename姓名,job职位,(sal+comm)*12as年薪2fromscott.emp3where(sal+comm)*12between15000and200004andjobin(manager,salesman);姓名职位年薪-turnersalesman18000/*-8.说明以下两条sql语句的输出结果：selectempno,commfromempwherecommisnull;selectempno

26、,commfromempwherecomm=null;-*/sqlselectempno,commfromempwherecommisnull;empnocomm-736975667698778277887839787679007902793410rowsselected-sqlselectempno,commfromempwherecomm=null;empnocomm-说明：isnull是判断某个字段是否为空，为空并不等价于为空字符串或为数字0；-而=null是判断某个值是否等于null，null=null和nullnull都为false。/*-9.让select语句的输出结果为selec

27、t*fromsalgrade;select*frombonus;select*fromemp;select*fromdept;列出当前用户有多少张数据表，结果集中存在多少条记录。-*/sqlselectselect*from|table_name|;fromuser_tables;select*from|table_name|;-select*frombonus;select*fromemp;select*fromdept;-.等等，在此不列出。-10.语句selectename,salfromempwheresal1500是否抱错？-sqlselectename,salfromempwher

28、esal1500;enamesal-allen1600.00jones2975.00blake2850.00clark2450.00scott4000.00king5000.00ford3000.00erichu5500.00huyong5500.00wangjing5500.0010rowsselectedsqlselectename,salfromempwheresal1500;enamesal-allen1600.00jones2975.00blake2850.00clark2450.00scott4000.00king5000.00ford3000.00erichu5500.00huy

29、ong5500.00wangjing5500.0010rowsselected-说明不会抱错，这儿存在隐式数据类型的。第三部分1、选择部门30中的雇员 2、列出所有办事员的姓名、编号和部门 3、找出佣金高于薪金的雇员 4、找出佣金高于薪金60%的雇员 5、找出部门10中所有经理和部门20中的所有办事员的详细资料 6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金=2000的所有雇员的详细资料 7、找出收取佣金的雇员的不同工作 8、找出不收取佣金或收取的佣金低于100的雇员 9、找出各月最后一天受雇的所有雇员 10、找出早于25年之前受雇的雇员 11、显示只有首字母

30、大写的所有雇员的姓名 12、显示正好为6个字符的雇员姓名 13、显示不带有r的雇员姓名 14、显示所有雇员的姓名的前三个字符 15、显示所有雇员的姓名，用a替换所有a 16、显示所有雇员的姓名以及满10年服务年限的日期 17、显示雇员的详细资料，按姓名排序 18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序 20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面 21、显示在一个月为30天的情况下所有雇员的日薪金 22、找出在（任何年份的）2月受聘的所有雇员

31、23、对于每个雇员，显示其加入公司的天数 24、显示姓名字段的任何位置，包含 a 的所有雇员的姓名 25、以年、月和日显示所有雇员的服务年限 -1、选择部门30中的雇员select * from emp where deptno=30;-2、列出所有办事员的姓名、编号和部门select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper(clerk);-3、找出佣金高于薪金的雇员select * from emp where commsal;-4、找出佣金高于薪金60%的雇员

32、select * from emp where commsal*0.6-5、找出部门10中所有经理和部门20中的所有办事员的详细资料select * from emp where (deptno=10 and job=upper(manager) or (deptno=20 and job=upper(clerk );-6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金=2000的所有雇员的详细资料select * from emp where (deptno=10 and job=upper(manager) or (deptno=20 and job=uppe

33、r(clerk ) or (jobupper(manager) and jobupper(clerk) and sal=2000)-7、找出收取佣金的雇员的不同工作select distinct job from emp where comm0;-8、找出不收取佣金或收取的佣金低于100的雇员select * from emp where nvl(comm,0)25;select * from emp where hiredate -11、显示只有首字母大写的所有雇员的姓名select ename from emp where ename=initcap(ename);-12、显示正好为6个字

34、符的雇员姓名select ename from emp where length(ename)=6-13、显示不带有r的雇员姓名select ename from emp where ename not like %r%;select ename from emp where instr(ename,r)=0;-14、显示所有雇员的姓名的前三个字符select substr(ename,1,3) from emp-15、显示所有雇员的姓名，用a替换所有aselect replace(ename,a,a) from emp-16、显示所有雇员的姓名以及满10年服务年限的日期select enam

35、e,add_months(hiredate,12*10) 服务年限的日期 from emp-17、显示雇员的详细资料，按姓名排序select * from emp order by ename-18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面select ename from emp order by hiredate-19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序select ename,job,sal from emp order by job desc ,sal asc-20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，

36、将最早年份的项目排在最前面select ename,to_char(hiredate,yyyy),to_char(hiredate,mm) from emp order by hiredate asc-21、显示在一个月为30天的情况下所有雇员的日薪金select ename,sal/30 from emp;-22、找出在（任何年份的）2月受聘的所有雇员select * from emp where to_char(hiredate,mm)=02;-23、对于每个雇员，显示其加入公司的天数select ename,sysdate-hiredate from emp-24、显示姓名字段的任何位置

37、，包含 a 的所有雇员的姓名select ename from emp where ename like %a%;select ename from emp where instr(ename,a,1)0;-25、以年、月和日显示所有雇员的服务年限select months_between(sysdate,hiredate)/12 as “年”, months_between(sysdate,hiredate) as “月”, sysdate-hiredate as “日” from emp-26、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员select dept.deptno,dname,loc,count(empno)from dept,empwhere dept.deptno=emp.deptno(+)group by dept.deptno,dname,loc-27、列出各种类别工作的最低工资select min(sal) from emp group by job-28、列出各个部门的manager（经理）的最低薪金select deptno,min(sal) from emp where job=upper(manager) group by deptno-29、列出按年薪排序的所有雇员的年薪select (sal+nvl(comm,0)*1