DBS301 – Database Design II and SQL using Oracle

$ 20.99
Category:

Description

Lab 5 – Week 5

Lab 5 – Week 5 (Joins)
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 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.

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.

a. Full name should be formatted: First / Last.
b. Use the JOIN and ON form of syntax.

1 | P a g e

DBS301 – Database Design II and SQL using Oracle Last updated: Spring 2017
Lab 5 – Week 5

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).

2 | P a g e

Reviews

There are no reviews yet.

Be the first to review “DBS301 – Database Design II and SQL using Oracle”

Your email address will not be published. Required fields are marked *