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

Question

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

User · Answer

select min DEPARTMENT DeptName  as deptname  from DEPARTMENT inner join employee on DEPARTMENT DeptId   employee DeptId where Salary  gt  1000 group by  EmpId  having count EmpId   gt   2

User · Answer

select D DeptName from  Department  D where D DeptID in         select E DeptId from  Employee  E     where E Salary  gt  1000     group by E DeptId     having count     gt  2

User · Answer

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  gt  1000   gt  2

User · Answer

SELECT DEPTNAME FROM SELECT D DEPTNAME COUNT EMPID  AS TOTEMP      FROM DEPT AS D EMPLOYEE AS E      WHERE D DEPTID E DEPTID AND SALARY gt 1000      GROUP BY D DEPTID        WHERE TOTEMP gt 2

User · Answer

hope this helps  select DeptName from DEPARTMENT inner join EMPLOYEE using  DeptId  where Salary gt 1000 group by DeptName having count    gt 2

User · Answer

My main advice would be to steer clear of the HAVING clause  see below    WITH HighEarners AS        SELECT EmpId  DeptId          FROM EMPLOYEE         WHERE Salary  gt  1000          DeptmentHighEarnerTallies AS         SELECT DeptId  COUNT    AS HighEarnerTally          FROM HighEarners         GROUP             BY DeptId   SELECT DeptName   FROM DEPARTMENT NATURAL JOIN DeptmentHighEarnerTallies  WHERE HighEarnerTally  gt  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

User · Answer

select deptname from dept 1 where exists   SELECT DeptId COUNT            FROM emp 1   where salary gt 1000 and emp 1 deptid dept 1 deptid    GROUP BY DeptId  having count    gt 2

User · Answer

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

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

Examples related to sql