oracle第四章单行函数

第四章  单行函数

练习1

• 1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）

--笔记：–INITCAP(列名|表达式) ：将每个单词的第一个字母转换成大写，其余的字母都转换成小写

select INITCAP(ename),LENGTH(ename) "ename长度" from emp where substr(ename,0,1) in ('J','A','M')  order by ename asc;

 

 

练习2

• 1.查询员工姓名中中包含大写或小写字母A的员工姓名。

SELECT ename FROM emp  WHERE upper(ename)LIKE'%A%'

 

• 2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函数，不能使用like进行判断)

select ename,length(ename) from emp  where deptno in (10,20)

and  instr(ename,'A')>0  OR  instr(ename,'a') >0  and hiredate  > '1-5月-81';

 

• 3.查询每个职工的编号,姓名，工资要求将查询到的数据按照 要求将查询到的数据按照定的格式合并成一个字符串.

– 前10位：编号,不足部分用*填充,左对齐

select LPAD(empno,10,'*'),ename,sal from emp ;

 

– 中间10位：姓名，不足部分用*填充,左对齐

select empno,LPAD(ename,10,'*'),sal from emp ;

 

– 后10位：工资，不足部分用*填充,右对齐

select LPAD(empno,10,'*'),LPAD(ename,10,'*'),RPAD(sal,10,'*')  from emp ;

 

练习3

1.四舍五入

select  round(100.456,2),round(100.456,1),round(100.456,0)  from dual;

2.没有四舍五入结果

select  trunc(100.456,2),trunc(100.456,1),trunc(100.456,0)  from dual;

 

练习4

 

1.查询员工一共入职多少天,当前时间-入职

select empno,ename, sysdate- hiredate from emp

 

练习5

• 1.查询服务器当前时间

select sysdate from dual;

 

• 2.查询部门10 20  的员工截止到2000年1月1日，工作了多少个月，入职的月份。(提示：使用months_between,extract)

SELECT ename, EXTRACT(MONTH FROM hiredate), MONTHS_BETWEEN('1-1月-2000',hiredate) months--EXTRACT(MONTH FROM '1-1月-2000')

FROM emp  where deptno in(10,20);

 

• 3.如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期,转正日期，入职日期后的第一个星期一,入职当月的最后一天日期。（提示：使用add_months,next_day,last_day)

select ename,hiredate, ADD_MONTHS(hiredate,6) new_date,  

NEXT_DAY(ADD_MONTHS(hiredate,6),'星期一'),

NEXT_DAY(ADD_MONTHS(hiredate,6),'星期一'),

LAST_DAY(ADD_MONTHS(hiredate,6))

from emp

where job <> 'MANAGER';

 

练习6

• 1.显示服务器系统当前时间，格式为2007-10-12

17:11:11( 17:11:11(提示：使用to char to_char函数)

select to_char(sysdate,'YYYY-MM-DD HH:MM:SS') from dual;

 

• 2.显示ename、hiredate 和雇员开始工作日是星期几(提示：使用to_char函数)

select 　ename,to_char(hiredate,'DAY') from emp;

 

• 3.查询员工姓名，工资，格式化的工资（￥999,999.99）(提示：使用to_char函数)

select 　ename,to_char(sal,'$999,999.99') from emp;

• 4.把字符串2015-3月-18 13:13:13 转换成日期格式 转换成日期格式，并计算

和系统当前时间间隔多少天。 (提示：使用to_date函数)

 

课后作业

1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。

--笔记：MONTHS_BETWEEN(现在的,原来日期) months,计算有多少月

select MONTHS_BETWEEN(sysdate,'01-1月-2000') months,

round(round(sysdate - to_date('2000-01-01','YYYY-MM-DD'))/7) week

from dual

 

2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。

select * from emp where ename like '__A%';

select * from emp where substr(ename,3,1)='A';

select * from emp where instr(ename,'A',3)=3;

 

3.使用trim函数将字符串‘hello’、‘ Hello ’、分别处理得到下列字符串ello、Hello、ll、hello。。

 

select trim(leading 'h' from 'hello') ,trim(' Hello '),

trim (both 'l'  from 'hello'),trim(' hello ')

from dual;

 

 

4.将员工工资按如下格式显示：123,234.00 RMB 。

select to_char('123234.00','000,999.00')|| 'RMB' from dual

 

5.查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

select ename, nvl(to_char(mgr) , 'No Manager') from emp;

select ename, coalesce(to_char(mgr) , 'No Manager') from emp;

select ename, ( case when mgr is not null  then to_char(mgr) else 'No Manager' end)

manager from emp;

 

6.将员工的参加工作日期按如下格式显示：月份/年份。

select ename,hiredate,to_char(hiredate,'HH/RR') from emp;

 

7.在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，

税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。

select

ename,sal, lpad(sal,15,'$'),

case

  when  sal > 0 and sal <1000 THEN sal * 0

  when  sal > =1000 and sal <2000 THEN sal * 0.1

  when  sal >= 2000 and sal <3000 THEN sal * 0.15

  when  sal >= 3000  THEN sal * 0.2

  else 0

  end tax

from emp;

 

8.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。

select ename, lpad(sal,15,'$') from emp;