代写INFS5710、代写Java，C++编程

日期：2022-11-01 08:37

Homework #4

The total mark for this assignment is 100.

This homework assignment accounts for 3% of the total marks for this course.

This homework is due in your lecture class in Week 7 (either 17 April or 18 April as

appropriate).

This homework is about using subqueries. While there may be many ways for solving each

question, you must follow the requirement given for each question.

Please refer to the document “Homework Submission Requirements” to prepare your

submission.

The following four questions refer to four data sets regarding the company Orion Star:

employee_master, customer, orders, and products that were used in your Homework#3.

Question 1 (25%)

This question is about gender pay gap at Orion Star, which refers to the difference between

women’s and men’s earning.

(a) (5%) Write a query to list the average salaries for male employees and female employees. (Using

one query only and no subquery is needed). Your solution should look like the following table.

Male Female

$xxxxxx.xx $xxxxxx.xx

(b) (20%) Write a query to list the average salaries for male employees and female employees who

earn more than the average salary for the same gender. For this question, you must use subqueries

in a WHERE clause. Your solution should look like the following table.

Male Female

$xxxxxx.xx $xxxxxx.xx

Question 2 (25%)

This question is related to Q2 of Homework#3.

Supplier Eclipse Inc is a partner of Orion Star. To encourage employees to sell Eclipse products, Orion

Star is paying a commission to each employee who has sold Eclipse products. The commission is 5%

of the total profit that an employee has helped the company to make from selling Eclipse products.

However, the commission is designed to be given to each employee’s manager, who will then

forward the money to the employee with verbal compliment. Note that some employees may have

the same manager. In that case, you will have to sum up all commissions for the manager.

Write a query to list the managers of the employees who will receive commissions and the total

commissions (of their employees). Your result should look like the following table. Sort your data

based on Manager_ID in ascending order. Provide the whole table. You will need to use subqueries

for this question.

Manager_ID Commission

$xxxx.xx

$xxxx.xx

$xxxx.xx

$xxxx.xx

Question 3 (25%)

Write a query to list the employee with the highest salary in each department. Your result should

look like the following table. Sort your data based on Department in ascending order. Display the

whole table. You must use subqueries in a FROM clause.

Department Employee_ ID Salary

$xxxxxx.xx

$xxxxxx.xx

$xxxxxx.xx

$xxxxxx.xx

Question 4 (25%)

Continued from Question 3, write a query to list the employee with the second highest salary in each

department. You must follow the instruction below to write your query. If you do it correctly, your

query can also be used to find the person with the n-th highest salary in each department with a

small change. You will receive no credit if you do not follow the instruction below.

Your query should involve a correlated subquery in a WHERE clause such that for each employee (ID)

in each department, you will list the salaries of the employees in the same department that are

higher than this employee’s salary in the subquery. In your WHERE clause, you should use COUNT to

count the number of such salaries obtained from the subquery. The person who has the second

highest salary has only one other salary higher than his/hers. Your result should look like the

following table. Sort your data based on Department in ascending order. Display the whole table.

Department Employee_ ID Salary

$xxxxxx.xx

$xxxxxx.xx

$xxxxxx.xx

$xxxxxx.xx

Comment on how your query can be modified to find the employee with the n-th highest salary in

each department. To simplify the question, assume that the salaries of all employees are distinct.

Hw 4 Solutions

Question 1 (25%)

(a) (5%)

proc sql;

select mean(e1.Salary) 'Male', mean(e2.Salary) 'Female'

from orion.Employee_master as e1, orion.Employee_master as e2

where e1.Employee_Gender='M' and e2.Employee_Gender='F';

quit;

(b) (20%)

proc sql;

select mean(e1.Salary) 'Male', mean(e3.Salary) 'Female'

from orion.Employee_master as e1, orion.Employee_master as e3

where e1.Employee_Gender='M' and e1.Salary >=

(Select mean(e2.Salary)

from orion.Employee_master as e2

where e2.Employee_Gender='M') and

e3.Employee_Gender='F' and e3.Salary >=

(Select mean(e4.Salary)

from orion.Employee_master as e4

where e4.Employee_Gender='F');

quit;

Question 2 (25%)

proc sql;

select e1.Manager_ID, sum(inline.pro) 'Total Commission'

format=dollar7.2

from orion.employee_master as e1,

(select e.Employee_ID, sum(o.Profit)*0.05 as pro

from orion.orders as o

inner join orion.products as p

on p.Product_ID = o.Product_ID

inner join orion.employee_master as e

on e.Employee_ID=o.Employee_ID

where p.Supplier_Name='Eclipse Inc'

group by e.Employee_ID) as inline

where e1.Employee_ID=inline.Employee_ID

group by e1.Manager_ID

order by e1.Manager_ID;

quit;

Question 3 (25%)

proc sql;

select e1.Department, e1.Employee_ID, e1.Salary

from (select e.Department as Dept, max(e.Salary) as maxsalary

from orion.employee_master as e

group by e.Department) as inline,

orion.Employee_master as e1

where e1.Department = inline.Dept and e1.Salary=inline.maxsalary

order by e1.Department;

quit;

Question 4 (25%)

proc sql;

select Department, Employee_ID, Salary

from orion.Employee_master as e

where 1 = (select count(distinct Salary)

from orion.Employee_master as e1

where e1.Salary > e.Salary and

e1.Department=e.Department)

order by Department;

quit;

To find the n-th highest salary in each department, one can simply change the 1 next to the WHERE

clause to n-1.