SQL select / group

Question

I am a student this is homework. I'm getting tired and confused. Any advice will be appreciated.

I have two tables.

Employee has the following columns:

• Last_name
• First_name
• Address
• Phone
• Job_title(FK)
• Wage

Job_title has

• job_title(PK)
• EEO classification
• Job_description
• Exempt_Non_Exempt

I need to select the employees’ last names and group them by salary within job titles that are grouped into exempt and non-exempt.

I'm using sql server to check my work but it needs to be hand scripted.

Answer 1

Can you provide sample data? Because it's not clear to me what the data type for JOB_TITLE.exempt_non_exempt is, or what is to be accomplished by the specified grouping criteria - EMPLOYEE.last_name will be mostly unique (but it can't be guaranteed due to the Mr. Smith principle), so it sounds like there's a need for aggregate function use.

Based on what I've read, this looks to be what you're after:

  SELECT e.last_name, e.wage, jt.exempt_non_exempt
    FROM EMPLOYEE e
    JOIN JOB_TITLE jt ON jt.job_title = e.job_title
GROUP BY e.last_name, e.wage, jt.exempt_non_exempt

You join on the foreign/primary key to get valid data from both tables.

The GROUP BY clause is where you define grouping, but SQL standard is that if you specify columns in the SELECT clause without being wrapped in aggregate functions (IE: COUNT/MAX/MIN/etc), then those columns need to be specified in the GROUP BY.