Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Help with SQL Query

Old 08-02-16, 01:16 PM
  #1  
DVD Talk Legend
Thread Starter
 
Raul3's Avatar
 
Join Date: Apr 2003
Location: Picture a cup in the middle of the sea
Posts: 10,676
Help with SQL Query

So this came in an interview and I blacked out.

Having this data in a table (People):

floor office name
1 102 Mike
2 201 Tom
2 202 Frank
3 301 Tom
3 302 Joe
1 101 Tom
give me the SQL query that will get where Mike and Tom are in the same floor.

So the result will look like:

1 102 Mike
1 101 Tom

I would normalize the data first , but thatís not the question. I thought about doing a join of the table with itself, or something more complicated, one temporal table with name=Mike, another temp table with name=Tom, and then a join where t1.floor=t2.floor

Additional question: where can I read more about advanced SQL queries.
Raul3 is offline  
Old 08-02-16, 07:17 PM
  #2  
DVD Talk Platinum Edition
 
Vipper II's Avatar
 
Join Date: Feb 2006
Location: Abingdon, MD
Posts: 3,115
Re: Help with SQL Query

As you suspected, a self-join is the quickest:

select a.*, b.*
from people a
join people b
on a.floor = b.floor
where a.name = 'Tom'
and b.name = 'Mike'

One of my favorite websites for SQL help is blog.sqlauthority.com. Additionally, a lot of times I can Google the basics of a query I want and get a lot of good information. dba.stackexchange.com frequently comes up in those search results.
Vipper II is offline  
Old 08-03-16, 02:05 AM
  #3  
DVD Talk Limited Edition
 
RoboDad's Avatar
 
Join Date: Aug 1999
Location: A far green country
Posts: 5,951
Re: Help with SQL Query

Why do you need to do something so unnecessarily complicated? Nothing in the requirements indicates that the data in the result set needs to be in one row. Even your example answer shows two.

You have a very simple dataset. Either you are leaving something out of the question you were given, or else you are overthinking the problem.

(Note: Avoid using SELECT *, especially in an interview. I regularly interview SQL developer candidates in my company, and that is one of the first things I look for as a sign of inexperience.)

Also, another very useful site for SQL topics is stackoverflow.com.
RoboDad is online now  
Old 08-03-16, 04:30 PM
  #4  
DVD Talk Legend
Thread Starter
 
Raul3's Avatar
 
Join Date: Apr 2003
Location: Picture a cup in the middle of the sea
Posts: 10,676
Re: Help with SQL Query

Thanks Viper, that worked.
I will check the websites you mentioned.

And Robo, it was a phone interviewing, so, it was explained kind of lazily, and as I mentioned my first thought would be to normalize, but the interviewing was looking more for a SQL query. That was the only question he asked about SQL.
Raul3 is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright © 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.