SQL q2uery help needed

[binita2908]

Hi all ,

I am using MySql5.0
My database schema 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

Here model (integer) in Product will be existing as the same model under PC,Laptop and Printer

I want to make a query like :
Find out the models and prices for all the products (of any type) produced by maker B.
Result set shud only contain 2 columns , viz : model,price

I am quite a baby in sql , your help would be appreciated.

Thanks

[r035198x]

Hi all ,

I am using MySql5.0
My database schema 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

Here model (integer) in Product will be existing as the same model under PC,Laptop and Printer

I want to make a query like :
Find out the models and prices for all the products (of any type) produced by maker B.
Result set shud only contain 2 columns , viz : model,price

I am quite a baby in sql , your help would be appreciated.

Thanks

Well you posted this in the wrong forum.
Moved to the MySQL forum.

[code green]

The query is possible but rather than banging your head attempting to write it, I would set about redesigning you database.
What you have here is a flat file design and a complete waste of database resources.
Think about something like this.

Product    (code, maker, model, type, price)
Common_Computer(code, speed, ram, hd, cd)
Laptop    (code,screen)
Printer    (code,color)

Then further normalisation with maker replaced by man_id and a manufacturer_id table,
and type replaced by type_id and a type table.
The query you require then becomes very simple.

[amitpatel66]

Hi all ,

I am using MySql5.0
My database schema 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

Here model (integer) in Product will be existing as the same model under PC,Laptop and Printer

I want to make a query like :
Find out the models and prices for all the products (of any type) produced by maker B.
Result set shud only contain 2 columns , viz : model,price

I am quite a baby in sql , your help would be appreciated.

Thanks

Try below Query:

SELECT model,price FROM PC WHERE model IN(SELECT model from product WHERE maker = 'B' AND type = 'PC')
UNION
SELECT model,price FROM LAPTOP WHERE model IN(SELECT model from product WHERE maker = 'B' AND type = 'LAPTOP')
UNION
SELECT model,price FROM PRINTER WHERE model IN(SELECT model from product WHERE maker = 'B' AND type = 'PRINTER')