This is a question from Oracle 12c SQL by Joan Casteel.Chapter 9, Hands-On Assig

Question

This is a question from Oracle 12c SQL by Joan Casteel.Chapter 9, Hands-On Assignment.

Note: Two SQL queries (Query 1: Using traditional approach. Query 2: Using the JOIN keyword) are needed for this question.

Question 6: A column named Mgr_ID has been added to the Prob_Officers table and contains the ID
number of the probation supervisor for each officer. Produce a list showing each probation
officer’s name and his or her supervisor’s name. Sort the list alphabetically by probation
officer name.

Explanation / Answer

1. Using traditional approach withot using JOIN operator

SELECT p1.Officer_name,p2.Officer_name as supervisor FROM Prob_Officers p1, Prob_Officers p2 where p1.Mgr_ID=p2.Officer_ID;

This query retrieves the the officer name and his manager name. In this we have self join with the same table by creating alliases as p1 and p2. here in this supervisor is also an employee like other but he is the manager to other employees. Like all employees he i also having ID.

Now we are comparing the MGR_ID with the OFFICER_ID so that it prints the name of that manager for the officer which matches his MGR_ID with the Officer_ID.

2. Using JOIN keyword.

SELECT e.ename,e1.ename as manager FROM emp e JOIN emp e1 on e.mgr=e1.empno;

This query retrieves the same as the above query. The only difference is in this we have used JOIN keyword. This is the self join of the same table. This exactly the same as the above query.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.