[Solved] DBS301 Lab 5 – (Joins)

This week’s lab continues using the SELECT command in addition to now incorporating multipletables in the FROM statement to gather information together.Getting StartedYour 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 L05_ID#_LASTNAME.sqlTasksPart-A – Simple Joins(FROM <table1, table2>)1. Display the department name, city, street address and postal code for departmentssorted by city and department name.2. Display full name of employees as a single field using format of “Last, First”, their hiredate, salary, department name and city, but only for departments with names startingwith an A or I sorted by department name and employee name.DBS301 – Database Design II and SQL using Oracle Lab 5 – Week 52 | P a g e3. Display the full name of the manager of each department in states/provinces of Ontario,New Jersey and Washington along with the department name, city, postal code andprovince name. Sort the output by city and then by department name.4. Display employee’s last name and employee number along with their manager’s lastname and manager number for employees in department 20,50, and 60. Label thecolumns Employee, Emp#, Manager, and Mgr# respectively.Part-B – Non-Simple JoinsUsing the JOIN statement5. Display the department name, city, street address, postal code and country name for allDepartments. Use the JOIN and USING form of syntax. Sort the output by departmentname descending.DBS301 – Database Design II and SQL using Oracle Lab 5 – Week 53 | P a g e6. Display full name of the employees, their hire date and salary together with theirdepartment name, but only for departments which names start with A or I.a. Full name should be formatted: First / Last.b. Use the JOIN and ON form of syntax.c. Sort the output by department name and then by last name.7. Display full name of the manager of each department in provinces Ontario, New Jerseyand Washington plus department name, city, postal code and province name.a. Full name should be formatted: Last, First.b. Use the JOIN and ON form of syntax.c. Sort the output by city and then by department name.8. Display the department name and Highest, Lowest and Average pay per eachdepartment. Name these results High, Low and Avg.a. Use JOIN and ON form of the syntax.b. Sort the output so that department with highest average salary are shown first.DBS301 – Database Design II and SQL using Oracle Lab 5 – Week 54 | P a g e9. Display the employee last name and employee number along with their manager’s lastname and manager number. Label the columns Employee,a. Emp#, Manager, and Mgr#, respectively.b. Include also employees who do NOT have a manager and also employees whodo NOT supervise anyone (or you could say managers without employees tosupervise).Example Submission— ***********************— Name: Your Name— ID: #########— Date: The current date— Purpose: Lab 3 DBS301— ***********************— Question 1 – write a brief note about what the question is asking— Q1 SOLUTION —SELECT * FROM TABLE;— Question 2 – blah blah blah