How to use two LIKE conditions?

Question

I need to find students with A and M in their first name but that do not have an a and m in their last name. This is my query but for some reason it keeps displaying last names that have M's... What am I doing wrong?

SELECT (firstname || ' ' || lastname) AS "FullName"
  FROM A5
 WHERE Lower(FirstName) LIKE '%a%m%' 
   AND Lower(LastName) NOT LIKE '%a%m%'
ORDER BY LastName,
         FirstName

Answer 1

WHERE clause should be:

WHERE (Lower(FirstName) LIKE '%a%m%'
OR Lower(FirstName) LIKE '%m%a%')
AND Lower(LastName) NOT LIKE '%a%'
AND Lower(LastName) NOT LIKE '%m%'

You were only including cases where FirstName had an a followed by an m. Similarly, you were only excluding the cases where LastName had both an a AND an m, where the a comes before the m.