Challenging SQL interview questions

Question

Challenging SQL interview questions

Viewing 15 posts - 31 through 45 (of 51 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Fair enough, Gus.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

I agree... Barry's initial response was a bit "curt"... and I also think it was absolutely appropriate.

Look at the list of items asked in the Original post... these are relatively simple things that a person applying for a job in that particular area should know. And the ever fishy "I'm asking for a friend" is 100% BS. Who do you know that works in IT that doesn't have access to an internet connection and also doesn't know how to use Google?

So far as smiley faces go, until I get to know someone, they simply don't count. Sometimes they don't count even after I've gotten to know the person. I've had many people stab me in the face and then follow it with a dozen smiley faces. Only the words and the implied attitude behind the words count. The smiley faces only count if they amplify what has really been said.

So far as answering homework and interview questions go... the reason why it is implied on this forum and every forum I've ever been a member of is because there are way too many hacks out there to begin with and they simply don't deserve any help. It's not a matter of needing a vacation, it's a matter of common sense (usually absent in cases like this) on the part of the people doing the asking. I won't help anyone who has not shown in any way that they're trying to help themselves. And, I agree... why would I help you get a job that you're obviously not qualified for or grade in a class where you have not tried. And, yes, a little curtness or even a little rudeness in those cases is probably appropriate. I know the instructor or the interviewer would absolutely break the horns off such a person if they found out.

On the flip side, if someone shows that they have tried (normally with a good explanation and, preferablly, with the code they tried) and they offer some sample data in a readily consumable format, then, I'll help even if they're in school.

Welcome aboard, Paul. :-):-D:-P;-):w00t::hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Here's a perfect example...

http://www.sqlservercentral.com/Forums/Topic688949-8-1.aspx

My answer would have been... "Thanks for the setup code... please post the code that you've tried so far so we can point you in the right direction."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 4

Jeff Moden (4/6/2009)
Here's a perfect example...
http://www.sqlservercentral.com/Forums/Topic688949-8-1.aspx
My answer would have been... "Thanks for the setup code... please post the code that you've tried so far so we can point you in the right direction."

Whereas I thought the problem was interesting enough to just plain go for it.

Different approaches for different folks.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

GSquared (4/6/2009)
If it had come up in an interview, I would have had to say I don't know what it is. After the interview, I'd have tried to look it up. If I couldn't find it, or couldn't figure out the explanation (I could in this case, I'm just being hypothetical here), I might ask about it in the forum. I'd explain, "In an interview I just failed, I ran into a question that I just can't get a clear explanation of. Can anyone explain...?"

I agree with that. That's what I tried to express in my previous post in general about posts/interview questions.

Gail,

I was in for the post of an Developer-cum-Administrator and I think he was just checking my level of intellect about SQL.

Like you all, I am also against the people who just asks "any type of questions" without doing a research from their side.

--Ramesh

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

GSquared (4/6/2009)
Jeff Moden (4/6/2009)
Here's a perfect example...
http://www.sqlservercentral.com/Forums/Topic688949-8-1.aspx
My answer would have been... "Thanks for the setup code... please post the code that you've tried so far so we can point you in the right direction."
Whereas I thought the problem was interesting enough to just plain go for it.
Different approaches for different folks.

Just in case... I hope you don't think that was directed at you personally, Gus. It wasn't meant to be... especially since I've done the very same thing in the past.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

Jeff Moden (4/6/2009)
Who do you know that works in IT that doesn't have access to an internet connection and also doesn't know how to use Google?

And how did it get posted here if you don't have access?

I think good points have been made, and some nerves touched. Paul, there's a bit of history here, and I think I understand why it looks bad to you. On the other hand, it's also a fair point that newcomers should a) tread a little lightly and b) accept responsibility for their actions (or lack thereof).

I post quietly on other forums, like video ones, and accept links and advice from people that are willing to give it.

Paul White SSC Guru Points: 150467 More actions · Answer 8

@jeff,

Thanks! But all those smileys have left me emotionally confused 😀

@Halloween,

My entirely personal view is that if someone is able to talk a little about things like the 'halloween problem' in an interview, it shows they have an interest in the internal workings of database engines, and have probably, at some stage, wondered why there is an extra TOP operator, SORT, or a table spool in UPDATE operations. They might even know what a read cursor and a write cursor are, as they relate to UPDATE operations. It's a valid question for an interview, depending on the post applied for I guess...

For anyone wondering, see Craig Freedmans article at http://blogs.msdn.com/craigfr/archive/2008/02/27/halloween-protection.aspx

@steve-2,

Thank you. I'm not extra good at treading lightly I'm afraid - what you see is what you get with me! Happy to take responsibility for re-igniting this apparently old discussion. My view would be that I am happy to answer interview questions after the event, if they are interesting. I would probably be more inclined to be helpful if the OP had a fair go first or could demonstrate that (s)he had given the issue some proper thought.

Cheers all!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Paul White (4/6/2009)
My entirely personal view is that if someone is able to talk a little about things like the 'halloween problem' in an interview, it shows they have an interest in the internal workings of database engines

My opinion is that there's a difference between questions for 'extra credit' and questions that result in an immediate fail if the candidate can't answer. Questions on Halloween protection, database page structure, the allocation mechanisms in the database, NTFS IO details, protocol descriptions, steps the engine follows to execute a query, etc are fine as the former, but not as the latter unless the job is to write a database engine.

I've seen and heard of too many cases where the interview asks those kind of questions to make themselves look smart rather than to see how much the candidate knows, or to make the candidate feel like an idiot.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 10

GilaMonster (4/6/2009)
My opinion is that there's a difference between questions for 'extra credit' and questions that result in an immediate fail if the candidate can't answer.

True. This is very much an extra credit type of question.

GilaMonster (4/6/2009)
I've seen and heard of too many cases where the interview asks those kind of questions to make themselves look smart rather than to see how much the candidate knows, or to make the candidate feel like an idiot.

Also true. One can usually tell which from the way the questions are asked, and that helps to decide whether one wants the job or not 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Paul White (4/6/2009)
@Jeff,
Thanks! But all those smileys have left me emotionally confused :-DPaul

BWAA-HAAA!!! Now that's funny! Like I said, "Welcome aboard"... you're gonna fit in just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Paul White (4/5/2009)
Also try to remember what it was like when you first started out.

Actually, that's a pretty good point. So is the point about newbies not knowing some of the rules. Some of us have gotten a little thin skinned because of the extremely demanding tone of some folks (especially lately) and we've let it migrate to others that don't necessarily deserve it. I don't know about other folks, but I've fallen into that very deep abyss and I need to re-evaluate some of my own mannerisms. Heh... don't get me wrong... if someone shows up with a terrible attitude, high velocity pork chops will continue to be launched. But the original post on this thread didn't display that.

Still, I'd rather answer questions where someone has demonstrated that they've actually tried something on their own.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

On the Halloween thingy... that's what I call an "oolie" and it shouldn't make or break an interview. If someone knows it, then it may be a bonus unless that's all they know. I also agree that it may just be the interviewer showing off. Like so much else in this profession, "It Depends".

P.S. Not only did I not know what the Halloween thing was, now that I know, I still don't care except that the premise may actually be wrong. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ssbp2we SSC Enthusiast Points: 149 More actions · Answer 14

I thank and appreciate all the inputs I am getting from the admins and all the users. Here are my views about the questions:

1. How would you check that there are an odd number of lines in a given file? What if there was a folder of many files to check?

A: From what I have understood, this question is not related to SQL Server. We have not had any topic like this. This question is more dealing with file system. I may be wrong. Please correct me if I am.

2. In a text, tabular file, how would you check that the second column contains only even numbers?

A: I see if the second column does not have even numbers, we can use a SELECT statement to output the odd numbers using the following:

SELECT second_column

FROM table_name

WHERE second_column%2 = 1

Here, I have used the % operator like we do in most programming language. I am yet to try this in SQL. I am working on reinstalling all the softwares on a fresh copy of Vista on my laptop. Can you confirm if this operator is allowed with WHERE clause?

3. Given the following ERD: http://www.fda.gov/cder/drugsatfda/datafiles/erd.png, write a select statement that would retrieve the count of "App Doc" records that had an "Action Date" greater than yesterday and a "CHEMICAL_TYPE" of "Bad".

A: Looking at the ERD, this is what I came up with to accomplish this:

SELECT COUNT(*)

FROM AppDoc

INNER JOIN RegActionDate

ON RegActionDate.ApplID = AppDoc.ApplID

WHERE ActionDate > 'YESTERDAY'

AND

INNER JOIN Application

ON Appication.ApplNo = RegActionDate.ApplNo

WHERE CHEMICAL_TYPE = 'BAD'

Not so sure about the third one, can anyone confirm?

Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 15

ssbp2we (4/6/2009)
1. How would you check that there are an odd number of lines in a given file? What if there was a folder of many files to check?
A: From what I have understood, this question is not related to SQL Server. We have not had any topic like this. This question is more dealing with file system. I may be wrong. Please correct me if I am.

I would agree with you - this isn't a SQL Server question. Feel free to attack it in your favourite programming language.

ssbp2we (4/6/2009)
2. In a text, tabular file, how would you check that the second column contains only even numbers?
A: I see if the second column does not have even numbers, we can use a SELECT statement to output the odd numbers using the following:
SELECT second_column
FROM table_name
WHERE second_column%2 = 1
Here, I have used the % operator like we do in most programming language. I am yet to try this in SQL. I am working on reinstalling all the softwares on a fresh copy of Vista on my laptop. Can you confirm if this operator is allowed with WHERE clause?

The % operator (with a preceding space) is valid and performs a mod, just like other languages. You can find out all about it in Books Online.

You haven't said how you would use to result of your query to answer the question.

ssbp2we (4/6/2009)
3. Given the following ERD: http://www.fda.gov/cder/drugsatfda/datafiles/erd.png, write a select statement that would retrieve the count of "App Doc" records that had an "Action Date" greater than yesterday and a "CHEMICAL_TYPE" of "Bad".
A: Looking at the ERD, this is what I came up with to accomplish this:
SELECT COUNT(*)
FROM AppDoc
INNER JOIN RegActionDate
ON RegActionDate.ApplID = AppDoc.ApplID
WHERE ActionDate > 'YESTERDAY'
AND
INNER JOIN Application
ON Appication.ApplNo = RegActionDate.ApplNo
WHERE CHEMICAL_TYPE = 'BAD'
Not so sure about the third one, can anyone confirm?
Thanks!

I'm not so sure either - the basic idea is coming along, but the ordering of the keywords is a bit off. As written, you will get an error if you try to run it. Also, 'YESTERDAY' is a string - and will not produce the effect you are looking for.

When you have all your software reinstalled, have another go at this query - you're not all that far off.

Just out of interest - are these questions related to a course you are doing, an interview you went for, or something else?

If it was an interview, did you get the job? 😉

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi