i need help with sql exercise.. pls help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

i need help with sql exercise.. pls help!

Author

Topic

paulpol87
Starting Member

7 Posts

Posted - 2012-02-06 : 11:20:42

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)

Exercise: Define the average size of the PC hard drive for each maker that also produces printers.
Result set: maker, average capacity of HD.

My query is:
select maker, avg(hd) as avg_hd from product join pc on product.model=pc.model where maker in (select maker from product where model in (select model from printer)) group by maker

Result:
Incorrect.
Your query produced correct result set on main database, but it failed test on second, checking database.
* Wrong number of records (less by 1)

The result of Your query:
maker avg_hd
A 14.75
E 10.0

I need the query in SQL, not PL/SQL
Thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 11:31:40

[code]
SELECT p.maker,
AVG(PC.hd * 1.0)
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paulpol87
Starting Member

7 Posts

Posted - 2012-02-06 : 11:36:09

this is what i was trying to find:
WHERE type in ('PC','printer')
HAVING COUNT(DISTINCT type)=2

thanks a lot visakh16!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 11:38:04

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources