SQL 单行函数 练习

–1：验证UPPER()、LOWER()函数
select upper(‘HXY’),lower(‘hxy’) from dual;

–2：现在查询出雇员姓名是“smith”的完整信息，但是由于失误，没有考虑到数据的大小写问题（在一些项目的运行之中经常会出现此类输入数据不考虑大小写的问题），此时可以使用UPPER()函数将全部内容变为大写
select * from emp where ename=upper(‘SMITH’);

–3：查询所有雇员的姓名，要求将每个雇员的姓名以首字母大写的形式出现
select ename,initcap(ename)from emp;

–4：现在要求查询所有雇员的姓名，但是要求将雇员姓名中所有的字母“A”替换成字母“_”
select ename,replace(ename,‘A’,’_’) from emp;

–5：查询出姓名长度是5的所有雇员信息
select * from emp where length(ename)=5;

–6：现在要求查询出雇员姓名前三个字母是“JAM”的雇员信息
select * from emp where substr(ename,0,3)=‘JAM’

–7：查询所有10部门雇员的姓名，但是不显示每个雇员姓名的前三个字母
select ename,substr(ename,3) from emp where deptno=10;

–8：要求显示每个雇员姓名及其姓名的后三个字母
select ename,substr(ename,length(ename)-2) from emp;

–9：在SUBSTR()函数中设置负数截取
select ename,substr(ename,-3) from emp;

–10: 下标从0开始
select ename,substr(ename,0,3) from emp ;

–11：下标从1开始
select ename,substr(ename,1,3) from emp ;

–12：返回指定字符的ASCII码
select ascii(‘L’),ascii(‘l’) from dual;

–13：验证CHR()函数，将ASC II码变回字符
select chr（100） from dual；

–14：去掉字符串左边空格 —— LTRIM()
select ’ CUIXU VDATA ‘,ltrim(’ CUIXU VDATA ') from dual;

–15：去掉字符串右边空格 —— RTRIM()
select ’ CUIXU VDATA ‘,rtrim(’ CUIXU VDATA ') from dual;

–16：去掉左右两边空格 —— TRIM()
select ’ CUIXU VDATA ‘,trim(’ CUIXU VDATA ') from dual;

–17：字符串左、右填充 —— LPAD()、RPAD()
select lpad(‘VDATA’,10,’’),rpad(‘VDATA’,10,’’) from dual;

–18：字符串查找 —— INSTR()
select
instr (‘VDATA Oracle’,‘VDATA’),
instr (‘VDATA Oracle’,‘Oracle’),
instr (‘VDATA Oracle’,‘ORACLE’)
from
dual;

–19：验证ROUND()函数的使用
select
round(789.652),
round(789.652,2),
round(789.652,-1)
form
dual;

–20：列出每个雇员的一些基本信息和日工资情况
select
empno,
ename,
job,
hiredate,
sal,
round(sal/30,2)
from
emp;

–21：验证TRUNC()函数

–22：验证MOD函数
select
mod(10,3)
from
dual;

–23：取得当前的系统时间
select
sysdate
from
dual;

–24：修改日期显示格式
alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
select
sysdate
from
dual;

–24：查询距离今天为止3天之后以及3天之前的日期
select
sysdate+3,
sysdate-3,
from
dual;

–25：查询出每个雇员的到今天为止的雇佣天数、以及十天前每个雇员的雇佣天数。
select
empno,
ename,
sysdate-hiredate,
(sysdate-10)-hiredate
from
emp;

–26：在查询结果之中使用TRUNC()函数完成功能
select
empno,
ename,
trunc(sysdate-hiredate),
trunc((sysdate- 10)-hiredate)
from
emp;

–27：验证ADD_MONTHS()函数

–28：要求显示所有雇员在被雇佣三个月之后的日期
select
empno,
ename,
job,
sal,
hiredate,
add_months(hiredate,3)
from
emp;

–29：验证NEXT_DAY()函数，NEXT_DAY()函数的功能主要是求出下一个指定的日期数，如果说现在的日期是“2012年01月30日 星期一”，那么如果现在要想知道下一个“星期一”或是“星期日”的具体日期，则可以使用

NEXT_DAY()函数。
select
sysdate,
next_day(SYSDATE,‘星期日’),
next_day(SYSDATE,‘星期一’)
from
dual;

LAST_DAY()函数
–30：验证LAST_DAY()函数，使用LAST_DAY()函数可以求得指定日期所在月的最后一天日期，如果今天的日期是“2012年01月19日”，则使用LAST_DAY()求出来的日期就是“2012年01月31日”
select
sysdate
last_day(sysdate)
from
dual;

–31：查询所有是在其雇佣所在月的倒数第三天被公司雇佣的完整雇员信息
select
empno,
ename,
job,
hiredate,
last_day(hiredate)
from
emp
where
last_day(hiredate) - 2 = hiredate;

–32：查询出每个雇员的编号、姓名、雇佣日期、雇佣的月数及年份

–33：查询出每个雇员的编号、姓名、雇佣日期、已雇佣的年数、月数、天数

–34：从日期时间之中取出年、月、日数据。
select
extract(year from date ‘2001-09-19’) years,
extract(month from date ‘2001-09-19’) months,
extract(day from date ‘2001-09-19’) days
from
dual;

–35：从时间戳之中取出年、月、日、时、分、秒。
select
extract(year from systimestamp) years,
extract(month from systimestamp) months,
extract(day from systimestamp) days,
extract(hour from systimestamp) hours,
extract(minute from systimestamp) minutes,
extract(second from systimestamp) seconds
from
dual;

–36：取得时间间隔

–37：复杂计算时间间隔（天数）
select
extract (day from to_timestamp(‘1982-08-13 12:17:57’,‘yyyy-mm-dd hh24:mi:ss’) - to_timestamp(‘1981-09-27 09:08:33’,‘yyyy-mm-dd hh24:mi:ss’
)) days
from
dual;

–34：格式化当前的日期时间
select
sysdate,
to-char(sysdate,‘yyyy-mm-dd hh24:mi:ss’)
from
dual;

–35：使用其他方法格式化年、月、日
select
sysdate,
to_char(sysdate,‘year-month-day’)
from
dual;

–36：查询出所有在每年2月份雇佣的雇员信息
select
*
from
emp
where
to_char(hiredate,‘mm’) = ‘02’;

–37：直接判断数字2
select
*
from
emp
where
to_char(hiredate,‘mm’) = 2;

–38：现在要求将每个雇员的雇佣日期进行格式化显示，要求所有的雇佣日期可以按照“年-月-日”的形式显示，也可以将雇佣的年、月、日拆开分别显示
select
empno,
ename,
job,
hiredate,
to_char(hiredate,‘yyyy-mm-dd’),
to_char(hiredate,‘yyyy’) 年,
to_char(hiredate,‘mm’) 月,
to_char(hiredate,‘dd’) 日
from
emp;

–39：使用英文的日期格式表示出每个雇员的雇佣日期
select
empno,
ename,
hiredate,
to_char(hiredate,‘year-month-day’)
from
emp;

–40：格式化数字显示
sclect
to_char(987654321.789,‘999,999,999,999.99999’)
from
dual;

–41：格式化货币显示
select
to_char(987654321.789,‘L999,999,999,999.99999’)
from
dual;

-***-42：使用TO_DATE()函数***
select
to_char(‘1979-09-19’,‘yyyy-mm-dd’)
from
dual;

–43：使用TO_TIMESTAMP()函数
select
to_timestamp(‘1981-09-27 18:07:10’,‘yyyy-mm-dd hh24:mm:ss’) datetime
from
dual;

–43：使用TO_NUMBER()函数将字符串变为数字
select
to_number(‘09’)
from
dual;

–44：不利用TO_NUMBER()函数字符串也可以自动变为数字
select
‘09’
from
dual;

–45：要求查询出每个雇员的编号、姓名、职位、雇佣日期、年薪
select
empno,
ename,
job,
hiredate,
(sal+comm)*12
from
emp;

–46：验证NVL()函数
select
nvl(null,0),
nvl(3,0)
from
dual;

–47：使用NVL()函数解决年薪为null的情况。
select
empno,
ename,
job,
hiredate,
(sal+nvl(comm,0))*12
from
emp;

–48：查询每个雇员的编号、姓名、年薪（sal + comm）、基本工资、奖金
select
empno,
ename,
nvl2(comm,sal + comm,sal),
sal,
comm
from
emp;

–49：验证NULLIF()函数
select
nullif(1,1),
nullif(1,2)
from
dual;

–50：验证NULLIF()函数
select
empno,
ename,
job,
length(ename),
length(job),
nullif(length(ename),length(job) ) nullif
from
emp;

–51：测试DECODE()函数
select
decode(2,1,‘内容为一’,2,‘内容为二’),
decode(2,1,‘内容为一’,‘没有条件满足’)
from
dual;

–52：现在雇员表中的工作有以下几种：
· CLERK：业务员
· SALESMAN：销售人员
· MANAGER：经理
· ANALYST：分析员
· PRESIDENT：总裁
要求可以查询雇员的姓名、职位、基本工资等信息，但是要求将所有的职位信息都替换为中文显示。
select
ename,
sal,
decode(job,‘CLERK’,‘业务员’,‘SALESMAN’,‘销售人员’,‘MANAGER’,‘经理’,‘ANALYST’,‘分析员’,‘PRESIDENT’,‘总裁’) job
from
emp;

–53：在DECODE()函数中只判断部分内容
select
ename,
sal,
decode(job,‘CLERK’,‘业务员’,‘SALESMAN’,‘销售人员’,‘MANAGER’,‘经理’) job
from
emp;

–54：显示每个雇员的姓名、工资、职位，同时显示新的工资（新工资的标准为：办事员增长10%、销售人员增长20%、经理增长30%、其他职位的人增长50%）
select
ename,
sal,
case job
when ‘CLERK’ then sal * 1.1
when ‘SALESMAN’ then sal * 1.2
when ‘MANAGER’ then sal * 1.3
else sal * 1.5
end 新工资
from
emp;

–55：验证COALESCE()函数的功能
select
ename,
sal,
comm,
coalesce(comm,null,2000)
from
emp;