|
收藏
oracle練習(xí)題和答案 筆記 使用scott/tiger用戶下的emp表完成下列練習(xí)，表的結(jié)構(gòu)說(shuō)明如下 emp員工表(empno員工號(hào)/ename員工姓名/job工作/mgr上級(jí)編號(hào)/hiredate受雇日期/sal薪金/comm傭金/deptno部門編號(hào))
------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)的姓名，編號(hào)和部門編號(hào).
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中所有經(jīng)理(MANAGER)和部門20中所有辦事員(CLERK)的詳細(xì)資料.
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中所有經(jīng)理(MANAGER),部門20中所有辦事員(CLERK),
既不是經(jīng)理又不是辦事員但其薪金大于或等于2000的所有員工的詳細(xì)資料. 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 消除重復(fù)值 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 這個(gè)不對(duì) ：select * from emp where comm < 100;
------9.找出各月倒數(shù)第3天受雇的所有員工. last_day(日期類型) 所在月的最后一天 啞元表 dual : 當(dāng)不需要表的地方可以考慮使用它 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個(gè)字符的員工的姓名.
select ename from emp where length(ename)=5; select ename from emp where ename like '_____';
like中 _ 表示一個(gè)字符 % 任意個(gè)字符 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.顯示所有員工姓名的前三個(gè)字符.
substr(字符,位置開始,位置結(jié)束)
select substr(ename,1,3) as ename from emp;
從第1位置開始 取3個(gè)字符 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年服務(wù)年限的員工的姓名和受雇日期.
months_between(sysdate,hiredate)兩個(gè)日期之間的月數(shù)
sysdate - hiredate 天數(shù) 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 不對(duì)：select ename,hiredate from emp where months_between(hiredate,sysdate)>120;
(<-120) ------17.顯示員工的詳細(xì)資料,按姓名排序. 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.顯示員工的姓名和受雇日期,根據(jù)其服務(wù)年限,將最老的員工排在最前面.
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.顯示在一個(gè)月為30天的情況所有員工的日薪金,忽略余數(shù).
忽略余數(shù)/截?cái)?/div>
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.對(duì)于每個(gè)員工,顯示其加入公司的天數(shù). floor 向下取整,不超過(guò)這個(gè)數(shù)最大整數(shù)
floor(21.5) 21
floor(-21.5) -22 ceil 向上取整,不小于這個(gè)數(shù)的最小整數(shù) 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.以年月日的方式顯示所有員工的服務(wù)年限. (大概)
mod 取余數(shù)
mod(12,3) 0 mod(5,2) 1 mod(4,2) 0 to_date
日期轉(zhuǎn)換 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))||'個(gè)月' ||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 天數(shù) from emp; to_number 數(shù)字轉(zhuǎn)換 select to_number('1999') from dual; select to_number('abc') from dual; asc碼
select ascii('A') from dual; select chr(65) from dual; 連接函數(shù)
|| select to_char(sysdate,'yyyy"年"mm"月"') from dual;
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月' from dual; decode函數(shù)
if ... else if .... else sal=800 顯示低工資
sal=5000 顯示高工資 其他sal 顯示正常工資 等值比較 select ename,sal,decode(sal,800,'低工資',5000,'高工資','正常工資') as 工資狀態(tài) from emp; decode(sal,800,'低工資',5000,'高工資','正常工資') 相當(dāng)于
<等值比較>
if sal =800 then 顯示 '低工資' else if sal=5000 then 顯示 '高工資' else 顯示 '正常工資' end if x > 1600 變成 x-1600 > 0
符號(hào)函數(shù)sign 判斷數(shù)的正/負(fù)/零 select sign(-12) from dual; -1代表負(fù)數(shù) select sign(12) from dual; 1 代表正數(shù) 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 工資狀態(tài) from emp; 相當(dāng)于 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 |
|
|
