DBS301 Lab 6 – Week 6 (Sub-Select)




5/5 - (3 votes)

This week’s lab continues using the SELECT command in addition to now incorporating multiple
tables in the FROM statement to gather information together.
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.
Create a new Worksheet in SQL Developer. Save the file as L06_ID#_LASTNAME.sql
It is important that Steps 1, 2, and 3 are completed first.
1. SET AUTOCOMMIT ON (do this each time you log on) so any updates, deletes and inserts
are automatically committed before you exit from Oracle.
2. Create an INSERT statement to do this. Add yourself as an employee with a NULL salary,
0.21 commission_pct, in department 90, and Manager 100. You started TODAY.
3. Create an Update statement to: Change the salary of the employees with a last name of
Matos and Whalen to be 2500.
You must use subqueries for these questions (must minimize the number of tables being used
in the main query)
4. Display the last names of all employees who are in the same department as the
employee named Abel.
5. Display the last name of the lowest paid employee(s)
6. Display the city that the lowest paid employee(s) are located in.
7. Display the last name, department_id, and salary of the lowest paid employee(s) in each
department. Sort by Department_ID. (HINT: careful with department 60)
8. Display the last name of the lowest paid employee(s) in each city
9. Display last name and salary for all employees who earn less than the lowest salary in
ANY department. Sort the output by top salaries first and then by last name.
10. Display last name, job title and salary for all employees whose salary matches any of the
salaries from the IT Department. Do NOT use Join method. Sort the output by salary
ascending first and then by last_name