Just a practice problem, little bit confusing I'd appreciate it if somebody could clear this up..
I have a DB called Product and another one called PC. I'm trying to execute a query to "find the average hard disk size of a PC for all those manufacturers who also make printers"
Here's my code:
SELECT maker, AVG(hd)
FROM pc, product
GROUP BY maker
Product(maker, model, type)
PC(model, hd, speed, ram, price)
Printer(model, color, type, price)
Try the following query:
SELECT maker, AVG(hd) FROM PC, Product WHERE PC.model=Product.model AND Product.maker IN (SELECT DISTINCT maker FROM Product WHERE type='PR') GROUP BY Product.maker;
You simply add a condition to make sure that the maker is one of the makers that have at least one printer product. You then group by the maker to find the individual averages.