Description
Objective: Create complex queries using subqueries
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