Update the salary of each manager to be double the average salary of the employees he/she manages

23 Mar 2006 10:23 AM

satish

create table employee(empid int,empname varchar(20),managerid int not
null, sal int)

insert into employee values(1,'ranga',22,5000)
insert into employee values(2,'satish',22,8000)
insert into employee values(3,'sunil',11,4500)
insert into employee values(4,'sridhar',22,2000)
insert into employee values(5,'ramesh',33,12000)
insert into employee values(6,'srini',22,16000)
insert into employee values(7,'sashi',33,54000)
insert into employee values(8,'rajani',22,71000)
insert into employee values(9,'praveen',11,6060)
insert into employee values(10,'bhaskar',22,11120)
insert into employee values(11,'baba',33,9000)

create table employment (managerid int,managername varchar(20),sal int)

insert into employment values(11,'rob',2500)
insert into employment values(22,'babu',5000)
insert into employment values(33,'ram',6000)

now my problem is

Update the salary of each manager to be double the average
salary of the employees he/she manages with out using joins

there are no constrainsts described on any column

pls help

sati

23 Mar 2006 10:46 AM

Uri Dimant

satish
See if this helps

update employment set sal =(select avg(sal)*2 from
employee e where e.managerid=employment.managerid)
where exists (select * from employee e where
e.managerid=employment.managerid)

Show quote

"satish" <satishkumar.gourabath***@gmail.com> wrote in message
news:1143109396.368606.225130@u72g2000cwu.googlegroups.com...
> create table employee(empid int,empname varchar(20),managerid int not
> null, sal int)
>
>
> insert into employee values(1,'ranga',22,5000)
> insert into employee values(2,'satish',22,8000)
> insert into employee values(3,'sunil',11,4500)
> insert into employee values(4,'sridhar',22,2000)
> insert into employee values(5,'ramesh',33,12000)
> insert into employee values(6,'srini',22,16000)
> insert into employee values(7,'sashi',33,54000)
> insert into employee values(8,'rajani',22,71000)
> insert into employee values(9,'praveen',11,6060)
> insert into employee values(10,'bhaskar',22,11120)
> insert into employee values(11,'baba',33,9000)
>
>
> create table employment (managerid int,managername varchar(20),sal int)
>
>
>
> insert into employment values(11,'rob',2500)
> insert into employment values(22,'babu',5000)
> insert into employment values(33,'ram',6000)
>
>
> now my problem is
>
>
> Update the salary of each manager to be double the average
> salary of the employees he/she manages with out using joins
>
> there are no constrainsts described on any column
>
> pls help
>
>
> sati
>

23 Mar 2006 11:37 AM

satish

Thank u very much

23 Mar 2006 11:48 PM

William Stacey [MVP]

I would hope that is just a challenge and not the way they want to
compensate managers. :-)

--
William Stacey [MVP]

26 Mar 2006 2:52 AM

Roger Wolter[MSFT]

On the other hand, this would motivate your manager to give you a raise
right? Sounds like a great idea to me.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Show quote

"William Stacey [MVP]" <william.sta***@gmail.com> wrote in message
news:uuj2KRtTGHA.4436@TK2MSFTNGP10.phx.gbl...
>I would hope that is just a challenge and not the way they want to
> compensate managers. :-)
>
> --
> William Stacey [MVP]
>
>

26 Mar 2006 2:59 AM

Kent Tegels

Hello Roger Wolter[MSFT],

> On the other hand, this would motivate your manager to give you a
> raise right? Sounds like a great idea to me.

You're in rare form tonight Roger... :)

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

26 Mar 2006 11:06 AM

Tom Moreau

Of course, if you have update privileges on the table, you could just give
yourself the raise. ;-)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"Roger Wolter[MSFT]" <rwol***@online.microsoft.com> wrote in message
news:OeVHoBIUGHA.4276@TK2MSFTNGP10.phx.gbl...