[Solved] DBS301 Lab 7

Lab on Subqueries and Set operators

Copy the query and screen shot of the result beneath each question in this document. Submit on Blackboard.

Solve the following:

1. Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary.

2. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a “u.”

3. The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is 1700.

4. Create a report for HR that displays the last name and salary of every employee who reports to King.

5. Create a report that displays the department number, last name, and job ID for every employee in the Executive department.

6. The HR department needs a list of department IDs for departments that do not contain the job ID ST_CLERK. Use the set operators to create this report.

7. Create a report that lists the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired by the company (that is, they changed jobs but have now gone back to doing their original job).