/*使用scott/tiger用户下的emp表完成下列练习，表的结构说明如下工资 = 薪金 + 佣金emp员工表 字段内容如下:empno员工编号ename员工姓名job 工作mgr 上级编号hiredate受雇日期sal 薪金comm佣金deptno部门编号*/--1.选择部门30中的所有员工.select * from emp where deptno = 30--2.列出所有办事员(CLERK)的姓名，编号和部门编号.select empno, ename, deptno from emp where job = 'CLERK'--3.找出佣金高于薪金的员工.select empno, ename, nvl(comm, 0) 佣金, nvl(sal, 0) 薪金 from emp where nvl(comm, 0) > nvl(sal, 0)--4.找出佣金高于薪金的60%的员工.select empno, ename, nvl(comm, 0) 佣金, nvl(sal, 0) "薪金的60%" from emp where nvl(comm, 0) > (nvl(sal, 0) * 0.6)--5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.select e.* from emp e where (e.deptno = 10 and job = 'MANAGER') or (e.deptno = 20 and job = 'CLERK')--6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.select e.* from emp e where (e.deptno = 10 and job = 'MANAGER') or (e.deptno = 20 and job = 'CLERK') or (e.job <> 'MANAGER' and e.job <> 'CLERK' and nvl(e.sal, 0) > 2000)--7.找出收取佣金的员工的不同工作.select distinct job from emp where comm is null--8.找出不收取佣金或收取的佣金低于100的员工.select empno,ename from emp where comm is null or nvl(comm, 0) < 100--9.找出各月倒数第3天受雇的所有员工.select e.ename, e.empno, e.hiredate from emp e where e.hiredate = last_day(hiredate) - 2--10.找出早于12年前受雇的员工.select e.empno,e.ename,e.hiredate from emp e where months_between(sysdate, e.hiredate) > (12 * 12)--11.以首字母大写的方式显示所有员工的姓名.select e.empno,initcap(e.ename) from emp e --12.显示正好为5个字符的员工的姓名.select e.empno, e.ename from emp e where length(e.ename) = 5--13.显示不带有"R"的员工的姓名.select a.ename from emp aminusselect b.ename from emp b where ename like ('%R%')--14.显示所有员工姓名的前三个字符.select substr(ename, 3) from emp--15.显示所有员工的姓名,用a替换所有"A"select replace(ename, 'A', 'a') from emp--16.显示满10年服务年限的员工的姓名和受雇日期.select e.empno,e.ename,e.hiredate from emp e where months_between(sysdate, e.hiredate) > (10 * 12)--17.显示员工的详细资料,按姓名排序.select * from emp order by ename--18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.select e.empno,e.ename,e.hiredate from emp e order by months_between(sysdate,e.hiredate) desc--19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.select e.ename,e.job,nvl(sal, 0) from emp e order by job desc,nvl(sal, 0)--20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.select e.ename, extract(year from e.hiredate) years, extract(month from e.hiredate) months from emp e order by extract(month from e.hiredate), extract(year from e.hiredate)--21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.select e.empno, e.ename, trunc((nvl(sal, 0) + nvl(comm, 0)) / 30) 日薪金 from emp e--22.找出在(任何年份的)2月受聘的所有员工。select e.empno,e.ename from emp e where extract(month from e.hiredate) = 2--23.对于每个员工,显示其加入公司的天数.select e.ename,e.empno,trunc(sysdate - e.hiredate)days from emp e--24.显示姓名字段的任何位置包含"A"的所有员工的姓名.select ename from emp where ename like ('%A%')--25.以年月日的方式显示所有员工的服务年限. (大概)select t.empno, t.ename, to_char(sysdate, 'yyyy-mm-dd') 当前时间, to_char(t.hiredate, 'yyyy-mm-hh') 受雇时间, t.years, t.months, trunc(sysdate - add_months(t.hiredate, t.years * 12 + t.months)) days from (select e.empno, e.ename, e.hiredate, trunc(months_between(sysdate, e.hiredate) / 12) years, trunc(mod(months_between(sysdate, e.hiredate), 12)) months from emp e) t
