Sub-Select
This lab continues using the SELECT command in addition to now incorporating multiple tables in the FROM statement to gather information together.
Getting Started
Create a new Worksheet in SQL Developer. Save the file as L06_ID#_LASTNAME.sql
Tasks
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
