SELECT
    EMP_CODE AS CODE
    , EMP_LAST_NAME AS SURNAME
    , EMP_FIRST_NAME AS NAME 
FROM
    EMP 
WHERE
    VALID_FLG = '1' 
   -- 社員姓、社員名の半角スペースを空文字に置き換えて比較 
    AND REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') IN ( 
        -- 副問合せで同姓同名が2件以上存在するデータを抽出
        SELECT
            REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') AS EMP_NAME 
        FROM
            EMP 
        WHERE
            VALID_FLG = '1' 
        GROUP BY
            EMP_NAME 
        HAVING
            COUNT(*) >= 2
    ) 
ORDER BY
    REPLACE (EMP_LAST_NAME || EMP_FIRST_NAME, ' ', '') ASC
    , EMP_CODE ASC;