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;

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

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

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!

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