In this lab, we will continue to explore SQL queries using HR schema. You can complete the lab using either SQL Development or SQL*Plus.

For this lab exercise, you will be using HR.EMPLOYEES, HR.DEPARTMENTS, HR.JOBS, and HR.LOCATIONS tables.

Lab Exercises: Submit the a file contains all your SQL statements of this lab

1. Create a query to display the last name, department number and department name for all employees.
2. Create a unique listing of all jobs that in department 80. Include the location of the department in the output.
3. Create a query to display the employees last name, department name, location ID and city of all employees who earn a commission.
4. For each employee who has an a (lowercase) in his/her last name, display the employees last name and department name.
5. Write a query that displays the last name, job, department number and department name for all employees who work in Toronto.
6. Create a query to display the employees last name and employee number along with his/her managers last name and manager number. Label the columns Employee, Emp#, Manager and Mgr#, respectively.
7. Modify Question 6 to display all employees including King, who has no manager. Order the results by the employee number.
8. Create a query to display the name and hire date of any employee hired after Davies.
9. Display the names and hire dates for all employees who were hired before their managers, along with their managers names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively.
10. Create a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the HR.EMPLOYEES table. Change the heading for the employee name to EMPLOYEE. After the view created, please write queries to answer following questions:
    1. Display the contents of the EMPLOYEES_VU view
    2. Select the view name and text from the USER_VIEWS data dictionary view.
    3. Using your EMPLOYEES_VU view, enter a query to display all employee names and department numbers.
11. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50. Label the view columns EMPNO, EMPLOYEE and DEPTNO. Do not allowan employee to be re-assigned to another department through the view. After the view created, please write queries to answer following questions:
    1. Display the structure and contents of DEPT50 view.
    2. Select the view name and text from the USER_VIEWS data dictionary view.
    3. Attempt to re-assign Matos to department 80 by doing an update command.