Results 1 to 6 of 6

Thread: SQL Query Help

  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: SQL Query Help

    My work recently gave me a test to see how good my SQL was even though it's not part of my job description I did fairly well but missed one question. I'm pretty disappointed I missed it because it seemed easy enough. Unfortunately they didn't tell me what the correct answer was... so hopefully someone here can tell me (or at least tell me whats wrong with my query):

    2 tables:
    Car

    ID Make Model Price InStock
    1 Honda Civic 15000 5
    2 Ferrari Testarossa 100000 2
    ...

    Buyers

    ID Name City Gender CarID
    1 Bob San Jose M 1
    2 Linda Los Angeles F 2
    ...

    For every city and gender combination, find the average price of a car bought where the average price of the car is over $25,000.

    My Answer:

    SELECT b.city, b.gender, avg(c.price)
    FROM cars c, buyers b
    where c.id = b.carid AND avg(c.price) > 25000
    GROUP BY b.city, b.gender;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This really smells like homework, but:

    In SQL, aggregate function values are computed after the rollup, the WHERE clause is executed before the rollup. You need to move the condition to the HAVING clause.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    3

    Post

    haha i wish it was homework. i'm a senior sqa engineer

    as for your answer, i really have no idea what you're talking about =/
    i haven't taken sql since my oracle classes way back in college.

    i'm assuming:

    SELECT b.city, b.gender, avg(c.price)
    FROM cars c, buyers b
    where c.id = b.carid
    GROUP BY b.city, b.gender
    HAVING avg(c.price) > 25000;

    ?

    thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You at least read fluently anyway! That makes me a lot more comfortable that you aren't just another leech looking for someone to do their homework for them.

    Yes, that is exactly what I meant. I can elaborate a lot more if you want, but you've already gotten the answer you originally asked for.

    -PatP

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by Pat Phelan
    You at least read fluently anyway! That makes me a lot more comfortable that you aren't just another leech looking for someone to do their homework for them.

    Yes, that is exactly what I meant. I can elaborate a lot more if you want, but you've already gotten the answer you originally asked for.

    -PatP
    no elaborations necessary. it just irked me that i missed something so simple. i totally forgot about the having clause.
    i agree on the leeching thing while i don't specifically deal w/sql, i do use it now and then to set up test cases i need for particular results. still have no idea why management gave me this test...
    thanks again!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It smells like they might be considering you for an upgrade of some sort, possibly requiring some additional training... That or maybe they are getting worried that the QA types are getting dangerous!

    Anywho, always glad to spread confusion.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •