Description
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.
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 I sorted by department name and employee name.
DBS301 – Database Design II and SQL using Oracle Lab 5 – Week 5
2 | P a g e
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 for employees in department 20,50, and 60. 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.
DBS301 – Database Design II and SQL using Oracle Lab 5 – Week 5
3 | P a g e
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 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 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.
DBS301 – Database Design II and SQL using Oracle Lab 5 – Week 5
4 | P a g e
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).
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