DBS 301 - Lab 5 - Solved

Joins 

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 L05_ID#_LASTNAME.sql

Tasks
Part-A – Simple Joins
(FROM <table1, table2>)

1.      Display the department name, city, street address and postal code for departments sorted by city and department name.



2.      Display full name of employees as a single field using format of “Last, First”, their hire date, salary, department name and city, but only for departments with names starting with an A or S sorted by department name and employee name. 



3.      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 and province 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 last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr# respectively.

Part-B – Non-Simple Joins
Using the JOIN statement

5.      Display the department name, city, street address, postal code and country name for all Departments. Use the JOIN and USING form of syntax.  Sort the output by department name descending.



6.      Display full name of the employees, their hire date and salary together with their department name, but only for departments which names start with A or S.

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 Jersey and 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 each department. 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.



9.      Display the employee last name and employee number along with their manager’s last name 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 who do NOT supervise anyone (or you could say managers without employees to supervise).