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
升级会员 