November 12, 2014 at 10:00 am
It's a nice question that might confuse some newbies. It's important to study the logical operators and the results given.
I'd love to see in the explanation that the correct answer is translated as follows:
SELECT EmployeeName
FROM #QOTD
WHERE NOT (EmployeeName = 'steve' OR EmployeeName = 'grant');
Which is very similar to one of the options.
If we want to remove the parenthesis, the operators should change.
SELECT EmployeeName
FROM #QOTD
WHERE NOT EmployeeName = 'steve'
AND NOT EmployeeName = 'grant';
It's all fun and games until we find an employee with a NULL value in its name.
November 12, 2014 at 10:18 am
I appreciated the little nod to De Morgan's Laws 🙂
November 12, 2014 at 10:32 am
Stuart Davies (11/12/2014)
SQLRNNR (11/12/2014)
hjp (11/12/2014)
SQLRNNR (11/12/2014)
Impossible. There are multiple correct answers.JK
Ducks and runs - snigger.:-D:-D:-D:hehe:
Like... what?? If you don't present your case and argue why you think so, then why bother writing a comment here?
Hint in the JK and the smileys.
It was a Joke.
Phew - that's a relief - I didn't know if I should respond to it or not and didn't know how to put my argument! 😉
ROFL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 12, 2014 at 12:26 pm
super easy but completely ran on auto pilot with a list of names by taking out duplicates.
November 12, 2014 at 3:47 pm
Without a good 'reason' for the table, the id column by itself is not enough to say that there are 2 different "Brian" employees. the id might be refering to something as silly as a key assigned to an employee, so Brian might be one person with keys 8 and 9.
The sample did not include the id column in the result so there is now way to notice the difference between the two "Brian" rows, and I tend to expect that duplicates are bad.
---> I tend to supply this logic to people every time someone gives me duplicate data <----
I tend to write the code when analysing tables from some unknown system.
[font="Courier New"]
with sourcedata as (
select potentialDuplCol as dupl_potentialDuplCol from sometable group by potentialDuplCol having count(*) > 1
)
select * from sourcedata
inner join sometable on potentialDuplCol = dupl_potentialDuplCol
[/font]
This tends to quickly show why a column which was expected to a unique key, isn't a unique key.
November 13, 2014 at 12:30 am
Great question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 13, 2014 at 3:38 am
I am always surprised that when the question and answers hint that the database is not case sensitive there is no mention about this. All our databases are case sensitive and therefore I notice this most of the time. Would it not be better practice to code expecting this to be the case? When you are looking for specific entries (or exclude some) code these as if the entries are case sensitive.
In this example there are multiple entries that are the same (Brian) without adding an ID of such, we are always arguing that this is bad practice. Would it not be better that we try to make all questions of a high standard unless we want to make a point that requires those criteria?
Just my 2 cents
November 13, 2014 at 5:14 am
Basically a good and interesting question, but Steffen Mantz-204830's point is a valid criticism. I don't think that the omission of ID from the select list would lead anyone to get the wrong answer, though - but if there had been a "none of teh above" otion that minor flaw would have been a critical one.
Tom
November 13, 2014 at 8:49 am
Thanks for the question.
November 13, 2014 at 3:31 pm
Luis Cazares (11/12/2014)
It's all fun and games until we find an employee with a NULL value in its name.
A NULL in the QOTD table would in this case not be a problem. The problems you are thinking about occur when there is a NULL in the IN list.
Which is why I only use IN with a list of constants, never ever with a subquery - not even when I know there will not be NULL values there. Too subject to future change. And every query with [NOT] IN + subquery can always be rewritten to use [NOT] EXISTS.
November 17, 2014 at 12:48 pm
First of all, the column, ID, is accessed in the subsequent SQL as id, so
that it will fail. Later, the names are capitalized, and the query will actually
return all the names, since "grant" and "steve" have no capital, they also
qualify for the "not in" statement. The correct answer is "none".
November 17, 2014 at 3:54 pm
Hugo Kornelis (11/13/2014)
Luis Cazares (11/12/2014)
It's all fun and games until we find an employee with a NULL value in its name.A NULL in the QOTD table would in this case not be a problem. The problems you are thinking about occur when there is a NULL in the IN list.
Which is why I only use IN with a list of constants, never ever with a subquery - not even when I know there will not be NULL values there. Too subject to future change. And every query with [NOT] IN + subquery can always be rewritten to use [NOT] EXISTS.
You're right, but even if the NULL is in the table and not in the list, the NULL value won't show up. I hope that most people are aware of that, but it's one of the problems people face when they start working with SQL and NULLs.
November 18, 2014 at 11:41 am
+1
November 20, 2014 at 8:49 am
Nice question
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply