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 -- ================================================================== Thomas Van Parys 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 thomas.vanparys@psb.vib-ugent.be http://bioinformatics.psb.ugent.be ==================================================================