(转)SQL例句.docVIP

001 emp员工表 使用scott/tiger用户下的emp表完成下列练习，表的结构说明如下 emp员工表 字段内容如下: empno 员工号 ename 员工姓名 job 工作 mgr 上级编号 hiredate 受雇日期 sal 薪金 comm 佣金 deptno 部门编号 4.找出佣金高于薪金的60%的员工? select ename,comm,sal,sal*0.6 from emp where nvl(comm,0) sal*0.6; select ename,empno from mm_emp where commsal+sal*0.06; //error 1.6 6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK), 既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. select ename,job ,deptno,sal from emp where deptno=10 and job=MANAGER or deptno=20 and job=CLERK or sal =2000 and job not in(MANAGER,CLERK); 7.找出收取佣金的员工的不同工作. select distinct job from emp where comm is not null; 9.找出各月倒数第3天受雇的所有员工. select ename from emp where to_char(hiredate,DD) = to_char(last_day(hiredate),DD)-3; where hiredate=last_day(hiredate)-3; //error 10.找出早于12年前受雇的员工. select ename from emp where (select to_char(sysdate,YYYY) from dual)-to_char(hiredate,YYYY) =12; select empno,hiredate from emp where months_between(sysdate,hiredate)12*12; select * from emp where to_char(hiredate,yyyy)1996; // 不灵活 select empno,ename,hiredate from emp where (sysdate-hiredate)=12 //error 11.以首字母大写的方式显示所有员工的姓名. select initcap(ename) ename from emp; 12.显示正好为5个字符的员工的姓名. select ename from emp where length(ename)=5; select ename from emp where ename like _____; 13.显示不带有R的员工的姓名. select ename from emp where instr(ename,R,1,1)=0; select ename from emp where ename not like%R%; 14.显示所有员工姓名的前三个字符. select ename,substr(ename,1,3) from emp; 15.显示所有员工的姓名,用a替换所有A select ename,replace(ename,A,a) from emp; 16.显示满10年服务年限的员工的姓名和受雇日期. select ename,to_char(hiredate,YYYY mm dd) r, to_char(sysdate,YYYY mm dd) x from emp where (select to_char(sysdate,YYYY) from dual)-to_char(hiredate,YYYY) =10 select ename,hiredate from emp where hiredateadd_months(sysdate,10*12); //error select ename,hiredate from emp where add_months(hiredate,10*12) sysdate; 18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面. select ename,to_char(hiredate,YYYY)