stumbled upon this question...

Posted by redthil On 7/08/2008 11:39:00 AM

I got a question from the newly joined fresher in our team. It was very very simple. but i answered it wrongly. so just thought of asking u ppl. This is not a rocket science question. just a basic SQL statement. One White-Forest Pastry is waiting for those who answer this question correctly. Post your answers in the comments.

Q.:
What will be the output of the following SQL statement:
select Employee_Name from Employee_Details where Employee_ID NOT IN (select Employee_ID from Position_Details);

Following are the values in the two tables..

Employee_Details






Employee_IDEmployee_NameManager_ID
1Lawrence Ellison(NULL)
2Safra Catz1
3Charles Phillips1
4Tony Kender3


Position_Details:



Position_NameEmployee_ID

CEO(NULL)
CFO1


Waiting for giving away that pastry to you....
And small note:
> please use your SQL knowledge only(you can use google). it would be great if u dont execute this stmt in a database and give me the answer.
> you need to give me the explanation on how u arrived at that answer
> its purely your choice to follow the above notes. But i always follow it.

Have fun...

Here's a big clue...

Hows this post? 

19 comments

  1. Vijesh Said,

    I would have answered him
    Safra Catz
    Charles Phillips
    Tony Kender

    Reason:
    Let me see how bad I'm in Sql. First sub-query execution, which would return (NULL, 1) Not sure if really NULL will be returned, still that doesn't matter.

    Now outer query! 2,3,4 are three ID's that are not in the set. So respective names will be returned.

    Posted on Thursday, July 10, 2008 9:40:00 PM

     
  2. redthil Said,

    first, thanks for ur reply...
    Secondly, thats the same answer which i also gave. :(

    "Sometimes, few things matters much more than what we have thought"

    Posted on Friday, July 11, 2008 10:10:00 AM

     
  3. Sandhya Said,

    if a DB admin like u is not able to give an answer to this question...i cant imagine that i would give the write one....anyway let me try...it will return all the records in employee_details!!

    Posted on Sunday, July 13, 2008 9:33:00 PM

     
  4. redthil Said,

    @ Sandhya...
    nope... :(
    i have updated the post with the very vital clue for this puzzle. Hope that'll help in getting the correct answer. :)
    All the Best!!!!

    Posted on Monday, July 14, 2008 3:31:00 PM

     
  5. Vijesh Said,

    This comment has been removed by the author.

    Posted on Monday, July 14, 2008 4:50:00 PM

     
  6. Vijesh Said,

    Could be,
    Lawrence Ellison
    Safra Catz
    Charles Phillips
    Tony Kender

    Posted on Monday, July 14, 2008 4:52:00 PM

     
  7. redthil Said,

    @vijesh...
    oops... i'm sorry to say that, its not the correct answer.. ;)

    will give u one more clue.. the way of solving that puzzle is to convert that query in terms of simple AND's and OR's, then try substitute the real values.. i have give u the key.. u need to find only the LOCK to unlock it... ;)

    Posted on Monday, July 14, 2008 5:04:00 PM

     
  8. Sandhya Said,

    i came to know the answer after executing the query..so am i allowed to post it or would u call it a spoiler??

    Posted on Monday, July 14, 2008 8:10:00 PM

     
  9. redthil Said,

    mmmmm.. thats kind of OK.. but u need to give valid explanation on how it returns that output...

    Posted on Monday, July 14, 2008 8:15:00 PM

     
  10. is it Safra catz
    charles phillips
    tony kender
    CEO ??

    Posted on Thursday, July 17, 2008 12:00:00 PM

     
  11. oops forgot abt the explaination part , well.. if my ans is correct then i'll give else there is no point in giving the wrong explaination rite ?? :)

    Posted on Thursday, July 17, 2008 12:03:00 PM

     
  12. Chandru Said,

    I guess it returns nothing! As every operation involves a comparison with NULL whose result is unknown in turn the end result would be unknown.

    Posted on Monday, July 21, 2008 11:20:00 AM

     
  13. Chandru Said,

    I am damn sure that the above answer and the explanation is right! And I haven't tried executing it like sandhya. But I used your clue. Anyway, the pastry is mine!

    Posted on Monday, July 21, 2008 11:24:00 AM

     
  14. Chandru Said,

    Am more interested in knowing eli's explanation than the pastry ;-) I still wonder what logic she applied to arrive at that resultant set. If Eli's logic is good I will give my pastry to her :)

    Posted on Tuesday, July 22, 2008 5:06:00 PM

     
  15. Giruba SP Said,

    I agree with chandru, it does not return anything. How i arrived at it is,
    i would translate the query to
    select * from Employee_Details where Employee_ID <> 1 and Employee_ID<>null

    id id<>1 id<>null and
    1 false Unknown false
    2 true Unknown Unknown
    3 true Unknown Unknown
    4 true Unknown Unknown

    So that final answer would be 0 rows. So who gets the pastry?

    Posted on Thursday, August 14, 2008 11:40:00 AM

     
  16. redthil Said,

    Ya.. u're right...
    And to answer "Who gets the pastry?", i would be glad to give each one, who replied to this, a pastry, if everyone agrees.. :)
    what say ppl?????? this is one time offer only... ;)

    Posted on Thursday, August 14, 2008 12:30:00 PM

     
  17. Thanks Mr.redthil. Got the solution for my issue

    Posted on Wednesday, November 04, 2009 7:00:00 PM

     
  18. Anonymous Said,

    What's up everyone, it's my first pay a visit at this web site, and post
    is genuinely fruitful in favor of me, keep up
    posting such articles.

    Check out my blog post - watch movies

    Posted on Friday, October 04, 2013 1:54:00 AM

     
  19. Anonymous Said,

    Hello! I could have sworn I've been to this web site before
    but after looking at a few of the articles I realized it's new to
    me. Anyways, I'm certainly delighted I found it and I'll be book-marking it and checking back often!


    Also visit my web blog ... gold ira

    Posted on Sunday, October 06, 2013 11:38:00 PM

     
My Photo
redthil
Some people go to priests; others to poetry; I to my friends.
View my complete profile

Labels