calculate min and max functions

Question

I am a student this is my LAST homework assignment : )

/* **(4.9) Calculate the minimum salary for exempt employees **and the maximum salary for non-exempt employees. */

I can only use one select statement... or without running two separate queries

I have 2 tables... Job_title

(Job_title varchar (50) PRIMARY KEY,
EEO_1_Classification varchar(200),
Job_description varchar(250),
Exempt_Non_Exempt_Status bit );

and

Employee

(Emp_id int NOT NULL IDENTITY(1,1)PRIMARY KEY, 
Last_name varchar(25), 
First_name varchar(25),
Address varchar(40),    
City varchar (15),
State char(2),
Telephone_area_code varchar(3), 
Telephone_number varchar(8),    
Job_title varchar(50) foreign key references job_title(job_title),  
Hire_date smalldatetime,    
Wage money,
Gender char(1),
Race varchar(25),
Age int );

They are linked by the job_title primary key/foreign key.

Any advice???

Answer 1

Since it's homework a few hints. You need to join the two tables by job_title, to find the min/max values you should use the aggregate functions min(Wage) max(Wage) and include Exempt_Non_Exempt_Status in the where clause.

Answer 2

You could do this in one query using some clever tricks, but it's not worth it. It's far simpler, easier to code, and easier to maintain if you do it in two queries.

• One query to get the min salary for exempt employees.
• A second separate query to get the max salary for non-exempt employees.

These two queries should be much easier for you to solve individually.

Answer 3

Calculate the minimum salary for exempt employees and the maximum salary for non-exempt employees:

select  min(case when j.Exempt_Non_Exempt_Status = 1 then e.Wage end)
,       max(case when j.Exempt_Non_Exempt_Status = 0 then e.Wage end)
from    Employee e
join    Job_title j
on      j.Job_title = e.Job_title