Sample 'gov_form' The correct query The question
I'm working on a question which asks me to select countries where the form of its government is not 'Constitutional Monarchy' or 'Republic'.
The answer given is WHERE (gov_form = 'Constitutional Monarchy' OR gov_form LIKE '%Republic%'). I tried to switch them off as (gov_form LIKE 'Constitutional Monarchy%' OR gov_form = 'Republic') but this is wrong, and apparently the number of rows was different.
Im so confused because there are two other fields that contain 'Constitutional Monarchy(Emirate) 'and 'Constitutional Monarchy,Federation' so why can't I use the wildcard '%'???
Also there are couples of 'variations' of 'Republic' such as 'People'sRepublic', 'Socialist Republic', 'Federal Republic' in the table just like those of 'Constitutional Monarchy'!! But considering the question is asking to not include 'Republic', why should I use '%' to exempt all the 'variations' of 'Republic'???
Can anyone help me out please?
To me
Countries that do not have gov_form of Constitutional Monarchy
(bullet point 1)
means
countries where gov_form <> (not equal) 'Constitutional Monarchy'
and
countries that do not have republic in their gov form
(bullet point 2)
means
Countries where gov_form does not contain the text "republic"
This would be
WHERE gov_form <> 'Constitutional Monarchy' AND gov_form NOT LIKE '%Republic%'
the subquery is looking to turn that on its head because it uses a NOT IN, so in the subquery, we are basically saying
Show me those where gov_frm IS EQUAL TO 'Constitutional Monarchy' and gov_form IS LIKE '%republic%'
which are the records we don't want to see, the outer query excludes these by using the NOT IN
The question is poorly written and laid out in my opinion and definitely open to interpretation. I have re-written this answer three times based on three different understanding of the question. Furthermore, I would say this would be better solved and easily more readable with a regular JOIN
The biggest challenge with any programming task is understanding in human language what is required. Once that is done, the rest is "easy"
