课后作业第四章

--Oracle作业 第四章

--1

SELECT round(months_between(SYSDATE,'1-1月-00')),round((SYSDATE - to_date('1-1月-00','dd-month-rr'))/7)

FROM dual

--2

  SELECT *FROM empWHERE INSTR(ename,'A',3,1)=3

  SELECT *FROM empWHERE SUBSTR(ename,3,1) ='A'

--3

SELECT TRIM('h' FROM 'hello'),TRIM(' 'FROM ' hello '),TRIM('b' FROM 'b||b'),TRIM(' 'FROM ' hello ')

FROM dual

--4

SELECT to_char(sal,'999,999.99') || 'RMB'

FROM emp

--5SELECT ename 员工姓名,NVL(to_char(mgr),'NO Manager') 经理编号

FROM emp

--6

SELECT to_char(hiredate,'mm/yyyy')FROM emp

--7

SELECT sal,

(CASE trunc(sal/1000)WHEN 0 THEN sal*0 

WHEN 1 THEN (sal-1000)*0.1 

WHEN 2 THEN 1000*0.1+(sal-2000)*0.15 

ELSE 1000*0.1+2000*0.15+(sal-3000)*0.2 END) tax

FROM emp

--8

SELECT ename,LPAD(sal,15,'$') SALARY

FROM emp