The T-SQL Test

Question

The T-SQL Test

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
December 19, 2007 at 7:34 pm

#201876

Comments posted to this topic are about the item The T-SQL Test

Viewing 15 posts - 1 through 15 (of 16 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Heh... you've all probably seen me post this before... Interviews with people who claim to be experts at T-SQL usually stink. In particular, I had one guy with (supposedly) a Masters in CS and 10 years of experience in Oracle and SQL Server claim that he was a (and I quote) "9 out of 10 in both Oracle and SQL Server" right on the resume...

... he didn't get past the first question...

"How do you return the current date and time in Oracle and SQL Server?"

--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)

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

How do you find the duplicate rows in a table with Integer ID and a varchar name fields?

Well, that depends on whether its the name that's duplicated, or the name and the ID.

Here's a couple of mine.

A table has a DateInserted column that has a default of GetDate(). Write a query to find all rows inserted 3 days ago.

Write a function that, when passed a date, returns the first day of that month.

A table has a CategoryID column, and an Entry column. How would you find all Categories that have more than 5 entries?

Why do so many people have problems with the date functions?

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

majorbloodnock SSCrazy Eights Points: 9389 More actions · Answer 3

The problem I find with tests like those you've mentioned is usually that they concentrate on the detail and not on the fundamental abilities. Personally, I like seeing people provided with scenarios, given the access to the standard resources (books online, t'interweb etc.), and then left to sort themselves out. I don't care if someone can't remember the syntax for reattaching an orphaned user, but alarm bells will definitely be ringing if someone suggests performing a truncate without taking a backup first. "Open book" scenarios seem to show that distinction up better IMHO.

Semper in excretia, suus solum profundum variat

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

majorbloodnock (12/20/2007)
The problem I find with tests like those you've mentioned is usually that they concentrate on the detail and not on the fundamental abilities. Personally, I like seeing people provided with scenarios, given the access to the standard resources (books online, t'interweb etc.), and then left to sort themselves out. I don't care if someone can't remember the syntax for reattaching an orphaned user, but alarm bells will definitely be ringing if someone suggests performing a truncate without taking a backup first. "Open book" scenarios seem to show that distinction up better IMHO.

I agree...I used to get right down to the very basics and give what I thought were embarrassingly simple tests (ones that I felt I had to apologize for) - I never looked for syntax - just concepts...like Jeff I discovered that the more claims to expertise the greater the fall...there was one question which showed 2 tables - an employee table with ID & name and another with ID, age, gender etc. - both tables had sample values in them and the question was to find the oldest male employee (or some such)....the query from a certified MCDBA was "select name from Emp where age = 51 and name = 'Albert'"

**ASCII stupid question, get a stupid ANSI !!!**

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Sushila! Haven't run into any of your posts in quite a while! So very happy that you're still part of the "community" and very happy to "see" you again!

--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)

Wayne West SSC-Insane Points: 22586 More actions · Answer 6

Steve,

I just read the questions that you linked to: fantastic set! It's also definitely a good set to study when you're in the market and going to be facing interviews. FYI, the comments link doesn't work.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Tony Mungor SSC Eights! Points: 850 More actions · Answer 7

How do you find the duplicate rows in a table with Integer ID and a varchar name fields?

select * from [table_name] a where

[varchar column] in (select [varchar column], count(*) from [table_name] group by [varchar column] having count(*) > 1) and

[ID column] not in (select min([ID column] from [table_name] b where b.[varchar column] = a.[varchar column])

This query will return the duplicate rows, it does not return the MIN(ID) for each duplicate set as that is most likely the row you would keep.

This part of the query can be used to get a list of the duplicate values and a count of how many rows exist with that given value.

select [varchar column], count(*) from [table_name] group by [varchar column] having count(*) > 1

Darren Wallace Right there with Babe Points: 774 More actions · Answer 8

Hi Tony

That'll work but I've always preferred:

WITH T AS (

SELECT *

, ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [ID] DESC) AS RowNum

FROM [Table]

)

SELECT * FROM T WHERE RowNum = 1

It only works in 2005 but it's nice and easy to read, you don't have to try and decipher the nested selects inline.

James_DBA Default port Points: 1422 More actions · Answer 9

Now a days, I think the first question an 'interviewee' needs to ask is "What version of SQL are you using?"

Tony's answer would be acceptable; but, if I were hiring for SQL 2005 then Darren's answer would be the one I would be looking for. SQL isn't just about getting the job done anymore, it's also about being able to pass your work on to another person in the future (rather it be from development to production, or looking for code maintainability in the future). I think readability has a prime in many situations.

Thanks,

James

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

Tony Mungor SSC Eights! Points: 850 More actions · Answer 10

I admit I'm a DBA/Developer for an ISV and support internal systems and 500 customers running SQL 6.5 - 2005 and Oracle 8 - 10. So by habit I always take the generic route.

David Goldstein-450388 SSC Veteran Points: 215 More actions · Answer 11

I used to work with a certified MCDBA who lacked basic T-SQL knowledge. I agree that there should be an "official" test. I have taken the 2000 and 2005 cert exams and was disappointed to find very little T-SQL knowledge required.

For the question, wouldn't the simplest solution be:

SELECT ID, [Name]

FROM tblWhatever

GROUP BY ID, [Name]

HAVING COUNT(*) > 1

This returns all the duplicate rows. The question does not say that the ID field is a primary key or has a unique index on it. If we are assuming those things, though, just remove ID from the SELECT and GROUP BY clauses of the above query.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

Nice 2005 solution. It's one I wouldn't have come up with, though I think any solution would work as long as it found the duplicates. After all, it's mainly a check for admins, not a high volume process.

Don't get too sidetracked on that question. How much T-SQL do you want your people to know? Should there be a cert? What questions do you ask?

James_DBA Default port Points: 1422 More actions · Answer 13

I think the amount of SQL knowledge requirement would be highly dependable on the situation. If it's a larger organization then the requirement might be less because of the amount of peers that can help to teach, and the learning curve may not be as large of an issue. If the person is going in for a solo position than there isn't really room for a learning curve and the requirement would be much higher.

I think from a certification standpoint it would become difficult because there are so many ways to go about the answer as we had just seen. I'd like to pose the question of how do you determine what the 'correct' answer is? Should the proctor have the authority to review incorrect (not matching exactly) answers and deem them acceptable if they still accomplish the answer? What level of depth would the requirements go into; only cover T-SQL that can easily match daily functions, or cover in-depth uncommon tasks such as integration services.

The final question I have would be what would be the different levels of requirements (such as; a general requirements and a specialists requirements, like one specializing in development T-SQL, or specializing in Administration T-SQL), if so how do you then determine the levels?

I guess the different ways to make these determinations will continue to pose more and more questions, really the only foreseen method is to start off with a general requirements test and stem off that as the need is determined from our feedback of how the requirements are meeting our expectations.

Thanks,

James

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

Wagner Junior Newbie Points: 3 More actions · Answer 14

Nice list of questions Steve.

I would like to see the answers, (the link is broken), especially for the mission critical sales database. 🙂