February 6, 2009 at 4:44 am
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)
The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "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.
Please give me query fro following =
Find out the models and prices for all the products (of any type) produced by maker B.
February 6, 2009 at 4:54 am
What have you tried so far?
Please read the link below, it will show you how to create sample data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 6, 2009 at 5:05 am
i tried with joins but it didnt work as price column was ambiguous as i was willing to populate prices for all
February 6, 2009 at 5:11 am
Nikhil (2/6/2009)
i tried with joins but it didnt work as price column was ambiguous as i was willing to populate prices for all
Please read the link below. People will be more likely to help you with your assignment if you post code to create the tables and populate them with some sample data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 6, 2009 at 6:25 am
>Find out the models and prices for all the products (of any type) produced by maker B.<
SELECT P.model, PC.price
FROM Product AS P
INNER JOIN PC
ON P.model = PC.model
WHERE P.maker = 'B'
UNION ALL
SELECT P.model, L.price
FROM Product AS P
INNER JOIN Laptop AS L
ON P.model = L.model
WHERE P.maker = 'B'
UNION ALL
SELECT P.model, R.price
FROM Product AS P
INNER JOIN Printer AS R
ON P.model = R.model
WHERE P.maker = 'B'
However your database design looks screwed up. A Laptop is a sort of Computer, the same as PCs are. I would include them in a table "Computers" and differentiate them by a computer_type column. Further you allow a printer model_code to be entered into the PC and the Laptop table and vice versa. Mixing different things into a vague "product" table does not sound right.
brgds
Philipp Post
brgds
Philipp Post
February 6, 2009 at 6:41 am
Nikhil (2/6/2009)
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)
The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "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.
Please give me query fro following =
Find out the models and prices for all the products (of any type) produced by maker B.
You would get much better assistance if you read and follow the guidelines given in the article that Chris Morris suggested you read. You will also find a link to the same article in my signature block. It is the first one regarding asking for assistance.
February 8, 2009 at 12:37 pm
[font="Verdana"]Judging by the change in wording in the descriptive section, this looks to me to be some sort of assignment or exam question.[/font]
February 8, 2009 at 11:21 pm
thanks all...
yes this is d Q. from one vague client...
i almost beacame bald after thinking .....
February 8, 2009 at 11:25 pm
thanks philip ur query worked
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply