Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
INFS5710
Homework
• 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