oracle练习题和答案 笔记
使用scott/tiger用户下的emp表完成下列练习，表的结构说明如下
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
------1.选择部门30中的所有员工.
select * from emp where deptno=30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
------2.列出所有办事员(CLERK)的姓名，编号和部门编号.
Select empno,ename,deptno from emp where job='CLERK';
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7876 ADAMS 20
7900 JAMES 30
7934 MILLER 10
------3.找出佣金高于薪金的员工.
select * from emp where comm > sal;
比较忽略comm为空的值
select * from emp where nvl(comm,0)>sal;
nvl(col1,p) 要求col1 和 p的类型是一致的
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
------4.找出佣金高于薪金的60%的员工.
select * from emp where nvl(comm,0)>(sal*0.6);
select * from emp where comm > sal * 60 / 100;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
------5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
------6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),
既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select * from emp
where (deptno=10 and job='MANAGER')
or (deptno=20 and job='CLERK')
or (job not in ('MANAGER','CLERK') and sal>=2000);
( job <> 'MANAGER' and job <> 'CLERK' and sal >= 2000)
!=
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
------7.找出收取佣金的员工的不同工作.
select distinct job from emp where nvl(comm,0)>0;
distinct 消除重复值
JOB
---------
SALESMAN
------8.找出不收取佣金或收取的佣金低于100的员工.
select * from emp where nvl(comm,0)<100;
select * from emp where comm is null or comm < 100;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
这个不对 ：select * from emp where comm < 100;
------9.找出各月倒数第3天受雇的所有员工.
last_day(日期类型) 所在月的最后一天
哑元表 dual : 当不需要表的地方可以考虑使用它
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select 'Hello World' as hw from dual;
select 1+1 from dual;
select * from emp where hiredate = last_day(hiredate) - 2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
------10.找出早于12年前受雇的员工.
add_months(sysdate,-12*12)
返回日期类型
select * from emp where months_between(sysdate,hiredate) > 144;
select * from emp where hiredate < add_months(sysdate,-12*12);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
------11.以首字母大写的方式显示所有员工的姓名.
select initcap(ename) as 姓名 from emp ;
姓名
----------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
James
Ford
Miller
------12.显示正好为5个字符的员工的姓名.
select ename from emp where length(ename)=5;
select ename from emp where ename like '_____';
like中 _ 表示一个字符
% 任意个字符
select ename from emp where ename like '张%';
select ename from emp where ename='张%';
select ename from emp where ename like '%张%';
select ename from emp where ename like 'S%';
select ename from emp where ename like '%S%';
ENAME
----------
SMITH
ALLEN
JONES
BLAKE
CLARK
SCOTT
ADAMS
JAMES
------13.显示不带有"R"的员工的姓名.
select ename from emp where instr(ename,'R') = 0;
select ename from emp where ename not like '%R%';
ENAME
----------
SMITH
ALLEN
JONES
BLAKE
SCOTT
KING
ADAMS
JAMES
------14.显示所有员工姓名的前三个字符.
substr(字符,位置开始,位置结束)
select substr(ename,1,3) as ename from emp;
从第1位置开始 取3个字符
select ename,substr(ename,4) as ename1 from emp;
ENAME
------
SMI
ALL
WAR
JON
MAR
BLA
CLA
SCO
KIN
TUR
ADA
JAM
FOR
MIL
------15.显示所有员工的姓名,用a替换所有"A"
select replace(ename,'A','a') as 替换后 from emp;
select translate(ename,'A','a') as 替换后 from emp;
替换后
---------
SMITH
aLLEN
WaRD
JONES
MaRTIN
BLaKE
CLaRK
SCOTT
KING
TURNER
aDaMS
JaMES
FORD
MILLER
------16.显示满10年服务年限的员工的姓名和受雇日期.
months_between(sysdate,hiredate)两个日期之间的月数
sysdate - hiredate 天数
select ename,hiredate from emp where months_between(sysdate,hiredate)>120;
select ename,hiredate from emp where sysdate>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
不对：select ename,hiredate from emp where months_between(hiredate,sysdate)>120;
(<-120)
------17.显示员工的详细资料,按姓名排序.
select * from emp order by ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7839 KING PRESIDENT 17-11月-81 5000 10
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
------18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select ename,hiredate from emp order by hiredate;
ENAME HIREDATE
---------- ----------
SMITH 17-12月-80
ALLEN 20-2月 -81
WARD 22-2月 -81
JONES 02-4月 -81
BLAKE 01-5月 -81
CLARK 09-6月 -81
TURNER 08-9月 -81
MARTIN 28-9月 -81
KING 17-11月-81
JAMES 03-12月-81
FORD 03-12月-81
MILLER 23-1月 -82
SCOTT 19-4月 -87
ADAMS 23-5月 -87
------19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
select ename,job,sal from emp order by job desc,sal;
ENAME JOB SAL
---------- --------- ----------
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ALLEN SALESMAN 1600
KING PRESIDENT 5000
CLARK MANAGER 2450
BLAKE MANAGER 2850
JONES MANAGER 2975
SMITH CLERK 800
JAMES CLERK 950
ADAMS CLERK 1100
MILLER CLERK 1300
SCOTT ANALYST 3000
FORD ANALYST 3000
------20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,
若月份相同则将最早年份的员工排在最前面.
select to_char(sysdate,'day') from dual;
select to_char(sysdate,'ddd') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'month') from dual;
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;
select ename,to_char(Hiredate,'yyyy"年"mm"月"') as 年月,hiredate from emp
order by to_char(Hiredate,'mm'),to_char(Hiredate,'yyyy');
ENAME 年月 HIREDATE
---------- ---------- ----------
MILLER 1982年01月 23-1月 -82
ALLEN 1981年02月 20-2月 -81
WARD 1981年02月 22-2月 -81
JONES 1981年04月 02-4月 -81
SCOTT 1987年04月 19-4月 -87
BLAKE 1981年05月 01-5月 -81
ADAMS 1987年05月 23-5月 -87
CLARK 1981年06月 09-6月 -81
MARTIN 1981年09月 28-9月 -81
TURNER 1981年09月 08-9月 -81
KING 1981年11月 17-11月-81
SMITH 1980年12月 17-12月-80
JAMES 1981年12月 03-12月-81
FORD 1981年12月 03-12月-81
select ename,to_char(Hiredate,'yyyy/mm') as 年月,hiredate from emp
order by to_char(Hiredate,'mm'),to_char(Hiredate,'yyyy');
------21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
忽略余数/截断
trunc(17.25) 17
trunc(19.98) 19
trunc(19.98,1) 19.9
四舍五入
round(20.5) 21
round(20.88,1) 21.9
select ename,trunc(sal/30) daysal from emp;
ENAME DAYSAL
---------- ----------
SMITH 26
ALLEN 53
WARD 41
JONES 99
MARTIN 41
BLAKE 95
CLARK 81
SCOTT 100
KING 166
TURNER 50
ADAMS 36
JAMES 31
FORD 100
MILLER 43
------22.找出在(任何年份的)2月受聘的所有员工。
select * from emp where to_char(hiredate,'mm')='02';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
------23.对于每个员工,显示其加入公司的天数.
floor 向下取整,不超过这个数最大整数
floor(21.5) 21
floor(-21.5) -22
ceil 向上取整,不小于这个数的最小整数
ceil(12.5) 13
ceil(-12.5) -12
select ename,floor(sysdate-hiredate) totdays from emp;
ENAME TOTDAYS
---------- ----------
SMITH 8721
ALLEN 8656
WARD 8654
JONES 8615
MARTIN 8436
BLAKE 8586
CLARK 8547
SCOTT 6407
KING 8386
TURNER 8456
ADAMS 6373
JAMES 8370
FORD 8370
MILLER 8319
------24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
select * from emp where instr(ename,'A')>0;
select * from emp where ename like '%A%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
------25.以年月日的方式显示所有员工的服务年限. (大概)
mod 取余数
mod(12,3) 0
mod(5,2) 1
mod(4,2) 0
to_date
日期转换
select to_date('1999-09-05','yyyy-mm-dd') from dual
select empno,ename,('在职'||trunc((months_between(sysdate,hiredate))/12)||'年'
||trunc(mod((months_between(sysdate,hiredate)),12))||'个月'
||round(sysdate-(add_months(hiredate,months_between(sysdate,hiredate))))||'天') tot from emp;
select ename,
to_char(to_date('0001-01-01','yyyy-mm-dd')+(sysdate-hiredate)-366-31,'yy:mm:dd') as tot, trunc(sysdate-hiredate) as 天数 from emp;
to_number
数字转换
select to_number('1999') from dual;
select to_number('abc') from dual;
asc码
select ascii('A') from dual;
select chr(65) from dual;
连接函数
||
select to_char(sysdate,'yyyy"年"mm"月"') from dual;
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月' from dual;
decode函数
if ... else if .... else
sal=800 显示低工资
sal=5000 显示高工资
其他sal 显示正常工资
等值比较
select ename,sal,decode(sal,800,'低工资',5000,'高工资','正常工资') as 工资状态
from emp;
decode(sal,800,'低工资',5000,'高工资','正常工资') 相当于
<等值比较>
if sal =800 then
显示 '低工资'
else if sal=5000 then
显示 '高工资'
else
显示 '正常工资'
end if
x > 1600 变成 x-1600 > 0
符号函数sign 判断数的正/负/零
select sign(-12) from dual; -1代表负数
select sign(12) from dual; 1 代表正数
select sign(0) from dual; 0 代表0
sal<1600 显示低工资
1600=<sal<=3000 显示正常工资
>3000 显示高工资
select ename,sal,decode(sign(sal-1600),-1,'低工资',
decode(sign(sal-3000),-1,'正常工资',0,'正常工资',1,'高工资')) as 工资状态
from emp;
相当于
if sign(sal-1600) = -1 then
显示 '低工资'
else
if sign(sal-3000) = -1 then
显示 '正常工资'
else if sign(sal-3000) = 0 then
显示 '正常工资'
else
显示 '高工资'
end if
end if
1888
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
