Problem with SQL relational logic

Question

Problem with SQL relational logic

Navigation

#1 by (3 votes)
#2 by (3 votes)

2

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 for women in the state of SP, MG and RJ in the period from 2006 to 2012 . So I wrote this 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, can anyone see the error in the relational logic of my syntax in relation to the query rule given above?

mysql sql

asked by anonymous 06.07.2018 / 16:29

2 answers

3

Fixed SQL:

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 I want using OR and I removed LIKE , since I did not see the need for it.

I changed AND to OR because in the original query you are trying to get the status records that are MG, SP and RJ at the same time . It did not make sense to your problem. What you want are the states that can be anyone of these.

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

ORDER BY num_ocorrencia DESC

06.07.2018 / 16:35

Convert String to Date Is it possible to create predefined values in css?

score 3 · Accepted Answer

See the query with comments:

WHERE genero LIKE 'F'

LIKE is used with '%', so for example 'F%' which means 'any genre started by F', the '%' in the end means 'anything.' If you want a search with the exact value, best to use = . It looks like this:

WHERE genero = 'F'

In status:

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

Using the AND operator will not work because a NEVER state will be at the same time (AND) equal to SP, MG and RJ, only one of them at a time, so use operator OR (one or other of the states), like this:

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

Or you could also use the IN operator 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