Query

  • Comments posted to this topic are about the item Query

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • Interesting point on filters and sub selects, but I thought the question was a bit misleading in terms of the possible answers provided:

    - From a syntax point of view, I agree 'B' would the correct answer

    - From a results/holistic view, 'C' would be the answer as the query (using the attached example) yields incorrect results as there is no filter on the join and only Customer 1 is on pricing plan 'X', but results say both customers are on pricing plan 'X'?

  • For the sake of precision, I think the correct answer should have been formulated as

    Query will succeed if the customerid and pricingplan columns are available in any table used in the first From Clause.

  • Yup I'm with Marius, the results would not be what is intended and therefore the query is just plain wrong, which is different to a syntax error as offered in option 1. So in fact it should have been a select all that apply because indeed results are returned assuming suitable columns exist but they are the wrong results.

    K.

  • Syntactically correct, but it doesn't produce meaningful results. :hehe:I agree with the previous posts, it compiles and executes, but what is it trying to provide?

    Honor Super Omnia-
    Jason Miller

  • Please avoid posting these sort of misleading questions.

    select CategoryID, Quantity, Price

    from Customer c, SalesOrderDetail sod

    where sod.clientid in (select customerid where pricingplan='X')

    works even without customerid in any of the table but not the intended result.

    If any table has customerid. eg: customer table has customerid then alias name should be there otherwise gives ambigous column

    select CategoryID, Quantity, Price

    from Customer c, SalesOrderDetail sod

    where sod.clientid in (select c.customerid where pricingplan='X')

  • I think it's a valid question. Was clear enough to me anyway.

    It brings up the caveat of: Compiles and Runs does NOT mean Correct

    That's an important lesson for any developer to learn.

    - 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

  • I got the question wrong, which is OK, but I think choices such as "Query is wrong" sound too vague. Perhaps "Query throws an error" or the specific error or something like that. It's not a big difference, but I think it's a meaningful one.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • I got it wrong as well, which, unfortunately, seems to be happening a lot lately. My only comment is that I would prefer that database context be given something like;

    Given the tables Customer (customerid, pricingplan) and SalesOrderId(clientid, category) what is the result of the following query.

    I assumed the context was AdventureWorks and the query is wrong in that context.

  • OK, just plain and simple, I am going to give my 2 cents worth here. This question is pure BS. I mean has anyone here ever seen a query written like this? In my house(place of business) the person who tried to get this through me, would be ridiculed beyond belief. I mean really, did I get the question wrong, yep. Did I also think that the question was misleading, yep. Do I know a developer that would try to get this past me, nope. Lets get some real questions in here. First off I do not even let anyone write in ANSI 89 SQL. Plain and simple it is my way or the highway.

    DEX
    😀
    The more you help the business, the more they will help you...well sometimes anyway.

  • ezlikesundaymorning (5/14/2008)


    OK, just plain and simple, I am going to give my 2 cents worth here. This question is pure BS. I mean has anyone here ever seen a query written like this? In my house(place of business) the person who tried to get this through me, would be ridiculed beyond belief. I mean really, did I get the question wrong, yep. Did I also think that the question was misleading, yep. Do I know a developer that would try to get this past me, nope. Lets get some real questions in here. First off I do not even let anyone write in ANSI 89 SQL. Plain and simple it is my way or the highway.

    I can sympathize with your frustration, but there have been several good questions in a row, and I agree that this one was not as good. But there has been an effort to post better questions.

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Excellent. I guess that I can just sit here and bitch or start contributing myself when it all boils down to it. Thanks for the reply. I am just not interested in seeing things like this make it into the questions, in the last several weeks, there have been quite a few really good questions and some that stretched me out a little because I had not become familiar with that new functionality yet.

    DEX
    😀
    The more you help the business, the more they will help you...well sometimes anyway.

  • ezlikesundaymorning (5/14/2008)


    Excellent. I guess that I can just sit here and bitch or start contributing myself when it all boils down to it. Thanks for the reply. I am just not interested in seeing things like this make it into the questions, in the last several weeks, there have been quite a few really good questions and some that stretched me out a little because I had not become familiar with that new functionality yet.

    That's probably a good idea - you'd write better questions than I would, that's for sure.

    I think the questions of the day tend to have trouble when they try to be too tricky, or rely only on one obscure trick instead of teaching you something about SQL or about how you think about problems. Today's question was a little like that, in my opinion, although some others thought it was fine.

    Cheers,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • ezlikesundaymorning (5/14/2008)


    OK, just plain and simple, I am going to give my 2 cents worth here. This question is pure BS. I mean has anyone here ever seen a query written like this? In my house(place of business) the person who tried to get this through me, would be ridiculed beyond belief. I mean really, did I get the question wrong, yep. Did I also think that the question was misleading, yep. Do I know a developer that would try to get this past me, nope. Lets get some real questions in here. First off I do not even let anyone write in ANSI 89 SQL. Plain and simple it is my way or the highway.

    Scott,

    This is one of the points about the QOD, to show that while something may compile and run, it is not the best way to do it or do what was expected.

    Glad to know you have standards, but unfortunately not everyone has the same standards so someone may encounter this scenario in real life and may now know how to remedy it.

  • I thought it was a good question, even if I did get it wrong 🙂

    The Redneck DBA

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

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