SQL Experts

Discussion in 'OT Technology' started by 98R-1144, Apr 5, 2004.

  1. 98R-1144

    98R-1144 V8 4.2L AWD = All Weather Fun

    Joined:
    Mar 14, 2000
    Messages:
    889
    Likes Received:
    0
    Location:
    CA
    My work recently gave me a test to see how good my SQL was even though it's not part of my job description :squint: 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. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    You have an aggregate in the WHERE clause... I would assume the "average" price of a car is just the price listed since it's the same price for any number of in-stock models. Therefore, just use c.price > 25000 in the WHERE clause.

    edit:
    And if you can have more than one tuple for a Honda Civic like:

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

    then just used a nested select with the AVG aggregate in the select list.
     

Share This Page