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
 Exercise: 7

Author  Topic 

fullypaglot
Starting Member

6 Posts

Posted - 2013-08-09 : 07:39:53
Find out the models and prices for all the products (of any type) produced by maker B.

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)

select product.type,
pc.price as pcprice,
laptop.price lapprice,
pc.model as pcmod,
laptop.model as lapmod
from product
join
pc on product.model=pc.model
join
laptop on laptop.model=product.model
where maker = 'B'

the syntex runs but its not displaying any results + I know that I have some extra columns there but its for some thing else I was trying

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-09 : 07:45:56
i'm assuming type field will hold values as PC ,etc
so you've to do something like this
i dont understand why you want separate tables for each types as attributes seems to be similar
i would have kept them in same table with additional column as Type

select *
from Product p
inner join (select model,price,'PC' AS type
from PC
UNION ALL
select model,price,'Laptop' AS type
from Laptop
union all
select model,price,'Printer' AS type
from Printer
)t
ON t.type = p.type




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

fullypaglot
Starting Member

6 Posts

Posted - 2013-08-09 : 08:28:06
i tried it worked but I am getting strange result. I need some time to analyze how did you wrote it but thank you very much for your time and efforts. Maybe I can tweek it a little bit if I can understand whats going on here, but I would like to thank you again for every thing.

maker model type model price type
A 1232 PC 1121 850.0000 PC
A 1232 PC 1121 850.0000 PC
A 1232 PC 1121 850.0000 PC
A 1232 PC 1232 350.0000 PC
A 1232 PC 1232 350.0000 PC
A 1232 PC 1232 400.0000 PC
A 1232 PC 1232 600.0000 PC
A 1232 PC 1233 600.0000 PC
A 1232 PC 1233 950.0000 PC
A 1232 PC 1233 970.0000 PC
A 1232 PC 1233 980.0000 PC
A 1232 PC 1260 350.0000 PC
A 1233 PC 1121 850.0000 PC
A 1233 PC 1121 850.0000 PC
A 1233 PC 1121 850.0000 PC
A 1233 PC 1232 350.0000 PC
A 1233 PC 1232 350.0000 PC
A 1233 PC 1232 400.0000 PC
A 1233 PC 1232 600.0000 PC
A 1233 PC 1233 600.0000 PC
A 1233 PC 1233 950.0000 PC
A 1233 PC 1233 970.0000 PC
A 1233 PC 1233 980.0000 PC
A 1233 PC 1260 350.0000 PC
A 1276 Printer 1276 400.0000 Printer
A 1276 Printer 1288 400.0000 Printer
A 1276 Printer 1401 150.0000 Printer
A 1276 Printer 1408 270.0000 Printer
A 1276 Printer 1433 270.0000 Printer
A 1276 Printer 1434 290.0000 Printer
A 1298 Laptop 1298 1050.0000 Laptop
A 1298 Laptop 1298 700.0000 Laptop
A 1298 Laptop 1298 950.0000 Laptop
A 1298 Laptop 1321 970.0000 Laptop
A 1298 Laptop 1750 1200.0000 Laptop
A 1298 Laptop 1752 1150.0000 Laptop
A 1401 Printer 1276 400.0000 Printer
A 1401 Printer 1288 400.0000 Printer
A 1401 Printer 1401 150.0000 Printer
A 1401 Printer 1408 270.0000 Printer
A 1401 Printer 1433 270.0000 Printer
A 1401 Printer 1434 290.0000 Printer
A 1408 Printer 1276 400.0000 Printer
A 1408 Printer 1288 400.0000 Printer
A 1408 Printer 1401 150.0000 Printer
A 1408 Printer 1408 270.0000 Printer
A 1408 Printer 1433 270.0000 Printer
A 1408 Printer 1434 290.0000 Printer
A 1752 Laptop 1298 1050.0000 Laptop
A 1752 Laptop 1298 700.0000 Laptop
A 1752 Laptop 1298 950.0000 Laptop
A 1752 Laptop 1321 970.0000 Laptop
A 1752 Laptop 1750 1200.0000 Laptop
A 1752 Laptop 1752 1150.0000 Laptop
B 1121 PC 1121 850.0000 PC
B 1121 PC 1121 850.0000 PC
B 1121 PC 1121 850.0000 PC
B 1121 PC 1232 350.0000 PC
B 1121 PC 1232 350.0000 PC
B 1121 PC 1232 400.0000 PC
B 1121 PC 1232 600.0000 PC
B 1121 PC 1233 600.0000 PC
B 1121 PC 1233 950.0000 PC
B 1121 PC 1233 970.0000 PC
B 1121 PC 1233 980.0000 PC
B 1121 PC 1260 350.0000 PC
B 1750 Laptop 1298 1050.0000 Laptop
B 1750 Laptop 1298 700.0000 Laptop
B 1750 Laptop 1298 950.0000 Laptop
B 1750 Laptop 1321 970.0000 Laptop
B 1750 Laptop 1750 1200.0000 Laptop
B 1750 Laptop 1752 1150.0000 Laptop
C 1321 Laptop 1298 1050.0000 Laptop
C 1321 Laptop 1298 700.0000 Laptop
C 1321 Laptop 1298 950.0000 Laptop
C 1321 Laptop 1321 970.0000 Laptop
C 1321 Laptop 1750 1200.0000 Laptop
C 1321 Laptop 1752 1150.0000 Laptop
D 1288 Printer 1276 400.0000 Printer
D 1288 Printer 1288 400.0000 Printer
D 1288 Printer 1401 150.0000 Printer
D 1288 Printer 1408 270.0000 Printer
D 1288 Printer 1433 270.0000 Printer
D 1288 Printer 1434 290.0000 Printer
D 1433 Printer 1276 400.0000 Printer
D 1433 Printer 1288 400.0000 Printer
D 1433 Printer 1401 150.0000 Printer
D 1433 Printer 1408 270.0000 Printer
D 1433 Printer 1433 270.0000 Printer
D 1433 Printer 1434 290.0000 Printer
E 1260 PC 1121 850.0000 PC
E 1260 PC 1121 850.0000 PC
E 1260 PC 1121 850.0000 PC
E 1260 PC 1232 350.0000 PC
E 1260 PC 1232 350.0000 PC
E 1260 PC 1232 400.0000 PC
E 1260 PC 1232 600.0000 PC
E 1260 PC 1233 600.0000 PC
E 1260 PC 1233 950.0000 PC
E 1260 PC 1233 970.0000 PC
E 1260 PC 1233 980.0000 PC
E 1260 PC 1260 350.0000 PC
E 1434 Printer 1276 400.0000 Printer
E 1434 Printer 1288 400.0000 Printer
E 1434 Printer 1401 150.0000 Printer
E 1434 Printer 1408 270.0000 Printer
E 1434 Printer 1433 270.0000 Printer
E 1434 Printer 1434 290.0000 Printer
E 2112 PC 1121 850.0000 PC
E 2112 PC 1121 850.0000 PC
E 2112 PC 1121 850.0000 PC
E 2112 PC 1232 350.0000 PC
E 2112 PC 1232 350.0000 PC
E 2112 PC 1232 400.0000 PC
E 2112 PC 1232 600.0000 PC
E 2112 PC 1233 600.0000 PC
E 2112 PC 1233 950.0000 PC
E 2112 PC 1233 970.0000 PC
E 2112 PC 1233 980.0000 PC
E 2112 PC 1260 350.0000 PC
E 2113 PC 1121 850.0000 PC
E 2113 PC 1121 850.0000 PC
E 2113 PC 1121 850.0000 PC
E 2113 PC 1232 350.0000 PC
E 2113 PC 1232 350.0000 PC
E 2113 PC 1232 400.0000 PC
E 2113 PC 1232 600.0000 PC
E 2113 PC 1233 600.0000 PC
E 2113 PC 1233 950.0000 PC
E 2113 PC 1233 970.0000 PC
E 2113 PC 1233 980.0000 PC
E 2113 PC 1260 350.0000 PC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-16 : 14:13:49
and what should be the sample output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -