LIKE Operator not returning desired result after using LOWER In Oracle SQL Developer, I have the following two queries : 

Sample Data for Reference from Employee Table

COMPANY_ID  NAME                                EMP_ID
1           Steven                                  4
2           Steven steven is working                4
3           That work was done by steven            4
4           Bradley is a good guy                   4
5           His name is yoshio                      4


Query #1:

SELECT COMPANY_ID,NAME,EMP_ID FROM employee WHERE emp_id = 4
AND NAME LIKE '%Steven%'


The above query returns the results like the following which is perfectly fine:

COMPANY_ID |  NAME                      | EMP_ID

1             Steven                        4   
2             Steven steven is working      4


Query #2:

The LOWER(NAME) as NAME converted all the upper case letters of the NAME column to lowercase and when I used the following 
query, I didn't not receive three rows which contains the word steven but only one as shown below: 

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


Here is the output of the above query in Oracle SQL Developer:

COMPANY_ID |  NAME                      | EMP_ID
2             steven steven is working      4


So, basically the pattern I am noticing after running the Query #2 is that if the searchable word(in our case it is steven) is 
surrounded by something on its left and right, that row is returned otherwise no other row with the searchable word is returned. 
For example, from the sample data above, rows with company_id 1 and 3 containing the word steven wasn't returned. 
Could anyone tell me why it's happening like this?
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%'