answer for query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

answer for query

Author

Topic

uday1149
Starting Member

4 Posts

Posted - 2007-11-20 : 04:46:18

The database scheme consists of four relations:

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)

que:Find out the models and prices for all the products (of any type) produced by maker B.

plzzzzzzzzz hlp....

hitman
Starting Member

23 Posts

Posted - 2007-11-20 : 04:52:14

homework? :-)

-----------------------
http://www.sqltips.info
-----------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-20 : 04:56:00

Assuming model wont duplicate in each table:-

SELECT p.model,COALESCE(pc.price,l.price,pr.price)
FROM Product p
LEFT OUTER JOIN PC pc
ON pc.model=p.model
LEFT OUTER JOIN laptop l
ON l.model=p.model
LEFT OUTER JOIN Printer pr
ON pr.model=p.model
WHERE p.maker='makerB'

uday1149
Starting Member

4 Posts

Posted - 2007-11-20 : 06:00:19

thx...... its really help full for me

uday1149
Starting Member

4 Posts

Posted - 2007-11-20 : 06:42:45

The database scheme consists of four relations:

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)
----------------------

QUESTION::For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and
printer.
For each maker, this table must include "yes" if a maker has products of corresponding type or "no" otherwise.
In the first case (yes), specify in brackets (without spaces) the quantity of available distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 08:56:55

Have you tired this yourself? If so post what you have tried.
If not, shame on you ;)

George
<3Engaged!

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-20 : 08:59:32

“We have tried nothing and we are all out of ideas” (c) The Simpsons

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 12:52:46