Not able to solve this query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Not able to solve this query

Author

Topic

ntizer
Starting Member

6 Posts

Posted - 2011-12-30 : 01:48:01

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.

ntizer
Starting Member

6 Posts

Posted - 2012-01-05 : 03:51:43

thnx Neil..for ur suggestions.

I got the expected o/p but with a warning which has to be removed.
Error it shows as :
Your query produced correct result set on main database, but it failed test on second, checking database.
* Data mismatch (1)
if its a compatibility issue
den here is the data types of PC table.

Query:
Insert into PC
select p.model+MAX(pc.code) 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 p.type= 'pc'
and p.model not in (select model from pc)
group by p.model

Datatypes of attributes:-
code int
model varchar(50)
speed smallint
ram smallint
hd real
cd varchar(10)
price money

O/p
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources