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.