SQL QUERY

[Brian21]

How are you doing lads, i have a question here from an
assingment i have to do. Ive been wrecking my head for some time now, does anyone have any suggestions?


HERE ARE THE TABLES:

customer(custcode,customername,address1,address2,town,age,gender)
custcode is primary key
shop(shopcode,shopname,shoptype,town)
shopcode is primary key
purchase(custcode,shopcode,date,itemcode,cost)
custcode,shopcode,date,itemcode is composite primary key
item(itemcode,itemcategory,itemname)
itemcode is primary key, itemname is unique
shopstock(shopcode,itemcode,qtyinstock,price)
shopcode,itemcode is composite primary key


AND HERE IS THE QUESTION

Q11. List the names of the shops having the largest and smallest amounts of money tied up in stock as well as the respective amounts.

 

[xrodge]

Ah, students.

Well we all cheated at one time or another when we were at school or uni...

Try this:

[tt]
select a.shopname,
sum(b.qtyinstock*b.price) money_in_stock
from shop a,
shopstock b
where a.shopcode = b.shopcode
and ((sum(b.qtyinstock*b.price) =
max(sum(b.qtyinstock*b.price)))
OR
(sum(b.qtyinstock*b.price) =
min(sum(b.qtyinstock*b.price))))
group by a.shopname;
[/tt]

That should do it.
Probably a typo in there and you may need to count out the brackets.

Is this what you wanted?