函数习题及答案

字符型函数

1.将EMP表中的数据展示为：‘XXX部门的部门名称为XXX，其员工是XXX’
–‘20部门的部门名称为research ，其员工是SMITH’
–‘20部门的部门名称为research ，其员工是SMITH，SCOTT,ALLEN…’

SELECT CONCAT(E.DEPTNO,
CONCAT(‘部门的部门名称为’,
CONCAT(D.DNAME,
CONCAT(‘其员工人数为’,
CONCAT( (SELECT COUNT(*) FROM EMP WHERE E.DEPTNO=DEPTNO),
CONCAT(’,其中有’, E.ENAME))))))
FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

---

SELECT CONCAT(E.DEPTNO,
CONCAT(‘部门的部门名称为’,
CONCAT(D.DNAME,
CONCAT(‘其员工人数为’,
CONCAT(COUNT(E.EMPNO),
CONCAT(’,其中有’, WM_CONCAT(E.ENAME)))))))
FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;

2.‘XXX部门的部门名称为XXX，其员工是XXX’ 其中数据要求: 员工工资均大于2000

SELECT CONCAT(E.DEPTNO,
CONCAT(‘部门的部门名称为’,
CONCAT(DNAME, CONCAT(’，其员工是’, ENAME))))
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+) AND E.SAL>2000;

3.将EMPNO和ENAME 列信息搭接在一起

select concat(EMPNO,ENAME) FROM EMP

4.将工资等于800和1250的员工姓名拼接起来–SMITHWARD SMITHMARTIN

SELECT CONCAT(E1.ENAME, E2.ENAME)
FROM (SELECT ENAME FROM EMP WHERE SAL = 800) E1,
(SELECT ENAME FROM EMP WHERE SAL = 1250) E2;

5.将工资大于2000的员工姓名大写

SELECT UPPER(ENAME) FROM EMP WHERE SAL>2000;

6.将工资小于2000的员工姓名小写

SELECT LOWER(ENAME) FROM EMP WHERE SAL<2000;

7.将工资大于2000的员工姓名大写，小于2000的员工姓名小写，其他首字
母大写
SELECT SAL, CASE WHEN SAL>2000 THEN UPPER(ENAME)
WHEN SAL<2000 THEN LOWER(ENAME)
ELSE INITCAP(ENAME)
END FROM EMP;

8.将emp表中职位为董事长的员工姓名全部大写，经理的员工姓名首字母大
写，其他职位的员工姓名小写，并以ename显示–24

SELECT CASE WHEN JOB=‘PRESIDENT’ THEN UPPER(ENAME)
WHEN JOB=‘MANAGER’ THEN INITCAP(ENAME)
ELSE LOWER(ENAME)
END ENAME FROM EMP;

9.将部门为OPERATIONS的员工姓名全部大写，部门为RESEARCH的员工姓名
首字母大写，并以ENAME显示—30

SELECT CASE WHEN DNAME=‘OPERATIONS’ THEN UPPER(ENAME)
WHEN DNAME=‘RESEARCH’ THEN INITCAP(ENAME)
END ENAME FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

10.查询所有员工的姓名，按照首字母小写，后续字母大写的形式显示
–截取首字母 大写，剩余字母小写 拼接在一起

SELECT LOWER (SUBSTR(ENAME,1,1))||UPPER(SUBSTR(ENAME,2)) FROM EMP ;

11.查询所有员工的姓名，按照首字母小写，末位字母小写，中间大写的形式显示
–将首字母 尾字母截取小写 剩余的字母大写 拼接一起

SELECT LOWER(SUBSTR(ENAME, 1, 1)) ||
SUBSTR((ENAME), 2, (LENGTH(ENAME)) - 2) ||
LOWER(SUBSTR(ENAME, -1))
FROM EMP;

12.将字符‘absdefgh’转换成“abs/def/gh/k” 的格式
–替换
select ‘absdefgh’, REPLACE(‘absdefgh’,‘absdefgh’,‘abs/def/gh/k’) from dual;
–CANCAT() RPAD() REPLACE() ||

---

13.将字符‘absdefgh’的在第三位后加个* 在第七位后面加个@符号,再把第五位字符去掉 – ‘abs*efg@h’,
–CONCAT SUBSTR RPAD REPLACE/TRIM

select concat(concat(concat(concat(substr(‘absdefgh’, 1, 3), ‘*’),
substr(‘absdefgh’, 5, 3)),
‘@’),
substr(‘adsdefgh’, 8, 1))
from dual;

14.将字符‘absdefgh’的第五位字符去掉，然后再在第三位后加个* ,在第七位后面加个@符号 --‘absdfgh@’
select concat(concat(concat(substr(‘absdefgh’,1,3),’’),substr(‘absdefgh’,5,4)),
‘@’)from dual;
15.将字符‘absdefgh’的第五位字符去掉，然后再在第三位后加个* ,再在第七位后面加个@符号 --ABSDFG@H
select upper(concat(concat(concat(concat (substr(‘absdefgh’,1,3),’’),substr(‘absdefgh’,5,3)),
‘@’),substr(‘adsdefgh’,8,1)))from dual;

16.将字符‘asdfghasdl’中第二次出现a之后的一位字符去除，再把第二次出现a之后的一位字符换成’牛‘字样–‘asdfgha牛l’
SELECT CONCAT(CONCAT(SUBSTR(‘asdfghasdl’,1,INSTR(‘asdfghasdl’,‘a’,1,2)),‘牛’),SUBSTR(‘asdfghasdl’,INSTR(‘asdfghasdl’,‘a’,1,2)+3)) FROM DUAL；

---

17.将’ASDASD’ 转换成‘对方团灭，我方全胜’ ，–过程体现：ASD—>对方团灭, ASD—>我方全胜
SELECT CONCAT(CONCAT(REPLACE(SUBSTR(‘ASDASD’,1,3),SUBSTR(‘ASDASD’,1,3),‘对方团灭’),’，’),REPLACE(SUBSTR(‘ASDASD’,-1,3),SUBSTR(‘ASDASD’,-1,3),‘我方全胜’)) FROM DUAL;

–思考：假设：不知道具体字符，知道是两组的字符串组成的STR，知道每组以A开头并且仅含有一个A ?
–若字符串时两组重复的数据，截取一半长度，替换
–若不重复，两组字符串组成的字符，每组以A开头

SELECT REPLACE(SUBSTR( STR,1, INSTR(STR,‘A’,1,2)-1),SUBSTR( STR,1, INSTR(STR,‘A’,1,2)-1),‘对方团灭’)||
REPLACE(SUBSTR( STR,INSTR(STR,‘A’,1,2)),SUBSTR( STR,INSTR(STR,‘A’,1,2)),’,我方全胜’)
FROM DUAL;

–思考：假设：不知道具体字符，知道是两组的字符串组成的STR，知道每组以A开头并且仅含有两个A ?

SELECT REPLACE(SUBSTR( STR,1, INSTR(STR,‘A’,1,3)-1),SUBSTR( STR,1, INSTR(STR,‘A’,1,3)-1),‘对方团灭’)||
REPLACE(SUBSTR( STR,INSTR(STR,‘A’,1,3)),SUBSTR( STR,INSTR(STR,‘A’,1,3)),’,我方全胜’)
FROM DUAL;

18.将EMP表中的数据展示为：‘XXX部门的部门名称为XXX，其员工人数为XXX，其中有XXX’ 思考？
----‘XXXX员工的部门是xxx,部门编号是XXX,该部门员工人数为XXXX’
SELECT CONCAT(ENAME,
CONCAT(‘员工的部门是’,
CONCAT(DNAME,
CONCAT(’，部门编号是’,
CONCAT(E.DEPTNO,
CONCAT(’，该部门员工人数为’,
C.CT ))))))
FROM EMP “E”,
DEPT “D”,
(SELECT DEPTNO, COUNT(*) “CT” FROM EMP GROUP BY DEPTNO) “C”
WHERE E.DEPTNO = D.DEPTNO(+)
AND E.DEPTNO = C.DEPTNO(+);

日期型函数:

1.MONTHS_BETWEEN()函数应用1

–高中时期经历多少个月

SELECT 3*12 FROM DUAL;

–15/09/01入学 18/06/01毕业

–去除寒暑假

寒假：01/23 02/18
暑假：07/13 09/01

–15/09/01入学 18/06/01毕业
SELECT MONTHS_BETWEEN(TO_DATE(‘2018/06/01’, ‘YYYY/MM/DD’),
TO_DATE(‘2015/09/01’, ‘YYYY/MM/DD’))
FROM DUAL;
–去除寒暑假/
寒假：01/23 02/18
暑假：07/13 09/01

SELECT MONTHS_BETWEEN(TO_DATE(‘2018/06/01’, ‘YYYY/MM/DD’),
TO_DATE(‘2015/09/01’, ‘YYYY/MM/DD’)) -
(MONTHS_BETWEEN(TO_DATE（ ‘02/18’, ‘MM/DD’),
TO_DATE(‘01/23’, ‘MM/DD’)) * 3 +
MONTHS_BETWEEN(TO_DATE（ ‘09/01’, ‘MM/DD’),
TO_DATE(‘07/13’, ‘MM/DD’)) * 2)
FROM DUAL;

---

SELECT ROUND(MONTHS_BETWEEN(TO_DATE(‘2018/06/01’, ‘YYYY/MM/DD’),
TO_DATE(‘2015/09/01’, ‘YYYY/MM/DD’)) -
(MONTHS_BETWEEN(TO_DATE（ ‘02/18’, ‘MM/DD’),
TO_DATE(‘01/23’, ‘MM/DD’)) * 3 +
MONTHS_BETWEEN(TO_DATE（ ‘09/01’, ‘MM/DD’),
TO_DATE(‘07/13’, ‘MM/DD’)) * 2),2)
FROM DUAL;

2. MONTHS_BETWEEN()函数应用

1)计算从元旦到今天经过了几个月
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE(‘2021/01/01’,‘YYYY/MM/DD’)) FROM DUAL;

2)计算从今天到国庆还有几个月
SELECT MONTHS_BETWEEN(TO_DATE(‘2021/10/01’,‘YYYY/MM/DD’),SYSDATE) FROM DUAL;

3)计算从今天到明年元旦还有几个月
SELECT MONTHS_BETWEEN(TO_DATE(‘2022/01/01’,‘YYYY/MM/DD’),SYSDATE) FROM DUAL;

3.将’2020-01-12‘转换为’2020/01/12‘
select to_date(‘2020-01-12’, ‘YYYY/MM/DD’) from dual;

两个日期：2020/12/13 2000/11/12
1）求两个日期对应的月份的差

select MONTHS_BETWEEN(to_date(‘2020/12/01’, ‘YYYY/MM/DD’),
to_date(‘2000/11/01’, ‘YYYY/MM/DD’))
from dual;

2）判断两个年份是闰年还是平年

select CASE WHEN TO_CHAR(LAST_DAY(to_date(‘2020/02/01’, ‘YYYY/MM/DD’)), ‘DD’) = 29
THEN ‘闰年’
ELSE ‘平年’
END
from dual;

–整百年被400整除 非整百年被4整除

SELECT CASE WHEN (MOD(1900,100)=0 AND MOD(1900,400)=0) OR (MOD(1900,100)<>0 AND MOD(1900,4)=0)
THEN ‘润年’
ELSE ‘平年’
END FROM DUAL;

–CASE WHEN 优先满足第一个条件，直接执行END

转换函数

1.将emp中工资大于2000的sal加上美元符号，其他加当地符号

select case when sal>2000 then to_char(sal,’$9999.00’)
else to_char(sal,‘L9999.00’)
end from emp
–去除货币符号

select case when sal>2000 then to_number(to_char(sal,’$9999.00’),’$9999.00’)
else to_number(to_char(sal,‘L9999.00’),‘L9999.00’)
end from emp

将在纽约工作的员工的工资加上美元符号
select case when deptno=(select deptno from dept where loc=‘NEW YORK’)then to_char(sal,’$9999.00’)
else to_char(sal,‘9999.00’) end from emp;

–去除货币符号
select case when deptno=(select deptno from dept where loc=‘NEW YORK’) then to_number(to_char(sal,’$9999.00’),’$9999.00’)
else to_number(to_char(sal,‘9999.00’),‘9999.00’)
end from emp ;

------------在以上两题基础上，将货币符号去掉?

MAJOR MA两列数据类型均为字符型
MAJOR :1,32,256,232
MA: 5,6,7,A,B,C
SELECT * FROM EMP3;

ALTER TABLE EMP3 ADD(MAJOR VARCHAR2(30));
ALTER TABLE EMP3 ADD(MA VARCHAR2(30));
ALTER TABLE TABLE_NAME ADD(COLUMN1 VARCHAR2(30));

SELECT * FROM EMP3 FOR UPDATE;

SELECT * FROM EMP3 WHERE MAJOR=32;–可以执行
SELECT * FROM EMP3 WHERE MA=6;—报错无效数字
SELECT * FROM EMP3 WHERE MA=‘6’;–可以执行
–为什么会出现该情况?

因为MA列含有非数字的数据，不能去隐式转换

3.某员工1981/10/08入职，将比该员工入职早的员工标记为老员工，其他员工标记为新员工（用MONTHS_BETWEEN()做）
select case when months_between(hiredate,to_date(‘1981/10/08’,‘YYYY/MM/DD’))>0 then ‘新员工’
when months_between(hiredate,to_date(‘1981/10/08’,‘YYYY/MM/DD’))<0 then ‘老员工’
end from emp;

4.某公司一三季度业绩不好，想将第一三季度入职员工降薪20%，给二四季度加薪10%
select case when to_char(hiredate,‘Q’) in (1,3) then sal0.8
when to_char(hiredate,‘Q’) in (2,4) then sal1.1
end from emp;

5.求下一个周的周五的日期
SELECT NEXT_DAY(NEXT_DAY(TO_DATE(‘2021/01/21’,‘YYYY/MM/DD’),‘星期日’),‘星期五’) FROM DUAL;

SELECT NEXT_DAY(NEXT_DAY(TO_DATE(‘2021/01/17’,‘YYYY/MM/DD’),‘星期日’),‘星期五’) FROM DUAL;

6.求下一个周五的日期
select next_day(sysdate,‘星期五’) from dual;

7.假设录入信息时将20部门的入职日期少算了3个月，请用SQL语句修正这个错误，并以hiredate显示

select add_months(hiredate,3) from emp where deptno=20;

数值及其他函数

1.emp表中工资能被3整除的提成加工资的10%，被4整除的提成加工资的20%，被5整除的提成加工资的50%，
其他不动；

SELECT SAL,
CASE
WHEN MOD(SAL, 3) = 0 THEN
NVL(COMM,0)+ SAL * 0.1
WHEN MOD(SAL, 4) = 0 THEN
NVL(COMM,0)+ SAL * 0.2
WHEN MOD(SAL, 5) = 0 THEN
NVL(COMM,0)+SAL * 0.5
ELSE
COMM
END
FROM EMP;

2.STUDENT表中10个学生，五间宿舍，一间宿舍住四个人，怎样合理分配？？

SELECT D.SNO,MOD(N,5) FROM (SELECT S.*,ROWNUM N FROM STUDENT S) D;

3.给经理工资上涨50%，销售上涨20%，分析师上涨10%，其他员工下调20%；

SELECT JOB,SAL,DECODE(JOB,‘MANAGER’,SAL1.5,‘SALESMAN’,SAL1.1,SAL*0.8) FROM EMP;

开窗函数

CREATE TABLE PRODUCT_ORDER(

ORDER_ID NUMBER(4),–产品ID
SALE_DATE DATE, —订单日期
WEEK VARCHAR2(30),–周
PRODUCT VARCHAR2(30),—产品名称
N NUMBER(5) , —交易量
AMOUNT NUMBER(15,5),–销售额
PRICE NUMBER(15,5),–单价
SCALE NUMBER(3,2)–折扣
);
SELECT * FROM PRODUCT_ORDER;

1.查询周一，周二…周日的销售额
SELECT WEEK, SUM(AMOUNT) FROM PRODUCT_ORDER GROUP BY WEEK;

2.查询每天的销售额

SELECT SUM(AMOUNT) FROM PRODUCT_ORDER GROUP BY SALE_DATE;

3.查询每天的累计销售额
–第一天 1000 累计销售额 1000
–第二天 2000 累计销售额 1000+2000
–第三天 3000 累计销售额 1000+2000+3000

SELECT AMOUNT,SUM(AMOUNT)OVER(ORDER BY SALE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM PRODUCT_ORDER;

SELECT AMOUNT,
SUM(AMOUNT) OVER(PARTITION BY SALE_DATE ORDER BY SALE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM PRODUCT_ORDER;—统计是：按照天分组，排序，计算里面数据的累加和，并不是计算的每天销售额的和的累加和

SELECT * FROM PRODUCT_ORDER;

4.查询每个部门的薪资倒序排列的前三名

–不考虑薪资重复的数据

SELECT *
FROM (SELECT EMP.*,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN
FROM EMP)
WHERE RN <= 3;

—考虑信息存在数据重复数据时

SELECT *
FROM (SELECT EMP.*, RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R
FROM EMP)
WHERE R <= 3;