Description
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.
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
DBS301 – Database Design II and SQL using Oracle Lab 7 – Week 8
2 | P a g e
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.