Description

Lab 7 – Week 8 (Set Operators)
This week’s lab continues using the SELECT command in addition to now incorporating multiple tables and various set operators to produce results.
Getting Started
Your submission will be a single text-based SQL file with appropriate header and commenting. Please ensure your file runs when the entire file is executed in SQL Developer.
Pop-Quiz
Do NOT submit the quiz, they are to prompt you to think about things. Answers are in the Oracle course notes.

1 | P a g e

DBS301 – Database Design II and SQL using Oracle Lab 7 – Week 8

Tasks
1. The HR department needs a list of Department IDs for departments that do not contain the job ID of ST_CLERK> Use a set operator to create this report.

2. Same department requests a list of countries that have no departments located in them. Display country ID and the country name. Use SET operators. The first result looks like:

3. The Vice President needs very quickly a list of departments 10, 50, 20 in that order. job and department ID are to be displayed.

4. Create a statement that lists the employeeIDs and JobIDs 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).
5. The HR department needs a SINGLE report with the following specifications:
a. Last name and department ID of all employees regardless of whether they belong to a department or not.
b. Department ID and department name of all departments regardless of whether they have employees in them or not.

Write a compound query to accomplish this.

2 | P a g e