文档库

最新最全的文档下载
当前位置:文档库 > Oracle经典练习题及标准答案

Oracle经典练习题及标准答案

oracle经典练习sql

/*1、选择在部门30中员工的所有信息*/

select * from scott.emp where deptno = '30'

/*2、列出职位为(MANAGER)的员工的编号,姓名*/

select empno, ename from scott.emp where job = 'MANAGER'

/*3、找出奖金高于工资的员工*/

select * from scott.emp where comm > sal

/*4、找出每个员工奖金和工资的总和*/

select ename, sal + nvl(comm, 0) from scott.emp

/*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */

select *

from scott.emp

where deptno = '10'

and job = 'MANAGER'

union

select *

from scott.emp

where job = 'CLERK'

and deptno = '20'

/*6、找出部门10中既不是经理也不是普通员工,而且工资大于等于2000的员工*/ select *

from scott.emp

where job != 'MANAGER'

and job != 'CLERK'

and sal > 2000

/*7、找出有奖金的员工的不同工作*/

select distinct(job) from scott.emp where comm is not null

/*8、找出没有奖金或者奖金低于500的员工*/

select *

from scott.emp

where comm is not null

and comm > 500

/*9、显示雇员姓名,根据其服务年限,将最老的雇员排在最前面*/

select ename

from scott.emp

order by (months_between(sysdate, hiredate) / 12) desc

select ename,hiredate from scott.emp order by hiredate

/*10、找出每个月倒数第三天受雇的员工*/

select * from scott.emp where hiredate = last_day(hiredate) - 2

/*11、分别用case和decode函数列出员工所在的部门,deptno=10显示'部门10',

免费下载Word文档免费下载: Oracle经典练习题及标准答案

(共7页)