《Oracle 11g数据库编程入门与实战》习题答案 第四章

--1）

SQL>SELECT cust_id, cname,

NVL(TO_CHAR(birthday, 'yyyy-mm-dd'), 'not avilable'), NVL(TO_CHAR(account), 'no data')

FROM customer;

--2）

SQL>SELECT * FROM customer

WHERE TO_CHAR(birthday, 'yyyy') = '1987';

--3）

SQL>SELECT SUM(account) FROM customer;

SQL>SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss' )

FROM DUAL;

SQL>SELECT empno, ename, NVL(sal, 0 ) + NVL(comm,0)

FROM emp;

SQL>SELECT ename, sal, job FROM emp

WHERE LENGTH(ename) = 5;

SQL>SELECT ename, hiredate,

ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)) worktime

FROM emp;

SQL>SELECT ename name, LPAD(sal, 15, '$') salary

FROM emp;

SQL>SELECT ename, TO_CHAR(sal, 'L9999.00') FROM emp

WHERE sal > 2000 ORDER BY sal DESC;

SQL>SELECT ename || ' earns ' || sal

||' monthly but wants ' || sal * 3

FROM emp;

SQL>SELECT ename,

TO_CHAR(hiredate, 'yyyy-mm-dd') hiredate,

TO_CHAR(hiredate, 'yyyy"年"mm"月"dd') review

FROM emp;

SQL>SELECT ename, job,

DECODE(job,'PRESIDENT','A',

'MANAGER','B',

'ANALYST','C',

'SALESMAN','D',

'CLERK','E'

) AS "Grade"

FROM EMP;

SQL>SELECT ename, job,

CASE job WHEN 'PRESIDENT' THEN 'A'

WHEN 'MANAGER' THEN 'B'

WHEN 'ANALYST' THEN 'C'

WHEN 'SALESMAN' THEN 'D'

WHEN 'CLERK' THEN 'E'

END AS "Grade"

FROM EMP;