SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help Asap
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spradhan01
Starting Member

USA
6 Posts

Posted - 10/18/2010 :  02:49:35  Show Profile  Reply with Quote
I am stuck in some queries and hope you profesionals can help me out.
Please suggest me in this

1)Retrieve name of employees who make at least $10000 more than the employee who is paid least in the company.

2)Retrieve names of all employees who work in the department that has the employee with the higest salary among all employees.

3)Retrieve name of all employees whose supervisor's supervisor has
'888665555'for SSN.

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 10/18/2010 :  02:56:46  Show Profile  Reply with Quote
LOL. This is the first time, i see someone just post a pic of the question from a book.


KH
Time is always against us

Go to Top of Page

spradhan01
Starting Member

USA
6 Posts

Posted - 10/18/2010 :  03:02:52  Show Profile  Reply with Quote
Its not a question though!
The database from which I need to extract the query.
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 10/18/2010 :  03:10:09  Show Profile  Reply with Quote
quote:
Originally posted by spradhan01

Its not a question though!
The database from which I need to extract the query.



Ok show us what have you got till far.

PBUH

Go to Top of Page

spradhan01
Starting Member

USA
6 Posts

Posted - 10/18/2010 :  12:28:57  Show Profile  Reply with Quote
I got no.1 and no.2
Still lost in no.3

No.1
Select Fname, Minit, Lname
From Employee
Where Dno=(select Dno from Employee where Salary=(SELECT MAX(Salary) from Employee));

No.2
Select Fname,Minit, Lname
From Employee
Where Salary>=( (SELECT Min(Salary) from Employee)+10000);

No.3????

Edited by - spradhan01 on 10/18/2010 12:29:30
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 10/19/2010 :  06:07:22  Show Profile  Reply with Quote
I could not think about anything better than this


declare @emp varchar(40)='a3'

declare @tbl table(Name varchar(20),SSN varchar(20),SupvSSN varchar(20),DepNo int)
insert into @tbl
select 'a','1',null,1 union 
select 'a1','11',1,1 union 
select 'a2','111',11,1 union 
select 'a3','1111',111,1 union 
select 'a4','11111',1111,1 union
select 'b1','2',null,2 union 
select 'b2','22',2,2 union 
select 'b3','222',22,2 

;with cte
as
(
select *,convert(varchar(20),null)s  from @tbl
union all
select t.*,t.SupvSSN from @tbl t inner join cte c on c.SSN =t.SupvSSN
)
,cte1
as
(
		select *,row_number()over(partition by DepNo order by s)rid from
		(
			select distinct * from cte where SupvSSN is  null or s is not null
		)T  
)

select c.Name,c.SupvSSN,c.DepNo from cte1 c where exists

		(
		    select c1.rid-2,DepNo as supid from cte1 c1
			  where c1.Name=@emp and c.DepNo=c1.DepNo and c.rid=c1.rid-2
		)

 order by Name



PBUH

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000