What is expert SQL knowledge?

[Don Vaillancourt]

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: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.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.
/

[rick]

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

[louis]

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

[Don Vaillancourt]

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: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.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.
/

[rick]

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.

[Don Vaillancourt]

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: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.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.
/

[nib]

Don Vaillancourt wrote:

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?

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

[Ryan]

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:

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: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.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.
/

[Don Vaillancourt]

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:

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.

--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.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.
/

[Don Vaillancourt]

Sorry, what's MVP?
--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: [phone] ext. 245
fax: [phone]
email: do**@web-impact.com <mailto:do**@we bimpact.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.
/