LIKE Operator not returning desired result after using LOWER
Selecting a column as LOWER(NAME) doesn't automatically apply to your WHERE clause...  You need to have LOWER in WHERE clause, minimally.

SELECT
  COMPANY_ID, NAME, EMP_ID
FROM employee
WHERE emp_id = 4
  AND LOWER(NAME) LIKE '%steven%'


In actuality, they are two completely separate pieces of instructions to the query planner..  The WHERE clause instructs the planner on which records to consider, and the SELECT clause instructs the planner for how to present the data to you.

The following query is equally valid

SELECT
  COMPANY_ID, UPPER(NAME) as NAME, EMP_ID
FROM employee
WHERE emp_id = 4
  AND LOWER(NAME) LIKE '%steven%'