Problem 1:
Create a query showing department_name and the count of employees in that department. Use a subquery (count(*) employees) as a column expression and label the column Count Emp. Order by department name.
Problem 2:
Create a query displaying the last_name concatenated with a comma and blank space and first_name, the hire date and the salary of any employee working in the same department as Sigal Tobias (employee_id of 117). Exclude that employee from the final result. Sort it by last name.
Problem 3:
Create a query displaying the employee_id, last_name and department_id of all employees who work in a department with any employee whose last name starts with Hi. Order the data by department_id, last_name.
Problem 4:
Display employee_id, last_name, department_id, salary, and hire_date where department_id and salary both match the department_id and salary of any employee who was hired before 1996. (Use a subquery with a compound where clause). Sort it by department_id and last_name.
Problem 5:
This problem is divided into 4 parts leading to a subquery factoring construct in part 4 based on the previous 3 parts.
Part 1:
Create a query showing the department manager (format: last_name, first_name, aliased dep_manager), department_id and department_name.
Part 2:
Create a query showing last_name (aliased emp), department_id and the employees manager (format: last_name, first_name, aliased as emp_mgr). Create an outer join as the self join to include the boss Steven King. Since Steven King does not have a manager, display Boss as the emp_mgr.
Part 3:
Create a query showing the average salary (aliased dep_avg_sal) and the count of employees (aliased dep_emp_count) by department_id.
Part 4:
Combine the queries from part 1 – 3 in the following subquery factoring construct:
WITH
q_dep_mgr (Part 1)
q_emp_mgr (Part 2)
q_dep_avg_sal_cnt (Part 3)
--Main Query:
Display emp (aliased Employee), department_name (aliased Department Name), dep_avg_sal (rounded to 2 decimal digits, aliased Average Salary), dep_emp_count aliased Dep. Employee Count, dep_manager (aliased Department Manager), and emp_manager (aliased Employee Manager) based on the 3 subqueries joined on department_id. Make sure to display all employees (107 records). Order the result by department name and employee.
Note: Please shorten the column lengths in the output by either using COLUMN col_name FORMAT a{n} or by using the
CAST function. Suggested lengths are:
• emp (20)
• department_name (20)
• dep_manager (25)
• emp_manager (25)
Below is a sample output, note that the values are not actual values.
Employee
Department Name
Average Salary
Dep. Employee Count
Department Manager
Employee Manager
Gietz
Finance
12540.33
4
Whalen, Jennifer
Higgins, Shelley
