question

indiver avatar image
indiver asked

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))
sql-serverhomework
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ Valentino Vranken .....sir i have made this query of the above problem 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))
0 Likes 0 ·
@indiver I know, my comment took your current attempt for a query already in consideration. I believe you're using that EXISTS to get the list of makers that produce printers. However, "select maker from printer" won't work because according to your description, the printer table does not contain maker. You'll need to combine the Product.type field with the Printer table somehow. Now it's up to you. As I said, simplify the assignment first by writing a query that produces the list of makers that make printers.
0 Likes 0 ·
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Valentino Vranken avatar image
Valentino Vranken answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SuhiniDeb avatar image
SuhiniDeb answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rohanrg avatar image
rohanrg answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rohanrg avatar image
rohanrg answered
select avg(pc.hd) avg from product pr full outer join pc pc on pr.model = pc.model where pr.type = 'PC' and pr.maker in (select maker from product where type = 'Printer') I found the result
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ankitguru avatar image
ankitguru answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Quazi avatar image
Quazi answered
select avg(hd) avg_hd from product full outer join pc on product.model = pc.model where type = 'PC' and maker in (select maker from product where type = 'Printer') Yes....Right Result
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.