Oracle实验报告3答案.docx
- 文档编号:30016996
- 上传时间:2023-08-04
- 格式:DOCX
- 页数:17
- 大小:19.23KB
Oracle实验报告3答案.docx
《Oracle实验报告3答案.docx》由会员分享,可在线阅读,更多相关《Oracle实验报告3答案.docx(17页珍藏版)》请在冰豆网上搜索。
Oracle实验报告3答案
使用scott/tiger用户下的emp表完成下列练习,表的结构说明如下
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
------1.选择部门30中的所有员工.
select*fromempwheredeptno=30;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------
7499ALLEN SALESMAN 769820-2月-81 1600 300 30
7521WARD SALESMAN 769822-2月-81 1250 500 30
7654MARTIN SALESMAN 769828-9月-81 1250 1400 30
7698BLAKE MANAGER 783901-5月-81 2850 30
7844TURNER SALESMAN 769808-9月-81 1500 0 30
7900JAMES CLERK 769803-12月-81 950 30
------2.列出所有办事员(CLERK)的姓名,编号和部门编号.
Selectempno,ename,deptnofromempwherejob='CLERK';
EMPNOENAME DEPTNO
------------------------------
7369SMITH 20
7876ADAMS 20
7900JAMES 30
7934MILLER 10
------3.找出佣金高于薪金的员工.
select*fromempwherecomm>sal;
比较忽略comm为空的值
select*fromempwherenvl(comm,0)>sal;
nvl(col1,p)要求col1和p的类型是一致的
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------
7654MARTIN SALESMAN 769828-9月-81 1250 1400 30
------4.找出佣金高于薪金的60%的员工.
select*fromempwherenvl(comm,0)>(sal*0.6);
select*fromempwherecomm>sal*60/100;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------
7654MARTIN SALESMAN 769828-9月-81 1250 1400 30
------5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select*fromempwhere(deptno=10andjob='MANAGER')or(deptno=20andjob='CLERK');
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------
7369SMITH CLERK 790217-12月-80 800 20
7782CLARK MANAGER 783909-6月-81 2450 10
7876ADAMS CLERK 778823-5月-87 1100 20
------6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),
既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select*fromemp
where(deptno=10andjob='MANAGER')
or(deptno=20andjob='CLERK')
or(jobnotin('MANAGER','CLERK')andsal>=2000);
(job<>'MANAGER'andjob<>'CLERK'andsal>=2000)
!
=
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------
7369SMITH CLERK 790217-12月-80 800 20
7782CLARK MANAGER 783909-6月-81 2450 10
7788SCOTT ANALYST 756619-4月-87 3000 20
7839KING PRESIDENT 17-11月-81 5000 10
7876ADAMS CLERK 778823-5月-87 1100 20
7902FORD ANALYST 756603-12月-81 3000 20
------7.找出收取佣金的员工的不同工作.
selectdistinctjobfromempwherenvl(comm,0)>0;
distinct消除重复值
JOB
---------
SALESMAN
------8.找出不收取佣金或收取的佣金低于100的员工.
select*fromempwherenvl(comm,0)<100;
select*fromempwherecommisnullorcomm<100;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------
7369SMITH CLERK 790217-12月-80 800 20
7566JONES MANAGER 783902-4月-81 2975 20
7698BLAKE MANAGER 783901-5月-81 2850 30
7782CLARK MANAGER 783909-6月-81 2450 10
7788SCOTT ANALYST 756619-4月-87 3000 20
7839KING PRESIDENT 17-11月-81 5000 10
7844TURNER SALESMAN 769808-9月-81 1500 0 30
7876ADAMS CLERK 778823-5月-87 1100 20
7900JAMES CLERK 769803-12月-81 950 30
7902FORD ANALYST 756603-12月-81 3000 20
7934MILLER CLERK 778223-1月-82 1300 10
这个不对:
select*fromempwherecomm<100;
------9.找出各月倒数第3天受雇的所有员工.
last_day(日期类型)所在月的最后一天
哑元表dual:
当不需要表的地方可以考虑使用它
selectto_char(sysdate,'yyyy-mm-ddhh24:
mi:
ss')fromdual;
select'HelloWorld'ashwfromdual;
select1+1fromdual;
select*fromempwherehiredate=last_day(hiredate)-2;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------
7654MARTIN SALESMAN 769828-9月-81 1250 1400 30
------10.找出早于12年前受雇的员工.
add_months(sysdate,-12*12)
返回日期类型
select*fromempwheremonths_between(sysdate,hiredate)>144;
select*fromempwherehiredate EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO ------------------------------------------------------------------------------- 7369SMITH CLERK 790217-12月-80 800 20 7499ALLEN SALESMAN 769820-2月-81 1600 300 30 7521WARD SALESMAN 769822-2月-81 1250 500 30 7566JONES MANAGER 783902-4月-81 2975 20 7654MARTIN SALESMAN 769828-9月-81 1250 1400 30 7698BLAKE MANAGER 783901-5月-81 2850 30 7782CLARK MANAGER 783909-6月-81 2450 10 7839KING PRESIDENT 17-11月-81 5000 10 7844TURNER SALESMAN 769808-9月-81 1500 0 30 7900JAMES CLERK 769803-12月-81 950 30 7902FORD ANALYST 756603-12月-81 3000 20 7934MILLER CLERK 778223-1月-82 1300 10 ------11.以首字母大写的方式显示所有员工的姓名. selectinitcap(ename)as姓名fromemp; 姓名 ---------- Smith Allen Ward Jones Martin Blake Clark Scott King Turner Adams James Ford Miller ------12.显示正好为5个字符的员工的姓名. selectenamefromempwherelength(ename)=5; selectenamefromempwhereenamelike'_____'; like中_表示一个字符 %任意个字符 selectenamefromempwhereenamelike'张%'; selectenamefromempwhereename='张%'; selectenamefromempwhereenamelike'%张%'; selectenamefromempwhereenamelike'S%'; selectenamefromempwhereenamelike'%S%'; ENAME ---------- SMITH ALLEN JONES BLAKE CLARK SCOTT ADAMS JAMES ------13.显示不带有"R"的员工的姓名. selectenamefromempwhereinstr(ename,'R')=0; selectenamefromempwhereenamenotlike'%R%'; ENAME ---------- SMITH ALLEN JONES BLAKE SCOTT KING ADAMS JAMES ------14.显示所有员工姓名的前三个字符. substr(字符,位置开始,位置结束) selectsubstr(ename,1,3)asenamefromemp; 从第1位置开始取3个字符 selectename,substr(ename,4)asename1fromemp; ENAME ------ SMI ALL WAR JON MAR BLA CLA SCO KIN TUR ADA JAM FOR MIL ------15.显示所有员工的姓名,用a替换所有"A" selectreplace(ename,'A','a')as替换后fromemp; selecttranslate(ename,'A','a')as替换后fromemp; 替换后 --------- SMITH aLLEN WaRD JONES MaRTIN BLaKE CLaRK SCOTT KING TURNER aDaMS JaMES FORD MILLER ------16.显示满10年服务年限的员工的姓名和受雇日期. months_between(sysdate,hiredate)两个日期之间的月数 sysdate-hiredate天数 selectename,hiredatefromempwheremonths_between(sysdate,hiredate)>120; selectename,hiredatefromempwheresysdate>add_months(hiredate,120); ENAME HIREDATE -------------------- SMITH 17-12月-80 ALLEN 20-2月-81 WARD 22-2月-81 JONES 02-4月-81 MARTIN 28-9月-81 BLAKE 01-5月-81 CLARK 09-6月-81 SCOTT 19-4月-87 KING 17-11月-81 TURNER 08-9月-81 ADAMS 23-5月-87 JAMES 03-12月-81 FORD 03-12月-81 MILLER 23-1月-82 不对: selectename,hiredatefromempwheremonths_between(hiredate,sysdate)>120; (<-120) ------17.显示员工的详细资料,按姓名排序. select*fromemporderbyename; EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO ------------------------------------------------------------------------------- 7876ADAMS CLERK 778823-5月-87 1100 20 7499ALLEN SALESMAN 769820-2月-81 1600 300 30 7698BLAKE MANAGER 783901-5月-81 2850 30 7782CLARK MANAGER 783909-6月-81 2450 10 7902FORD ANALYST 756603-12月-81 3000 20 7900JAMES CLERK 769803-12月-81 950 30 7566JONES MANAGER 783902-4月-81 2975 20 7839KING PRESIDENT 17-11月-81 5000 10 7654MARTIN SALESMAN
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 实验 报告 答案
![提示](https://static.bdocx.com/images/bang_tan.gif)