On Thu, 7 Jan 2010, Thomas Van Parys wrote:
Op 07-01-10 16:12, Sebastian Proost schreef:
Kenny Billiau schreef:
Giving following schema:
Books: id, language, price, title
List all books with their price and the difference between the book's price and the averange price of all books of the same language. Use anything except a linked subquery.
Testing possible exam questions on us ?
1 Drop table temp 2 Create table temp with fields language, average 3 perform query that calculates the average per language and store in the table (INSERT INTO temp (language, average)SELECT language, AVG(price) FROM books GROUP BY language) 4 join books with that table
5 congratulate yourself as you've done exactly the same as what would have happened in memory when using a subquery but not half as efficient.
Too lazy too actually try it, but this could be close:
SELECT b.title, b.price, b.price - at.avg_prize FROM books b JOIN (SELECT lang, avg(price) as avg_prize FROM books GROUP BY lang) as at ON b.lang = at.lang
Seppe, your solution really is elaborate, but correct :) But I said 'without _linked_ subquery', which means you still use a normal subquery :) (linked subquery is when you use tables from your outer query in your subquery, which for this question just makes it easier on the mind to solve it. With linked subquery the result would be: SELECT Bo.title , Bo.price, Bo.price - ( SELECT avg(sub.price) FROM Books sub WHERE Bo.language = sub.language ) AS Difference FROM Books Bo; but this solution is _slow_. On the otherhand: Thomas is deadon. Anyway, if you people can solve it, so can my students (for their exam ;)) Kenny -- ================================================================== Kenny Billiau Web Developer Tel:+32 (0)9 331 36 95 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM kenny.billiau@ugent.be http://bioinformatics.psb.ugent.be ================================================================== "When we have each other, we have everything" - Douglas P.