By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,474 Members | 966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,474 IT Pros & Developers. It's quick & easy.

Multiple table query

P: 2
Hi guys ,

I am quite a baby to sql , pls help me out in this .
My 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)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "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.
The realtion b/w Product and PC/Laptop/Printer is that they share the model.

I want a query that would be :
Find out the models and prices for all the products (of any type) produced by maker B

I could make a quesry that would return all the PC's producedby maker B ie
select distinct PC.model,price from PC inner join Product on (PC.model=P
roduct.model and Product.maker='B');

But , cant figure out , how to query PC , Laptop and Printer table at one go

Your help would be much appreciated
Thanks
Oct 8 '07 #1
Share this Question
Share on Google+
3 Replies


P: 46
Hi guys ,

I am quite a baby to sql , pls help me out in this .
My 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)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "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.
The realtion b/w Product and PC/Laptop/Printer is that they share the model.

I want a query that would be :
Find out the models and prices for all the products (of any type) produced by maker B

I could make a quesry that would return all the PC's producedby maker B ie
select distinct PC.model,price from PC inner join Product on (PC.model=P
roduct.model and Product.maker='B');

But , cant figure out , how to query PC , Laptop and Printer table at one go

Your help would be much appreciated
Thanks

Expand|Select|Wrap|Line Numbers
  1. (SELECT DISTINCT pc.model, pc.price
  2. FROM pc INNER JOIN product ON (pc.model = product.model AND product.maker = 'B'))
  3. UNION
  4. (SELECT DISTINCT laptop.model, laptop.price
  5. FROM laptop INNER JOIN product ON (laptop.model = prouct.model AND laptop.maker = 'B'))
  6. UNION
  7. (SELECT DISTINCT printer.model, printer.price
  8. FROM printer INNER JOIN product ON (printer.model = product.model AND printer.maker = 'B'))
  9.  
Is that what you're looking for?
Oct 8 '07 #2

P: 1
Hi guys ,

I am quite a baby to sql , pls help me out in this .
My 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)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "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.
The realtion b/w Product and PC/Laptop/Printer is that they share the model.

I want a query that would be :
Find out the models and prices for all the products (of any type) produced by maker B

I could make a quesry that would return all the PC's producedby maker B ie
select distinct PC.model,price from PC inner join Product on (PC.model=P
roduct.model and Product.maker='B');

But , cant figure out , how to query PC , Laptop and Printer table at one go

Your help would be much appreciated
Thanks

Hi,

Please try this code it will work


SELECT DISTINCT PC.model, PC.price
FROM PC,Product WHERE PC.model=Product.model AND Product.maker='B'
UNION
SELECT DISTINCT l.model, l.price
FROM Laptop l,Product WHERE l.model=Product.model AND Product.maker='B'
UNION
SELECT DISTINCT p.model, p.price
FROM Printer p,Product WHERE p.model=Product.model AND Product.maker='B'
Oct 15 '07 #3

P: 1
This is a problem from a text book for a database class. This work is given as an assignment. You learn nothing by having other developers do it for you.
Oct 23 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.