[X] My Assistant

[quick2u]

I have to answer this question -

Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees in ONE query.

I wrote this statement -

CODE

select max(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt_status = 'non_exempt_status'

I want to know is this corrent. I am on another computer that does not have SQL on it and just want to know about this question.

[corliss]

CODE

select max(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt_status = 'non_exempt_status'

This code only returns the max salarys for employees who have non-exempt status.
I would suggest grouping by exempt_status.
Also...what table is exempt_status coming from is it employee.exempt_status or job_title.exempt_status?

Assuming that he exempt_status is coming from job_title table:

CODE

select max(employee.salary) as salary, job_title.exempt_status
from employee
join job_title on
employee.job_title_id = job_title.job_title_id
group by job_title.exempt_status

This post has been edited by corliss: 2 Jul, 2009 - 09:07 AM

[CamoDeveloper]

For that question yes, that statement is correct. You might need to add a LEFT, RIGHT, or INNER to the join, but I'm sure it will be LEFT for this statement.

CODE

SELECT
    MAX(salary)
FROM
    employee
    LEFT JOIN job_title ON employee.job_title_id = job_title.job_title_id
WHERE
    exempt_status = 'non_exempt_status'

If you want to calculate all of the salaries for the employess, use COUNT()

CODE

SELECT
    COUNT(salary)
FROM
    employee
    LEFT JOIN job_title ON employee.job_title_id = job_title.job_title_id
WHERE
    exempt_status = 'non_exempt_status'

~Camo