select dept names who have more than 2 employees whose salary is greater than 1000

How would do the following in SQL

"select dept names who have more than 2 employees whose salary is greater than 1000" ?

DeptId DeptName
------ --------
1     开发者_运维问答     one
2          two
3        three

EmpId DeptId Salary
----- ------ ------
121      1    2000
122      1    2000
123      1    5000
124      1    4000
131      2    2000
132      2    6000
133      2    1000
134      2    1000
125      3    1000
126      3   20000


RESULT: one

How about something like this?

SELECT D.DeptName FROM
Department D WHERE (SELECT COUNT(*) 
                    FROM Employee E 
                    WHERE E.DeptID = D.DeptID AND
                            E.Salary > 1000) > 2

SELECT DEPTNAME
FROM(SELECT D.DEPTNAME,COUNT(EMPID) AS TOTEMP
     FROM DEPT AS D,EMPLOYEE AS E
     WHERE D.DEPTID=E.DEPTID AND SALARY>1000
     GROUP BY D.DEPTID
     )
WHERE TOTEMP>2;

select min(DEPARTMENT.DeptName) as deptname 
from DEPARTMENT
inner join employee on
DEPARTMENT.DeptId = employee.DeptId
where Salary > 1000
group by (EmpId) having count(EmpId) > =2 

hope this helps

select DeptName from DEPARTMENT inner join EMPLOYEE using (DeptId) where Salary>1000 group by DeptName having count(*)>2

select D.DeptName from [Department] D where D.DeptID in 
( 
    select E.DeptId from [Employee] E
    where E.Salary > 1000
    group by E.DeptId
    having count(*) > 2
)

select deptname from dept_1
where exists
(
SELECT DeptId,COUNT(*)        
FROM emp_1  
where salary>1000
and emp_1.deptid=dept_1.deptid   
GROUP BY DeptId 
having count(*)>2)

1:list name of all employee who earn more than RS.100000 in a year.

2:give the name of employee who earn heads the department where employee with employee I.D

My main advice would be to steer clear of the HAVING clause (see below):

WITH HighEarners AS
     ( SELECT EmpId, DeptId
         FROM EMPLOYEE
        WHERE Salary > 1000 ), 
     DeptmentHighEarnerTallies AS 
     ( SELECT DeptId, COUNT(*) AS HighEarnerTally
         FROM HighEarners
        GROUP 
           BY DeptId )
SELECT DeptName
  FROM DEPARTMENT NATURAL JOIN DeptmentHighEarnerTallies
 WHERE HighEarnerTally > 2;

The very early SQL implementations lacked derived tables and HAVING was a workaround for one of its most obvious drawbacks (how to select on the result of a set function from the SELECT clause). Once derived tables had become a thing, the need for HAVING went away. Sadly, HAVING itself didn't go away (and never will) because nothing is ever removed from standard SQL. There is no need to learn HAVING and I encourage fledgling coders to avoid using this historical hangover.