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.
hh, Kenny
-- ================================================================== Sebastian Proost PhD Student Tel:+ 32 (0) 9 33 13 822 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM sebastian.proost@psb.vib-ugent.be http://www.psb.ugent.be ================================================================== "If I knew what I was doing, it wouldn't be called research." --Albert Einstein