SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 i need help with sql exercise.. pls help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paulpol87
Starting Member

7 Posts

Posted - 02/06/2012 :  11:20:42  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 02/06/2012 :  11:31:40  Show Profile  Reply with Quote

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


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

Go to Top of Page

paulpol87
Starting Member

7 Posts

Posted - 02/06/2012 :  11:36:09  Show Profile  Reply with Quote
this is what i was trying to find:
WHERE type in ('PC','printer')
HAVING COUNT(DISTINCT type)=2

thanks a lot visakh16!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/06/2012 :  11:38:04  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000