课堂笔记

第四章练习题

--练习1

SELECT ename,initcap(ename),length(ename)
FROM emp
WHERE substr(ename,1,1) IN('J','A','M')

--练习2

SELECT ename 
FROM emp
WHERE instr(lower(ename),'a',1)>0

SELECT ename,length(ename)
FROM emp
WHERE deptno IN(10,20)
AND hiredate > '1-5月-81'
AND instr(ename,'A',1)>0

SELECT lpad(deptno,10,'*') || lpad(ename,10,'*') || rpad(sal,10,'*')
FROM emp

--练习3

SELECT round(100.456,2),round(100.456,1),round(100.456,0) FROM dual
SELECT trunc(100.456,2),trunc(100.456,1),trunc(100.456,0) FROM dual

--练习4

SELECT ename,SYSDATE-hiredate
FROM emp

2001

1995

2001

1995

--练习5
SELECT SYSDATE FROM dual

SELECT ROUND(months_betWeen('1-1月-00',hiredate),0) working,
extract(MONTH FROM HIREDATE) coming
FROM emp
WHERE deptno IN(10,20)