What is expert SQL knowledge?

I do a lot of hiring for my company and a lot of the people I interview
say that they are experts at SQL queries, but when I give them something
simple just beyond the typical SELECT type of queries, they choke.

For example I have a table that looks like this:

PK_ID - primary key
PARENT_ID - a FK to another row in the same table

This essentially is a tree structure. I will ask interviewees to write
a query that will return all the rows that have 2 direct children. No
one knows how to do this.

I don't get it. I have done queries which boggle the mind and they are
far more complex than this.

Am I asking too much?


--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

re: What is expert SQL knowledge?

I don't know what lever programmer you are interviewing for but I
have a BC in IS and have been working in the field for about 4yr

My answer would be yes and no

No, it is a simple query

Select PK_ID
from <table name>
where PK_ID in
(
select Parent_ID from <table name> group by Parent_ID having count(*)
= 2
)

Yes, if I was nervous in a job interview I would probably fail the test

But I'm sure your not basing your decision solely on this criteria.
Good luck
Rick

re: What is expert SQL knowledge?

For interviews, I personally like written tests. I think it is a more
objective measure of sql mastery.

???
select c.*
from tbl c
join
(
select a.pk_id
from tbl a
join tbl b
on a.parent_id = b.pk_id
group by a.pk_id
having count(*)=2
) d
on c.pk_id = d.pk_id

re: What is expert SQL knowledge?

I am not not basing my decision only on this. As long as they show an
effort and are very close to solving it, then I would consider that good.

But I have had programmers telling me they were experts, yet never think
of using subqueries or aggregate functions. Yet, do great in other
parts of the written test which are not SQL based.

I even had one programmer who over-programmed his query.


--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

re: What is expert SQL knowledge?

I would never claim to be an expert, and it only took me a few minutes
to figure it out. So yes, if that's the case I would bust there
balls. I have come across several cocky programmers that think they
know it all. It has been my experience that programmers that can admit
that they don't know everything but are still willing to learn
typically are
easier to work with.

re: What is expert SQL knowledge?

You may not consider yourself an expert, nor do I, but I'm sure you
would be well equipped to handle most complex queries. And understand
enough to know what you need to research to complete your task.

I've managed to take fairly complex queries that involved subqueries and
converted them to be used on MySQL where subqueries were not supported
not long ago.

--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

re: What is expert SQL knowledge?

Don Vaillancourt wrote:[color=blue]
> I do a lot of hiring for my company and a lot of the people I interview
> say that they are experts at SQL queries, but when I give them something
> simple just beyond the typical SELECT type of queries, they choke.
>
> For example I have a table that looks like this:
>
> PK_ID - primary key
> PARENT_ID - a FK to another row in the same table
>
> This essentially is a tree structure. I will ask interviewees to write
> a query that will return all the rows that have 2 direct children. No
> one knows how to do this.
>
> I don't get it. I have done queries which boggle the mind and they are
> far more complex than this.
>
> Am I asking too much?
>
>[/color]

First, I don't throw the term "expert" around lightly. Usually when
people call themselves experts they don't realize that they don't know
enough about the subject matter to know they aren't an expert. ;)

As to your specific point. I've been working in SQL for many, many years
(back from 6.0) but in all my years have never had to deal with a
hierarchy type query. I'd likely fail your test. However, I can work out
just about every query I need to, know where to go when I can't and am
probably more well versed in "proper" MS SQL programming than most
people that call themselves experts. I don't consider myself an expert.

Just my .02

Zach

re: What is expert SQL knowledge?

The term 'expert' as you know is difficult to justify. It's far easier
to look at qualifications and experience, but I've seen some very
experienced people make the simplest of mistakes. I've spent a few
years re-working a previous developers work as it was very poor and yet
he probably has more experience than me. Would I call myself an expert
? Probably not. I'd leave that to my colleagues to decide. Have I got
anything seriously wrong ? Never. Would I take on any challenge ? Yes,
absolutely. However, when I talk to the people on the forum, I realise
that there is still a lot to learn and understand.

By the way, your question was very simple. I generally work with
complex queries with a lot of tables so I personally would find this
very easy. I do, however, come back here for advice from time to time
just for a little guidance as it's always good to make sure you are on
the right track.

Ryan

p.s. MVP is always a good indicator :-)

Don Vaillancourt wrote:[color=blue]
> I do a lot of hiring for my company and a lot of the people I[/color]
interview[color=blue]
> say that they are experts at SQL queries, but when I give them[/color]
something[color=blue]
> simple just beyond the typical SELECT type of queries, they choke.
>
> For example I have a table that looks like this:
>
> PK_ID - primary key
> PARENT_ID - a FK to another row in the same table
>
> This essentially is a tree structure. I will ask interviewees to[/color]
write[color=blue]
> a query that will return all the rows that have 2 direct children.[/color]
No[color=blue]
> one knows how to do this.
>
> I don't get it. I have done queries which boggle the mind and they[/color]
are[color=blue]
> far more complex than this.
>
> Am I asking too much?
>
>
> --
> * Don Vaillancourt
> Director of Software Development
> *
> *WEB IMPACT INC.*
> phone: 416-815-2000 ext. 245
> fax: 416-815-2001
> email: donv@web-impact.com <mailto:donv@webimpact.com>
> web: http://www.web-impact.com
>
>
>
> / This email message is intended only for the addressee(s)
> and contains information that may be confidential and/or
> copyright. If you are not the intended recipient please
> notify the sender by reply email and immediately delete
> this email. Use, disclosure or reproduction of this email
> by anyone other than the intended recipient(s) is strictly
> prohibited. No representation is made that this email or
> any attachments are free of viruses. Virus scanning is
> recommended and is the responsibility of the recipient.
> /[/color]

re: What is expert SQL knowledge?

Actually, that's a good point. Maybe I should provide another kind of
test which is still as complex, but based on a different hierarchy.

nib wrote:
[color=blue]
> As to your specific point. I've been working in SQL for many, many years
> (back from 6.0) but in all my years have never had to deal with a
> hierarchy type query. I'd likely fail your test.[/color]


--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

re: What is expert SQL knowledge?

Sorry, what's MVP?


--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

re: What is expert SQL knowledge?

http://www.microsoft.com/communities/mvp/mvp.mspx
or
http://mvp.support.microsoft.com/

Most Valuable Professional award from Microsoft. Quite a number of
people on here have been awarded the MVP status.
Don Vaillancourt wrote:[color=blue]
> Sorry, what's MVP?[/color]

re: What is expert SQL knowledge?

BTW, This one girl working for us now made a small mistake on her query.

I had her wait in the meeting room while I went to my computer to test
her query. It didn't work right off the bat, but after a small change,
which I'm sure she would have resolved, the query ran perfectly. She
was hired on the spot.

So if the potential is there

--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

re: What is expert SQL knowledge?

Another approach is to ask them to bring their own code samples and
have them explain their reasoning. Or present them with your code,
have them explain your reasoning and fix a bug.

I have had the problem of getting my own SQL puzzles presented to me
for interviews back when I was writting in DBMS and DBP&D. I also
have the problem of "over-answering" a question, based on ANSI/ISO
standards and all the SQL dialects I know.

re: What is expert SQL knowledge?

Eh Joe,

Nice to hear from you. You solved one of my biggest problems a few
years ago.

As to your ideas. Makes sense. Never thought of that. I will
certainly try that.

Thank You


--CELKO-- wrote:[color=blue]
> Another approach is to ask them to bring their own code samples and
> have them explain their reasoning. Or present them with your code,
> have them explain your reasoning and fix a bug.
>
> I have had the problem of getting my own SQL puzzles presented to me
> for interviews back when I was writting in DBMS and DBP&D. I also
> have the problem of "over-answering" a question, based on ANSI/ISO
> standards and all the SQL dialects I know.
>[/color]


--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email: donv@web-impact.com <mailto:donv@webimpact.com>
web: http://www.web-impact.com



/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/

re: What is expert SQL knowledge?

--CELKO-- wrote:
[color=blue]
> Another approach is to ask them to bring their own code samples and
> have them explain their reasoning. Or present them with your code,
> have them explain your reasoning and fix a bug.
>
> I have had the problem of getting my own SQL puzzles presented to me
> for interviews back when I was writting in DBMS and DBP&D. I also
> have the problem of "over-answering" a question, based on ANSI/ISO
> standards and all the SQL dialects I know.[/color]

I had someone bring me "their own code samples" last year when I was
hiring a developer. After he left I was forced by professional ethics
to call the bank it had previously worked for and inform them that he
was running around distributing their source code with every interview.
So my advice to developers and DBAs is to NEVER bring code unless they
are absolutely sure it won't cost them the job. Think it through
clearly. Do you think I would have wanted what was developed on my
project used as an entree into the developer's next round of interviews?

I often bring bad code to interviews and ask people what's wrong with
it. You learn as much from what is not said as from what is said.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

re: What is expert SQL knowledge?

"Don Vaillancourt" <donv@webimpact.com> wrote in message
news:TkaFd.88326$vO1.526644@nnrp1.uunet.ca...[color=blue]
> Actually, that's a good point. Maybe I should provide another kind of
> test which is still as complex, but based on a different hierarchy.
>
> nib wrote:
>[color=green]
>> As to your specific point. I've been working in SQL for many, many years
>> (back from 6.0) but in all my years have never had to deal with a
>> hierarchy type query. I'd likely fail your test.[/color][/color]

I've a fair bit of sql development experience.
I'd likely fail that test.
Can't recall ever having to write a query like that.
Mind you.
Been a while since I've worked on bill of material stuff.
Last time I was using cobol rather than sql.
Usually though, you have to check the number of levels you can go down and
such a BOM would be one of those one off tasks where you lock yourself in a
quiet room for a long think.

I also have some experience interviewing people.
The guy who's overconfident ( and nothing rattles him ) in an interview is
usually the sort is cocky and abrasive.
I don't want them working in my team.
The one falls to pieces is probably not going to be able to deal with
problems.
The one I want is the bloke who is going to be a bit nervous but not
gibbering.

I don't like written questions.

Anyhow, FWIW.
The way I test people is to ask them several open questions and just see
what they come out with.
You can tell a lot about the way they think and the way they work.
One is how they go about programmatically adding a record.
Believe it or not, I was sat next to a fellow contractor actually asked me
this.
If they can't answer that coherently then they're off to a pretty bad start.
On the face of it that's possibly a rather easy one, isn't it?
Although it's an easy question it's very open and puts a lot of pressure on
people.
But do they talk about stored procedures, build sql strings or what?

The others depend on the language we're using.