ONE SQL STATEMENT for \"Retrieve the social security numbers of all direct and i

Question

ONE SQL STATEMENT for "Retrieve the social security numbers of all direct and indirect supervisees James E Borg has"

NAME                SSN       BDATE     SEX     SALARY SUPERSSN DNO           
------------------- --------- --------- --- ---------- --------- --------      
John B Smith        123456789 09-JAN-55 M        30000 333445555 5             
Franklin T Wong     333445555 08-DEC-45 M        40000 888665555 5             
Alicia J Zelaya     999887777 19-JUL-85 F        25000 987654321 4             
Jennifer S Wallace 987654321 20-JUN-31 F        43000 888665555 4              
Ramesh K Narayan    666884444 15-SEP-52 M        38000 333445555 5             
Joyce A English     453453453 31-JUL-62 F        25000 333445555 5             
Ahmad V Jabbar      987987987 29-MAR-59 M        25000 987654321 4             
James E Borg        888665555 10-NOV-27 M        55000           1             

ONE SQL STATEMENT for "Retrieve the social security numbers of all direct and indirect supervisees James E Borg has"

Explanation / Answer

Select ssn from employee where superssn in(select ssn from employee where name='James E Borg') union select e.ssn from employee e where e.superssn in(select e2.ssn from employee e2 where e2.superssn in(select e3.ssn from employee e3 where e3.name='James E Borg'));

This contains the sub queries the first line gives the result of the direct supervisees and the 2 and 3 lines will gives the indirect supervisees and the union operator between this will combine the both tables and gives as 1 output.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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