pls Help me in solving this query

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

pls Help me in solving this query

Author

Topic

ntizer
Starting Member

6 Posts

Posted - 2012-01-03 : 01:00:19

Pls help me to solve this query.I am not able to solve this.

Database has 2 tables
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)

Add into the PC table all the models from the Product table that are absentfrom the PC table.
As this takes place, the inserted models must have the features:
1. The code should be equal to the model number plus maximal code value before insert operation.
2. Speed, RAM and HD capacities, and CD-speed should be of maximal values among all available corresponding values in the PC table.
3. The price should be an average among all the PCs before insert operation.

my query is partially correct bt m not able to do the code part which shows me null
here it goes..

Insert into PC(model,speed,ram,hd,cd,price)
Select p.model,max(pc.speed),max(pc.ram),max(pc.hd),max(pc.cd),avg(pc.price) from Product p,PC pc
where p.type='PC'
and p.model not in (Select model From PC)
group by p.model

Sachin.Nand

2937 Posts

Posted - 2012-01-03 : 01:28:33

Could you post some sample data and the expected o/p ?

After Monday and Tuesday even the calendar says W T F ....

ntizer
Starting Member

6 Posts

Posted - 2012-01-03 : 01:46:32

code model speed ram hd cd price
1 1232 500 64 5.0 12x 600.0000
2 1121 750 128 14.0 40x 850.0000
3 1233 500 64 5.0 12x 600.0000
4 1121 600 128 14.0 40x 850.0000
5 1121 600 128 8.0 40x 850.0000
6 1233 750 128 20.0 50x 950.0000
7 1232 500 32 10.0 12x 400.0000
8 1232 450 64 8.0 24x 350.0000
9 1232 450 32 10.0 24x 350.0000
10 1260 500 32 10.0 12x 350.0000
11 1233 900 128 40.0 40x 980.0000
2122 2111 900 128 40.0 50x 648.1818
2123 2112 900 128 40.0 50x 648.1818

sry dat printscreen is not done.last 2 rows of code column nt getting.
code
2122
2123 m not getting which shows null 4 me.

whatamouth
Starting Member

16 Posts

Posted - 2012-01-03 : 03:48:03

insert pc
select p.model+MAX(pc.code),
p.model,
MAX(pc.speed) speed,
MAX(pc.ram) ram,
MAX(pc.hd) hd,
MAX(pc.cd) cd,
AVG(pc.price) price
from product p, pc pc
where type= 'pc'
and p.model not in (select model from pc)
group by p.model

Neil Matias
neilmatias@yahoo.com

ntizer
Starting Member

6 Posts

Posted - 2012-01-04 : 00:05:25

thnx whatamouth for ur help..I got the desired o/p but with a warning:
Here it goes:
Your query produced correct result set on main database, but it failed test on second, checking database.
* Data mismatch (1)
So i cannot proceed further..ur help would be appreciated

whatamouth
Starting Member

16 Posts

Posted - 2012-01-04 : 02:30:02

Run the select first without the insert

select p.model+MAX(pc.code),
p.model,
MAX(pc.speed) speed,
MAX(pc.ram) ram,
MAX(pc.hd) hd,
MAX(pc.cd) cd,
AVG(pc.price) price
from product p, pc pc
where type= 'pc'
and p.model not in (select model from pc)
group by p.model

Then check the resulting rows if its compatible with the pc table's columns datatype.

Neil Matias
neilmatias@yahoo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources