case when then

--DROP TABLE BUILDING_MANAGER;
CREATE TABLE BUILDING_MANAGER
   (BUILDING_NO1 VARCHAR2(3) NOT NULL ENABLE,
  BUILDING_NO2 VARCHAR2(5) NOT NULL ENABLE,
  MANAGER_NAME VARCHAR2(30) NOT NULL ENABLE,
  SAFE_APPOINT_DE VARCHAR2(8) NOT NULL ENABLE,
  SAFE_LICENSE_NO VARCHAR2(30),
  REGIST_DT DATE NOT NULL ENABLE,
 CONSTRAINT PK_BULDING_MANAGER PRIMARY KEY (BUILDING_NO1, BUILDING_NO2, SAFE_LICENSE_NO)
 );


 INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','200','lisa','20150923','00100123F',sysdate);
 INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','875','tyra','20150722','00109921I',sysdate);
 INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('315','123','emma','20140123','00178542K',sysdate);
 INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('211','229','sally','20120722','00153211C',sysdate);
 INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('211','229','hera','20110329','00198723B',sysdate);
 INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','875','sophia','20130730','00158322A',sysdate);
 INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('315','123','wendy','20120211','00122231E',sysdate);


SELECT * FROM BUILDING_MANAGER;
----------------
result:

=================================================================================================

SELECT MANAGER_NAME
,SAFE_APPOINT_DE
,CASE
WHEN SAD < 1 THEN '0 years'
WHEN SAD >= 1 AND SAD < 3 THEN '1-2 years'
WHEN SAD >= 3 AND SAD < 5 THEN '3-4 years'
WHEN SAD >= 5 THEN '5 years'
END AS SAD
FROM   (SELECT BUILDING_NO1
                   ,BUILDING_NO2
      ,MANAGER_NAME
                   ,SAFE_APPOINT_DE
                   ,(TO_CHAR(SYSDATE, 'yyyy') - SUBSTR(SAFE_APPOINT_DE, 1, 4)) SAD
    FROM   BUILDING_MANAGER
   )

 ----------------
result: