Oracle的sql语句练习题含答案（二）Oracle_SQL练习题1 1.选择部门30中的所有员工.

Select ename from emp where emp.deptno=?30?; 2.列出所有办事员(CLERK)的姓名，编号和部门编号

Select ename,empno,deptno from emp where job=?CLERK?. 3.找出佣金高于薪金的员工.

Select ename from emp where comm>sal 4.找出佣金高于薪金的60%的员工.

Select ename from emp where comm>sal*0.6

5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.

Select * from emp where (job=?MANAGER? and deptno=10) or (job=?CLERK? and deptno=20) 6.找出部门10中所有经理(MANAGER),

部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. Select * from emp where job=?MANAGER? and deptno=10 or (job=?CLERK?) or(job not in (?MANAGER?,?CLERK?) and sal >=2000) 7.找出收取佣金的员工的不同工作.

Select distinct job from emp where NVL (comm,0)>0 8.找出不收取佣金或收取的佣金低于100的员工.

Select ename from emp where NVL(comm,0)<100 9.找出各月倒数第3天受雇的所有员工.

Select ename from emp where last_day(hiredate)-2=hiredate 10.找出早于12年前受雇的员工.

Select ename from emp where months_between(sysdate,hiredate)/12 >12 11.以首字母大写的方式显示所有员工的姓名. select initcap(ename) name ,job, hiredate from emp;

12.显示正好为5个字符的员工的姓名

Select ename from emp where ename like ?_____?

select ename,job,hiredate from emp where length(ename)=5; 13.显示不带有\的员工的姓名.

Select ename from emp where ename not like ?%R%? 14.显示所有员工姓名的前三个字符.

15.显示所有员工的姓名,用a替换所有\

16.显示满10年服务年限的员工的姓名和受雇日期.

Select ename,hiredate from emp where months_between(systemdate,hiredate)/12 >=10 17.显示员工的详细资料,按姓名排序.

Select * from emp order by ename

18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.

Select ename,hiredate from emp order by months_between(sysdate,hiredate) desc

select ename, hiredate from emp order by hiredate desc;

19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. Select ename,job,sal from emp order by job desc,sal asc

20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.

Select ename,to_char(hiredate,?yyyy?) as year,to_char(hiredate,?mm?) as month from emp order by year,month asc

21.显示在一个月为30天的情况所有员工的日薪金,忽略余数. Select ename,trunk(sal/30) as dailysal from emp 22.找出在(任何年份的)2月受聘的所有员工。

Select ename from emp where to_char(hiredate,?mm?) = 2 23.对于每个员工,显示其加入公司的天数.

Select ename,systemdate-hiredate as tianshu from emp 24.显示姓名字段的任何位置包含\的所有员工的姓名. Select ename from emp where ename like ?%A%? 25.以年月日的方式显示所有员工的服务年限.

Select ename,to_char(hiredate,?yyyy/mm/dd?) from emp 答案：

1.select ename,job,mgr,hiredate,sal,comm ,deptno from emp where deptno=30; 2.select ename,empno,deptno from emp where job='CLERK'; 3.select * from emp where comm>sal;

4.select * from emp

where comm>sal*0.6;

5.select empno ,ename, job,hiredate,sal,comm,deptno from emp

where( job='MANAGER' and deptno=10) or( job='CLERK' and deptno=20);

6.select empno,ename,job, hiredate,sal, deptno from emp

where ( job='MANAGER' and deptno=10) or( job='CLERK' and deptno=20)

or((job <>'MANAGER' and job<>'CLERK') and sal>=2000); 7.select distinct ename,job,comm from emp

where NVL(comm,0)>0;

注意如果像上面这样写，就 job ename comm 3个同时不同的都列出来 正确的是：

select distinct job from emp

where NVL(comm,0)>0;

select job from emp

where NVL(comm,0)>0;

group by job

8.select ename,comm. from emp where NVL(comm,0)=0 or NVL(comm,0)<100; 如果按上面的写法就重复了，直接可以写成 select ename,comm from emp

where NVL(comm,0)<100;

9 select ename, job,last_day(hiredate)-3 from emp;

select ename, job,hiredate

from emp

where hiredate=last_day(hiredate)-2; 10.select ename，job from emp

where months_between(sysdate,hiredate)/12>12 11.select initcap(ename) name ,job, hiredate from emp;

12. select ename,job,hiredate from emp where length(ename)=5; 13 select ename, job from emp where ename not like '%R%'; 14.select substr(ename, 1,3) name,job,hiredate from emp； 15select replace(ename, 'A','a') name,job,hiredate from emp; 16select ename, job, hiredate from emp

where months_between(sysdate,hiredate)/12>=10;

17. select * from emp order by ename;

18.select ename, hiredate from emp order by hiredate desc;

19.select ename,job, sal from emp order by job desc, sal asc;

20.select ename, to_char(hiredate,'yyyy') year, to_char(hiredate,'mm') months from emp

order by months, year asc;

21.select ename, trunc(sal/30) dailysal

from emp;

22. select ename, hiredatefrom empwhere to_char(hiredate,'mm')='02'; 23. select ename,sysdate-hiredate 天数 from emp; 24.select ename from emp where instr(ename,'A',1)>0;

25. select ename, to_char(hiredate,'yyyy/mm/dd') from emp；

--26.列出至少有一个员工的所有部门.

select dname from emp,dept where emp.deptno=dept.deptno group by dname; select distinct dname from emp,dept where emp.deptno=dept.deptno; --说明：（1）select中出现的字段一定要出现在group by中;

-- （2）如果select中出现的字段不出现在group by中，则一定要出现在select的聚合函数中，对于想出现的字段，可用max()或min()。

-- （3）默认的情况是按group by中的字段来排序，如果要用order by来自己排序，则order by后的

字段一定是group by后的字段，不能是聚合函数的字段

--27.列出薪金比“SMITH”多的所有员工. Select ename,sal from emp where

select ename, sal from emp where sal > (select sal from emp where upper(ename)='SMITH'); --28.列出所有员工的姓名及其直接上级的姓名.

select a.ename, b.ename mgrname from emp a,emp b where a.mgr = b.empno; --29.列出受雇日期早于其直接上级的所有员工

select e.ename, m.ename from emp e, emp m where e.mgr=m.empno and (e.hiredate < m.hiredate);

--30.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select dname, emp.* from dept, emp where emp.deptno(+) = dept.deptno order by dname; --说明：外连接（使一个都不能少，（+）对面的表里的字段都不能少）

--31.列出所有“CLERK”（办事员）的姓名及其部门名称

select distinct emp.ename, dept.deptno from emp, dept where emp.deptno = dept.deptno and upper(emp.job)='CLERK';

--32.列出最低薪金大于1500的各种工作

select job from emp group by job having min(sal) > 1500;

--33.列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp, dept where emp.deptno = dept.deptno and dname = 'SALES'; --34.列出薪金高于公司平均薪金的所有员工

select ename, sal from emp where sal > (select avg(sal) from emp);

--35.列出与“SCOTT”从事相同工作的所有员工 --SCOTT从事一种工作

select ename from emp where job = (select job from emp where upper(ename) = 'SCOTT'); --SCOTT从事多种工作

select ename, job from emp where job in (select job from emp where upper(ename) = 'SCOTT'); --36.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

select ename, sal from emp where sal in(select sal from emp where deptno = 30); --37.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金

select ename, sal from emp where sal > (select max(sal) from emp where deptno = 30);

--38.列出在每个部门工作的员工数量、平均工资和平均服务期限

select deptno \部门号\员工数量\平均工资\平均服务期限(天)\ from emp group by deptno;

--39.列出所有员工的姓名、部门名称和工资

select ename, dname, sal+nvl(comm,0) from emp, dept where emp.deptno = dept.deptno; --40.列出从事同一种工作但属于不同部门的员工的一种组合

select distinct e.deptno, e.job from emp e, emp m where e.deptno != m.deptno and e.job = m.job order by e.job;

--41.列出所有部门的详细信息和部门人数

select dept.*, (select count(ename) from emp where emp.deptno(+)=dept.deptno) 数 from dept;

select dept.deptno,dept.dname,dept.loc,

人

count(ename) from emp,dept where