Problem with SQL relational logic

Question

Considering this scheme

CREATE TABLE bra_ocorrencias (
    estado varchar2(2),
    genero varchar2(1),
    ano number,
    nome varchar2(30),
    num_ocorrencia number
);

I need to make a query that returns me the most registered name by women in the state of SP, MG and RJ from 2006 to 2012. So I wrote it that way

SELECT nome
FROM bra_ocorrencias 
WHERE genero LIKE 'F'
AND estado LIKE 'SP' AND estado LIKE 'MG' AND estado LIKE 'RJ'
AND ano BETWEEN 2006 AND 2012

The query has no syntax errors, it returns me results but not according to the proposed logic, someone can see the error in the relational logic of my syntax in relation to the query rule given above?

Answer 1

SQL corrected:

SELECT nome
FROM bra_ocorrencias 
WHERE genero = 'F'
AND (estado = 'SP' OR estado = 'MG' OR estado = 'RJ')
AND ano BETWEEN 2006 AND 2012

I changed to bring only the states you want using OR and took the LIKE, because I didn’t see the need for it.

I switched the AND for OR because in the original consultation you are trying to get the records of states that are MG, SP and RJ at the same time. It made no sense to your problem. What you want are the states that can be anyone of these.

You can add a ORDER BY at the end to sort by occurrences:

ORDER BY num_ocorrencia DESC

Answer 2

View the query with comments:

WHERE genero LIKE 'F'

LIKE is used with '%', so for example 'F%' meaning "any gender initiated by F", '%" at the end means "anything". If you want a search with the exact value, best use =. Stay like this:

WHERE genero = 'F'

In the state:

AND estado LIKE 'SP' AND estado LIKE 'MG' AND estado LIKE 'RJ'

using the operator AND won’t work because a state NEVER will be at the same time (AND) equal to SP, MG and RJ, only one at a time, so should use the operator OR (one or other of the states), thus:

AND (estado = 'SP' OR estado = 'MG' OR estado = 'RJ')

Or you could also use the operator IN to use a list of values:

 AND estado IN ('SP','MG','RJ')

Full query

SELECT nome
  FROM bra_ocorrencias 
 WHERE genero = 'F'
   AND (estado='SP' OR estado='MG' OR estado='RJ')
   AND ano BETWEEN 2006 AND 2012