Write sql query for retrieving employee names from employee table who are having salary greater than 5000 without using where clause?

Editorial / Best Answer

Answered by: Parished.D Chennai India

  • Apr 24th, 2007


create table ee (eno int, ename varchar(200), sal int)

insert into ee values(1, 'a', 2000)
insert into ee values(2, 'b', 6000)
insert into ee values(3, 'c', 8000)

select ENO, ENAME, min(sal) AS SAL from ee group by eno,ename having min(sal) > 5000

Showing Answers 1 - 21 of 21 Answers

Swetha Reddy

  • Jul 18th, 2006
 

Try this:

select * from emp group by eno having sal > 5000

I have used eno incase ename is the same for one or more employees.

Pankaj Rastogi

  • Jul 23rd, 2006
 

I think

Select Ename from EMP where Sal > 5000

Is the correct query. Because we don't have to access the number of the records. The above query will show all the employees' name who have salary more then 5000.

No need to make group by and having caluse.

  Was this answer useful?  Yes

Ashok

  • Jul 27th, 2006
 

Hi Pankaj

Just read the Question once.

G.Ashok

  Was this answer useful?  Yes

umaGold

  • Aug 1st, 2006
 

select ename from employee where salary > 5000

  Was this answer useful?  Yes

Raj Sekhar

  • Aug 17th, 2006
 

In Sql Server try this

select max(empname) "EName",empid,salary from emp_temp group by empid,salary having salary > 5000

  Was this answer useful?  Yes

Saravanan

  • Sep 14th, 2006
 

select emp_name,salary from employee group by emp_name,salary having salary>5000

Code
  1. SELECT ENAME,SALARY FROM EMPLOYEES GROUP BY ENAME,SALARY HAVING SALARY>5000;

Hi, If you don't want to use where clause to get the employee names who are getting salary higher than 5000, you can use having clause.

  Was this answer useful?  Yes

Vinay Singh

  • Dec 29th, 2006
 

SELECT ENAME,SAL FROM Emp
having sal>2000
group by empno,ename,SAL;

  Was this answer useful?  Yes

Parished.D Chennai India

  • Apr 24th, 2007
 

create table ee (eno int, ename varchar(200), sal int)

insert into ee values(1, 'a', 2000)
insert into ee values(2, 'b', 6000)
insert into ee values(3, 'c', 8000)

select ENO, ENAME, min(sal) AS SAL from ee group by eno,ename having min(sal) > 5000

  Was this answer useful?  Yes

snktheone

  • Aug 3rd, 2007
 

Try using good old self joins.


SELECT e.empname , e.salary FROM employee e INNER JOIN employee m ON E.EMPID
= M.EMPID AND E.SALARY >5000


Cheers!
Snktheone


nirajkvinit

  • Dec 17th, 2008
 

I don't understand why you are using the group by clause. There is no need. The question is very simple. It is already hinting of using having clause.

  Was this answer useful?  Yes

r

  • Feb 21st, 2012
 

Using Group By

Code
  1. FROM Employees

  2. GROUP BY eid,ename,salary

  3. HAVING salary>15000

  Was this answer useful?  Yes

Pushpa

  • May 3rd, 2012
 

If we want to check any values to retrieve the data,case is the best.Case statement is simple and easy to understand.

Code
  1. SELECT case when sal>1000 then ename end

  2. FROM emp

  Was this answer useful?  Yes

Venkat Rahul

  • Jun 5th, 2012
 

select ename from emp group by ename,sal having sal>5000;

  Was this answer useful?  Yes

SHRIRAM SHARMA

  • Sep 9th, 2015
 

SELECT ED.First_Name, ED.Salary,
CASE
WHEN ED.Salary > 5000 THEN GREATER THEN 5000
ELSE NOT GREATER THEN 5000
END AS SALARY
FROM dbo.EmpolyeeDeatils ED

  Was this answer useful?  Yes

Chaitali

  • Sep 29th, 2015
 

SELECT ENO, ENAME, SAL FROM EE GROUP BY ENO, ENAME, SAL HAVING SALARY > 5000

  Was this answer useful?  Yes

virendra kumar mishra

  • Jan 6th, 2016
 

Use join

Code
  1. SELECT ED.empname, ED.Salary,

  2. CASE

  3. WHEN ED.Salary > 5000 THEN 1

  4. END AS SAL

  5. FROM dbo.employee ED

  6. INNER JOIN employee e ON ed.empid =e.empid AND CASE

  7. WHEN ED.Salary > 5000 THEN 1

  8. END  IS NOT NULL

  Was this answer useful?  Yes

Shakeer

  • Nov 2nd, 2016
 

Just presenting one more way is
;with cte as(
Select case when sal>5000 Then Name Else Null end as Name from Employee
)
Select * From Cte where Name is not null

  Was this answer useful?  Yes

Elan

  • Jun 28th, 2017
 

Select Name,Sum(Salary) Salary From Employee Group By Name Having Sum(Salary) >=5000

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions