oracle10g第二版sql语言基础第一题答案.docx

查询20号部门的所有员工信息Select * from emp where deptno=20;查询所有工种为CLERK的员工的员工号、员工名和部门号Select empno, ename, deptno from emp where job=’CLERK’;查询奖金（COMM）高于工资（SAL）的员工信息Select * from emp where commsal;查询奖金高于工资的20%的员工信息Select * from emp where commsal*0.2;查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息Select * from emp wheredeptno=20 and job=’MANAGER’Union Select * from emp where deptno=10 and job=’CLERK’; Select * from emp where (job=’Manager’ and deptno=10) or (deptno=10 and job=’CLERK’);查询所有工种不是MANAGER 和CLERK ，且工资大于或等于2000 的员工地详细信息select * from emp where job not in (MANAGER,CLERK) AND sal=2000;查询有奖金的员工的不同工种selectdistinct job from emp where comm0;查询所有员工工资与奖金的和Select sum(nvl(sal,0)+nvl(comm,0)) from emp;查询没有奖金或奖金低于100的员工信息Select * from emp where comm.100 or commIs null;查询各月到数第2天入职的员工信息Alter session set NLS_DATE_FORMAT=’yyyy-mm-dd’;select * from emp where last_day(hiredate)-1=hiredate;查询工龄大于或等于10年的员工信息Select * from emptrunk(months_between(sysdate,hiredate)/12)10;select * from emp where ( (sysdate-hiredate)/365) 10;查询员工信息，要求以首字母大写的方式显示所有员工的姓名Select initcap(ename) from emp;select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename))) from emp;查询员工名正好为6个字符的员工的信息select * from emp where length(ename)=6;查询员工名字中不包含字母“S”的员工select * from emp where ename not like %S% ;查询员工姓名的第2个字母为“M”的员工信息select * from emp where ename not like _M% ;查询所有员工姓名的前3个字符Select substr(ename,1,3) from emp;查询所有员工的姓名，如果包含字母“s”,则用“S”替换select replace(ename,s,S) from emp ;查询员工的姓名和入职日期，并按入职日期从先到后进行排序selectename,hiredate from emp order by hiredate;显示所有员工的姓名、工种、工资和奖金，按工种降序排序，若工种相同则按工资升序排序selectename,job,sal,comm from emp group by job , sal, ename, comm;显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序selectempno, extract(month from hiredate), extract(year from hiredate) from emp group by empno,extract(month fromdate), extract(year from hiredate) ;selectename, to_char(hiredatem,’yyyymm’) from emp order by to_char(hiredate,’mm’), to_char(hiredate,’yyyy’);查询在2月份入职的所有员工信息select * from emp where extract(month from hiredate)=2;sel