Define the average HD size (single value for all makers) of PCs produced by those makers that also produce printers. Result set: average size of HD
Short database description "Computer firm" The database scheme consists of four tables: Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price) Laptop(code, model, speed, ram, hd, screen, price) Printer(code, model, color, type, price) The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price Define the average HD size (single value for all makers) of PCs produced by those makers that also produce printers. Result set: average size of HD i have made this query....but this is wrong....what should be the query of this... select avg(hd) from pc join product on pc.model=product.model where maker in(select maker from product as p where type='pc' and exists (select maker from printer where maker =p.maker))
Based on the following requirement: *"Define the average HD size (single value for all makers) of PCs produced by those makers that also produce printers."* I'd start with writing a query that selects all the makers that produce printers. So, write that query first and post it here so we can check if you're on the right track. When you've got that part of the requirement solved, we can move on to the next part.
Select avg(pc.hd) from product a inner join pc on a.model=pc.model, product b inner join printer c on b.model=c.model where a.maker=b.maker Sir, I have used the above query. but it does give the correct result. pls help
Select avg(av.size) as Total from ( select p.maker,avg(hd) as size from Product as p join PC as pc on p.model=pc.model and p.maker in (select maker from Product where type='Printer') group by p.maker )as av Hi, I am an SQL novice and have produced the above query but I still get the wrong answer MY Answer:12.375 Correct Answer:14.222222222222221
The correct query are these You can choose any one of these. 1. SELECT p.maker, AVG(PC.hd * 1.0) avg_hd FROM Product p INNER JOIN (SELECT maker FROM Product WHERE type in ('PC','printer') GROUP BY maker HAVING COUNT(DISTINCT type)=2 )p1 ON p1.maker = p.maker INNER JOIN PC ON PC.model = p.model GROUP BY p.maker 2. select maker, avg(h.hd) avg_hd from PC h, Product p where p.model=h.model and exists (select * from Product p1 where p1.maker=p.maker and p1.type ='Printer ') group by maker Both are correct,result is same: maker avg_hd A 14.75 E 10.0