Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2012
    Posts
    5

    Unanswered: Department names(with employee name) with more than 2 employee and salary greater tha

    I am trying to generate a SQL query to find the Department names(with employee name) with more than 2 employee whose salary greater than 90% of respective department average salary. My SQL Code is working fine , it has no Syntax errors but the output is giving me additional data. The table is as follos

    JONES ACCOUNTING 3000
    STEEL ACCOUNTING 2500
    WILSON RESEARCH 3000
    WOLFE RESEARCH 2500
    LEE RESEARCH 2400
    LANCASTER SALES 2000
    JACKSON SALES 2500
    FISHER SALES 3000
    ADAMS IT 2000
    MILLER IT 1000
    SCOTT IT 2500
    SMITH IT 2900
    KING EXECUTIVE 5000
    JOST EXECUTIVE 4500
    CLARK EXECUTIVE 4000
    My code is as follows.

    Select department_name , employee_name
    from department d , employee e
    where e.department_id = d.department_id
    and (SELECT COUNT(*)
    FROM Employee E
    WHERE E.department_ID = D.department_ID) > 2
    and salary >
    0.9*(SELECT ROUND(AVG(salary),2)
    FROM employee e_inner
    WHERE e_inner.department_id = e.department_id);
    I notice that my code returns the value of department with more than 2 employees and salary > 90% of department's average salary. whereas I am looking for departments with more than 2 employees whose salary is more than 90% of department avg salary

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Gary12 View Post
    I notice that my code returns the value of department with more than 2 employees and salary > 90% of department's average salary. whereas I am looking for departments with more than 2 employees whose salary is more than 90% of department avg salary
    i had to read that three times before i concluded that your best bet is simply to repost the homework assignment verbatim
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12

  4. #4
    Join Date
    Aug 2012
    Posts
    5
    @r937: To put it in simpler words , all dept with more than 2 employees. but the salary of those department employees should be greater than 90% of the avg(salary) of entire dept.

    if a department has more than 2 employess , but their salaries isnt greater than 90% of the Avg(salary) of the department then they dont make the list.

    also if a department has only one employee whose salary is greater than 90% of the avg(sal) of department , then that department doesnt make the list. Should be 2 or above.
    Last edited by Gary12; 08-04-12 at 16:15.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Gary12 View Post
    @r937: To put it in simpler words ...
    didn't help me, even more confused than before

    verbatim problem, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2012
    Posts
    5
    "write a PL/SQL anonymous block to find all departments that have more than two employees whose salary is above 90% of the average salary of their respective departments"

  7. #7
    Join Date
    Aug 2012
    Posts
    5
    I am looking for a good approach not necessarily the exact answer

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Gary12 View Post
    I am looking for a good approach not necessarily the exact answer
    I for one cannot think of anything better than you already got on SO
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •