1.

 

SELECTemp_ssn,emp_last_name,emp_dpt_number,emp_salary  AS [Max Department Salary]

FROMemployeeAS [e1]

WHEREemp_salary=(

                SELECTMAX(emp_salary)

                FROMemployeeAS [e2]

                WHERE e2.emp_dpt_number = e1.emp_dpt_number)

ORDERBYemp_dpt_numberASC;

 

 

2.

 

SELECTemp_dpt_number,MAX(emp_salary)AS [Top Salary]

FROM employee 

GROUPBYemp_dpt_number

ORDERBYemp_dpt_numberASC;

 

While you can get the top salaries from each department, you cannot get it in a format which would allow you to include the social security number or employee last name due to those being UNIQUE. If you include the employee social security number and employee name in the above SQL, you will get the following:

 

SELECTemp_ssn,emp_last_name,emp_dpt_number,MAX(emp_salary)AS [Top Salary]

FROM employee 

GROUPBYemp_dpt_number

ORDERBYemp_dpt_numberASC;

 

Msg 8120, Level 16, State 1, Line 1

Column 'employee.emp_ssn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

Therefore, the use of Grouping and Aggregation while only get you so much information but not in the format requested.

 

3.

 

SELECTwork_emp_ssn,work_pro_number,work_hoursAS [Highest Work Hours]

FROMassignmentAS [a1]

WHEREwork_hours=(

                SELECTMAX(work_hours)

                FROMassignmentAS [a2]

                WHERE a2.work_pro_number = a1.work_pro_number)

ORDERBYwork_hoursDESC;

 

 

 

 

4.

 

SELECTwork_emp_ssn,CONCAT(emp_first_name,' ',emp_last_name)AS [Name],emp_dpt_number,work_pro_number,work_hoursAS [Highest Work Hours]  

FROM assignment AS [a1] JOIN employee AS [e] ON (e.emp_ssn= a1.work_emp_ssn)

WHEREwork_hours=(

                SELECTMAX(work_hours) 

                FROMassignmentAS [a2] 

                WHERE a2.work_pro_number = a1.work_pro_number)

ORDERBYwork_hoursDESC;

 

 

 

 

 

 

 

 

 

5.

 

SELECTemp_last_nameAS [Last Name],emp_first_nameAS [First Name]

FROM employee

WHEREemp_ssnIN

    (SELECTwork_emp_ssn 

     FROM assignment

     WHEREwork_hours>= 8 ANDwork_pro_numberIN 

        (SELECTpro_number

         FROM project

         WHEREpro_name='Payroll'));

 

 

6.

 

SELECTdpt_nameAS [Department Name],pro_numberAS [Project Number],pro_nameAS [Project Name],pro_locationAS [Project Location]

FROM department dINNERJOIN project p ON (d.dpt_no=pro_dept_number)

WHEREpro_location='Edwardsville'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

7.

 

SELECTemp_last_name,emp_first_name,work_pro_number,work_hours

FROM employee e LEFTOUTERJOIN assignment a ON (e.emp_ssn=a.work_emp_ssn)

ORDERBYemp_last_name

 

 

 

8.

 

SELECTemp_last_name,emp_first_name,work_pro_number,work_hours

FROM employee e LEFTOUTERJOIN assignment a ON (e.emp_ssn=a.work_emp_ssn)

WHEREwork_hoursISNULL

ORDERBYemp_last_name